-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
After upgrading to RT3, I found that just viewing the front page took 10-12
seconds. First some basic info: RH7.3, Apache 1.3.27, mod_perl 1.26, mysql
4.0.12; approx 9200 tickets and 3800 users.
I was watching ‘show full processlist’ while loading the frontpage, and just
picked a query to explain; this is what I caught:
mysql> explain SELECT ACL.id from ACL, Groups, Principals, CachedGroupMembers
WHERE (ACL.RightName = ‘SuperUser’ OR ACL.RightName = ‘SeeQueue’) AND
Principals.Disabled = 0 AND CachedGroupMembers.Disabled = 0 AND
Principals.id = Groups.id AND Principals.id = CachedGroupMembers.GroupId AND
CachedGroupMembers.MemberId = ‘24’ AND ( ACL.ObjectType = ‘RT::System’ OR
(ACL.ObjectType = ‘RT::Queue’ AND ACL.ObjectId = ‘15’)) AND ( (
ACL.PrincipalId = Principals.id AND ACL.PrincipalType = ‘Group’ AND
(Groups.Domain = ‘SystemInternal’ OR Groups.Domain = ‘UserDefined’ OR
Groups.Domain = ‘ACLEquivalence’ OR Groups.Domain = ‘Personal’)) OR (
((Groups.Domain = ‘RT::Queue-Role’ AND Groups.Instance = ‘15’) ) AND
Groups.Type = ACL.PrincipalType AND Groups.Id = Principals.id AND
Principals.PrincipalType = ‘Group’) ) LIMIT 1;
| table | type | possible_keys | key | key_len
| ref | rows | Extra |
| Groups | range | PRIMARY,Groups1,Groups2 | Groups1 | 130
| NULL | 7463 | Using where; Using index |
| Principals | eq_ref | PRIMARY | PRIMARY | 4
| Groups.id | 1 | Using where |
| CachedGroupMembers | ref | DisGrouMem,GrouMem | DisGrouMem | 12
| Principals.id,const,const | 1 | Using where; Using index |
| ACL | range | ACL1 | ACL1 | 54
| NULL | 10 | Using where; Using index |
4 rows in set (0.01 sec)
The part that concerned me was that the Groups table matched 7463 rows; that
seemed like a lot to me, so I did the following query:
mysql> select count() from Groups;
| count() |
| 39550 |
1 row in set (0.12 sec)
39550 groups? I only have 3800 users; after doing ‘select * from Groups’ I
noticed many of the group names are null, and there are 4 different ticket
roles for each instance (CC, AdminCC, Owner, Requestor). Any idea why this
table is so huge? The Groups table in my RT2 instance has only 8 rows.
- -Doug
Douglas E. Warner dwarner@ctinetworks.com Network Engineer
CTI/PAdotNET http://ctinetworks.com +1 717 975 9000
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.7 (GNU/Linux)
iD8DBQE+ggBpJV36su0A0xIRAqgAAKCJHATvbTXxe7HSCAiP0tIeJJKsOACeK/Dz
Waz1t342gJB6o+Fxn5sbxLI=
=u7k1
-----END PGP SIGNATURE-----