Let it go to completion!

InnoDB does a lot of things to speed up execution. One of them is to delay DELETEs via the Change Buffer (research that). It essentially writes changes (inserts, updates, deletes) to the Change Buffer, then eventually gathers those changes (in a more I/O efficient way!) together to write to disk. The effect is two-fold:

• Your DELETE finishes much faster. That is, you get control back from the DELETE query must sooner.
• Less I/O needs to be done.

Meanwhile, the rows are DELETEd as far as any query can tell. It is just that not all the work has finished yet.

Alternatives

When you need to delete "most" of a table, it is faster (in all respects) to copy over what you want to keep. Something like this:

CREATE TABLE new LIKE real;
INSERT INTO new
    SELECT * FROM real WHERE NOT (delete-expression);
RENAME TABLE real TO old,
             new TO real;
DROP TABLE old;

More big-delete tips

Let it go to completion!

InnoDB does a lot of things to speed up execution. One of them is to delay DELETEs via the Change Buffer (research that). It essentially writes changes (inserts, updates, deletes) to the Change Buffer, then eventually gathers those changes (in a more I/O efficient way!) together to write to disk. The effect is two-fold:

• Your DELETE finishes much faster. That is, you get control back from the DELETE query must sooner.
• Less I/O needs to be done.

Meanwhile, the rows are DELETEd as far as any query can tell. It is just that not all the work has finished yet.

Tuning tips

innodb_buffer_pool_size should be about 70% of available RAM.

Alternatives

When you need to delete "most" of a table, it is faster (in all respects) to copy over what you want to keep. Something like this:

CREATE TABLE new LIKE real;
INSERT INTO new
    SELECT * FROM real WHERE NOT (delete-expression);
RENAME TABLE real TO old,
             new TO real;
DROP TABLE old;

More big-delete tips