[rt-users] Very slow query in RT 3.4.1
Ramon Kagan
rkagan at yorku.ca
Tue Apr 5 15:11:29 EDT 2005
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,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.
>
>
More information about the rt-users
mailing list