This article is contributed. See the original author and article here.
MySQL traditionally assigns a thread for every client connection. As the number of concurrent users grows, there is a corresponding drop in performance. Many active threads can impact the performance significantly due to increased context switching, thread contention, and bad locality for CPU caches.
Thread pools which is a server side feature and distinct from connection pooling, maximize performance by introducing a dynamic pool of worker thread that you can use to limit the number of active threads running on the server and minimize thread churn. This helps ensure that a burst of connections will not cause the server to run out of resources or crash with an out of memory error. Thread pools are most efficient for short queries and CPU intensive workloads, for example OLTP workloads.
You can configure a thread pool on Azure Database for MySQL service and gain the resulting performance benefits. This blog post describes different usage scenarios and explains how to enable the thread pool on Azure Database for MySQL.
Note: Thread pool feature is not supported for MySQL 5.6 version.
When using a thread pool is most efficient
Configuring thread pools is most efficient to scale the performance for your short chatty and CPU intensive workloads, for example OLTP workloads. Following are some of the scenario where you should configure and test enabling thread pool on your Azure Database for MySQL server.
- Large number of connections: when the performance drops with the increase of number of connections but you want to have a stable and predictable throughput and latency then thread pool will be efficient for you.
- Avoid out of memory scenario: It can also be efficient to use a thread pool for scenarios in which you can’t manage the number of connections to the server directly and you want to ensure that a burst of connections won’t create database resource issues or cause a server crash because of an out of memory error.
- Sysbench testing: If you plan to perform sysbench testing to evaluate the performance and scale of your workload, you should consider enabling thread pool at higher connection scales.
While using the thread pools with Azure Database for MySQL, we see improved and more stable performance.
* We ran sysbench against Azure Database for MySQL running on general purpose tier with 16 vCores (GP_Gen_16) for the above test.
When using a thread pool is not as efficient
There are some scenarios in which using a thread pool is likely to be less efficient, and therefore, their use is not recommended.
- Workload experiences long periods of inactivity followed by high activity: If your workload isn’t active for a long period, then is required for short periods to process very high activity by many users, using a thread pool may not work well.
- Workload with many long concurrent and non-yielding queries: If your workload expects queries which are long running and never waits (does not indicate waits to thread pool) then the thread pool will be less efficient for your workload.
Configuring thread pool on your Azure Database for MySQL server
To enable thread pool, update the thread_handling server parameter to “pool-of-threads“. By default, this parameter is set to one-thread-per-connection, which means MySQL creates a new thread for each new connection. Please note that this is a static parameter and requires a server restart to apply.
You can also configure the maximum and minimum number of threads in the pool by setting the following server parameters:
- thread_pool_max_threads : This value ensures that there will not be more than this number of threads in the pool.
- thread_pool_min_threads: This value sets the number of threads that will be reserved even after connections are closed.
To improve performance issues of short queries on the thread pool, in Azure Database for MySQL you can enable batch execution so that instead of returning to the thread pool immediately after executing a query, threads will remain active for a short time to wait for the next query through this connection. The thread then executes the query rapidly and when finished, the thread waits for the next query, until the overall time consumption of this process exceeds a threshold. The batch execution behavior is determined using the following server parameters:
- thread_pool_batch_wait_timeout: This value specifies the time a thread waits for another query to process.
- thread_pool_batch_max_time: This value sets the max time a thread will repeat the cycle of query execution and wait for the next query
To give some estimates of the performance improvement when using batch execution feature with Azure Database for MySQL, we ran benchmark with read-only simple queries mainly hitting the InnoDB buffer pool with sysbench. The following are the observations with or without batch execution.
Disclaimer: Please test thread pool before turning it ON in production.
Please reach out to the Azure Database for MySQL team at AskAzureDBforMySQL@service.microsoft.com if you have any questions.
Program Manager – Microsoft
Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.