Each query in MySQL is running as its own transaction, assuming you didn't change this default configuration, and unless you're starting a transaction manually and running multiple deletes in that one transaction.
Because every query is a transaction, MySQL has to save the data being deleted in case of a rollback. Large deletes means saving a TON of data for that potential case.
Additionally, deletes cause a LOT of writes to the binary log. When the delete completes, the query/results of the delete are committed to the binary log, which can take time.
This means that even after the delete query is done, the database may not 'recover' from it for a while.
Many, smaller deletes spread over time as you have in code definitely helps!
However, as mentioned above, there are side effects to large deletes to know about outside of code.
We hired Percona to help us as this issue was locking up a HUGE rds instance we have (with many databases) when we had to delete many millions of rows across those databases.
Here's what we set.
NOTE: Don't blindly copy/paste these. They each have pros and cons. Percona get us recommendations based on our specific usage patterns and database size.
They're worth hiring for at least a one-time review in exchange for a few thousand bucks.
- innodb_log_file_size = 2G
- innodb_flush_log_at_trx_commit = 2
- could lose up to 1 second of data in a server crash
- innodb_autoinc_lock_mode = 2
- Fastest for inserting, can be downsides when replicating or rolling back
- https://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html
- binlog_row_image = minimal
- sync_binlog = 0