Using standalone httpd to debug RT slowness

I’m trying to track down the cause of the slowness of RT with Postgres
(from what I see so far, the indexes are not really being used due to
how searchbuilder makes the queries). To do this, I’m trying to set up
a simple RT config on a crash test box with the standalone httpd.
However when I run it I get this:

[crash]% perl standalone_httpd
Use of uninitialized value in numeric ge (>=) at
/usr/local/rt3/lib/RT/Interface/Web/Handler.pm line 27.
[Tue Sep 7 13:51:47 2004] [crit]: Undefined subroutine
&RT::Interface::Web::NewCGIHandler called at standalone_httpd line 15.
(/usr/local/rt3/lib/RT.pm:257)

There is a noticeable pause between the two lines. The curious part is
that NewCGIHandler is defined in RT/Interface/Web/Handler.pm which
was obviously just loaded.

Vivek Khera, Ph.D.
+1-301-869-4449 x806

smime.p7s (2.42 KB)

I’m trying to track down the cause of the slowness of RT with Postgres
(from what I see so far, the indexes are not really being used due to
how searchbuilder makes the queries). To do this, I’m trying to set
up a simple RT config on a crash test box with the standalone httpd.
However when I run it I get this:

What RT?
What Postgres?
What SearchBuilder?

What RT?
What Postgres?
What SearchBuilder?

You’d think I’d know better … :slight_smile:

RT 3.2.1
Pg 7.4.5
SearchBuilder 1.01
FreeBSD 5.2.1

There is a noticeable pause between the two lines. The curious part
is that NewCGIHandler is defined in RT/Interface/Web/Handler.pm
which was obviously just loaded.

Well, it becomes obvious once you stop staring at it for a while…
wrong package name!

— standalone_httpd Fri Sep 3 16:08:51 2004
+++ /tmp/standalone_httpd Tue Sep 7 12:40:50 2004
@@ -12,7 +12,7 @@

my $port = shift || ‘8080’;
if ( @RT::MasonParameters) {} # Shut up the warnings;
-my $h = &RT::Interface::Web::NewCGIHandler(@RT::MasonParameters);
+my $h =
&RT::Interface::Web::Handler::NewCGIHandler(@RT::MasonParameters);

main_loop($port);

smime.p7s (2.42 KB)

Ah. yeah. that was the result of a last-minute change before 3.2.1
that’s been fixed in SVN for almost two months. Getting toward time
for another release.
FWIW, give searchbuilder 1.10 a shot

JOn Sep 7, 2004, at 12:42 PM, Vivek Khera wrote:

On Sep 7, 2004, at 11:19 AM, Vivek Khera wrote:

There is a noticeable pause between the two lines. The curious part
is that NewCGIHandler is defined in RT/Interface/Web/Handler.pm
which was obviously just loaded.

Well, it becomes obvious once you stop staring at it for a while…
wrong package name!

— standalone_httpd Fri Sep 3 16:08:51 2004
+++ /tmp/standalone_httpd Tue Sep 7 12:40:50 2004
@@ -12,7 +12,7 @@

my $port = shift || ‘8080’;
if ( @RT::MasonParameters) {} # Shut up the warnings;
-my $h = &RT::Interface::Web::NewCGIHandler(@RT::MasonParameters);
+my $h =
&RT::Interface::Web::Handler::NewCGIHandler(@RT::MasonParameters);

main_loop($port);


Rt-devel mailing list
Rt-devel@lists.bestpractical.com
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-devel

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
killing
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
indexes.

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
either,
– 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
plain
– search so the first index will be used. this index will cover the
search
– for where type is specified with lower(). Order of fields makes
sense
– 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
instance

– 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
used
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…
ANALYZE;

smime.p7s (2.42 KB)

Very cool. Thanks!

Just for kicks, do you have perf #s for your at a glance page?

Loading with /?Debug=1 will tell you how long it takes to load.

I’d love to know what your #s were for baseline 1.01, 1.10 and 1.10 with
your tweaks.

Best,
Jesse

Just for kicks, do you have perf #s for your at a glance page?

Loading with /?Debug=1 will tell you how long it takes to load.

I’d love to know what your #s were for baseline 1.01, 1.10 and 1.10
with
your tweaks.

If you’re interested in other’s speed aswell, I might as well post my
results before and after the 1.01 -> 1.10 upgrade the other day.

1.01: Average 2.35 Sec (Min: 2.27 Sec)
1.10: Average 0.86 Sec (Min: 0.77 Sec)

So it’s a speed boost of more than 250%.

Unfortunately I we don’t run RT on PgSQL, but I’ll try the mentioned
two indexes aswell.

-HK

If you’re interested in other’s speed aswell, I might as well post my
results before and after the 1.01 -> 1.10 upgrade the other day.

1.01: Average 2.35 Sec (Min: 2.27 Sec)
1.10: Average 0.86 Sec (Min: 0.77 Sec)

So it’s a speed boost of more than 250%.

Unfortunately I we don’t run RT on PgSQL, but I’ll try the mentioned
two indexes aswell.

I added the following two indexes to our mysql rt3 database, note that
I renamed the ones from the suggested pgsql setup since I want to remove
these again later to make sure the db schema is identical to that of
rt 3.2.1 again. I hope these and some deletions as suggested will be
part of the 3.2.2 upgrade.

CREATE INDEX Tickets7 ON Tickets (Status);
CREATE INDEX GroupMembers2 ON GroupMembers (GroupID);

Before: Average 0.84 Sec (Min: 0.82 Sec)
After: Average 0.82 Sec (Min: 0.80 Sec)

The difference was small, but it was consistent.
Averages were calculated from 10 tests on each.

I’d like to test further Mysql enhancements, but only if there is
an easy way to revert the changes back to original again. After all
this is a production system.

-HK

Ok. Spent the whole afternoon logging queries and analyzing them,
squeezing out the most performance I could by just adjusting the
indexes.

Well, Jesse says SearchBuilder 1.10 does queries differently, so I
re-evaluated the changes I made. Turns out they’re still valid. I did
notice that the only query I found that used the Parent field of the
Attachments table didn’t use the index specific to it, so I decided
that I can also drop the Attachments1 index.

Speed improvements with SB 1.10:

Main page speed is still about the same, perhaps .5 seconds faster
(still under 3 seconds)

The display of a ticket is a bit faster. A 4.5 second load time went
down to 3.3 seconds.

The searches are faster. The searches I tested were clicking on the
queue names to list all open/new tix in those queues. One list went
from about 4.6 seconds down to 1.3 seconds.

In any case, the functional indexes are necessary in postgres to even
consider using them the way SB builds the queries.

I’m sticking with these indexes. In case anyone tries out my updated
indexes, here they are. I also have the antidote script that restores
the original indexes, below… :slight_smile:

–cut here–
– only query I see that uses Parent field doesn’t use index1 or index3.
– i could have missed something, though.
DROP INDEX Attachments1;
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
plain
– search so the first index will be used. this index will cover the
search
– for where type is specified with lower(). Order of fields makes
sense
– 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
instance

– 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
used
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)) ;
– i’m assuming this one needs to be lower() as well; haven’t seen it
used
CREATE INDEX Users2 ON Users (lower(EmailAddress));

– redundant indexes. if “id” is specified, we use PK
DROP INDEX Tickets4;
DROP INDEX Tickets5;
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…
ANALYZE;
–cut here–

The antidote:

–cut here–
CREATE INDEX Attachments1 ON Attachments (Parent) ;
CREATE INDEX Attachments3 ON Attachments (Parent, TransactionId) ;

DROP INDEX Queues1;
CREATE UNIQUE INDEX Queues1 ON Queues (Name) ;

DROP INDEX Groups2;
CREATE INDEX Groups2 On Groups (Type, Instance, Domain);

DROP INDEX GroupMembers1;

CREATE INDEX CachedGroupMembers2 on CachedGroupMembers (MemberId);
CREATE INDEX CachedGroupMembers3 on CachedGroupMembers (GroupId);

DROP INDEX Users1;
DROP INDEX Users2;
CREATE UNIQUE INDEX Users1 ON Users (Name) ;
CREATE INDEX Users2 ON Users (Name);
CREATE INDEX Users3 ON Users (id, EmailAddress);
CREATE INDEX Users4 ON Users (EmailAddress);

DROP INDEX Tickets4;
CREATE INDEX Tickets4 ON Tickets (id, Status) ;
CREATE INDEX Tickets5 ON Tickets (id, EffectiveId) ;

CREATE INDEX TicketCustomFieldValues2 ON TicketCustomFieldValues
(CustomField,Ticket);

ANALYZE;
–cut here–

smime.p7s (2.42 KB)

Vivek,
This is all very cool. I’ll look at getting these into 3.2.2

JesseOn Wed, Sep 08, 2004 at 03:41:10PM -0400, Vivek Khera wrote:

On Sep 7, 2004, at 4:40 PM, Vivek Khera wrote:

Ok. Spent the whole afternoon logging queries and analyzing them,
squeezing out the most performance I could by just adjusting the
indexes.

Well, Jesse says SearchBuilder 1.10 does queries differently, so I
re-evaluated the changes I made. Turns out they’re still valid. I did
notice that the only query I found that used the Parent field of the
Attachments table didn’t use the index specific to it, so I decided
that I can also drop the Attachments1 index.

Speed improvements with SB 1.10:

Main page speed is still about the same, perhaps .5 seconds faster
(still under 3 seconds)

The display of a ticket is a bit faster. A 4.5 second load time went
down to 3.3 seconds.

The searches are faster. The searches I tested were clicking on the
queue names to list all open/new tix in those queues. One list went
from about 4.6 seconds down to 1.3 seconds.

In any case, the functional indexes are necessary in postgres to even
consider using them the way SB builds the queries.

I’m sticking with these indexes. In case anyone tries out my updated
indexes, here they are. I also have the antidote script that restores
the original indexes, below… :slight_smile:

–cut here–
– only query I see that uses Parent field doesn’t use index1 or index3.
– i could have missed something, though.
DROP INDEX Attachments1;
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
plain
– search so the first index will be used. this index will cover the
search
– for where type is specified with lower(). Order of fields makes
sense
– 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
instance

– 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
used
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)) ;
– i’m assuming this one needs to be lower() as well; haven’t seen it
used
CREATE INDEX Users2 ON Users (lower(EmailAddress));

– redundant indexes. if “id” is specified, we use PK
DROP INDEX Tickets4;
DROP INDEX Tickets5;
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…
ANALYZE;
–cut here–

The antidote:

–cut here–
CREATE INDEX Attachments1 ON Attachments (Parent) ;
CREATE INDEX Attachments3 ON Attachments (Parent, TransactionId) ;

DROP INDEX Queues1;
CREATE UNIQUE INDEX Queues1 ON Queues (Name) ;

DROP INDEX Groups2;
CREATE INDEX Groups2 On Groups (Type, Instance, Domain);

DROP INDEX GroupMembers1;

CREATE INDEX CachedGroupMembers2 on CachedGroupMembers (MemberId);
CREATE INDEX CachedGroupMembers3 on CachedGroupMembers (GroupId);

DROP INDEX Users1;
DROP INDEX Users2;
CREATE UNIQUE INDEX Users1 ON Users (Name) ;
CREATE INDEX Users2 ON Users (Name);
CREATE INDEX Users3 ON Users (id, EmailAddress);
CREATE INDEX Users4 ON Users (EmailAddress);

DROP INDEX Tickets4;
CREATE INDEX Tickets4 ON Tickets (id, Status) ;
CREATE INDEX Tickets5 ON Tickets (id, EffectiveId) ;

CREATE INDEX TicketCustomFieldValues2 ON TicketCustomFieldValues
(CustomField,Ticket);

ANALYZE;
–cut here–


Rt-devel mailing list
Rt-devel@lists.bestpractical.com
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-devel