Let's say I have a reasonably large (100GB) INNODB table on a 3-node Percona Xtradb Cluster that uses innodb_file_per_table=1 and has about 80GB buffer_pool per node and uses ext4. I want to drop this large table without causing any global lock. I use this approach:

  • I delete all the rows from the table with pt-archiver. At this point the table is empty, but the innodb file for that table is still ~100GB.

  • I issue a DROP TABLE tablename

Will I get any global lock due to either ws-rep or the fact that Innodb has to go through the LRU list and discard the pages which belong to this tablespace?

  • Next time, do a rolling DROP TABLE. – Rick James Oct 9 '17 at 20:47
  • I'm asking this before doing it. – paetor Nov 16 '17 at 15:53
  • If you do it rolling (a la RSU ALTER), you get to see how long it takes without hurting production. – Rick James Nov 16 '17 at 22:12

What version of MySQL are you using?

I'd do a RENAME table then delete rows in chunks (something like DELETE FROM TABLE LIMIT 5000; in a loop) once it's empty you should be able to drop it.

Adjust the LIMIT 5000 up or down depending on how long that operation takes, slave replication falls behind, or it's affecting other queries performance.

If it's an empty table then no, it shouldn't cause a long lock.

| improve this answer | |

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.