Getting weird problems with my server that put it out of the action for most of the day. The error being reported was too many connections which I thought is a nice problem to have as loads of people using my website. But when I ran ‘SHOW PROCESSLIST;’ in mysql it showed loads of sleep processes that were waiting for ages.
To find out which queries were causing this I restarted mysql with log=/home/logfile which logs all queries. Then run SHOW PROCESSLIST again and match the ID of the sleeping queries to the log file. Loads of different SQL queries were returned so that didn’t really help. Then I got lazy and just edited the /etc/my.cnf file and set wait_timout to 60 seconds, so mysql just kills anything over 60 seconds. The default was 8 hours! So that’s a temporary fix, still don’t know what the root cause is.
My server has a dual core Xeon and running msql version 4.1 and there’s reports of this problem with that version and processer. So I’ll upgrade to mysql 5 and see if that fixes it.