[Rt-devel] using standalone httpd to debug RT slowness
vivek at khera.org
Tue Sep 7 16:40:58 EDT 2004
On Sep 7, 2004, at 2:07 PM, Jesse Vincent wrote:
> On Tue, Sep 07, 2004 at 01:52:21PM -0400, Vivek Khera wrote:
>>> FWIW, give searchbuilder 1.10 a shot
>> I plan to once I get some baseline data with 1.01.
>> Already it seems that the ... where lower(foo) = 'bar' stuff is
>> Pg because indexes are not being used.
> Nod. I'd forgotten whether we'd ended up with functional indexes for
> that stuff.
Ok. Spent the whole afternoon logging queries and analyzing them,
squeezing out the most performance I could by just adjusting the
Some indexes need to be removed because they are redundant, or just
plain never used due to the queries being functional. The latter were
replaced with functional indexes. Dropping redundant indexes speeds
up inserts and updates.
Anyone running Postgres should consider trying these out. It has made
loading my "RT at a glance" page amazingly faster, and tickets seem to
load faster as well.
MySQL users may want to see if the two new indexes, GroupMembers1 and
my version of Tickets4 speed things up.
The rest of the message is suitable for cut/paste into psql when
connected as the PG superuser to the rt3 database:
-- seems redundant this one... don't see use of queries on Parent
-- so that one may be droppable.
DROP INDEX Attachments3;
-- only query as lower(). seems confusing to allow queues with same
-- name but differing only in case.
DROP INDEX Queues1;
CREATE UNIQUE INDEX Queues1 ON Queues (lower(Name)) ;
-- unable to analyze usage of Links table indexes.
-- when Instance, Type, and Domain are all used it is usually with just
-- search so the first index will be used. this index will cover the
-- for where type is specified with lower(). Order of fields makes
-- from queries I see: sometimes only first one or two are queried.
DROP INDEX Groups2;
CREATE INDEX Groups2 On Groups (lower(Type),lower(Domain),Instance);
-- Transactions table indexes not analyzed.
-- ACL table index not analyzed. too small to use indexes in my
-- group members needs an index! This one has massive speedups.
CREATE INDEX GroupMembers1 ON GroupMembers (GroupID);
-- I think these are redundant since I only ever see DisGrouMem index
DROP INDEX CachedgroupMembers2;
DROP INDEX CachedgroupMembers3;
-- the old Users2 is 100% redundant with Users1, and neither gets used
-- since we always query with lower(). Users3 is redundant since "id"
-- is already a PK so if that is queried, we have a unique return.
DROP INDEX Users1;
DROP INDEX Users2;
DROP INDEX Users3;
DROP INDEX Users4;
CREATE UNIQUE INDEX Users1 ON Users (lower(Name)) ;
CREATE INDEX Users2 ON Users (lower(EmailAddress));
-- redundant indexes. if "id" is specified, we use PK
DROP INDEX Tickets4;
DROP INDEX Tickets5;
-- there are queries that make good use of this one...
CREATE INDEX Tickets4 ON Tickets (Status);
-- redundant index -- just a prefix of index 1.
DROP INDEX TicketCustomFieldValues2;
-- don't see Attributes used. looks like an empty table for me.
-- and update the statistics...
-------------- next part --------------
A non-text attachment was scrubbed...
Size: 2476 bytes
Desc: not available
Url : http://lists.bestpractical.com/pipermail/rt-devel/attachments/20040907/f64bff55/smime.bin
More information about the Rt-devel