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.
Best Answer
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?OFF
(or0
), the table is inibdata1
, which will not shrink and return space to the OS. It will only free up blocks for subsequent use.ON
(or1
), thenDROP TABLE
will be much faster, but still not instantaneous.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.