[rt-users] Very slow Bulk Update Page under 3.6.5

[Ham MI-ID, Torsten Brumm]

Hi Ruz,
sorry for the missing information. OK, Upgrade to 3.6.7 will not be possible, we are in the progress to upgrade to 3.8.1 at this moment (hopefully done Q1/09) but until then i have to fix the Bulk Problem.

OK, MySQL Version from old Server: MySQL 5.0.45, new Server 5.0.67

Old Server was AIX 5.3, new Server is running under Solaris 10. 

The Tables were not analyzed after move :-( (not sure how to do this)

Torsten


Kühne + Nagel (AG & Co.) KG, Geschäftsleitung: Hans-Georg Brinkmann (Vors.), Uwe Bielang (Stellv.), Bruno Mang, Dirk Blesius (Stellv.), Alfred Manke, Christian Marnetté (Stellv.),  Mark Reinhardt (Stellv.), Jens Wollesen, Rainer Wunn, Sitz: Bremen, Registergericht: Bremen, HRA 21928, USt-IdNr.: DE 812773878, Persönlich haftende Gesellschaft: Kühne & Nagel A.G., Sitz: Contern/Luxemburg Geschäftsführender Verwaltungsrat: Klaus-Michael Kühne


-----Ursprüngliche Nachricht-----
Von: ruslan.zakirov at gmail.com [mailto:ruslan.zakirov at gmail.com] Im Auftrag von Ruslan Zakirov
Gesendet: Montag, 10. November 2008 19:25
An: Ham MI-ID, Torsten Brumm
Cc: rt-users at lists.bestpractical.com
Betreff: Re: [rt-users] Very slow Bulk Update Page under 3.6.5

Upgrade to 3.6.7? And faster database server is running mysql version X where X is?
Have you analized tables after move?

On Mon, Nov 10, 2008 at 8:10 PM, Ham MI-ID, Torsten Brumm <torsten.brumm at kuehne-nagel.com> wrote:
> Hi RT Users,
> just spent a whole day to find a solution or hint. Since some weeks (after migration to a faster Database Server) the bulk update page is getting very slow (with slow i mean > 200sec). Everything else is fine and very fast.
>
> I just had a look onto the queries agains the DB and found for my bulk update the following:
> --
> Command: Query State: Copying to tmp table
>
> SELECT DISTINCT main.*
>  FROM Users main CROSS JOIN ACL ACL_4 JOIN Principals Principals_1 ON (Principals_1.id = main.id) JOIN CachedGroupMembers CachedGroupMembers_2 ON
>     (CachedGroupMembers_2.MemberId = Principals_1.id) JOIN Groups 
> Groups_3 ON (Groups_3.id = CachedGroupMembers_2.GroupId)  WHERE (Principals_1.Disabled = '0') AND (ACL_4.PrincipalType = Groups_3.Type) AND (Principals_1.id != '1') AND (Principals_1.PrincipalType = 'User') AND
>     (ACL_4.RightName = 'OwnTicket') AND ((ACL_4.ObjectType = 'RT::Queue') OR (ACL_4.ObjectType = 'RT::System')) AND ((Groups_3.Domain = 'RT::Queue-Role') OR
>     (Groups_3.Domain = 'RT::System-Role'))  ORDER BY main.Name ASC
>
> table       |type    |possible_keys       |key         | ken_len|ref     |  rows|
> 1           |SIMPLE  |Groups_3            |range       |PRIMARY,|Groups1 |    67|Using where; Using index; Using temporary; Using filesort
> 1           |SIMPLE  |CachedGroupMembers_2|ref         |DisGrouM|DisGrouM|     5|Using where; Using index
> 1           |SIMPLE  |main                |eq_ref      |PRIMARY,|PRIMARY |     4|
> 1           |SIMPLE  |Principals_1        |eq_ref      | PRIMARY|PRIMARY |     4|Using where; Distinct
> 1           |SIMPLE  |ACL_4               |range       |    ACL1|ACL1    |    54|Using where; Using index; Distinct
>
> --
>
> Any ideas where to start to fix this?
>
> Thanks
>
> Torsten
>
> Kühne + Nagel (AG & Co.) KG, Geschäftsleitung: Hans-Georg Brinkmann 
> (Vors.), Uwe Bielang (Stellv.), Bruno Mang, Dirk Blesius (Stellv.), 
> Alfred Manke, Christian Marnetté (Stellv.),  Mark Reinhardt (Stellv.), 
> Jens Wollesen, Rainer Wunn, Sitz: Bremen, Registergericht: Bremen, HRA 
> 21928, USt-IdNr.: DE 812773878, Persönlich haftende Gesellschaft: 
> Kühne & Nagel A.G., Sitz: Contern/Luxemburg Geschäftsführender 
> Verwaltungsrat: Klaus-Michael Kühne
>
>
>
> _______________________________________________
> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
>
> Community help: http://wiki.bestpractical.com Commercial support: 
> sales at bestpractical.com
>
>
> Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
> Buy a copy at http://rtbook.bestpractical.com
>



--
Best regards, Ruslan.