[rt-users] Poor performance in upgrade from RT2 to RT3

[wesley.sheldahl at ihigh.com]

First, run that query inside of psql, then run the same query with EXPLAIN
ANALYZE added to the front; it should look like:
EXPLAIN ANALYZE SELECT DISTINCT main.* FROM Tickets main ....

with the rest the same. The output should suggest which parts of the query
are taking longest, which indexes are being used (if any), and so on.
Indexing fields that are used in WHERE and ORDER BY clauses generally makes
SELECT statements faster. 

If you need more detailed query analysis after doing that, I would post the
query and the output from EXPLAIN ANALYZE to the postgresql users mailing
list. They'll be able to make more specific suggestions based on that
information.

-- 
Wes Sheldahl
iHigh NetOps 

-----Original Message-----
From: Carl Gibbons [mailto:cgibbons at du.edu]
Sent: Wednesday, September 17, 2003 12:57 PM
To: rt-users at lists.fsck.com
Cc: bshafer at du.edu
Subject: Re: [rt-users] Poor performance in upgrade from RT2 to RT3


Thanks for the replies. I have since upgraded to RT 3.0.5, and I have 
tuned a few more things in postgresql.conf.  Still very slow.  I set the 
following in postgresql.conf:

log_statement = true
log_duration = true
log_timestamp = true

to reveal the inefficient RT3 query, copied below. I suspect that some 
"create index..." hacking might improve this horrible query, but I'm not 
a DBA and I don't know how to analyze this one. Would somebody on this 
list please help me figure out what to do about this?

- Carl

2003-09-17 16:19:16 LOG:  query: SELECT DISTINCT main.* FROM Tickets 
main , Groups Groups_1, Principals Principals_2, CachedGroupMembers 
CachedGroupMembers_3, Users Users_4  WHERE ((main.EffectiveId = 
main.id)) AND ((main.Type = 'ticket')) AND ( (  ( 
(lower(Users_4.EmailAddress) = 'cgibbons at du.edu')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 = Users_4.id  ORDER BY main.Priority DESC 
LIMIT 10
2003-09-17 16:20:27 LOG:  duration: 71.308111 sec

About a couple of weeks ago, Carl Gibbons wrote:

>I am trying to migrate an 8,000+ ticket system.
>
>Existing system:
>  Red Hat 7.1
>  perl 5.6.0 installed from RPMs
>  RT 2.0.13
>  Apache 1.3.23/mod_perl 1.23
>  PostgreSQL 7.1.3, installed from RPMs
>New system:
>  Red Hat ES 2.1
>  perl 5.8.0 (compiled from source)
>  RT  3.0.4
>  Apache 1.3.28/mod_perl 1.28
>  PostgreSQL 7.3.4, compiled from source
>
>Both systems are dual-processor 700MHz PIII with 1024MB RAM.
>(I'm not sure, but I think both have the same SCSI disk and
>RAID configuration, too.) The rt2-to-rt3-v1.20 tool successfully
>moved users and tickets from the existing system to the new one.
>
>Logging into the RT2 system and clicking on "Home" to display the
>"25 highest priority tickets I own" takes a fraction of a second.
>
>Logging into the RT3 system and clicking on "Home" to display the
>"10 highest priority tickets I own" takes about 50 seconds.
>
>Running "top" on the new RT3 system while I click on "Home" shows
>that a PostgreSQL "postmaster" process pegs one of the CPUs at 99%
>throughout those 50 seconds.
>
>(I tried increasing the shared_buffers parameter in postgresql.conf,
>but it did not help.) Any ideas on how to make RT3 give me the
>fraction-of-a-second response to which I am accustomed?
>
>-- Carl Gibbons, Network Security Engineer, University of Denver
>
>_______________________________________________
>rt-users mailing list
>rt-users at lists.fsck.com
>http://lists.fsck.com/mailman/listinfo/rt-users
>
>Have you read the FAQ? The RT FAQ Manager lives at http://fsck.com/rtfm
>  
>


_______________________________________________
rt-users mailing list
rt-users at lists.fsck.com
http://lists.fsck.com/mailman/listinfo/rt-users

Have you read the FAQ? The RT FAQ Manager lives at http://fsck.com/rtfm
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20030917/27046578/attachment.htm>