[rt-users] RT Large Scale Performance Issues: Resolved

[Andrew Armstrong]

Hi Everyone,

 

We have been making use of RT for almost a year now, however the performance
began to quickly degrade (tickets would take 30+ seconds to open at times)
over time.

 

We're using RT v3.6.3

 

After we checked both this list and other resources for a solution, we were
unable to find a solid fix which appeared to correct the issue.

 

We have managed to change a few core queries to our installation which
appears to have made a massive difference (tickets load in less than one
second most of the time).

 

We thought others would benefit from these changes and so I am passing on
the findings we made to fix up our RT install.

 

Force a JOIN order of important tables

>From Nathan at mammoth:

--

This is lib/RT/SearchBuilder_Local.pm - its a new file which is
automatically used by RT's extension system. We're using 3.6.3

 

It works by changing the SQL to do a straight join of tables in a specific
order. This appears to improve things because MySQL appears unable to choose
the optimal join order on its own.

 

Regards

Nathan

--

 

This changed file is attached.

 

Use another locking scheme

By default, it appears the user settings locking scheme (for read/write
locks) by default is done via MySQL (using the GET_LOCK() method). When a
user browses RT.

While this sounds fine, unfortunately its because there is no 'read/shared
lock' concept using GET_LOCK(), and so every lock the user makes (eg,
locking their preferences for READ during a page load) is promoted to an
exclusive write lock. This slows performance when loading multiple pages at
once as every page is competing for this exclusive lock, instead of using a
shared read lock.

 

Regards,

Andrew

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20070626/eaa6a0b0/attachment.htm>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: SearchBuilder_Local.pm
Type: application/octet-stream
Size: 1368 bytes
Desc: not available
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20070626/eaa6a0b0/attachment.obj>