MySQL: Alter Table on very active table gives “Waiting for table metadata lock”

best practicesdeadlockhigh-availabilitylockingmysql-5.5

At my work, we've recently upgraded to MySQL 5.5 and since then, have been bitten a few times by the new (and intended) behavior of metadata locking. Basically, whenever we run an ALTER TABLE statement on a fairly active table, the table becomes inaccessible to both reads and writes and threads are shown hanging, basically forever, with the "Waiting for table metadata lock" message.

I've done a bit of research and found good explanations of this in the MySQL manual and here and see that this is an expected and actually intended behavior. What I haven't found anywhere online is a graceful way to mitigate the obvious problems this causes in production. The only way we have been able to run alter table statements on our more active tables since upgrading is by disabling read and write permissions for any usernames our application runs under, killing all processes that are still running, and then running the ALTER TABLE statements. This works, but results in many errors from our app: both from background processes and our web site.

In the past (ie before 5.5) schema changes were no problem. I understand the reason for the new behavior, but it causes obvious problems in production and I figure folks in the community must have work arounds.

Any ideas would be appreciated. Thanks!

Best Answer

You can achive nonbloking AlTER TABLE using tools like http://www.percona.com/doc/percona-toolkit/2.2/pt-online-schema-change.html and http://openarkkit.googlecode.com/svn/trunk/openarkkit/doc/html/oak-online-alter-table.html

They are both tested in production and reliable. Percona's tool is very smart about checking slave lag. Also checkout other percona tools, they are very handy.

OPTION #1

Keep the Read DBVIP on the Slave

OPTION #2

Keep the Read DBVIP on the Master
When you must do an ALTER TABLE, move the Read DBVIP to the Slave
When ALTER TABLE has been completed, move the Read DBVIP to the Master

OPTION #3

Setup Read DBVIP on a LoadBalancer
Remove Master from LoadBalanced DBVIP on demand

Option #1 seems to be the simplest way in the long run.

Mysql – Changing the thesql.proc table directly after upgrade

Here is a suggestion: do a bait-and-switch.

ALTER TABLE mysql.proc RENAME mysql.proc_orig;
CREATE TABLE mysql.proc LIKE mysql.proc_orig;
ALTER TABLE mysql.proc MODIFY comment TEXT;
INSERT INTO mysql.proc SELECT * FROM mysql.proc_orig;
FLUSH TABLES;

CREATE TABLE mysql.proc_orig LIKE mysql.proc;
ALTER TABLE mysql.proc_orig MODIFY comment TEXT;
INSERT INTO mysql.proc_orig SELECT * FROM mysql.proc;
ALTER TABLE mysql.proc RENAME mysql.proc_bckp;
ALTER TABLE mysql.proc_orig RENAME mysql.proc;
ALTER TABLE mysql.proc_bckp RENAME mysql.proc_orig;
FLUSH TABLES;

If the Stored Procedures work, you can drop mysql.proc_orig or keep it as a backup.

If not, undo everything as follows:

DROP TABLE mysql.proc;
ALTER TABLE mysql.proc_orig RENAME mysql.proc;

Give it a Try !!!

CAVEAT : The table INFORMATION_SCHEMA.ROUTINES for MySQL 5.5 should have ROUTINE_COMMENT as LONGTEXT. Make sure the contents of mysql.proc and INFORMATION_SCHEMA.ROUTINES match.

Since you cannot manipulate INFORMATION_SCHEMA.ROUTINES, If you want to feel safe about this, just restart MySQL.

Best Answer

Related Solutions

Mysql – Avoiding “Waiting for table metadata lock” when `ALTER TABLE DROP PARTITION`

OPTION #1

OPTION #2

OPTION #3

Mysql – Changing the thesql.proc table directly after upgrade

Related Question