[rt-users] Very slow query in RT 3.4.1
Tim Suter
tsuter at cait.org
Tue Apr 5 15:33:14 EDT 2005
You all might want to check out this before you do any upgrading or
downgrading...speaks of memory and swap management....could be what's
ailing you.
http://www.ale.org/archive/ale/ale-2001-09/msg00165.html
On Tue, 2005-04-05 at 12:24 -0700, Marc Prud'hommeaux wrote:
> 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.
>
> _______________________________________________
> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
>
> RT Administrator and Developer training is coming to your town soon! (Boston, San Francisco, Austin, Sydney) Contact training at bestpractical.com for details.
>
> Be sure to check out the RT Wiki at http://wiki.bestpractical.com
More information about the rt-users
mailing list