[rt-users] Urgent Problem with CachedGroupMembers

[Ruslan U. Zakirov]

sent this to rt-bugs@

Ruslan U. Zakirov wrote:
> 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
> 
> 
> _______________________________________________
> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
> 
> Be sure to check out the RT wiki at http://wiki.bestpractical.com