[rt-users] Urgent Problem with CachedGroupMembers

[Sven Sternberger]

Hello Ruslan!

a big thank you! after inserting the index the speed
increases enormous. our rt is back in production everybody
is happy and I'm still alive ;-)

And now I think I understand what this triggered, in the last days there 
were some hoax mails about childs in thailand, singapure and so on, 
which are looking for their parents. and surprise! you should
send these mails to all your friends. we received several
from these mails each about 1mb in size and with hundreds of recipients.
bad luck the people dealing with spam also use the rt :-(

best regards and if will fill a bug report as soon as possible

sven

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