[Rt-devel] 3.4.0rc1 slow

[David Kerry]

On Fri, Jan 07, 2005 at 05:59:08PM -0500, Jesse Vincent wrote:
> 
> >    I've looked at the postgres logs, and it seems to do many many iterations
> >    of this type of query:
> > 
> >    LOG:  duration: 1983.993 ms  statement:                                                                                         
> >               SELECT DISTINCT main.* FROM (Groups main LEFT JOIN Principals Principals_2 ON ( main.id = Principals_2.id)),
> >    ACL ACL_1 WHERE ((ACL_1.RightName = 'OwnTicket')) AND ((Principals_2.Disabled = '0')) AND ( ( ACL_1.PrincipalId = main.id AND
> >    ACL_1.PrincipalType = 'Group' AND ( main.Domain = 'SystemInternal' OR main.Domain = 'UserDefined' OR main.Domain =
> >    'ACLEquivalence')) OR ( ( (main.Domain = 'RT::Queue-Role' AND main.Instance = 102) ) AND main.Type = ACL_1.PrincipalType) ) AND
> >    (ACL_1.ObjectType = 'RT::System' OR (ACL_1.ObjectType = 'RT::Queue' AND ACL_1.ObjectId = 102) ) ORDER BY main.Name ASC
> > 
> >    At the time, there were about 50k+ users and 20k+ groups in the
> >    database.
> 
> I'd love it if you could dig a bit more into what's killing that query.
> Pull off the distinct and see if we're doing something stupid and
> generating a huge cartesian porduct?

Ok - my mistake - I hadn't done an analyze on the tables/indexes when I ran
that (was taking 30+ seconds without the analyze to bring up the query page).

After the analyze, that same query drops down to ~500ms.  Still fairly long
given the hardware I'm running on, however.

I've played with the query a bit, and removing the distinct doesn't really
change things much.  The result is 8 rows returned instead of 7.  Removing
the distinct and order by results in the same cost by the optimizer
(according to 'explain', anyways).

I also did some more investigation and there are other things amiss I 
think.

Simply bringing up the tickets->query builder page generates over 129 sql
queries ?!  19 of those queries took between 400 and 600ms apiece.  That's
an awful lot of data trolling to bring up one screen.  Is that right, or
is my setup misbehaving/borked somehow?  I have saved the entire sql trace
in case anyone is interested in looking at it in detail.

Everything else so far seems to be reasonable snappy except for this one
screen, which is unfortunate, because my users will be running into that
particular one continuously.

> > 2) The method used in the Postgres version of SearchBuilder/Handle/Pg.pm
> >    for insert() is suboptimal.  I've turned on postgres logging to dump
> >    any queries taking > .5 seconds and the query in this method comes up
> >    continually:
> 
> Interesting. At the time we did that (years ago now), that was the
> recommended way to do unique ids on postgres. I'd love to see a patch
> for improved behaviour.

I've got a patch worked up for this - basically I just copied the oracle
way (which should be many many times faster, but I still have to test it).
This should give improved insert performance (which has been an issue
for us in the past as well under particularly high incoming mail volume).

-- 
David Kerry