We got the database hanging twice already and trying to find a cause.

show processlist
Waiting for global read lock | INSERT INTO {myisam_table} ...

In here the disk space was full so we thought the problem was over after giving it some more but the next day at midday it hanged again:

show processlist
Waiting for table level lock | UPDATE {myisam_table} ... 

What could be causing it?

Mysql Default Engine: InnoDB.

Database has a mixture of tables with both MyISAM and InnoDB engines.

Log posted here:


  • The two events might be unrelated, since you say you had a disk space problem at one time and since these are two different kinds of locks they could have been caused by unrelated things. One possibility that could have caused both of them is a backup with mysqldump. Were you running a backup at the time? Commented Aug 28, 2013 at 12:12
  • All backups finish at 7:00 am and never run during working hours. Database got stuck at lunchtime. Commented Aug 28, 2013 at 12:55
  • If a MyISAM table is locked in one DB Session, there has to be another DB Session that locked it. Please show the complete processlist next time this happens. Commented Aug 28, 2013 at 13:41
  • arturito.net/2013/08/28/… Commented Aug 28, 2013 at 15:31
  • @Arturito we probably need SHOW FULL PROCESSLIST rather than SHOW PROCESSLIST so we can see the entire query for each thread... but as it stands right now, if there are MyISAM tables involved, then it looks like the long-running SELECT query in 42686 is blocking the UPDATE query in 43506, which is, in turn, blocking every SELECT query that follows it. Commented Aug 28, 2013 at 18:31

1 Answer 1



  • 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


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

enter image description here 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


Suggestion #1: Map tmpdir to another disk

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.

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


  • Great advice on RAM Disk - great speed gains , you need lots of memory for that - is like making SSD.
    – Up_One
    Commented Feb 25, 2014 at 17:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.