How to improve performance?

RT Users
1

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@clue.co.za

2

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

3

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

after you “analyze” your tables, what does “explain” have to say about
your above queries?

I posted here a while back some alternative indexes for RT 3.2 based on
my experiences. There are a few indexes that are totally unused which
just add disk I/O during updates/inserts that are wasted.

Vivek Khera, Ph.D.
+1-301-869-4449 x806