Why MySQL Connections Stall and How Adjusting thread_cache_size Fixes It

When using Percona‑MySQL 5.6.16 with the built‑in thread pool, occasional queries take hundreds of milliseconds instead of the usual few‑dozen, even after adjusting the connection pool. The root cause is the thread pool’s handling of connections and an undersized thread_cache_size.

How MySQL Thread Pool Works

MySQL divides worker threads into groups based on the thread_pool_size parameter. Each incoming connection is assigned to a group by taking the connection’s thread ID modulo thread_pool_size. A group can run at most thread_pool_oversubscribe + 1 workers. If all workers are busy, additional requests wait in the group’s queue, increasing query response time (RT).

Diagnosing the Problem

Run the following commands to inspect thread‑related status variables:

show status like '%thread%';
Variable_name          Value
Threads_cached         0
Threads_connected       219
Threads_created         655068
Threads_running         48

Key meanings:

Threads_cached : number of idle threads currently cached.

Threads_connected : total connections (each needs a thread).

Threads_created : total threads created since the server started.

Threads_running : threads that are active (not sleeping).

Ideally, thread_cache_size should be close to Threads_connected so that most connections can reuse cached threads instead of creating new ones.

Inspecting Configuration Variables

show variables like '%thread%';
+-----------------------------------------+-----------------+
| Variable_name                           | Value           |
+-----------------------------------------+-----------------+
| thread_cache_size                       | 128             |
| thread_concurrency                      | 24              |
| thread_handling                         | pool-of-threads |
| thread_pool_high_prio_mode              | transactions    |
| thread_pool_high_prio_tickets            | 4294967295      |
| thread_pool_idle_timeout                | 60              |
| thread_pool_max_threads                 | 100000          |
| thread_pool_oversubscribe               | 40              |
| thread_pool_size                        | 12              |
| thread_pool_stall_limit                 | 500             |
| thread_stack                            | 262144          |
| thread_statistics                       | OFF             |
+-----------------------------------------+-----------------+

If Threads_cached stays at 0 while Threads_created keeps growing, the cache size is too small.

Solution: Increase thread_cache_size

Set thread_cache_size to a value comparable to Threads_connected. A practical rule of thumb based on physical memory is:

1 GB → 8

2 GB → 16

3 GB → 32

> 3 GB → 64

For the 64 GB server in the article, the author chose thread_cache_size = 512. After applying this change and retesting, connection latency dropped dramatically and the occasional slow‑response issue disappeared.

Key Takeaways

Monitor Threads_cached and Threads_created to detect insufficient thread cache.

Adjust thread_cache_size to be close to the peak number of concurrent connections.

Increasing the cache eliminates the need for MySQL to constantly create new threads, reducing connection latency.