Scale the purge effort according to the workload

InnoDB in MySQL 5.6 (and MariaDB 10.0) introduced multiple inodb_purge_threads (4 by default).

When there are few modifications that require purging, the purge threads will either be mostly sitting idle, or they will form ‘thundering herds’ whenever some work turns up. In such a scenario, it would be better to have only one purge worker.

With this work, the poorly working parameter innodb_max_purge_lag should be revised or removed as well. The logic behind that parameter does not take into account the actual number of purgeable records; committed modifications cannot be purged while old read views exist that can access the old data.

Benchmarking is the key

This work must involve creating a benchmark to measure purge performance under various conditions:

1. Ensure that everything has been purged (history list length is 0).
2. Block purge by issuing START TRANSACTION WITH CONSISTENT SNAPSHOT from a new connection.
3. From other connections, perform modifications that create workload for purge. Try different variations: tables with many indexes, many small transactions on many tables, large transactions on a single table.
4. Commit or disconnect the connection that blocked purge.
5. Measure the time to reach History list length 0 in SHOW ENGINE INNODB STATUS. (Some workaround for the batching logic related to innodb_purge_rseg_truncate_frequency may be needed.)
6. Alternatively, measure the time to shutdown with innodb_fast_shutdown=0. But be sure to separate the impact of innodb_change_buffering. (Slow shutdown runs both purge and change buffer merge to completion.)
7. We might also want to run with innodb_force_recovery=2 to disable the purge altogether, and then restart without that parameter and initiate a slow shutdown, to measure how long it takes to process all the undo log that was accumulated.