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.
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.
While using the thread pools with Azure Database for MySQL, we see improved and more stable performance.
There are some scenarios in which using a thread pool is likely to be less efficient, and therefore, their use is not recommended.
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:
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:
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.
Thank you!
Amol Bhatnagar
Program Manager - Microsoft
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.