Part of the InnoDB duties, being an MVCC-implementing storage engine, is to get rid of–purge–the old versions of the records as they become obsolete. In MySQL 5.1 this is done by the master InnoDB thread. Since then, InnoDB has been moving towards the parallelized purge: in MySQL 5.5 there is an option to have a single separate dedicated purge thread and in MySQL 5.6.2 one can have multiple dedicated purge threads.
Percona Server 5.1 supports multiple purge threads too, although using more than one is considered experimental at the moment. Unfortunately this patch hasn’t been ported to Percona Server 5.5 yet.
Let’s test these two implementations and find out what benefits, if any, do the additional purge threads bring.
The test workload makes a long history list and then lets purge thread(s) work through it while having a regular OLTP load on the server. The OLTP part of this is provided by Sysbench, a single table workload, 16 client threads. The growth of the history list and the subsequent purge work is ensured by a long-running transaction with a REPEATABLE READ isolation level and started WITH CONSISTENT SNAPSHOT. In the middle of the workload this transaction commits and so the purge begins.
From these results we can see that the multiple purge threads achieve their goal of processing the history list faster. Of course, this is achieved at the cost of lower TPS. There is one possible area for improvement: once the purge threads fully catch up with the history list (e.g. at around 2800 seconds in 4 thread experiment), their activity could be throttled better as to not to penalize further TPS.
Now let’s test MySQL 5.6.2. It is important to remember that 5.6.2 multiple purge thread support is very experimental and is likely to receive a lot of tuning in the future. In fact, as of the time of this writing, the code is already different in the trunk.
Huh? The dedicated purge threads in 5.6.2 completely fail to stop the history list growth, but at least the additional threads do not penalize TPS. On the other hand, TPS does slightly drop with time in the second half of the experiments.
Here it becomes very clear that there are no significant differences caused by varying the number of dedicated purge threads. The workload is single-table, and the InnoDB team advises using just one thread for such workloads. On the other hand Percona Server 5.1 implementation is effective in this setting with multiple threads too.
The ineffectiveness of 5.6.2 here has been found by Dimitri Kravtchuk before, and the linked post suggests a fix: in 5.6.2 the purge activity is interspersed with short sleeps and apparently those sleep delays are too long, so let’s try not sleeping at all. This is a quick and dirty change and by no means a replacement to proper future 5.6 tuning.
I’ve made a tiny change along these lines (the patch can be found at the Benchmark Wiki with the rest of the scripts and results) and here are the updated 5.6.2 results.
We can see that the change has made difference and that the purge thread started working through the history list, at the cost of TPS.
Multiple dedicated purge threads even with the change are not effective enough: they only slow the growth of the history list. But, TPS in the second half of the experiments is stable.
Again, we can see that there is no difference in history list length or TPS caused by the number of dedicated purge threads, as long as that number is more than one.
To conclude, the only viable–at least for the tested setting–multiple purge thread implementation at the moment seems to be the one in Percona Server 5.1. MySQL 5.6.2 multiple purge threads have major issues and just removing the purge sleeps is not enough to solve them. However, MySQL 5.6.2 is experimental code and I am sure that next MySQL 5.6 versions will contain a fixed and much better performing implementation, which I am looking forward to.