[rt-users] postgres slowdowns? (pg 7.3.4, rt 3.0.4)

[Justin Hawkins]

On Tue, Sep 09, 2003 at 02:05:51PM -0400, Jesse Vincent wrote:

> On Tue, Sep 09, 2003 at 12:55:58PM -0400, Kevin Murphy wrote:
> > Vivek,
> > 
> > Thanks.  Did I mention that a restart of postgres temporarily solves 
> > the problem?  (Actually, I also restarted Apache at the same time, so I 
> > can't really say which is relevant.)
> > 
> > >> are you by chance running a vacuum on your DB at the same time?
> > 
> > No.   However, ahem, I hadn't actually, er, vacuumed this database 
> > before.   I just did it, and I will make this a cron job.  Note, 
> > however, there was no perceptible improvement in performance after the 
> > 'vacuumdb --analyze rt3'.  Probably because my database is still so 
> > small that full table scans are still practical (and the machine has 
> > 1.5 GB RAM, some of which the OS might use for disk caching?  The 
> > machine is doing other things, but not very often, at the moment -- and 
> > not that I have seen during the slowdowns).
> 
> For kicks, you might want to try reverting to DBIx::SearchBuilder 0.90
> to see if the change which fixed the postgres search-looping problem
> introduced this performance issue.

Hi Jesse,

I too am having performance issues. I tried DBIx::SearchBuilder 0.90
without any benefit.

I turned on statement logging in postgres and narrowed down the
troublesome query. This query occurs each time the frontpage is hit:

SELECT DISTINCT main.*
FROM   Tickets main, 
       Groups Groups_1, 
       Principals Principals_2, 
       CachedGroupMembers CachedGroupMembers_3, 
       Principals Principals_4, 
       Users Users_5
WHERE  ((main.EffectiveId = main.id)) AND ((main.Type = 'ticket'))
  AND  (
        (
         (
          (lower(Users_5.EmailAddress) = '') AND 
          (Groups_1.Domain = 'RT::Ticket-Role') AND 
          (Groups_1.Type = 'Requestor') AND 
          (Principals_2.PrincipalType = 'Group') 
         ) 
        ) AND ( (main.Status = 'new') OR (main.Status = 'open') ) 
       )
  AND     main.id = Groups_1.Instance 
  AND     Groups_1.id = Principals_2.ObjectId 
  AND     Principals_2.id = CachedGroupMembers_3.GroupId 
  AND     CachedGroupMembers_3.MemberId = Principals_4.id  
  AND     Principals_4.ObjectId = Users_5.id
ORDER BY main.Priority DESC LIMIT 10;

Postgres doesn't actually seem to think this is a particularly
stressful query:

Limit  (cost=531.38..531.45 rows=1 width=212)

But it is - execution time is: 47218.74 ms !!!

OK - I've just figured out what it is.... it's sort of my problem,
sort of not my problem :-)

My RT system is peculiar in that it's a test system for development,
with live mail going into it (autoresponses are turned off).

I left it for a week or so without touching it. There are about 2500
tickets in there.

The problem here is that almost all of those are 'New'.

If I alter the above query to say (main.Status = 'open') instead of
'new' OR 'open', the query drops to about 125ms :-)

Now, I'm happy to admit that this is a weird case and wouldn't
normally happen. 2500 open tickets would be a cause for concern I'm
thinking :-)

Still, is it possible that this query can be optimized in some way? Or
is the postgresql query analyser at fault?

At any rate, I'll delete the bulk of my tickets and I expect
performance will be back up to scratch again.

	- Justin