AW: Re: [rt-users] clean up user table

[Kenneth Marshall]

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.

Ken