[rt-users] Performance Issue - RT 3.xx

[Greg Kuhnert]

Hi list folks.
 
I have been working with some folks at my company to doing an upgrade to
RT 3.xx from our existing 2.xx system.
 
We ran a database export and import into the new system. Initially there
were some errors during the export and import process that related to
some custom scrips, and a few other bits 'n pieces, but this was a
"trial run" to see how the new system performed.
 
Our old system was running on a T1, and the new system is on a Netra 420
with heaps more memory .... I was really hoping to see a significant
performance increase after the upgrade. Unfortunately, I saw the
opposite.
 
The problems appear to be related to the number of users we have on our
system. RT 2.xx has been used for tracking inbound
abuse at comindico.com.au email messages. As a result, we have a VERY large
number of users in our RT 2.xx system.
 
When this was imported over to our new 3.xx system, we found some
queries being generated by RT were taking 11 seconds to execute. After
futher analysis, we found the following which might be a contributing
factor:
 
Groups: 77792 records
Principals: 77792 records
CachedGroupMembers: 167064 records
 
 
The initial results from some mysql debugging from one of our staff is
listed below.
 
My question to people on the list, is what can we do to reduce the size
of the above tables? I know it is related to the number of users. I
assume we need to find a way to reduce the number of users on our
system... Most users in our userfile do not even have a password - they
have just sent a message to abuse at comindico.com.au ..... I understand
that RT 3 can have tickets created by people who are not defined in the
userfile? Might this be a hint that will help find a fix this problem? 
 
Greg
 

	-----Original Message-----
	From: Jim Crumpler 
	Sent: Tuesday, July 15, 2003 12:26 PM
	To: Greg Kuhnert
	Subject: the slow query.
	
	
	Here's the query that's going slow..  (It contains someone's
email address, in case you want to post it).
	 
	To enable the query logging, start mysqld with -l.  There's
probably a long option name you can put into my.cnf to do the same
thing. It spews out alot of trash though.
	 
	I haven't really had a good look at the query, but to work it
out you'd have to check each index of the tables listed and see if the
key fields match..  The optimiser says its got a key for
CachedGroupMembers, but its obviously scanning the whole table
(rows=167064 is the whole table).. it may be possible to re-order the
query or to explicitly specify the JOIN type to force a smaller table to
be scanned first and use a key for the larger CachedGroupMembers table..
	 
	Jim..
	 
	 
	
  _____  

	mysql> describe SELECT DISTINCT main.* FROM Tickets main, Groups
Groups_1, Principals Principals_2, CachedGroupMembers
CachedGroupMembers_3, Principals Principals_4, Users Users_5  WHERE
((main.EffectiveId = main.id)) AND ((main.Type = 'ticket')) AND ( (  (
(Users_5.EmailAddress = 'anthony at comindico.com.au')AND(Groups_1.Domain
<mailto:'anthony at comindico.com.au')AND(Groups_1.Domain>  =
'RT::Ticket-Role')AND(Groups_1.Type =
'Requestor')AND(Principals_2.PrincipalType = 'Group') )  ) AND (
(main.Status = 'new')OR(main.Status = 'open') ) )  AND Groups_1.Instance
= main.id  AND Groups_1.id = Principals_2.ObjectId  AND Principals_2.id
= CachedGroupMembers_3.GroupId  AND CachedGroupMembers_3.MemberId =
Principals_4.id  AND Principals_4.ObjectId = Users_5.id  ORDER BY
main.Priority DESC LIMIT 10;
	
+----------------------+--------+---------------------------+---------+-
--------+-------------------------------+--------+----------------------
------------------------+
	| table                | type   | possible_keys             |
key     | key_len | ref                           | rows   | Extra
|
	
+----------------------+--------+---------------------------+---------+-
--------+-------------------------------+--------+----------------------
------------------------+
	| CachedGroupMembers_3 | index  | DisGrouMem,GrouMem        |
GrouMem |      10 | NULL                          | 167064 | Using
index; Using temporary; Using filesort |
	| Principals_2         | eq_ref | PRIMARY,Principals2       |
PRIMARY |       4 | CachedGroupMembers_3.GroupId  |      1 | where used
|
	| Groups_1             | eq_ref | PRIMARY,Groups1,Groups2   |
PRIMARY |       4 | Principals_2.ObjectId         |      1 | where used
|
	| main                 | eq_ref | PRIMARY,Tickets4,Tickets5 |
PRIMARY |       4 | Groups_1.Instance             |      1 | where used
|
	| Principals_4         | eq_ref | PRIMARY,Principals2       |
PRIMARY |       4 | CachedGroupMembers_3.MemberId |      1 | Distinct
|
	| Users_5              | eq_ref | PRIMARY,Users3,Users4     |
PRIMARY |       4 | Principals_4.ObjectId         |      1 | where used;
Distinct                         |
	
+----------------------+--------+---------------------------+---------+-
--------+-------------------------------+--------+----------------------
------------------------+
	6 rows in set (0.00 sec)
	 
	mysql> SELECT DISTINCT main.* FROM Tickets main, Groups
Groups_1, Principals Principals_2, CachedGroupMembers
CachedGroupMembers_3, Principals Principals_4, Users Users_5  WHERE
((main.EffectiveId = main.id)) AND ((main.Type = 'ticket')) AND ( (  (
(Users_5.EmailAddress = 'anthony at comindico.com.au')AND(Groups_1.Domain
<mailto:'anthony at comindico.com.au')AND(Groups_1.Domain>  =
'RT::Ticket-Role')AND(Groups_1.Type =
'Requestor')AND(Principals_2.PrincipalType = 'Group') )  ) AND (
(main.Status = 'new')OR(main.Status = 'open') ) )  AND Groups_1.Instance
= main.id  AND Groups_1.id = Principals_2.ObjectId  AND Principals_2.id
= CachedGroupMembers_3.GroupId  AND CachedGroupMembers_3.MemberId =
Principals_4.id  AND Principals_4.ObjectId = Users_5.id  ORDER BY
main.Priority DESC LIMIT 10;
	Empty set (11.98 sec)
	 
	mysql> 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20030715/90003e2f/attachment.htm>