MySQL RDS: Drop formerly huge table without freezing DB?

Question

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.

Answer 1

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 !!!

Answer 2

It's true that the InnoDB buffer pool may still contain pages for the table you want to drop. When you drop a table, MySQL has a tendency to "lock" the buffer pool as it scans for pages associated with that table. The larger your buffer pool, the longer this scan takes. RAM is fast, but it's not infinitely fast.

But after your queries stop using data from that table, over time the pages in the buffer pool associated with that table are replaced by other query activity requesting pages from other tables. This is a gradual process.

What I've done at past jobs is when I want to drop a large table, I instead RENAME TABLE to move the table to another schema, for which the applications don't have privileges. This ensures any applications that used to access this table won't be able to find it.

Then wait a while for the buffer pool to gradually evict the pages for that table id.

We used a delay of 7 days, then an automatic job would check once per day and delete any tables in that "pending drops" schema.

By then, we assumed the buffer pool had recycled all the pages for that table, and then dropping it would be quick.

Was 7 days long enough? Was it too long? It depends on a lot of factors, like how large the table is, how much of that table was occupying pages in the buffer pool, how fast do other queries cause the idle pages to become evicted, etc. There's not a good way to predict how long it will take precisely.