If the mysql server hangs with the State “Copying to tmp table”
Tuesday, January 19th, 2010This one took me a WHILE to figure out. The damn server would start hanging, and websites would not be displayed when using a particular table.
I started by restarting the mysql service once a day. Finally, obviously, I got sick of having to do that (even on the beach one weekend) so I buckled down and got to figuring out what the hell was wrong with my server. I started by shelling in to the server, and running SHOW PROCESSLIST time after time, every 5 seconds. This got old fast. I found and installed Navicat for MySQL, a great monitoring program.
I found that a custom search done on a products table was giving me the MySQL State “Copying to tmp table” and not releasing the process. This would hang that table, allowing some things to be served, but nothing having to do with this table.
The MySQL dev forum had a thread a mile long regarding the topic, I dredged it for days, trying all the suggestions. I tried tweaking the server, I tried screwing around with the mysql user’s ulimits, etc. Nothing worked.
Finally, a solution presented itself. The tables involved in the query were “over-indexed.” It was the simplest thing to remove a few columns from the index and all of a sudden I was done. I am now 48 hours and the server is running flawlessly. Also, since I did some serious tweaking, it’s running even leaner than I originally had it.
Amazing something so simple caused such a big problem, but there you have it.
