[rt-users] Slow down in RT w/ strange mysql-slow.log queries

[Todd Chapman]

On Tue, Nov 07, 2006 at 02:00:20AM +1100, Adrian Carter wrote:
> Noticing lately that RT has really slowed down - its a never ending 
> battle with shredder stripping spam versus the db filling... ANYWAY...
> 
> been getting a lot of these with in mysql-slow.log lately:
> 
> # Time: 061107  1:55:13
> # User at Host: rt_user[rt_user] @ localhost []
> # Query_time: 11  Lock_time: 0  Rows_sent: 1  Rows_examined: 0
> SELECT GET_LOCK('Apache-Session-b22c6874f84cb8e2ababf7c26a7c7a80', 3600);
> 
> 
> This is after having run rt-clean-sessions.pl and freshly logging in - 
> the moment this gets logged out the interface displays the ticket - 
> prior to that its just hanging 'Transfering data...'
> 
> Has anyone got any suggestions ? Its mysql 4.1 on Debian-Sarge..
> 
> Thanks!
> 
> Adrian

Adrian,

I think the GET_LOCK slow queries is mis-leading. RT's session
management only allows a user to make one web request at a
time. I have found GET_LOCK simply means that either the user
is try to load 2 different things in RT at once, or the browser
is trying to download css and javascript but the session is
tied up serving a request.

I have found that if you have a lot of custom fields there is
another index that can be applied to the ObjectCustomFieldValues
table that helps with speed. Especially when you are shredding.
Unfortunatley I don't have that info available to be now.

I would get and run mytop while you are shredding and have
it explain any slow queries you see. Running standalone_httpd
will help separate the real issues from the one request at
a time per session issues.

-Todd