[rt-users] Very slow query in RT 3.4.1

Marc Prud'hommeaux marc at solarmetric.com
Tue Apr 5 15:24:48 EDT 2005


Ramon & all-

Is there reason to believe that MySQL 4.1 would do any better than 4.0  
with the query? Have other people solved this problem by upgrading? I'm  
a bit frightened by the menacing 4.1 release notes that discuss how  
difficult it is to roll back to 4.0, so I'd rather not attempt it  
unless people are confident that 4.1 will solve this problem.


On Apr 5, 2005, at 12:11 PM, Ramon Kagan wrote:

> any reason you don't want to upgrade to mysql 4.1.  I've been running  
> 4.1
> for about 6 months now and have been very happy with the results.
>
> Seems to me that if you're upgrading RT, upgrading mysql at the same  
> time
> seems logical (how often to you get to do an upgrade anyways?).
>
>
> Ramon Kagan
> York University, Computing and Network Services
> Information Security  -  Senior Information Security Analyst
> (416)736-2100 #20263
> rkagan at yorku.ca
>
> -----------------------------------    
> ------------------------------------
> I have not failed.  I have just	       I don't know the secret to  
> success,
> found 10,000 ways that don't work.     but the secret to failure is
> 				       trying to please everybody.
> 	- Thomas Edison				- Bill Cosby
> -----------------------------------    
> ------------------------------------
>
> On Tue, 5 Apr 2005, Marc Prud'hommeaux wrote:
>
>>
>> That's what I was afraid of. Short up switching from MySQL to
>> PostgreSQL or upgrading to MySQL 4.1, can anyone recommend anything at
>> all that might help with the search? Would trying to trim the size of
>> the Principals or Groups tables help (they have 200,000+ rows in each,
>> due primarily to the large amount of spam we receive via support)?
>>
>> Has anyone had any luck with making any indexes that would help with
>> that query, or is it just doomed to always be slow for MySQL? Sadly,
>> MySQL's query cache doesn't help with it either, since the query
>> contains a ticket-specific key in the WHERE clause.
>>
>> We're desperate to solve this. We'll need to downgrade to 3.0.0 if we
>> can't fix the slow page loading time.
>>
>>
>>
>> On Apr 5, 2005, at 10:16 AM, Anya Figlin wrote:
>>
>>>  we've experienced a similar problem when running large queries on
>>> postgresql 7.3.  Once we upgraded postgres version to 7.4, the time  
>>> to
>>> run
>>> the same large query went down from 50 seconds to 5 seconds!
>>> As I was looking through RT-Users digest a couple of days ago, I
>>> noticed
>>> that one of the developers replied to a slow query question by saying
>>> that
>>> there was no optimizer in mysql (as far as I recall).  If that'd be  
>>> the
>>> case, queries like this one would take forever ...
>>>
>>> -anya
>>>
>>> On Apr 4, 2005, at 10:57 AM, Marc Prud'hommeaux wrote:
>>>
>>>> RT Users-
>>>>
>>>> We're using RT 3.4.1 with MySQL 4.0.21 with perl 5.8.6 on Linux
>>>> (kernel 2.2.20).
>>>>
>>>> We recently upgraded from RT 3.0.0 to 3.4.1 in the hopes that it
>>>> would speed up our ever-slowing-down system. A number aspects of the
>>>> system did indeed seem to speed up, but replying to a ticket  
>>>> (hitting
>>>> the "Reply" link from Display.html) has become unbearably slow.
>>>> Looking in my "mysql-slow.log" log, I see that the main offending
>>>> query is as follows:
>>>>
>>>> SELECT DISTINCT main.* FROM Users main , Principals Principals_1,  
>>>> ACL
>>>> ACL_2, Groups Groups_3, CachedGroupMembers CachedGroupMembers_4
>>>> WHERE ((ACL_2.RightName = 'OwnTicket')) AND
>>>> ((CachedGroupMembers_4.MemberId = Principals_1.id)) AND  
>>>> ((Groups_3.id
>>>> = CachedGroupMembers_4.GroupId)) AND ((Principals_1.Disabled =
>>>> '0')or(Principals_1.Disabled = '0')) AND ((Principals_1.id != '1'))
>>>> AND ((main.id = Principals_1.id)) AND  ( (    ACL_2.PrincipalId =
>>>> Groups_3.id AND ACL_2.PrincipalType = 'Group' AND (    
>>>> Groups_3.Domain
>>>> = 'SystemInternal' OR Groups_3.Domain = 'UserDefined' OR
>>>> Groups_3.Domain = 'ACLEquivalence')) OR ( ( (Groups_3.Domain =
>>>> 'RT::Queue-Role' AND Groups_3.Instance = 5)  OR ( Groups_3.Domain =
>>>> 'RT::Ticket-Role' AND Groups_3.Instance = 43004)  )  AND
>>>> Groups_3.Type = ACL_2.PrincipalType) )  AND (ACL_2.ObjectType =
>>>> 'RT::System' OR  (ACL_2.ObjectType = 'RT::Queue' AND ACL_2.ObjectId  
>>>> =
>>>> 5) ) ORDER BY main.Name ASC;
>>>>
>>>> If I manually run the query, it takes around 50 seconds to complete.
>>>> If I run an EXPLAIN on it, I see:
>>>>
>>>> +----------------------+--------
>>>> +------------------------------------------------------------------- 
>>>> --
>>>> --------------------------------+----------+---------
>>>> +------------------------------+-------
>>>> +-----------------------------------------------------------+
>>>> | table                | type   | possible_keys
>>>>                                                                 |  
>>>> key
>>>>      | key_len | ref                          | rows  | Extra
>>>>                                             |
>>>> +----------------------+--------
>>>> +------------------------------------------------------------------- 
>>>> --
>>>> --------------------------------+----------+---------
>>>> +------------------------------+-------
>>>> +-----------------------------------------------------------+
>>>> | ACL_2                | range  |
>>>> ACL1,RightName,PrincipalType,ObjectId,ObjectId_2,ObjectType,Principa 
>>>> lI
>>>> d,PrincipalType_2,RightName_2 | ACL1     |      54 | NULL
>>>>             |     3 | Using where; Using index; Using temporary;
>>>> Using filesort |
>>>> | main                 | ALL    | PRIMARY,Users3,Users_id_idx_mwp
>>>>                                                                 |
>>>> NULL     |    NULL | NULL                         | 35305 |
>>>>                                                 |
>>>> | Principals_1         | eq_ref | PRIMARY,Disabled,id
>>>>                                                                 |
>>>> PRIMARY  |       4 | main.id                      |     1 | Using
>>>> where; Distinct                                     |
>>>> | CachedGroupMembers_4 | ref    | DisGrouMem,GrouMem,MemberId
>>>>                                                                 |
>>>> MemberId |       5 | Principals_1.id              |     1 | Using
>>>> where; Distinct                                     |
>>>> | Groups_3             | eq_ref |
>>>> PRIMARY,Groups1,Groups2,Type,Domain,id,Instance,Type_2
>>>>                               | PRIMARY  |       4 |
>>>> CachedGroupMembers_4.GroupId |     1 | Using where; Distinct
>>>>                            |
>>>> +----------------------+--------
>>>> +------------------------------------------------------------------- 
>>>> --
>>>> --------------------------------+----------+---------
>>>> +------------------------------+-------
>>>> +-----------------------------------------------------------+
>>>>
>>>>
>>>> Does anyone know of any quick fix for this? Creating a new index or
>>>> something? I've seen a number of people report the problem on the
>>>> list in the past, but I couldn't find any satisfactory solution.
>>>>
>>>>
>>>> A second question: is the file rt-3.4.1/etc/constraints.mysql meant
>>>> to be used? It looks like it might contain some useful foreign key
>>>> and index definitions, but it never appears to be automatically
>>>> called, and I can't find any reference to the file in any
>>>> documentation anywhere. Is it experimental, or can it be applied to
>>>> an existing RT schema?
>>>>
>>>> --
>>>> Marc Prud'hommeaux
>>>> SolarMetric Inc.
>>
>>
>> --
>> Marc Prud'hommeaux
>> SolarMetric Inc.
>>
>>
>>
--
Marc Prud'hommeaux
SolarMetric Inc.

-------------- next part --------------
A non-text attachment was scrubbed...
Name: smime.p7s
Type: application/pkcs7-signature
Size: 2108 bytes
Desc: not available
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20050405/70eea2e2/attachment.bin>


More information about the rt-users mailing list