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.

  • What if you TRUNCATE TABLE first? – Michael - sqlbot Nov 4 '18 at 17:42
  • i tried , the server stacking . becouse of the high amount of data – Stas Rozen Nov 6 '18 at 14:53

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.

| improve this answer | |
  • Hey , MySQL 5.6.39 , innodb_file_per_table = 1 when table created , it dont have any foreign keys . already checked this one mysql.rjweb.org/doc.php/deletebig – Stas Rozen Nov 6 '18 at 14:57
  • Alas, I am town to "clutching at straws". RDS has a bunch of different things going on in the OS; the only "big" step in DROP TABLE (when file_per_table) is actual releasing of all the blocks to the OS. So, I guess this is an RDS problem. (I know MySQL, but not RDS.) – Rick James Nov 6 '18 at 15:24

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.