[rt-users] How to improve performance ?

[Kenneth Marshall]

On Thu, Mar 03, 2005 at 03:29:54PM +0200, Andre Sachs wrote:
> 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

Upgrading to RT 3.4.1 will also require the DBIx::SearchBuilder upgrade
and is probably the biggest improvement you can make. 3.2.x is much
faster than 3.0.x and 3.4.x is another 2X faster still.

> - Change database engines from PostgreSQL to MySQL

We use PostgreSQL 8.0.1 here and it works very well. You might consider
the upgrade to get the point-in-time recovery features.

> - Use RTx::Shredder to remove old tickets (preferably to another 
>   database for infrequent reporting)

This is probably not much of an issue.

> - Move from mod_perl Apache 1 to FastCGI and Apache 2
> 

The speed is about the same between mod_perl and FastCGI. If you already
have mod_perl running, it is probably not worth changing.

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

Are you running "VACUUM ANALYZE"? We use pg_autovacuum here.

Ken