Home / server / Questions / 558693
Accepted
Ztyx
Asked: 2013-12-03 07:14:09 +0800 CST

How to drop an 8 GB InnoDB table smoothly without global locks

  • 772

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.

mysql

1 Answers

  1. Best Answer

    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.

    • 4