TL&DR: Depending on the workload, the thread pool plugin can cause serious performance drops.
This post was motivated by two recent cases I’ve worked with. Two setups running in cluster mode: one in MariaDB+Galera and one with Percona XtraDB Cluster (Percona Server+Galera). In both cases, the clusters had the thread pool plugin enabled and were serving requests coming from ProxySQL. Pretty standard setups, nothing odd. In each situation, I’ve seen huge drops in the performance where the QPS were going to almost zero.
When analyzing the workload, I found that the performance drop was caused by applications executing some very long queries like UPDATE affecting many rows, INSERT….SELECT without limit clause and other similar DMLs.
After removing the thread pool plugin from the clusters, the performance was more steady, even when running these queries. We decided to run a few benchmarks to understand how the thread pool affects long-running transactions.
The method:
When thread pool should be used in a nutshell: CPU bound workload, i.e., a lot of short running queries with very high concurrency.
For this reason, I’ve set up a server with the following specs:
- r6a.2xlarge ec2 VM (8 vCPU – 64GB of RAM)
- 1TB gp2 drive (roughly 3k IOPS)
- Percona Server 8.0.34-26
- my.cnf:
1 2 3 4 5 6 7 8 9 10 11 12 13 | [mysqld] innodb_buffer_pool_size=50G innodb_buffer_pool_instances = 8 innodb_flush_log_at_trx_commit=2 innodb_flush_method=O_DIRECT innodb_io_capacity_max=10000 innodb_log_file_size=4G #thread_handling=pool-of-threads #thread_pool_size=8 max_connections = 500 thread_cache_size = 500 table_definition_cache = 10000 table_open_cache = 10000 |
A basic configuration with nothing remarkable. This server is not intended to be a perfectly tuned one but just a baseline to compare the behavior under different concurrency configurations.
We executed two rounds of benchmarks using sysbench:
– created five tables with 10MM rows each, roughly 12GB of data (meaning the dataset fits completely in memory)
– executed the following script to capture metrics after warming up the buffer pool:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | #!/bin/bash for NUM_THREADS in 32 128; do pmm-admin annotate "Number of threads: $NUM_THREADS - no-thread-pool - innodb_thread_concurrency=8 - long queries" --tags "$NUM_THREADS" sysbench /usr/share/sysbench/oltp_read_write.lua --threads=$NUM_THREADS --report-interval=10 --time=600 -reconnect=100 --mysql-user=sysbench --mysql-password=sysbench --mysql-host=localhost --mysql-db=sbtest --reconnect --tables=5 --table-size=10000000 run sleep 120 done |
– the script was executed in three rounds, with three changes in configuration:
- innodb_thread_concurrency=0 (default)
- innodb_thread_concurrency=8
- thread pool enabled
– one important setting used in the script is the –reconnect flag that forces the connections to be closed and reopened at every x events. This is especially important when the thread pool is used because it is where it should outperform the one-thread-per-connection setting.
The goal was to saturate the CPU while increasing concurrency and analyze how performance behaves with the different ways of handling high concurrent scenarios in MySQL using different algorithms.
Baseline numbers
Default configuration (innodb_thread_concurrency=0)
innodb_thread_concurrency=8
thread pool enabled
Generally speaking, the maximum amount of QPS were around 70k, and the best performance under high concurrency was provided by innnodb_thread_concurrency=8. It’s slightly better than the other two configurations, and at the same time, I haven’t observed significant differences in CPU utilization.
Now, let’s make the workload a little uglier.
– I’ve executed an additional sysbench in the background while the main sysbench was running to introduce some extra noise of random queries that execute read/write OLTP load within short events:
1 2 | while true; do sysbench /usr/share/sysbench/oltp_read_write.lua --table-size=10000000 --events=2000 --mysql-user=sysbench --mysql-password=sysbench --mysql-host=10.10.10.161 --mysql-db=sysbench2 --num-threads=$((1 + $RANDOM % 3)) run > /dev/null 2> /dev/null & sleep $(echo "scale=2; $RANDOM/32768" | bc); done |
And then manually introduced two queries to create and fill a table and update another non–related table, knowing these actions will be performance killers because they run full table scans:
1 2 3 4 5 6 7 8 9 10 11 12 | mysql> CREATE TABLE `sbtest43` (`id` int NOT NULL AUTO_INCREMENT, `k` int NOT NULL DEFAULT '0', `c` char(120) NOT NULL DEFAULT '', `pad` char(60) NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `k_4` (`k`) ) ENGINE=InnoDB; mysql> insert into sbtest43 (select * from sbtest1); Query OK, 10000000 rows affected (5 min 9.68 sec) mysql> update sbtest5 set k=k+1; Query OK, 10000000 rows affected (32.00 sec) Rows matched: 10000000 Changed: 10000000 Warnings: 0 |
Metrics with long-running queries
Default configuration (innodb_thread_concurrency=0)
innodb_thread_concurrency=8
thread pool enabled
As expected, we’ve observed a drop in performance on average in the first two scenarios with the thread pool disabled. Adding the INSERT…SELECT and UPDATE queries, the performance degradation was around 15%.
But with the thread pool enabled, the results are really scary. The performance impact is roughly 25% when the INSERT….SELECT and UPDATE queries kicked in. However, in a sort of random way we can see a huge performance drop with queries per second going to almost zero with no other load than the random sysbench while loop. In addition, while this issue was happening, the CPU utilization went down too.
Some quick conclusions:
- Personally, I’ve never been a big fan of the thread pool because I think it may work only under certain very specific circumstances (i.e., CPU-bound workload with super fast and optimized queries).
- The thread pool dispatcher seems to be the bottleneck and is highly affected by the execution time of queries being processed, even if forcing a reconnection. So eventually, this can cause a huge performance drop even if the server looks idle.
- When being CPU-bound, it seems best to configure MySQL by limiting the concurrency through innodb_thread_concurrency parameter.
- Additionally, innodb_thread_concurrency is a dynamic parameter, so finding a sweet spot for configuration is easier than using the thread pool plugin that requires a restart.
- For setting up a pool of threads to handle massive amounts of connections from applications I’d rather use 3rd party tools like ProxySQL or application native drivers like Hikari.
