[rt-users] Urgent Problem with CachedGroupMembers

[Ruslan U. Zakirov]

In mysql shell run: SHOW KEYS FROM CachedGroupMembers;
this will show all indexes on that table.

And you can create next INDEX:
CREATE INDEX CacheMemId ON CachedGroupMembers(MemberId);

We have this index for a long time, has this problem too.

Also turn on mysql slow queries log:
http://wiki.bestpractical.com/?Debug

Sven Sternberger wrote:
> RT: 3.0.12
> mysql: 4.0.22
> 
> Hello!
> 
> since we restarted our RT it get VERY slow. When
> I look at the mysql proccesses ther are about 20 processes
> like:
> 
> SELECT main.* FROM CachedGroupMembers main   WHERE ((main.MemberId = 
> 438260))
> 
> this proccess last only a few seconds but it will be often
> repeated.  I counted about 6500 lines per GroupId in the log
> 
> There is no Group with the id '438260'. And we have about
> 1.3 millions rows in CachedGroupMembers.
> Groups:233666
> Users:23717
> 
> And so I think the real question
> is how get I rid of all the old CachedGroupMembers rows.
> 
> in the mysql log I see (here it is 438346):
> 
> SELECT  * FROM GroupMembers WHERE GroupId = '438346' AND MemberId = 
> '437242'
> SELECT  * FROM GroupMembers WHERE GroupId = '438346' AND MemberId = 
> '437242'
> INSERT INTO GroupMembers (GroupId, MemberId) VALUES ('438346', '437242')
> INSERT INTO CachedGroupMembers (GroupId, Via, MemberId, 
> ImmediateParentId, Disabled) VALUES ('438346', '0', '437242', '438346', 
> '0')
> 
> SELECT main.* FROM CachedGroupMembers main   WHERE ((main.MemberId = 
> 438346))
> SELECT  * FROM Principals WHERE id = '438346'
> INSERT INTO CachedGroupMembers (GroupId, Via, MemberId, 
> ImmediateParentId, Disabled) VALUES ('438346', '1310816', '437242', 
> '438346', '0')
> 
> SELECT  * FROM GroupMembers WHERE GroupId = '438346' AND MemberId = '35874'
> SELECT  * FROM GroupMembers WHERE GroupId = '438346' AND MemberId = '35874'
> INSERT INTO GroupMembers (GroupId, MemberId) VALUES ('438346', '35874')
> INSERT INTO CachedGroupMembers (GroupId, Via, MemberId, 
> ImmediateParentId, Disabled) VALUES ('438346', '0', '35874', '438346', '0')
> 
> SELECT main.* FROM CachedGroupMembers main   WHERE ((main.MemberId = 
> 438346))
> SELECT  * FROM Principals WHERE id = '438346'
> 6 Query
> INSERT INTO CachedGroupMembers (GroupId, Via, MemberId, 
> ImmediateParentId, Disabled) VALUES ('438346', '1310816', '35874', 
> '438346', '0')
> 
> hope there is somebody out there who can help otherwise I think
> our user/admins will be very unpleasent to me.
> 
> 
> bets regards!
> _______________________________________________
> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
> 
> Be sure to check out the RT wiki at http://wiki.bestpractical.com