RT Large Scale Performance Issues: Resolved

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

SearchBuilder_Local.pm (1.34 KB)

Hi Andrew,

the first part of your mail was fine, its working like a charme, but this
part:

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.
I dont understand, do you have an idea how to change this like you described
it???

Thanks

Torsten2007/6/26, Andrew Armstrong andrew@mammoth.com.au:

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


The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

MFG

Torsten Brumm

http://www.torsten-brumm.de