Deleting old data in MySQL becomes slower over time

Question

I have an application running on Amazon MySQL/RDS that needs to maintain 2 weeks of transactional history after which time data can (usually) be purged. Because this isn't a hard rule I cannot use partitioning by day or week as a means to delete old data most efficiently.

There are several tables that need purging, many of them have multiple indexes and foreign key constraints (parent/child).

I'm deleting data in chunks (1000-3000 rows at a time) and committing after each chunk. After a set number of chunks have been deleted the purge code pauses for a set amount of time.

My issue is that the process is very efficient for 10-15 minutes, after which I begin to see large delays in deleting data chunks. I believe my SQL is as efficient as it can be. Where in MySQL can I look to better understand the bottleneck? If I stop my code and wait 15-20 minutes the delete performance is restored for another 10 minutes.

Answer 1

I would look to the InnoDB redo log file size.

The symptoms you describe is typical if you fill up the redo log with changes, which forces a "synchronous flush" — MySQL blocks further changes until it can free up a portion of the redo log by flushing dirty pages from the buffer pool.

RDS used to use an absurdly small redo log file size by default, 128M if I recall. For years they did not allow changing the size. But in the last couple of years they do allow changing it.

Here's how to check the size of your redo log file in megabytes:

mysql> SELECT @@innodb_log_file_size / 1024 / 1024;

To change it, I think you'd use the RDS parameter groups UI, then restart your RDS instance to apply the change.

---

To monitor this, I'd watch the number of bytes written to the redo log:

mysql> SHOW GLOBAL STATUS LIKE 'Innodb_os_log_written';

Measure that every 10 minutes or so, and plot it. The redo log files are of fixed size, and writes will eventually reach the end and wrap around to the beginning of the file. They must not overwrite changes in the log that represent dirty pages in the buffer pool, so before they get close to doing that, MySQL forces a synchronous flush.

So you can watch the rate of Innodb_os_log_written, by reading that variable periodically at even intervals. Compare this rate of log writes to the log file size (remember that you have two redo log files by default, so your redo log capacity is Innodb_log_file_size * 2).

This allows you to estimate "we overwrite the whole redo log file(s) every N minutes." This should correlate (roughly) to your 10-15 minutes time period when deletes are fast.

I think I recall there are some nuances to this calculation ... the Innodb_os_log_written might include some overwrites, i.e. some writes seek backwards to re-write a block under some circumstances. So there might be some cases where the numbers don't add up. I don't know deep details here.

In any case, InnoDB has long been known to be better able to handle heavy write workload if you increase the size of redo logs. It's tempting to increase it as large as you are allowed, but this may be overkill for most of your day-to-day workload with more modest write traffic.

See also:

• https://www.percona.com/blog/2011/04/04/innodb-flushing-theory-and-solutions/
• https://www.percona.com/blog/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/
• https://www.percona.com/blog/2016/05/31/what-is-a-big-innodb_log_file_size/

Answer 2

It depends on how you do the DELETEs. If each DELETE scans from the start of the table, stepping over rows that should not be deleted, then it gets slower and slower.

I discuss several techniques here for doing efficient deletes.

Some involve remembering where you left off, rather than starting over.