[rt-users] Search hangs RT

[Anne-Marie Achrenius]

Kenneth Marshall skrev:
> On Tue, Feb 10, 2009 at 02:21:15PM +0100, Anne-Marie Achrenius wrote:
>   
>> Hello!
>>
>> I'm new to the list and I'm in a bit of a pickle, our RT-guru is on
>> paternity leave and we've started having problems with our RT-installation.
>> I've tried searching the list archives for something similar, but
>> nothing fits exactly:
>>
>> The problem: Often (not every time, maybe 8 out of 10) when I perform a
>> Search in RT, the search hangs.
>> The mysqld process is at 100 % cpu. When reading the log files, this
>> jumps out:
>>
>> /var/lib/mysql/blaha.log:
>>
>> SELECT GET_LOCK('Apache-Session-ed5bea237ae21873fecd13ade9cb3f63', 3600)
>>
>> and after a few minutes the web-interface returns with "500 Internal
>> Server Error"
>>
>> I've so far tried to decrease the LOCK time in
>> /usr/lib/perl5/site_perl/5.8.5/Apache/Session/Lock/MySQL.pm
>> but that doesn't seem to help at all.
>> I've enabled logging of slow mysql queries, but so far no log entries
>>
>> We're running RT 3.7.5
>>                      mysql 14.7
>>                      apache 2.0.52
>>
>> Does anyone have any suggestions or similar problems?
>> I can't pinpoint when we started having problems, 2-3 weeks ago, and the
>> problem seems to slowly escalate.
>>
>> /Anne-Marie
>>
>>
>>     
> Maybe your session table needs to be cleaned up. Try deleting any
> sessions over a couple of days old and then optimize the table. I
> cannot help with the specific commands since we use PostgreSQL for
> our RT instance but in SQL I run a command like:
>
> DELETE FROM sessions WHERE lastupdated < '2009-1-30';
>
> or even
>
> TRUNCATE TABLE sessions;
>
> to get them all. You may just have a bloat problem. Try a
>
> SELECT COUNT(*) FROM sessions;
>
> Hope this helps.
>
> Cheers,
> Ken
>   

Thanks for the help, unfortunately the problem doesn't seem to lie there:

mysql> SELECT COUNT(*) FROM sessions;
+----------+
| COUNT(*) |
+----------+
|      323 |
+----------+
1 row in set (0.00 sec)

and deleting anything older than 30/1 left 11. Still the same problem,
search keeps hanging.
How do I optimize the table? (I used to work with Oracle db:s around 10
years ago, I'm pretty rusty!)

Depressed,

/Anne-Marie