Very slow Bulk Update Page under 3.6.5

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

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@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@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.

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 :frowning: (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ühneVon: ruslan.zakirov@gmail.com [mailto:ruslan.zakirov@gmail.com] Im Auftrag von Ruslan Zakirov
Gesendet: Montag, 10. November 2008 19:25
An: Ham MI-ID, Torsten Brumm
Cc: rt-users@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?

Hi RT Gurus,
is there any way to access the content of a CustomField from Ticket/Elements/Tabs ?

I try to create a new button called “Reinstate” which will set back the status to last know status (stored in a CF).

Something like this:

$actions->{‘AC’} = { path => “Ticket/Display.html?Status={CustomFieldValue(‘OldStat’)}&id=” . $id,
title => loc(‘Reinstate’) };

Not sure if this is possible.

Any ideas or solutions as workaround?

Greetings

Torsten

Kuehne + Nagel (AG & Co.) KG, Geschaeftsleitung: Hans-Georg Brinkmann (Vors.), Dirk Blesius (Stellv.), Reiner Heiken (Stellv.), Bruno Mang, Alfred Manke, Christian Marnetté (Stellv.), Mark Reinhardt (Stellv.), Jens Wollesen, Rainer Wunn, Sitz: Bremen, Registergericht: Bremen, HRA 21928, USt-IdNr.: DE 812773878, Persoenlich haftende Gesellschaft: Kuehne & Nagel A.G., Sitz: Contern/Luxemburg Geschaeftsfuehrender Verwaltungsrat: Klaus-Michael Kuehne

OK, sometime the vacuum in my head is preventing me from thinking.

Thanks to Scott Lander for Support.

Solution:
my $path = ‘Ticket/Display.html?Status=’;
my $CFStat=$Ticket->FirstCustomFieldValue(‘Saved_Status’);
$actions->{‘ZZZ-Clone8’} = {
title => loc(’[Reinstate]’),
path => $path . $CFStat . ‘&id=’ . $Ticket->id,
};

Torsten-----Ursprüngliche Nachricht-----
Von: rt-users-bounces@lists.bestpractical.com [mailto:rt-users-bounces@lists.bestpractical.com] Im Auftrag von Brumm,Torsten / Kuehne + Nagel / Ham MI-ID
Gesendet: Mittwoch, 16. September 2009 15:22
An: rt-users@lists.bestpractical.com
Betreff: [rt-users] How to access a CustomField from Ticket/Tabs

Hi RT Gurus,
is there any way to access the content of a CustomField from Ticket/Elements/Tabs ?

I try to create a new button called “Reinstate” which will set back the status to last know status (stored in a CF).

Something like this:

$actions->{‘AC’} = { path => “Ticket/Display.html?Status={CustomFieldValue(‘OldStat’)}&id=” . $id,
title => loc(‘Reinstate’) };

Not sure if this is possible.

Any ideas or solutions as workaround?

Greetings

Torsten

Kuehne + Nagel (AG & Co.) KG, Geschaeftsleitung: Hans-Georg Brinkmann (Vors.), Dirk Blesius (Stellv.), Reiner Heiken (Stellv.), Bruno Mang, Alfred Manke, Christian Marnetté (Stellv.), Mark Reinhardt (Stellv.), Jens Wollesen, Rainer Wunn, Sitz: Bremen, Registergericht: Bremen, HRA 21928, USt-IdNr.: DE 812773878, Persoenlich haftende Gesellschaft: Kuehne & Nagel A.G., Sitz: Contern/Luxemburg Geschaeftsfuehrender Verwaltungsrat: Klaus-Michael Kuehne

http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

Community help: http://wiki.bestpractical.com Commercial support: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com