The previous Part 1 focused on explaining the idea of thread pool, configuration options and analysis of thread pool efficiency in Percona Server for MySQL 9.7.1-1 against the base line, which is the same server without thread pooling.
We recommend reading the post with Part 1 before proceeding with reading Part 2 in the current post.
For better understanding of Percona Server 9.7.1-1 and MySQL 26.7.0 / 9.7.2 thread pool performance against each other we will start with a brief look at MySQL on its own.
The test runs did not find any significant differences in the performance of the thread pool between MySQL Server 26.7.0 and 9.7.2:
Graph 1 – Comparison of MySQL 26.7.0 and 9.7.2 [ INTERACTIVE GRAPH ][ TABLE ]
As can be seen on the Graph 1 – the variation in performance between the versions is minimal. Configurations without thread pool go extremely close to each other, same as with the thread pool.
Lines that correspond to similar configurations converge to the same values across all server buffer tiers (2G / 12G / 32G) and across thread pool parameter ranges (thread_pool_size=[10/20/40/80/120/160] and thread_pool_oversubscribe=[2/3/4]).
NOTE: MySQL 26.7.0 is very slightly faster than 9.7.2 in the high connection threads count.
Therefore, to avoid the crowded graphs the data and performance comparisons will be presented for MySQL Version 26.7.0 only. The results for MySQL 9.7.2 can be viewed on the interactive graph page on GitHub.
Graph 2 – MySQL with 2G buffer [ INTERACTIVE GRAPH ][ TABLE ]
The thread pool implementation behaves differently in MySQL Server 26.7.0. Although it does not bring a huge performance boost as it was in Percona Server, the buffer pool for a high number of client connection threads (640 / 1280 / 2560) helps to keep TPS performance better than without the thread pool. Especially with 2560 connections the pooled TPS is three times the number of non-pooled TPS: 371 and 122 respectively.
The selection of the optimal value for thread_pool_size is important. The value itself (10) looks the same as it was with Percona Server. In the same way, higher values for thread_pool_size (20 / 40 / 80 / 120 / 160) do less TPS improvements.
Unlike Percona Server the oversubscription variations do not impact TPS much when innodb_buffer_pool_size=2G. However, thread_pool_oversubscribe=2 does slightly better than 3 or 4.
Graph 3 – MySQL with 12G buffer [ INTERACTIVE GRAPH ][ TABLE ]
Increasing buffer pool (innodb_buffer_pool_size=12G) brings the overall TPS performance up, but thread pool makes less impact at the highest number of connections: 1273 with thread pool and 718 without (TPS is improved by 77%).
With the fully buffered data set (innodb_buffer_pool_size=32G) MySQL thread pool configuration managed to achieve better TPS performance than non-pooled configuration.
Graph 4 – MySQL with 32G buffer [ INTERACTIVE GRAPH ][ TABLE ]
The pooled configuration yielded 18,975 TPS against 17,011 for non-pooled server configuration, which is 11.6% win for the thread pool.
The p95 latency in MySQL had a very similar characteristic to Percona Server with thread pool reducing the latency from 759ms down to 153 ms.
Finally, we arrived at the direct TPS comparison.
Let’s start with innodb_buffer_pool_size=2G:
For the comparison we will pick the best performing thread pool configurations for Percona Server and MySQL Server along with baseline (thread pool disabled) configurations. It was established before that thread_pool_size=10 works best for both servers (TP 10 on the graph), but the oversubscription 4 (os4 on the graph) works better for Percona Server while the optimal oversubscription for MySQL Server is 2 (os2 on the graph).
Graph 5 – Percona Server and MySQL with 2G buffer [ INTERACTIVE GRAPH ][ TABLE ]
MySQL Server starts well, winning with 40 connections, but we do not consider thread pool efficiency to be good with low number of connections.
At 2560 connections mark the Percona Server implementation of the thread pool helps it to produce more TPS than MySQL server by a factor of 6.2 (2,304 against 371 TPS).
Graph 6 – Percona Server and MySQL with 12G buffer [ INTERACTIVE GRAPH ][ TABLE ]
Again, at a lower number of connections (40 and 80) MySQL Server goes faster than Percona Server.
Moving to the high number of connections (2560) with innodb_buffer_pool_size=12G the Percona thread pool performs better than MySQL.
On the next graph corresponding to innodb_buffer_pool_size=32G, the best performing configurations are different for Percona Server (thread_pool_size=160/thread_pool_oversubscribe=2) and MySQL Server (thread_pool_size=10/thread_pool_oversubscribe=2). It looks that in Percona Server increasing innodb_buffer_pool_size requires increasing thread_pool_size for the thread pool to work optimally.
Graph 7 – Percona Server and MySQL with 32G buffer [ INTERACTIVE GRAPH ][ TABLE ]
In the tier of fully buffered data (innodb_buffer_pool_size=32G) both servers performance was close, but MySQL Server was slightly faster. When it comes to the thread pool, MySQL is a clear winner with 19,054 TPS against 15,519 TPS from Percona Server (22% difference).
With regard to p95 the Percona Server showed slightly better (lower) latency than MySQL server in 2G and 12G buffer tiers. However, with 32G the MySQL Server is ahead of Percona Server:
Graph 8 – p95 latency in Percona Server and MySQL with 32G buffer [ INTERACTIVE GRAPH ][ TABLE ]
With disabled thread pool the latencies for both Percona Server and MySQL land close to each other with MySQL being slightly better (shorter).
When thread pool is enabled the p95 latency in MySQL is notably better (by ~25%) with 177ms against 235ms in Percona server.
Despite sharing the base server code, the thread pool implementations in Percona Server for MySQL and MySQL are very different. When the data set is too big to be fully buffered the Percona Server thread pool shows better performance compared to MySQL Server. However, MySQL Server and its thread pool wins with the configurations when data is fully buffered. Also, in the fully buffered scenario the p95 latency in MySQL Server is slightly shorter than in Percona Server.
Another important observation is that the thread pool inside Percona Server for MySQL is very sensitive to the number of thread groups (thread_pool_size) and the number of active threads in the same group (thread_pool_oversubscribe). These options had the biggest impact on the performance and setting optimal values is important to reach the full potential of the thread pool.
With MySQL Server change of these parameters has less dramatic effect. Also, the noticeable benefit of thread pooling in case of MySQL is visible only on high connection numbers such as 1280/2560 while Percona Server implementation does it much earlier with 80+ connections.
