On Nov 27, 2007 9:25 PM, Kenneth Marshall <[EMAIL PROTECTED]> wrote: > On Tue, Nov 27, 2007 at 09:49:45AM -0800, Gordon Messmer wrote: > > Kenneth Marshall wrote: > >> The time to open and display a ticket varies based on the number > >> of transactions involved, but typically takes on the order of 1-3s. > > > > With RT3 3.6.5 and PG 8.1.9, I'm seeing newish tickets open in a little > > more than two seconds. I don't have numbers for MySQL 5, but I recall it > > being less than half a second. It was much longer than that with our > > slightly older rt3 on MySQL 5, and also much longer before we'd done vacuum > > analyse on postgres. ;) > > > > Yes, having seen the system behave much faster, I do think that >2 seconds > > is slow. > > > We are currently running 3.4.5pre1 and in most respects, the actual > browser/RT-frontend are the hold ups, not the database backend. The > RT 3.6.4 test instance was much faster in most areas. I cannot give > you any timings because the web server that I was using is in the > process of being upgraded to a newer release of the software. I do suspect > that the MySQL query cache could help you eek out a few more fractions of > a second in performance. Once we had the indexes adjusted for the slow > queries, the biggest gain was in minimizing the information that needed > to be rendered by the browser. For example, we pruned the list of > transactions that are displayed by default. This easily provided a big > speed increase for rendering, particularly as the tickets acquired > updates. As a reference point, it takes my DB 230ms to return the > query below uncached and 75-100ms once cached. > > >> A new ticket with a few updates will take about 1s and an older > >> ticket with many updates takes 3-4s. We are using PostgreSQL 8.2 > >> for the backend DB with the index patches from the mailing list > >> posting. I did have to watch the logs for slow queries, to figure > >> out where indexes were needed. That and keeping the session table > >> cleaned is pretty much all that we need to do. From your description, > >> it sounds like you are just missing an index or two. Once you find > >> out what you need and add them, you will be fine. I can help with > >> PostgreSQL but I do not have much experience with tuning MySQL. > > > > After enabling the log, this is the single slowest query that I saw. > > > > 2007-11-27 09:39:53 PST rt3 - LOG: duration: 450.262 ms statement: SELECT > > main.* FROM ( SELECT main.id FROM GroupMembers main JOIN Groups Groups_1 > > ON ( Groups_1.id = main.GroupId ) WHERE (Groups_1.Domain = > > 'SystemInternal' OR Groups_1.Domain = 'UserDefined') AND (main.MemberId = > > '169133') GROUP BY main.id ORDER BY min(Groups_1.Domain) ASC, > > min(Groups_1.Name) ASC ) distinctquery, GroupMembers main WHERE (main.id = > > distinctquery.id) > > > > Where would I look for "index patches"? > > I sent you our list of indexes. You can see how your setup compares and > see if any of the missing ones help your performance. "EXPLAIN ANALYZE..." > can give you detailed information about your query plans. > > Good luck with your pruning, but I suspect that that may not have > much of an effect if your indexes are correct. I do believe index on GroupMembers(MemberId, GroupId) will help this query much.
> > Ken > -- Best regards, Ruslan. _______________________________________________ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users SAVE THOUSANDS OF DOLLARS ON RT SUPPORT: If you sign up for a new RT support contract before December 31, we'll take up to 20 percent off the price. This sale won't last long, so get in touch today. Email us at [EMAIL PROTECTED] or call us at +1 617 812 0745. Community help: http://wiki.bestpractical.com Commercial support: [EMAIL PROTECTED] Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
