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?
Best Answer
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.