[rt-users] RT freezes on some big tickets

[Kenneth Marshall]

On Tue, Mar 11, 2008 at 09:05:20PM -0400, The Anarcat wrote:
> On Tue, Mar 11, 2008 at 07:45:24AM -0500, Kenneth Marshall wrote:
> > Welcome to the world of stateless page loads. :)
> 
> Whee! :)
> 
> > The only suggestions
> > that I have are in two areas. Can you modify the page load to skip the
> > un-interesting transactions/attachments?
> 
> er... sure i'd like to!
> 
> > We had to do that here with
> > system events (not attachments) and added a brief/full button on the
> > history. The default is only needed information and then if you select
> > "full" you get everything which may require a wait.
> 
> How did you go around doing that?
> 

I modified Tickets/Elements/ShowHistory to skip any uninteresting
transactions and used a modification of "the show history in reverse"
documented in the wiki to add another toggle for full/brief display.
Just nixing the display of some of the attachments should help you
a lot.

> > The second area
> > is database monitoring. Have you identified the queries that are running?
> > Are they optimized? Would clustering your attachments improve retrieval
> > performance? Good luck.
> 
> This being a production server, running tracing on the postgresql
> database imposes major load, so no, I haven't looked at the queries,
> even less optimize them...

I would dump the DB to a test instance, if you cannot afford the logging
of queries that take longer than 1 second or so, and run some tests on it.

> 
> How can I go around clustering the attachments?
> 

The CLUSTER command will allow you to cluster the table around the
appropriate index. The question is which index to use. It may be
that since the attachments table is always incremented/added to that
it is basically already clustered so there would not be a substantial
change. The slow queries determined from the above testing may show
where clustering would be effective. Good luck.

Regards,
Ken