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.

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!

share|improve this question
add comment

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

share|improve this answer
    
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
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.