INITIAL OBSERVATIONS
- Process ID 42686 says its preparing to execute a SELECT query
- There are some sleeping connections
- All other processes cannot acquire a table lock
- I would have expected an UPDATE, DELETE, or INSERT to do the lock. There are no claiming ownership of the table in question.
- Can't see the full query in Process ID 42686, but I suspect it involves a
JOIN, GROUP BY, or ORDER BY
WORKING THEORY
If you ran out of diskspace with the process list you gave me, then we can place blame on the MyISAM storage engine. Why?
In your particular case, it is not one of your tables. If a JOIN,GROUP BY, or ORDER BY was being executed and a temp table was being written to disk (on disk temp tables use the MyISAM storage engine) the MySQL simply freezes when out of space. How do I know that ?
According to MySQL 5.0 Certification Study Guide
Page 408,409 Section 29.2 Bulletpoint 11 says:
If you run out of disk space while adding rows to a MyISAM table, no
error occurs. The server suspends the operation until space becomes
available, and then completes the operation.
I have discussed this situation before
Something tells me that you have one of these two situations
- disk-based temp tables for your SELECTs and competing for space with your regular data
- If temp table are landing in
/tmp in the root partition, that's running out of space
SUGGESTIONS
Suggestion #1: Map tmpdir to another disk
[mysqld]
tmpdir = /another/disk/besides/root/partition
Suggestion #2: Create a RAM Disk
Run this code to install a RAM disk that will available on Linux reboot.
RAMDISK_SIZE=32g
service mysql stop
mkdir /var/tmpfs
echo "none /var/tmpfs tmpfs defaults,size=${RAMDISK_SIZE} 1 2" >> /etc/fstab
mount -t tmpfs -o size=${RAMDISK_SIZE} none /var/tmpfs
cp -R /var/lib/mysql/* /var/tmpfs
mv /var/lib/mysql /var/lib/mysql_old
ln -s /var/tmpfs /var/lib/mysql
chown -R mysql:mysql /var/tmpfs
chown -R mysql:mysql /var/lib/mysql
service mysql start
Then, map tmpdir to /var/tmpfs
GIVE IT A TRY !!!
