Very slow Bulk Update Page under 3.6.5

RT Users
1

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

2

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

The rt-users Archives

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.

3

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?

4

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

5

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