[rt-users] Performance on PostgreSQL

[Kenneth Marshall]

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