MySQL RDS: Drop formerly huge table without freezing DB?

[Alberto]

I am using MySQL 5.6 in AWS RDS. I want to drop a table that used to have 8 billion rows.

Hearing that dropping a large table can freeze the database, I instead chose to delete the rows using a loop. This took a couple of days, but the table is now empty. RDS was using 95% (or more) CPU during deletion, but it didn't seem to slow down regular usage.

It now seems a no-brainer that I can go ahead and drop the empty table, but I guess I'm worried that whatever... stuff...buffers?...logs?... might still be around and could cause issues.

[rosemadder]

This is a classic problem in any environment (cloud, VMWare, bare-metal)

What you need to do you are already doing: Delete the rows in the table in chunks but you need to delete all the rows. Why ???

The data and index pages (each 16K) in the InnoDB Buffer Pool carry the tablespace ID reference to the table they came from. When you drop am InnoDB table, are the data and index pages are checked to see if they are in the InnoDB Buffer Pool and are up-to-date. Those pages must be invalidated in the InnoDB Buffer Pool if they are there. That can take time as you already surmised.

Once all 8 billion rows are deleted, drop the table should be faster.

You have already done your due diligence in this are.

What to do next ???

SUGGESTION #1 : Truncate the Table

TRUNCATE TABLE mygianttable;

SUGGESTION #2 : Make the table BLACKHOLE

ALTER TABLE mygianttable ENGINE=BLACKHOLE;
DROP TABLE mygianttable;

SUGGESTION #3 : Restart MySQL with empty InnoDB Buffer Pool, and Drop Table

• Go to DB Parameter
• Set innodb_buffer_pool_dump_at_shutdown OFF
• Set innodb_buffer_pool_load_at_startup to OFF
• Restart RDS Instance (Starts with Empty Buffer Pool)
• Drop the Table
• Set innodb_buffer_pool_dump_at_shutdown to OriginalValue
• Set innodb_buffer_pool_load_at_startup to OriginalValue

Give it a Try !!!