Thread pool¶
Introduction¶
Thread pooling improves performance and scalability for MySQL-compatible databases by reusing a fixed number of pre-created threads to manage multiple client sessions. This design reduces resource overhead, lowers contention, and avoids context-switching bottlenecks during high concurrency.
The default MySQL method creates one thread per client connection. This approach works efficiently under moderate connection loads. However, as the number of active connections increases, especially beyond 20,000, system overhead becomes significant, and throughput decreases.
Percona Server for MySQL includes an integrated thread pool that replaces the default model. The thread pool manages connections more efficiently by queuing work and reusing threads, especially in OLTP environments with many short-lived queries.
Version-specific notes¶
Thread pool support in Percona Server for MySQL evolved across releases. Review the following version details to avoid configuration errors and ensure compatibility.
8.0.14¶
Beginning with 8.0.14, the upstream implementation of admin_port
replaced the
previous mechanism that used extra_port
and extra_max_connections
.
Removed:
- These options are removed:
extra_port
andextra_max_connections
. They are no longer recognized and cause server startup failure.
Migration steps:
-
Remove all references to
extra_port
andextra_max_connections
from the configuration file before upgrading to 8.0.14 or later. -
Use the
admin_port
variable to configure administrative access if needed.
8.0.12-1¶
Percona Server for MySQL 8.0.12-1 introduced native support for the thread pool. This version ported the feature from Percona Server 5.7.
Core concepts and usage examples¶
Fixed pool of threads¶
The server initializes a specified number of threads during the startup process and maintains these active threads to handle incoming queries efficiently. The server minimizes the overhead associated with creating and destroying threads for each query by reusing threads, enhancing overall performance and responsiveness.
The following configuration setting activates the thread pool model:
[mysqld]
thread_handling = pool-of-threads
In this model, the server allocates threads from the pool to process queries as they arrive, allowing for better resource management and improved throughput, particularly in environments with high query loads. This approach ensures that available threads are quickly assigned to incoming requests, enabling timely query processing while optimizing system resources.
How the thread pool works¶
When a client connects, the server assigns the connection to a thread group using a round-robin method. Each group consists of a listener thread and multiple worker threads. The listener thread monitors active connections and queues incoming statements for processing.
The server assigns new connections to thread groups using a round-robin method. This approach balances workload evenly and prevents any group from becoming a bottleneck.
Connection 1 goes to Group 1
Connection 2 goes to Group 2
Connection 3 goes to Group 3
Connection 4 goes to Group 4
Connection 5 returns to Group 1
New queries are routed to either the high-priority queue or the low-priority queue. A query enters the high-priority queue if the connection has an open transaction and there are available high-priority tickets, which allow certain queries to be processed more quickly, ensuring that critical operations receive immediate attention. All other queries are directed to the low-priority queue.
Worker threads first scan the high-priority queue for queries to process. Once all high-priority queries have been handled, the threads process queries from the low-priority queue. After completing a query, a worker thread becomes idle and waits for the next task, helping to avoid excessive thread creation and maintaining steady performance under load.
The thread pool adapts to shifts in workload by redistributing queued tasks and prioritizing available threads. This dynamic management improves system responsiveness while minimizing the number of threads in use.
Thread groups¶
The thread pool organizes threads into distinct groups to manage client connections efficiently. Each group consists of a listener thread and multiple worker threads. The listener thread monitors incoming client connections and queuing requests for processing. Meanwhile, the worker threads handle the actual processing of these requests, allowing for concurrent execution of tasks.
[mysqld]
thread_pool_size = 8
This configuration spreads work across eight thread groups.
Priority queues¶
Each thread group maintains both a high-priority and low-priority queue to manage incoming queries effectively. When a new query arrives, the system determines its placement in one of the two queues based on the availability of high-priority tickets and the state of the transaction. The thread pool prioritizes processing by constantly checking the high-priority queue first, ensuring that critical queries receive immediate attention before any lower-priority queries are addressed. This approach optimizes resource allocation and enhances overall system responsiveness.
High-priority ticketing¶
Each connection begins with a set number of high-priority tickets that allow for the prioritization of a limited number of queries. Every time the thread pool promotes a query to the high-priority queue, one ticket is used. When queries are submitted to the server, each query is assigned a “ticket” indicating its priority level, which the thread pool uses to determine the processing order.
Here’s how the ticket system works in more detail:
Feature | Description |
---|---|
Ticket Assignment | When a query is submitted, it is evaluated based on its priority. High-priority queries receive a special ticket that allows them to be processed ahead of lower-priority queries. This assignment ensures that critical tasks are addressed promptly. |
Queue Management | The thread pool maintains separate queues for high-priority and low-priority queries. High-priority queries are placed in a queue that is processed first, while lower-priority queries wait in a separate queue. This separation helps manage resources effectively and ensures that important tasks do not get delayed. |
Resource Allocation | The thread pool allocates resources based on the tickets. When a thread becomes available, it checks the high-priority queue first. If there are high-priority queries waiting, those queries are executed before any from the lower-priority queue. This approach maximizes the responsiveness of the system for critical operations. |
Dynamic Adjustment | The ticket system allows for dynamic adjustments based on the current workload. If the number of high-priority queries increases, the system can adapt by allocating more resources to handle them efficiently, ensuring that performance remains optimal even during peak times. |
Configure thread pool¶
For example, this configuration setting in the server’s configuration file, my.cnf
, specifies that each new connection receives five high-priority tickets. These tickets allow up to five queries to be processed before lower-priority ones.
[mysqld]
thread_pool_high_prio_tickets = 5
By executing this command in the client, users enable the server to prioritize up to five queries for processing ahead of lower-priority queries. This setting is beneficial in environments where certain operations require immediate attention. The change applies globally, affecting all new connections established after the command is executed.
mysql> mysql> SET GLOBAL thread_pool_high_prio_tickets = 5;
Either of these configurations assign five tickets to each new connection.
High-priority modes¶
The thread_pool_high_prio_mode
setting is an option that manages how queries are scheduled in the thread pool using a ticket system.
When this setting is turned on, the system prioritizes high-priority queries. Adjusting this setting can improve the performance of your applications, especially during busy times.
Configuration and monitoring¶
Administrators can fine-tune thread pool behavior by adjusting dynamic and static variables.
[mysqld]
thread_pool_stall_limit = 100
mysql> SET GLOBAL thread_pool_stall_limit = 100;
This value, in milliseconds, limits how long a task can stall before being redistributed. As with other thread pool settings, existing sessions retain their previous values.
Thread Pool Configuration Reference Sheet¶
A quick overview of system variables available for configuring the thread pool in Percona Server for MySQL.
General Variables
Variable name | Default | Scope | Dynamic | Config file | Description |
---|---|---|---|---|---|
thread_handling |
one-thread-per-connection |
Global | No | Yes | Chooses the thread model. Use pool-of-threads to enable thread pooling. |
thread_pool_size |
16 |
Global | No | Yes | Defines the number of thread groups. |
thread_pool_oversubscribe |
3 |
Global | Yes | Yes | Defines per-group queue saturation threshold. |
Priority Handling
Variable name | Default | Scope | Dynamic | Config file | Description |
---|---|---|---|---|---|
thread_pool_high_prio_tickets |
4294967295 |
Global | Yes | Yes | Maximum high-priority executions allowed per connection. |
thread_pool_high_prio_mode |
transactions |
Global | Yes | Yes | Determines which queries receive high priority. |
Thread Management
Variable name | Default | Scope | Dynamic | Config file | Description |
---|---|---|---|---|---|
thread_pool_max_threads |
1000 |
Global | No | Yes | Maximum number of worker threads per group. |
thread_pool_stall_limit |
6 |
Global | Yes | Yes | Wait time in milliseconds before reassigning stalled queries. |
thread_pool_idle_timeout |
60 |
Global | Yes | Yes | Seconds before idle threads above min are released. |
Extra Port Behavior
Deprecated variables (click to expand)
Variable name | Default | Scope | Dynamic | Config file | Description |
---|---|---|---|---|---|
extra_port |
0 |
Global | No | Yes | Legacy variable replaced by admin_port . |
extra_max_connections |
1 |
Global | No | Yes | Legacy limit for extra_port access. |
Status Metrics (Read-Only)
Variable name | Scope | Dynamic | Description |
---|---|---|---|
threadpool_threads |
Session | N/A | Total worker threads across all groups. |
thread_pool_idle_threads |
Session | N/A | Idle worker threads available for reuse. |
Observability Commands¶
Use these statements to inspect runtime thread pool behavior:
mysql> SHOW STATUS LIKE 'Threadpool%';
mysql> SHOW ENGINE THREAD_POOL STATUS;
These commands reveal thread activity, idle counts, and queue saturation by group.
Note: For dynamic variables, changes apply only to new connections. Existing sessions retain their original settings until reconnect.
Configuration reference¶
This section consolidates key thread pool variables, their behavior, and how to configure them both statically and dynamically.
thread_handling
¶
Controls which thread management model the server uses.
-
Allowed values:
one-thread-per-connection
,pool-of-threads
-
Description: Use
pool-of-threads
to enable the thread pool. This setting must be configured inmy.cnf
before server startup. -
Default:
one-thread-per-connection
-
This value is not dynamic and requires a server restart.
thread_pool_size
¶
Defines the number of thread groups.
-
Allowed values: Any positive integer (commonly 2 to 64)
-
Description: Each group manages client connections with its own listener and worker threads.
-
Default: 16
-
This value is static and requires a server restart.
thread_pool_high_prio_tickets
¶
Limits the number of high-priority queries per connection.
-
Allowed values: 0 to 4294967295
-
Description: Each time a connection uses high-priority scheduling, one ticket is consumed.
-
Default: 4294967295
-
This setting can be configured statically.
-
This setting can be configured dynamically.
-
This change applies only to new client connections.
thread_pool_high_prio_mode
¶
Controls which queries qualify for high-priority scheduling.
-
Allowed values:
transactions
,statements
,none
-
Description: Use
transactions
to prioritize active transactions, orstatements
to prioritize all statements from ticketed connections. -
Default:
transactions
-
This setting can be configured statically.
-
This setting can be configured dynamically.
-
This change affects new sessions only. Existing sessions retain the previous mode until reconnect.
thread_pool_stall_limit
¶
Controls how long to wait before rescheduling stalled queries.
-
Allowed values: Integer ≥ 1 (milliseconds)
-
Description: If a query remains blocked beyond this time, the pool may redistribute it to another group.
-
Default: 6
-
This setting can be configured statically.
-
This setting can be configured dynamically.
-
This setting helps improve query responsiveness under unbalanced workloads.
thread_pool_min_threads
¶
Defines the minimum number of worker threads per group.
-
Allowed values: Integer ≥ 1
-
Description: Sets a floor for thread availability within each group.
-
Default: 4
-
This value is not dynamic.
thread_pool_max_threads
¶
Defines the maximum number of worker threads per group.
-
Allowed values: Integer ≥
thread_pool_min_threads
-
Description: Controls upper bound on thread creation. If reached, new queries wait in the queue.
-
Default: 1000
-
This setting is not dynamic and requires a server restart.
Use these variables together to tune concurrency, queue behavior, and scheduling control based on your workload and connection volume.