Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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/

share|improve this question
    
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? –  Michael - sqlbot Aug 28 '13 at 12:12
    
All backups finish at 7:00 am and never run during working hours. Database got stuck at lunchtime. –  Arturito Aug 28 '13 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. –  RolandoMySQLDBA Aug 28 '13 at 13:41
    
arturito.net/2013/08/28/… –  Arturito Aug 28 '13 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. –  Michael - sqlbot Aug 28 '13 at 18:31
add comment

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

share|improve this answer
    
Great advice on RAM Disk - great speed gains , you need lots of memory for that - is like making SSD. –  Up_One Feb 25 at 17:48
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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