5

MySQL version: 5.1.63.

I am about to DROP a bigger ~8 GB InnoDB table. Last week I dropped a four times bigger table on another machine and a global lock kicked in that took us down for ~90 seconds. I suspect it has to do with table_cache lock. Anyone with input on how to make this DROP more smoothly? DELETEs in small batches first, then TRUNCATE it and finally a DROP TABLE? Obviously I'd like to keep this databas in production if possible. I've also been considering making the buffer pool smaller before the drop.

I'd love to hear your input.

4

DROP TABLE always needs a few global locks. I would do exactly what you described in your question: delete rows as quickly as is safe (don't upset disk IO and various caches too much, be careful about replication as well), and then drop it.

  • That's what I did. Worked great. – Ztyx Jan 13 '14 at 15:35

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.