Stack Exchange network consists of 183 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.
The test server, with magnetic disk, took 3 days of idle time to flush all changes from 14M deleted rows. I decided to test again with a server identical to our production, with more RAM and provisioned IOPS, and it took less than one hour. Not perfect, but good enough.
I tried both, min(id) or min(updated_date), it makes no difference. MySQL docs says the server flushes the buffer when idle or during a slow shutdown . I can't change the innodb_fast_shutdown on Amazon RDS, so the last resort is checking how long it takes to do it while idle.
That's what I'm doing, but that's not the problem. The problem is how the index affects other queries. Anyway, the 'purges' value doesn't work either. After deleting 6 million rows the min(id) query is taking 1s. I'll check how long it takes to catch up while idle. I wonder how pt-archiver works and how it solves the same problem, if it does.
