4

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!

2

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.

  • Thanks for the tip, I'll definitely check those solutions out and update if we end up going with one. I was hoping, ideally, for more of a "best practice" type suggestion that doesn't rely on external tools, but I understand that that might not be possible... – stymo Sep 16 '13 at 17:40
  • Innodb locks table for alter. So there is nothing you can do about it. There is one neat method using slave, described here 37signals.com/svn/posts/… – netneladno Sep 16 '13 at 17:52
  • There is some article that addresses this: lxadm.com/… – swdon May 18 '18 at 9:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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