Introduction MySQL Enterprise Thread Pool (or Percona's implementation) groups connections into thread pools to handle high concurrency efficiently. When all thread groups are saturated and the queue is full, new connections are refused with `Too many connections` or `Thread pool is full` errors, even if `max_connections` has not been reached.

Symptoms - `ERROR 1040 (HY000): Thread pool is full` or similar connection refused errors - `SHOW STATUS LIKE 'Threadpool_%';` shows `Threadpool_threads_stalled` increasing - Connections queue up and eventually time out - CPU is saturated but not at 100% (thread contention) - Application connection pool reports increasing wait times

Common Causes - `thread_pool_size` set too small for the CPU core count - Long-running queries occupying all thread groups - `thread_pool_stall_limit` too low, causing premature thread creation - Mixed workload (OLTP + OLAP) competing for the same thread pool - Connection storm from application restart or deployment

Step-by-Step Fix 1. **Check thread pool status": ```sql SHOW STATUS LIKE 'Threadpool_%'; -- Key metrics: -- Threadpool_threads_stalled -- Threadpool_threads_idle -- Threadpool_threads_running -- Threadpool_queue_length ```

  1. 1.**Tune thread pool configuration":
  2. 2.```sql
  3. 3.-- Set thread pool size to CPU core count (or slightly higher)
  4. 4.SET GLOBAL thread_pool_size = 16;

-- Increase stall limit before creating new threads SET GLOBAL thread_pool_stall_limit = 30;

-- Increase maximum threads SET GLOBAL thread_pool_max_threads = 1000;

-- Make persistent SET PERSIST thread_pool_size = 16; SET PERSIST thread_pool_stall_limit = 30; ```

  1. 1.**Configure thread pool for mixed workloads":
  2. 2.```sql
  3. 3.-- Use thread_pool_oversubscribe for I/O-bound workloads
  4. 4.SET GLOBAL thread_pool_oversubscribe = 3;

-- Set separate queues for different priorities SET GLOBAL thread_pool_high_prio_mode = 'TRANSACTIONS'; SET GLOBAL thread_pool_high_prio_tickets = 4294967295; ```

  1. 1.**Kill long-running queries occupying thread groups":
  2. 2.```sql
  3. 3.-- Find queries running longer than 30 seconds
  4. 4.SELECT
  5. 5.id, user, host, db, command, time, state, info
  6. 6.FROM information_schema.processlist
  7. 7.WHERE command != 'Sleep' AND time > 30
  8. 8.ORDER BY time DESC;

-- Kill problematic queries KILL <query_id>; ```

  1. 1.**Implement connection queue management at the application level":
  2. 2.```python
  3. 3.# Use a connection pool with maximum size matching thread_pool_size
  4. 4.from mysql.connector import pooling

pool = pooling.MySQLConnectionPool( pool_name="mypool", pool_size=16, # Match thread_pool_size pool_reset_session=True, host="localhost", user="app_user", password="secret", database="mydb" ) ```

Prevention - Set `thread_pool_size` to match the number of CPU cores - Monitor `Threadpool_threads_stalled` and alert on increases - Separate OLTP and OLAP workloads into different MySQL instances - Use connection pooling at the application level to limit concurrent connections - Set `thread_pool_stall_limit` appropriately (30s for OLTP, 60s for mixed) - Implement query timeouts to prevent long-running queries from occupying thread groups - Monitor thread pool metrics in Grafana/Prometheus dashboards