What is causing Waiting for table level lock errors?

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:

http://arturito.net/2013/08/28/mysql-waiting-for-table-level-lock-errors/

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

• Apr 05, 2013 : "Site Offline" MySQL server failing to start and stop

• Dec 17, 2012 : How to solve MySQL “The table is full” error 1114 with Amazon RDS?

• Apr 25, 2012 : Very Large Log Files

• Mar 15, 2012 : Why do MySQL tables crash? How do I prevent it?

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 !!!

StackExchange Database Administrators Q#48864, answer score: 8