drop big table freeze server

Question

I am using amazon rds mysql 5.6 master instance and 4 slaves. there is big table with 800 GIB size that i want to drop it for releasing space. But when i execute drop command, the server entering to "freeze mode" and cant execute any kind of commands.

similar issue : https://bugs.mysql.com/bug.php?id=44757

I need to remove it from production rds.

• there is no transactions on this table
• innodb_file_per_table = ON

I though about Deleting rows in chunks for not create big lag for slaves and then (optional optimize) drop the table.

Answer 1

What exact version?

DELETE on a huge table will take a huge amount of time because it is building an "undo" log in case of a crash. Don't use that technique.

What was the value of innodb_file_per_table when the table was built?

• If OFF (or 0), the table is in ibdata1, which will not shrink and return space to the OS. It will only free up blocks for subsequent use.
• If ON (or 1), then DROP TABLE will be much faster, but still not instantaneous.
• If you have FOREIGN KEYs connecting the table (in either direction), well, let's see them. (SHOW CREATE TABLE)

If you need to delete a big part of a table, here are several techniques. This discusses how to do the delete in chunks, and takes Replication issues into account.