Thread pools enhance the scalability of the Azure Database for MySQL – Flexible Server. The graph below represents queries per seconds (QPS) for read only workloads which you can achieve on a General Purpose D16ds_v4 (16 vCores, 64 GiB memory) server with and without thread pools.
Using a thread pool allows you to support higher connections, extract more throughput, and overcommit the number of vCores available on the server to maximize performance, assuming the server has enough memory. For instance, the max_connection limit for a General Purpose D16ds_v4 (16 vCores, 64 GB memory) compute size is 10923 connections, while using a thread pool enables you to achieve far more throughput by exceeding the max connection limit.
MySQL typically assigns a thread for every client connection. As the concurrency level increases, performance degrades because of high CPU utilization specifically when active sessions exceed the CPU capacity available on the server. A high number of active threads may impact performance significantly as a result of increased context switching, thread contention, and bad locality for CPU caches.
A thread pool, which is a server-side feature distinct from connection pooling, maximizes performance by introducing a dynamic pool of worker threads that limit the number of active threads running on the server, minimizing thread churn. Instead of creating a new thread for each new connection, a thread pool assigns one of the threads from the pool to handle the request and then returns the connection to the pool after the operation is completed. This helps to ensure that a burst of connections won't cause the server to run out of resources or crash with an out of memory error.
Thread pools are most efficient for short lived connections, short transaction queries, and CPU-intensive workloads, for example OLTP workloads.
With Azure Database for MySQL – Flexible Server, you can now configure a thread pool to gain the resulting performance benefits. This blog post describes different usage scenarios and explains how to enable thread pools on Azure Database for MySQL – Flexible Server.
Note: The thread pool feature in Azure Database for MySQL – Flexible Server is supported only on MySQL version 8.0.
To estimate the potential performance improvement when using a thread pool in Azure Database for MySQL – Flexible Server, we ran a sysbench benchmark with read-only queries on a 16 vCore General Purpose server. The image below shows that thread pooling improves performance by up to 50% for the read-only workload, a result of the reduced latency.
When using a thread pool is most efficient
Configuring thread pools is most efficient when scaling the performance of short, chatty but persistent, and CPU-intensive workloads, for example OLTP workloads. Some scenarios in which you should consider configuring, testing, and enabling thread pools on your Azure Database for MySQL server are listed below.
- Large number of connections: Because performance drops with a high number of connections, using a thread pool can benefit scenarios with a large number (>500) of simultaneous connections, especially if there is a high parallel connection count. A thread pool theoretically can support an infinite number of connections if the server has enough memory to allocate a connection object. Thread pooling in this scenario provides stable and predictable throughput and latency.
- Avoid high CPU utilization driven by a high number of server connections: The higher the number of newly created connections against a server, the higher the CPU utilization. A thread pool can help in this scenario as is reduces the number of newly created threads by using existing ones.
- Avoid out of memory scenario: Using a thread pool can also be efficient for scenarios in which you can’t manage the number of connections to the server directly. Using a thread pool can help 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, consider enabling a thread pool at higher connection scales.
Using thread pools with Azure Database for MySQL provides improved and more stable performance.
You can see the benefits of using a thread pool with high concurrency workload. For example, the graph below shows QPS for sysbench OLTP workload (70% read / 30% write) on a General Purpose D16ds_v4 (16 vCores, 64 GB memory) Azure Database for MySQL - Flexible Server instance. As you can tell, the value of the thread pool (blue chart) is only visible when the concurrent connections on the server exceeds 100, as the thread contention is higher at higher concurrency.
When using a thread pool is less efficient
Using thread pools in some situations is likely to be less efficient, and therefore their use is not recommended in these scenarios.
- Workload experiences long periods of inactivity followed by high activity: If your workload isn’t active for a long period and 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 receives queries that are long running and never wait (does not indicate waits to thread pool), then the thread pool will be less efficient for your workload.
- Insufficient IOPS: Be sure that the amount of IOPS is large enough to avoid a thread pool running into starvation mode for IOPS. Azure Database for MySQL – Flexible Server enables you to provision additional IOPS up to 20K IOPs above the complimentary IOPS limit, independent of storage. Using this feature, you can increase or decrease the number of IOPS provisioned at any time based on your workload requirements. Learn more here.
Configuring a thread pool on an Azure Database for MySQL server
To enable a thread pool, update the value of the thread_handling server parameter to "pool-of-threads" by navigating to the Server Parameter blade in Azure portal or using Azure CLI. By default, this parameter is set to one-thread-per-connection, which means MySQL will create a new thread for each new connection. 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_size: This parameter refers to the number of thread groups in the thread pool, which determines how many statements can execute simultaneously. The default value is the number of CPUs on the system. The maximum value is capped at 2x the vCore count.
- thread_pool_stall_limit: This parameter refers to the number of milliseconds between each stall check performed by the timer thread. Stall detection is used to prevent a single client connection from monopolizing a thread group. When the timer thread detects that a thread group is stalled, it wakes up a sleeping worker thread in the thread group, if one is available. If there isn't one, then it creates a new worker thread in the thread group. This temporarily allows several client connections in the thread group to run in parallel. However, the timer thread will not create a new worker thread if the number of threads in the thread pool is already greater than or equal to the maximum defined by the thread_pool_max_threads variable, unless the thread group does not already have a listener thread. The default value is 500. Lower the value if you have very fast queries.
- thread_pool_idle_timeout: This parameter specifies the number of seconds before an idle worker thread exits. The default value is 60.
- threadpool_new_conn_high_prio: When enabled, this parameter specifies to send new connections to the high priority queue for processing. Otherwise, new connections wait in the low priority queue, which could cause a time out while the database is busy with a long running query and the timeout is less than 500ms.It’s a best practice to keep this parameter set to ON.
To improve the performance of short persistent queries on the thread pool, in Azure Database for MySQL - Flexible Server you can enable batch execution so that threads remain active for a short time to wait for the next query through this connection rather than returning them to the thread pool immediately after executing a query. The thread then executes the query rapidly and when finished, it waits for the next query, until the overall time consumption of the process exceeds a threshold.
The batch execution behavior is determined using the following server parameters:
- thread_pool_batch_wait_timeout: This parameter specifies the time that a thread waits for another query to process.
- thread_pool_batch_max_time: This parameter specifies the maximum time that a thread will repeat the cycle of query execution and wait for the next query.
Conclusion
I hope this information provides valuable guidance about when and how to use thread pools to improve the throughput of your Azure Database for MySQL - Flexible Server instance! As always, we welcome your feedback and suggestions, which you can provide by posting a comment below or by reaching out to us via the Ask Azure DB for MySQL alias, AskAzureDBforMySQL@service.microsoft.com.
Thank you!
Posted at https://sl.advdat.com/2Y788Xz
