<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META NAME="Generator" CONTENT="MS Exchange Server version 5.5.2653.12">
<TITLE>RE: [rt-users] Poor performance in upgrade from RT2 to RT3</TITLE>
</HEAD>
<BODY>
<P><FONT SIZE=2>First, run that query inside of psql, then run the same query with EXPLAIN ANALYZE added to the front; it should look like:</FONT></P>
<P><FONT SIZE=2>EXPLAIN ANALYZE SELECT DISTINCT main.* FROM Tickets main ....</FONT>
</P>
<P><FONT SIZE=2>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. </FONT></P>
<P><FONT SIZE=2>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.</FONT></P>
<P><FONT SIZE=2>-- </FONT>
<BR><FONT SIZE=2>Wes Sheldahl</FONT>
<BR><FONT SIZE=2>iHigh NetOps </FONT>
</P>
<P><FONT SIZE=2>-----Original Message-----</FONT>
<BR><FONT SIZE=2>From: Carl Gibbons [<A HREF="mailto:cgibbons@du.edu">mailto:cgibbons@du.edu</A>]</FONT>
<BR><FONT SIZE=2>Sent: Wednesday, September 17, 2003 12:57 PM</FONT>
<BR><FONT SIZE=2>To: rt-users@lists.fsck.com</FONT>
<BR><FONT SIZE=2>Cc: bshafer@du.edu</FONT>
<BR><FONT SIZE=2>Subject: Re: [rt-users] Poor performance in upgrade from RT2 to RT3</FONT>
</P>
<BR>
<P><FONT SIZE=2>Thanks for the replies. I have since upgraded to RT 3.0.5, and I have </FONT>
<BR><FONT SIZE=2>tuned a few more things in postgresql.conf. Still very slow. I set the </FONT>
<BR><FONT SIZE=2>following in postgresql.conf:</FONT>
</P>
<P><FONT SIZE=2>log_statement = true</FONT>
<BR><FONT SIZE=2>log_duration = true</FONT>
<BR><FONT SIZE=2>log_timestamp = true</FONT>
</P>
<P><FONT SIZE=2>to reveal the inefficient RT3 query, copied below. I suspect that some </FONT>
<BR><FONT SIZE=2>"create index..." hacking might improve this horrible query, but I'm not </FONT>
<BR><FONT SIZE=2>a DBA and I don't know how to analyze this one. Would somebody on this </FONT>
<BR><FONT SIZE=2>list please help me figure out what to do about this?</FONT>
</P>
<P><FONT SIZE=2>- Carl</FONT>
</P>
<P><FONT SIZE=2>2003-09-17 16:19:16 LOG: query: SELECT DISTINCT main.* FROM Tickets </FONT>
<BR><FONT SIZE=2>main , Groups Groups_1, Principals Principals_2, CachedGroupMembers </FONT>
<BR><FONT SIZE=2>CachedGroupMembers_3, Users Users_4 WHERE ((main.EffectiveId = </FONT>
<BR><FONT SIZE=2>main.id)) AND ((main.Type = 'ticket')) AND ( ( ( </FONT>
<BR><FONT SIZE=2>(lower(Users_4.EmailAddress) = 'cgibbons@du.edu')AND(Groups_1.Domain = </FONT>
<BR><FONT SIZE=2>'RT::Ticket-Role')AND(Groups_1.Type = </FONT>
<BR><FONT SIZE=2>'Requestor')AND(Principals_2.PrincipalType = 'Group') ) ) AND ( </FONT>
<BR><FONT SIZE=2>(main.Status = 'new')OR(main.Status = 'open') ) ) AND main.id = </FONT>
<BR><FONT SIZE=2>Groups_1.Instance AND Groups_1.id = Principals_2.ObjectId AND </FONT>
<BR><FONT SIZE=2>Principals_2.id = CachedGroupMembers_3.GroupId AND </FONT>
<BR><FONT SIZE=2>CachedGroupMembers_3.MemberId = Users_4.id ORDER BY main.Priority DESC </FONT>
<BR><FONT SIZE=2>LIMIT 10</FONT>
<BR><FONT SIZE=2>2003-09-17 16:20:27 LOG: duration: 71.308111 sec</FONT>
</P>
<P><FONT SIZE=2>About a couple of weeks ago, Carl Gibbons wrote:</FONT>
</P>
<P><FONT SIZE=2>>I am trying to migrate an 8,000+ ticket system.</FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>>Existing system:</FONT>
<BR><FONT SIZE=2>> Red Hat 7.1</FONT>
<BR><FONT SIZE=2>> perl 5.6.0 installed from RPMs</FONT>
<BR><FONT SIZE=2>> RT 2.0.13</FONT>
<BR><FONT SIZE=2>> Apache 1.3.23/mod_perl 1.23</FONT>
<BR><FONT SIZE=2>> PostgreSQL 7.1.3, installed from RPMs</FONT>
<BR><FONT SIZE=2>>New system:</FONT>
<BR><FONT SIZE=2>> Red Hat ES 2.1</FONT>
<BR><FONT SIZE=2>> perl 5.8.0 (compiled from source)</FONT>
<BR><FONT SIZE=2>> RT 3.0.4</FONT>
<BR><FONT SIZE=2>> Apache 1.3.28/mod_perl 1.28</FONT>
<BR><FONT SIZE=2>> PostgreSQL 7.3.4, compiled from source</FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>>Both systems are dual-processor 700MHz PIII with 1024MB RAM.</FONT>
<BR><FONT SIZE=2>>(I'm not sure, but I think both have the same SCSI disk and</FONT>
<BR><FONT SIZE=2>>RAID configuration, too.) The rt2-to-rt3-v1.20 tool successfully</FONT>
<BR><FONT SIZE=2>>moved users and tickets from the existing system to the new one.</FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>>Logging into the RT2 system and clicking on "Home" to display the</FONT>
<BR><FONT SIZE=2>>"25 highest priority tickets I own" takes a fraction of a second.</FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>>Logging into the RT3 system and clicking on "Home" to display the</FONT>
<BR><FONT SIZE=2>>"10 highest priority tickets I own" takes about 50 seconds.</FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>>Running "top" on the new RT3 system while I click on "Home" shows</FONT>
<BR><FONT SIZE=2>>that a PostgreSQL "postmaster" process pegs one of the CPUs at 99%</FONT>
<BR><FONT SIZE=2>>throughout those 50 seconds.</FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>>(I tried increasing the shared_buffers parameter in postgresql.conf,</FONT>
<BR><FONT SIZE=2>>but it did not help.) Any ideas on how to make RT3 give me the</FONT>
<BR><FONT SIZE=2>>fraction-of-a-second response to which I am accustomed?</FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>>-- Carl Gibbons, Network Security Engineer, University of Denver</FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>>_______________________________________________</FONT>
<BR><FONT SIZE=2>>rt-users mailing list</FONT>
<BR><FONT SIZE=2>>rt-users@lists.fsck.com</FONT>
<BR><FONT SIZE=2>><A HREF="http://lists.fsck.com/mailman/listinfo/rt-users" TARGET="_blank">http://lists.fsck.com/mailman/listinfo/rt-users</A></FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>>Have you read the FAQ? The RT FAQ Manager lives at <A HREF="http://fsck.com/rtfm" TARGET="_blank">http://fsck.com/rtfm</A></FONT>
<BR><FONT SIZE=2>> </FONT>
<BR><FONT SIZE=2>></FONT>
</P>
<BR>
<P><FONT SIZE=2>_______________________________________________</FONT>
<BR><FONT SIZE=2>rt-users mailing list</FONT>
<BR><FONT SIZE=2>rt-users@lists.fsck.com</FONT>
<BR><FONT SIZE=2><A HREF="http://lists.fsck.com/mailman/listinfo/rt-users" TARGET="_blank">http://lists.fsck.com/mailman/listinfo/rt-users</A></FONT>
</P>
<P><FONT SIZE=2>Have you read the FAQ? The RT FAQ Manager lives at <A HREF="http://fsck.com/rtfm" TARGET="_blank">http://fsck.com/rtfm</A></FONT>
</P>
</BODY>
</HTML>