On Fri, Oct 13, 2006 at 12:22:36AM -0400, Kevin White wrote: > Jesse Vincent wrote: > > > > > >On Thu, Oct 12, 2006 at 11:51:52PM -0400, Kevin White wrote: > >>The production server is running Centos 4 (RHEL 4) on a machine that > >>really doesn't have enough RAM (1GB) and only has IDE disks, running as > >>a software RAID1. It IS an Opteron, and it is running 64-bit. rt 3.4.1 > >>and postgresql 8.0.3. > > > > > >Strongly recommend: > > * newer RT > > * newer Pg (8.x before 8.1.4 had some specialness) > > * vacuum analyzing your postgres instance. > > Thanks Jesse...when you say newer RT, do you mean newer than 3.4.5? > > My test box has Pg 8.1.4, and my next step will be attempting to take > that to RT 3.4.5, then 3.6.1 (.2 by then, probably). I have some small > customizations I need to carry forward. > > I do vacuum analyze, 3 times a day actually, so I do that. :) > > Thanks for the advice. If I find anything else, I'll post (and > contribute to the Wiki). > > Kevin > Kevin,
I had meant to pass this on to the list for possible inclusion in the PostgreSQL related FAQ/schema. When we first rolled RT out, performance was reasonable. As the number of tickets and users grew, the ticket update, creation, and display processes continued to degrade. Finally, the slowdown was enough to cause the slowing queries to cross the query log threshold on the DB server. The slow queries all involved lookups based on OID. I added indexes on OID for all of the tables involved and the overall responsiveness is back to normal in all areas. Here are the indexes that needed to be added to the DB to enable fast OID based queries. Hopefully, they can be included in 3.6.2+. CREATE INDEX attachmentsoid ON attachments USING btree ( oid ); CREATE INDEX cachedgroupmembersoid ON cachedgroupmembers USING btree ( oid ); CREATE INDEX objectcustomfieldvaluesoid ON objectcustomfieldvalues USING btree ( oid ); CREATE INDEX transactionsoid ON transactions USING btree ( oid ); Ken _______________________________________________ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users 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
