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

[Ruslan Zakirov]

On Nov 27, 2007 9:25 PM, Kenneth Marshall <ktm at rice.edu> 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.