<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><TITLE>Message</TITLE>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.2800.1170" name=GENERATOR></HEAD>
<BODY>
<DIV><SPAN class=689421102-15072003><FONT face=Arial size=2>Hi list
folks.</FONT></SPAN></DIV>
<DIV><SPAN class=689421102-15072003><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=689421102-15072003><FONT face=Arial size=2>I have been working
with some folks at my company to doing an upgrade to RT 3.xx from our existing
2.xx system.</FONT></SPAN></DIV>
<DIV><SPAN class=689421102-15072003><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=689421102-15072003><FONT face=Arial size=2>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.</FONT></SPAN></DIV>
<DIV><SPAN class=689421102-15072003><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=689421102-15072003><FONT face=Arial size=2>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.</FONT></SPAN></DIV>
<DIV><SPAN class=689421102-15072003><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=689421102-15072003><FONT face=Arial size=2>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 <A
href="mailto:abuse@comindico.com.au">abuse@comindico.com.au</A> email messages.
As a result, we have a VERY large number of users in our RT 2.xx
system.</FONT></SPAN></DIV>
<DIV><SPAN class=689421102-15072003><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=689421102-15072003><FONT face=Arial size=2>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:</FONT></SPAN></DIV>
<DIV><SPAN class=689421102-15072003><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=689421102-15072003><FONT face=Arial size=2>Groups: 77792
records</FONT></SPAN></DIV>
<DIV><SPAN class=689421102-15072003><FONT face=Arial size=2>Principals: 77792
records</FONT></SPAN></DIV>
<DIV><SPAN class=689421102-15072003><FONT face=Arial size=2>CachedGroupMembers:
167064 records</FONT></SPAN></DIV>
<DIV><SPAN class=689421102-15072003><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=689421102-15072003><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=689421102-15072003><FONT face=Arial size=2>The initial results
from some mysql debugging from one of our staff is listed
below.</FONT></SPAN></DIV>
<DIV><SPAN class=689421102-15072003><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=689421102-15072003><FONT face=Arial size=2>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 <A
href="mailto:abuse@comindico.com.au">abuse@comindico.com.au</A> ..... 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?
</FONT></SPAN></DIV>
<DIV><SPAN class=689421102-15072003><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=689421102-15072003><FONT face=Arial
size=2>Greg</FONT></SPAN></DIV>
<DIV><SPAN class=689421102-15072003>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV></DIV>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left><FONT
face=Tahoma size=2>-----Original Message-----<BR><B>From:</B> Jim Crumpler
<BR><B>Sent:</B> Tuesday, July 15, 2003 12:26 PM<BR><B>To:</B> Greg
Kuhnert<BR><B>Subject:</B> the slow query.<BR><BR></FONT></DIV>
<DIV><FONT face=Tahoma size=2><SPAN class=976451202-15072003>Here's the query
that's going slow.. (It contains someone's email address, in
case you want to post it).</SPAN></FONT></DIV>
<DIV><FONT face=Tahoma size=2><SPAN
class=976451202-15072003></SPAN></FONT> </DIV>
<DIV><FONT face=Tahoma size=2><SPAN class=976451202-15072003>To enable the
query logging, start mysqld with -l. </SPAN></FONT><FONT face=Tahoma
size=2><SPAN class=976451202-15072003>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.</SPAN></FONT></DIV>
<DIV><FONT face=Tahoma size=2><SPAN
class=976451202-15072003></SPAN></FONT> </DIV>
<DIV><FONT face=courR12 size=2><SPAN class=976451202-15072003>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..</SPAN></FONT></DIV>
<DIV><FONT face=courR12 size=2><SPAN
class=976451202-15072003></SPAN></FONT> </DIV>
<DIV><FONT face=courR12 size=2><SPAN
class=976451202-15072003>Jim..</SPAN></FONT></DIV>
<DIV><FONT face=courR12 size=2><SPAN
class=976451202-15072003></SPAN></FONT> </DIV>
<DIV><FONT face=courR12 size=2><SPAN
class=976451202-15072003></SPAN></FONT> </DIV>
<DIV><FONT face=courR12 size=2>
<HR>
</FONT></DIV>
<DIV><FONT size=2><FONT face=courR12>mysql> <STRONG>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 = </STRONG></FONT></FONT><A
href="mailto:'anthony@comindico.com.au')AND(Groups_1.Domain"><FONT
face=courR12 color=#000000
size=2><STRONG>'anthony@comindico.com.au')AND(Groups_1.Domain</STRONG></FONT></A><FONT
size=2><FONT face=courR12><STRONG> = '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;<BR></STRONG>+----------------------+--------+---------------------------+---------+---------+-------------------------------+--------+----------------------------------------------+<BR>|
table
| type |
possible_keys
| key | key_len |
ref
| rows |
Extra
|<BR>+----------------------+--------+---------------------------+---------+---------+-------------------------------+--------+----------------------------------------------+<BR>|
CachedGroupMembers_3 | index |
DisGrouMem,GrouMem | GrouMem
| 10 |
NULL
| 167064 | Using index; Using temporary; Using filesort |<BR>|
Principals_2 | eq_ref |
PRIMARY,Principals2 | PRIMARY
| 4 | CachedGroupMembers_3.GroupId
| 1 | where
used
|<BR>|
Groups_1
| eq_ref | PRIMARY,Groups1,Groups2 | PRIMARY
| 4 |
Principals_2.ObjectId
| 1 | where
used
|<BR>|
main
| eq_ref | PRIMARY,Tickets4,Tickets5 | PRIMARY
| 4 |
Groups_1.Instance
| 1 | where
used
|<BR>| Principals_4 | eq_ref |
PRIMARY,Principals2 | PRIMARY
| 4 | CachedGroupMembers_3.MemberId
| 1 |
Distinct
|<BR>|
Users_5
| eq_ref | PRIMARY,Users3,Users4 | PRIMARY
| 4 |
Principals_4.ObjectId
| 1 | where used;
Distinct
|<BR>+----------------------+--------+---------------------------+---------+---------+-------------------------------+--------+----------------------------------------------+<BR>6
rows in set (0.00 sec)</FONT></FONT></DIV>
<DIV><FONT face=courR12 size=2></FONT> </DIV>
<DIV><FONT face=courR12 size=2>mysql> <STRONG>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 = </STRONG></FONT><A
href="mailto:'anthony@comindico.com.au')AND(Groups_1.Domain"><FONT
face=courR12
size=2><STRONG>'anthony@comindico.com.au')AND(Groups_1.Domain</STRONG></FONT></A><FONT
face=courR12 size=2><STRONG> = '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;<BR></STRONG>Empty set (<FONT color=#ff0000>11.98 sec</FONT>)</FONT></DIV>
<DIV><FONT face=courR12 size=2></FONT> </DIV>
<DIV><FONT face=courR12 size=2>mysql>
</FONT></DIV></BLOCKQUOTE></SPAN></DIV></BODY></HTML>