MySQL Thread Pool plugin and application connection pools: coordinating two pooling layers for a low-traffic workload

In a low-traffic environment where the application tier already uses a connection pool, enabling the MySQL Thread Pool plugin is generally unnecessary and may introduce needless complexity. Its primary purpose is to prevent CPU exhaustion caused by thousands of concurrent active threads; if your client-side pool already limits active connections to a small number, the default 'one-thread-per-connection' model is more efficient.

Resource Savings and Overhead

For low-traffic workloads, the Thread Pool plugin does not produce measurable CPU or memory savings. In fact, it can introduce a slight overhead due to the logic required to manage thread groups and statement queues. Since the application-side pool already bounds the number of concurrent connections, the memory footprint of a few idle-side threads is negligible compared to the computational cost of the plugin's scheduling logic.

Statement Queueing and Timeouts

The Thread Pool plugin works by assigning connections to groups. If the number of active queries exceeds the thread_pool_size, new queries are queued. In a low-traffic scenario, this queue is rarely hit. However, if it does occur, the delay happens at the server level. Application-side pool timeouts might misinterpret this as a network lag or a slow query because the connection remains 'open' but the query is waiting for a worker thread. This can lead to 'ghost' timeouts where the client drops a connection while the server is just about to process the request.

Configuration Strategy

If you choose to enable the plugin for future-proofing or consistency across environments, follow these sizing guidelines to coordinate the two layers:

• thread_pool_size: Set this based on the number of physical CPU cores (typically 2x to 4x core count), not the application pool's max connections. This ensures the server is not oversubscribed.
• thread_pool_instances: Keep this at the default (usually 8) or low for small traffic to avoid the overhead of managing too many thread groups.
• Client Pool Keepalive: Ensure your application connection pool keepalive settings are significantly longer than your maximum expected execution time to prevent the pool from recycling connections while the thread pool is queueing a task.

Assumption: This analysis assumes 'low-traffic' refers to fewer concurrent active queries than the server's CPU core count, regardless of the total number of idle connections in the pool.