Query drastically slowing down RT

RT Users
1

Hi Folks,

I have problems with a couple of user generated queries that drastically
slow down the RT database.

The database is MySQL 5 running on a separate server from RT ( 4.0.5 )

The queries in question are of a similar format to the following,

Queue = ‘Solutions’
AND ( Status = ‘new’ OR Status = ‘open’ )
AND ( Requestor.EmailAddress = ‘user1@site.net.nz’ OR
Requestor.EmailAddress = ‘user2@site.net.nz’ OR Requestor.EmailAddress = ’
user3@site.net.nz’ OR Requestor.EmailAddress = ‘user4@site.net.nz’ OR
Requestor.EmailAddress = ‘user5@site.net.nz’ OR Requestor.EmailAddress = ’
user6@site.net.nz’ OR Requestor.EmailAddress = ‘user7@site.net.nz’ )

With only a few email addresses entered the query runs in an acceptable
time, however as the number increase the query almost forces the database
to a standstill.

Does anyone have any ideas on how I can make any improvements on
performance?

Thanks in advance.

Cheers,
Glyn

2

Hi Folks,

I have problems with a couple of user generated queries that drastically
slow down the RT database.

The database is MySQL 5 running on a separate server from RT ( 4.0.5 )

The queries in question are of a similar format to the following,

Queue = ‘Solutions’
AND ( Status = ‘new’ OR Status = ‘open’ )
AND ( Requestor.EmailAddress = ‘user1@site.net.nz’ OR
Requestor.EmailAddress = ‘user2@site.net.nz’ OR Requestor.EmailAddress = ’
user3@site.net.nz’ OR Requestor.EmailAddress = ‘user4@site.net.nz’ OR
Requestor.EmailAddress = ‘user5@site.net.nz’ OR Requestor.EmailAddress = ’
user6@site.net.nz’ OR Requestor.EmailAddress = ‘user7@site.net.nz’ )

With only a few email addresses entered the query runs in an acceptable
time, however as the number increase the query almost forces the database
to a standstill.

Does anyone have any ideas on how I can make any improvements on
performance?

Thanks in advance.

Cheers,
Glyn

There is a bug in the way joins are calculated by DBIx::SearchBuilder in
some circumstances. If you log the SQL being run you will see numerous
joins to the same table if it’s the same bug.

http://issues.bestpractical.com/Ticket/Display.html?id=18414

I attached a work around to that bug, it’s not complete but it works for
us. Actually the patch was out of date as there was another side affect,
so I’ve attached a newer patch to the bug.

Note that even the newer patch has the issues Ruslan talked about in the
bug, I simply don’t have time to cover all the angles required for a
full upstream fix.

Cheers, Jeff.

Jeff Fearn jfearn@redhat.com
Senior Software Engineer
Red Hat