[rt-users] How to improve performance ?

[Andre Sachs]

Hi,

I am running RT (3.0.11) with roughly 100 000 tickets using mod_perl
under Apache 1.3.31 and PostgreSQL 7.4 on a Gentoo Linux (2.6.10) based host.
The filesystem that both the database and RT reside on are Reiserfs.

The web interface is really slow, almost unusable.

There are a number remedial actions I can take, I would appreciate some
help with prioritising (biggest benefit first):

- Update DBIx::SearchBuilder (currently 1.01)
- Upgrade to RT 3.4
- Change database engines from PostgreSQL to MySQL
- Use RTx::Shredder to remove old tickets (preferably to another 
  database for infrequent reporting)
- Move from mod_perl Apache 1 to FastCGI and Apache 2

After some digging there are 2 queries that are run very frequently.

The first ran 22,263 times and the second ran 12,190 times during the
debugging.

First query:
SELECT ACL.id from ACL, Groups, Principals, CachedGroupMembers WHERE
(ACL.RightName = { } OR ACL.RightName = { }) AND Principals.Disabled =
{ } AND CachedGroupMembers.Disabled = { } AND Principals.id = Groups.id
AND Principals.id = CachedGroupMembers.GroupId AND
CachedGroupMembers.MemberId = { } AND ( ACL.ObjectType = { } OR
(ACL.ObjectType = { } AND ACL.ObjectId = { })) AND ( ( ACL.PrincipalId =
Principals.id AND ACL.PrincipalType = { } AND (Groups.Domain = { } OR
Groups.Domain = { } OR Groups.Domain = { } OR Groups.Domain = { })) )
LIMIT { }

Second query:
SELECT ACL.id from ACL, Groups, Principals, CachedGroupMembers WHERE
(ACL.RightName = { } OR ACL.RightName = { }) AND Principals.Disabled =
{ } AND CachedGroupMembers.Disabled = { } AND Principals.id = Groups.id
AND Principals.id = CachedGroupMembers.GroupId AND
CachedGroupMembers.MemberId = { } AND ( ACL.ObjectType = { } OR
(ACL.ObjectType = { } AND ACL.ObjectId = { })) AND ( ((Groups.Domain =
{ } AND Groups.Instance = { }) ) AND Groups.Type = ACL.PrincipalType AND
Groups.Id = Principals.id AND Principals.PrincipalType = { }) LIMIT { }

The row counts for the table involved in the queries are:
ACL =  541
Groups = 391,598
Principals = 417,555
CachedGroupMembers = 887,219

We have indexed the tables but no appreciable speed up has been witnessed.

Regards
  Andre Sachs

-- 
Andre Sachs
Developer
Clue Technologies
asachs at clue.co.za