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. 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.

Changed in 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.

What changed:

• extra_port and extra_max_connections are removed.

• These variables are no longer recognized and cause server startup failure.

Migration steps:

• Remove all references to extra_port and extra_max_connections from the configuration file before upgrading to 8.0.14 or later.

• Use the admin_port variable, as supported by upstream MySQL, to configure administrative access if needed.

Implemented in 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 defined number of threads at startup and reuses them to process incoming queries.

[mysqld]
thread_handling = pool-of-threads

This setting activates the thread pool model.

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 contains a listener thread and worker threads. The listener monitors active connections and queues incoming statements.

Each new query is routed to either the high-priority queue or the low-priority queue within the group. A query enters the high-priority queue if the connection has an open transaction and available high-priority tickets. All other queries are sent to the low-priority queue.

Worker threads scan the high-priority queue first, then process queries from the low-priority queue when no high-priority items remain. After finishing a query, the thread becomes idle and waits for the next task. This reuse avoids excessive thread creation and maintains steady performance under load.

The pool adapts to workload shifts by redistributing queued tasks and prioritizing available threads. The result is better system responsiveness with fewer total threads.

Thread groups

The thread pool organizes threads into groups. Each group contains a listener thread, a set of worker threads, and handles a portion of the client connections.

[mysqld]
thread_pool_size = 8

This configuration spreads work across eight thread groups.

Priority queues

Each thread group maintains a high-priority queue and a low-priority queue. New queries are added to one of the two based on ticket availability and transaction state. The thread pool always checks the high-priority queue first.

mysql> SHOW STATUS LIKE 'Threadpool%';

This command displays queue lengths and thread statistics.

High-priority ticketing

Each connection begins with a set number of high-priority tickets. These tickets allow prioritization for a limited number of queries. One ticket is used every time the thread pool promotes a query to the high-priority queue.

Static configuration

[mysqld]
thread_pool_high_prio_tickets = 5

Dynamic configuration

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 controls how the thread pool schedules queries using tickets.

mysql> SET GLOBAL thread_pool_high_prio_mode = 'transactions';

This setting limits prioritization to queries within open transactions.

Configuration thread_pool_high_prio_mode

To set the variable persistently, add the following to your my.cnf file:

[mysqld]
thread_pool_high_prio_mode = transactions

You can also change this setting at runtime without restarting:

mysql> SET GLOBAL thread_pool_high_prio_mode = 'transactions';

This change takes effect immediately for new connections only. Existing client sessions continue to operate under the previously active mode. To ensure all clients reflect the new behavior, either:

• Restart the server, or

• Have connected clients disconnect and reconnect manually.

This flexibility allows scheduling behavior to be fine-tuned without disrupting active workloads.

Adaptive scheduling

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

Configuration and monitoring

Administrators can fine-tune thread pool behavior by adjusting dynamic and static variables.

Static configuration

[mysqld]
thread_pool_stall_limit = 100

Dynamic configuration

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.

Check the status

mysql> SHOW ENGINE THREAD_POOL STATUS;

This command returns information about thread usage and group activity.

When to use the thread pool

Choosing between the thread pool and the default threading model depends on system size, workload patterns, and performance goals. Use the guidelines below to decide which approach fits best.

Use the thread pool when:

• The system experiences hundreds or thousands of concurrent connections.

• Most queries are short, frequent, and CPU-bound (typical of OLTP workloads).

• The server suffers from context switching, thread contention, or memory overhead due to excessive threads.

• Transactions should be prioritized, and precise query scheduling is required.

• Thread count must remain predictable and resource usage needs tighter control.

Use the default model when:

• The deployment serves fewer than 500 concurrent clients.

• Queries are long-running or resource-heavy and better suited to isolated execution.

• Thread overhead is not a problem, and the simplicity of one-thread-per- connection is acceptable.

• There is no pressing need to control concurrency or prioritize active transactions.

Thread pooling offers greater control and efficiency, but only in workloads that justify that complexity. Measure concurrency patterns and CPU behavior before enabling the feature.

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_algorithm	first-in, first-out	Global	Yes	Yes	Controls how queued queries are scheduled.
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_min_threads	4	Global	No	Yes	Minimum number of worker threads per group.
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 in my.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, or statements 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_algorithm

Selects how queued queries are scheduled across thread groups.

• Allowed values: first-in, first-out, lowest_priority

• Description: The first-in, first-out algorithm processes requests in the order received. The lowest_priority option favors the least active group.

• Default: first-in, first-out

• This setting can be configured statically.

• This setting can be configured dynamically.

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.