Hello!
This is my first post on this forum... I am having a rather strange problem with mysql. We run a fairly large website with over 20,000 members and about 150 queries/sec.
The code is written in php/mysql. And the problem is, every 8-10 hours or so, mysql crashes! I can't seem to figure out why, however I have found a few things that may lead to something, but I have no idea how to resolve it.
Firstly... I've noticed that every 10-15 mins or so, if I do a SHOW PROCESSLIST, a new process is stuck in the ROLLBACK state indefinately:
Kill 17907 cspeed localhost regdb Query 7389 NULL ROLLBACK
Kill 18115 cspeed localhost regdb Query 7363 NULL ROLLBACK
Kill 31316 cspeed localhost regdb Query 5821 NULL ROLLBACK
Kill 31658 cspeed localhost regdb Query 5821 NULL ROLLBACK
Kill 36253 cspeed localhost regdb Query 5322 NULL ROLLBACK
Kill 44861 cspeed localhost regdb Query 4368 NULL ROLLBACK
Kill 57627 cspeed localhost regdb Query 2954 NULL ROLLBACK
Kill 60878 cspeed localhost regdb Query 2602 NULL ROLLBACK
Kill 66261 cspeed localhost regdb Query 2007 NULL ROLLBACK
Kill 69933 cspeed localhost gmgdb Query 1581 NULL ROLLBACK
Kill 81246 cspeed localhost gmgdb Query 258 NULL ROLLBACK
Also, once mysql crashes, I get this message in the .err log file:
mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked agaist is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail
key_buffer_size=268431360
record_buffer=1044480
sort_buffer=1048568
max_used_connections=278
max_connections=500
threads_connected=248
It is possible that mysqld could use up to
key_buffer_size + (record_buffer + sort_buffer)*max_connections = 1284136 K
bytes of memory
Hope that's ok, if not, decrease some variables in the equation
Mysql is version 3.2.3 and the system is a dual p4 2.1 with 2gig RAM.
I'm thinking the crashing has to do with a bad query of somekind? But I have no idea how to find which one it is. I have AUTOCOMMIT =1 so i'm not doing any batch queries or updates, and i have no explicit Rollbacks in the code.. so the Rollbacks that appear in the processlist are starting by themselves.
Any help with this would be greatly appreciated. Thanks!
