[rt-users] Very slow query in RT 3.4.1

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


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,PrincipalI 
>> 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.

-------------- 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/8cac5f6a/attachment.bin>


More information about the rt-users mailing list