MySQL Bugs: #69030: Optimize table locks whole database

Bug #69030	Optimize table locks whole database
Submitted:	22 Apr 2013 12:55	Modified:	24 Apr 2013 11:23
Reporter:	Jens Rantil	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S1 (Critical)
Version:	5.1.66	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	documentation, locking, opening tables, optimize table

Description:
Background: I recently had to execute OPTIMIZE TABLE on a fairly big (preoptimize index+data >= 50 GB) InnoDB table (with "innodb_file_per_table = 1"). The reason was that I needed to free up space from a major cleanup (a bunch of DELETEs). The harddrive disk usage was ~97%, which meant the file system (ext4) was rather sluggish.

It's also worth noting that the database in question has over 200 tables and that some of them are expected to have rather low latency queries.

Execution: I first dropped all indexes on the table, executed OPTIMIZE TABLE and then readded the indexes*. The full OPTIMIZE took 20 min 6.08 sec. When executing OPTIMIZE TABLE things first went fairly well. I was continously keeping an eye on the temporary file building up in /var/lib/mysql/mydb. After some time I noticed that the temporary file was not building up anymore, although the OPTIMIZE TABLE was still running. This was not an issue in itself. However, after 612 seconds "SHOW PROCESSLIST" suddenly started showing a lot of SELECTs with status "Opening tables". According to documentation this is due to locking.

* Optimizing table without index was faster in development environment, probably due to fast index creation of InnoDB Plugin.

Problem: A global read lock of all tables was taken. This is not documented in OPTIMIZE TABLE documentation.

Reproducability: The locking behaviour was observed on two separate slaves. Whether the master excibited the same behaviour I cannot tell for certain. If it did, the locks were taken for a short period of time. The master has three times as much disk space, which could have made the locked time shorter.

How to repeat:
This is how I did this:

1. Create a giant table, testtable, that almost fills up the harddrive.
2. Execute "OPTIMIZE TABLE testtable" while running SELECTs against another table on the same database.
3. Notice the status of the queries using "SHOW PROCESSLIST;".

Suggested fix:
1. (easy) For MySQL documentation writers: Document that an OPTIMIZE TABLE can make a _global lock_ lock on all tables in a databas, if the OPTIMIZEd table is very big.
2. For MySQL developers: Make no global locking of a database.
3. For users: Take your database out of production when executing OPTIMIZE TABLE.

Hi !

MySQL 5.1 does not lock entire database when optimizing a single table within it. Regardless of whether it is MyISAM or InnoDB SE.

There are two possible causes of what you see. Your other queries involved a table that was optimized, in which case they had to wait on locked table.

Much more plausible explanation is that there was that relatively little space was left on disk and that a filesystem mounted on that partition was highly fragmented. In such a case and especially when I/O load is above 90 % , operating system becomes very slow in opening tables. Do not forget, also, that 5.1 has the bug that lock on the table is held during entire time that it takes to remove it from filesystem. This was fixed in 5.6.

I have tried your case on a partition with over 500 Gb free and could access other tables without any problems.

Sinisa,

Thank you for taking your time to look into this. See answers inline below.

> There are two possible causes of what you see. Your other queries involved a table that was optimized, in which case they had to wait on locked table.

This was not the case. None of the blocked SELECTs touched the table that I was optimizing. 

> Much more plausible explanation is that there was that relatively little space was left on disk and that a filesystem mounted on that partition was highly fragmented. In such a case and especially when I/O load is above 90 % , operating system becomes very slow in opening tables. Do not forget, also, that 5.1 has the bug that lock on the table is held during entire time that it takes to remove it from filesystem. This was fixed in 5.6.

Very well, it must have been the fragmented file system then. This might explain why I did not notice the blocked SELECTs on the master database.

> I have tried your case on a partition with over 500 Gb free and could access other tables without any problems.

Thank you for taking your time to look into this.

For your own education do two more things.

1) Check whether queries on tables from other databases work or not ... do make sure that other databases are on the same partition /  filesystem.

2) Create a database (manually) on some other (mostly with ample space) partition / filesystem and symlink it to datadir. Create some tables there and try running queries while you run OPTIMIZE on the table in your original database.