By default, for every client connection the MySQL server spawns a separate thread which will process all statements for this connection. This is the ‘one-thread-per-connection’ model. It’s simple and efficient until some number of connections N is reached. After this point performance of the MySQL server will degrade, mostly due to various contentions caused by N threads that are trying to access shared resources: either system ones like CPU, IO, memory or MySQL specific: structures/locks/etc. To keep the system stable and avoid degradation in the performance we need to limit the number of active threads, and at the same time we do not want to limit number of the client connections. The ‘Thread Pool’ model helps us to achieve that. It allows mapping N client connections to M number of active threads (actually performing work) while demonstrate a smooth and stable throughput for the MySQL server.
There are several implementations of the Thread Pool model:
– Commercial: Oracle/MySQL provides thread_pool plugin as part of the Enterprise subscription
– Open Source: The MariaDB thread_pool developed by Vladislav Vaintroub
In Percona Server we have included the latter, yet we further enhanced and improved it.
To demonstrate how thread pool may help to improve scalability, we run sysbench/OLTP_RW workload up to 16,384 threads with the latest MySQL server, Percona Server and Percona Server with thread_pool setup for IO and CPU bound load on a Dell R720 server with 16Cores/32vCPU.
The current thread pool implementation of Percona server is built into the server, unlike Oracle’s commercial version which is implemented as a plugin. In order to enable Thread Pool with Percona Server, you simply need to specify ‘thread_handling=pool-of-threads’ in the my.cnf file (before startup/restart) and also adjust the number of thread_pool groups with the ‘thread_pool_size’ variable. You can do the latter after a server start. In our runs with thread_pool we used ‘thread_pool_size=36’.
As you can see in both scenarios above, after 1024 threads, the standalone server is not really capable with keeping throughput at the same level. However, with thread_pool enabled the throughput is quite stable and smooth up to 16384 client connections.
Conclusion: if you regularly go over > 512/1024 connections, it is definitely worth trying Percona’s thread pool implementation to protect your server from serious performance degradations due to server overload.
UPDATE: updated chart for IO bound scenario that includes results for regular MySQL server, MySQL server with innodb_thread_concurrency=36, Percona Server, Percona Server with innodb_thread_concurrency=36 and Percona Server with thread_pool_size=36:
Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.
Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below and we’ll send you an update every Friday at 1pm ET.