Mysql – causing Waiting for table level lock errors

innodblockingmyisamMySQL

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/

Best Answer

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

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

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

Related Question