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

[Carl Gibbons]

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
>  
>