Basic Details
The thread_pool_size system variable sets the number of thread groups in the thread pool.
Releem automatically tunes thread_pool_size and 44 other variables to improve MySQL performance. Try Releem for Free, or deepen your understanding by reading our detailed article.
Releem automatically tunes thread_pool_size and 44 other variables to improve MySQL performance. Try Releem for Free, or deepen your understanding by reading our detailed article.
| Name | MySQL | Percona | MariaDB |
|---|---|---|---|
| Variable | thread_pool_size | thread_pool_size | thread_pool_size |
| Configuration | Supported | Supported | Supported |
| Scope | Gobal | Global, Session | Global, Session |
| Dynamic | No | Yes | Yes |
| Data Type | Integer | Numeric | Numeric |
| Default Value | 16 | Number of CPU | Number of CPU |
| Minimum Value | 1 | 1 | 1 |
| Maximum Value | 64 (<= 8.0.18), 512 (>=8.0.19) | 128 | 128 (< MariaDB 5.5.537, MariaDB 10.0.11), 100000 (>= MariaDB 5.5.37, MariaDB 10.0.11) |
thread_pool_size – Usage
Thread_pool_size controls how many statements can proceed at the same time. By default, thread_pool_size is set to the number of CPUs. There are a lot of complex interactions going on behind the scenes for managing the thread pool.
For example, the thread pool tries to keep one thread executing for each thread group but may allow other threads to execute in situations where this action would improve performance. As client connections come in, the thread pool assigns the connections to a thread group. Thread pool is the most useful for systems with OLTP workloads.
For example, the thread pool tries to keep one thread executing for each thread group but may allow other threads to execute in situations where this action would improve performance. As client connections come in, the thread pool assigns the connections to a thread group. Thread pool is the most useful for systems with OLTP workloads.
thread_pool_size – Configuration
Thread_pool_size system variable can be configured using the command line or configuration file. Thread_pool_size can only be configured dynamically with Percona and MariaDB:
Command Line:
mysqld> set global thread_pool_size = XX
Replace XX with value to suit your database needs. This change will only be applied to new connections. Make a new connection to the server and the values will update. SET GLOBAL will not persist through a server restart.
Configuration File:
[mysqld]
thread_pool_size = XX
Replace XX with value to suit your database needs. Must restart MySQL server to see changes applied.
Command Line:
mysqld> set global thread_pool_size = XX
Replace XX with value to suit your database needs. This change will only be applied to new connections. Make a new connection to the server and the values will update. SET GLOBAL will not persist through a server restart.
Configuration File:
[mysqld]
thread_pool_size = XX
Replace XX with value to suit your database needs. Must restart MySQL server to see changes applied.
thread_pool_size – Considerations
When configuring the thread pool, there are a few system variables to keep track of, but thread_pool_size has the greatest effect on thread pool performance. Don't try calculating how many threads are sufficient to prevent deadlocks and other performance drops because, more often than not, it's a value near the default. Remember the default is the number of CPUs.
Usually, there is no need to adjust the defaults, as the thread_pool_sized default is designed to provide good performance. With thread_pool_size, the goal is to reduce context switching while also maximizing the number of client connections. The right balance will prevent performance drops.
If you want to find the best possible value for thread_pool_size, let Releem do the work for you.
Usually, there is no need to adjust the defaults, as the thread_pool_sized default is designed to provide good performance. With thread_pool_size, the goal is to reduce context switching while also maximizing the number of client connections. The right balance will prevent performance drops.
If you want to find the best possible value for thread_pool_size, let Releem do the work for you.
Releem Agent will collect metrics and important system information that is transferred to the Releem Cloud Platform. Releem Cloud Platform will calculate MySQL Performance Score and recommend improving your server performance with its recommended configuration. Releem can automatically apply these settings, including any recommendations for thread_pool_size.