Mysql Enterprise Thread Pool
Mysql enterprise edition comes with enterprise thread pool, implemented using server plugin. By default mysql handles statements using one thread per client connection, which is fine for a small number of clients but as more clients connect to server and execute statements, the performance degrades.
Greater is the number of threads, greater parallelism is obtained. Sometimes greater parallelism can create bottlenecks on servers. The disadvantages of unwanted parallelism are:-
Too many threads make CPU cache almost useless in highly parallel workloads. Thread pool limits the thread executing in parallel to minimize the cpu cache footprint.
With too many threads executing in parallel, context switching overhead is high. This presents a challenge to the operating system scheduler. The thread pool controls the number of active threads to keep the parallelism within the MySQL server at a level that it can handle.
Too many transactions executing in parallel increases resource contention. The thread pool controls when transactions start to ensure that not too many execute in parallel.
Thread Pool Elements
Thread pool plugin comprises code for thread pooling implementation, as well as several associated monitoring tables to provide information about thread pool operation:
For >= 8.0.14, monitoring tables are performance_schema tables.
For < 8.0.14, monitoring tables are information_schema tables.
Note: The information_schema tables are now deprecated, expect them to be removed in the future releases.
Installation
To be usable by the server, the plugin must be located in the mysql plugin dir (select @@plugin_dir (if necessary, configure the plugin dir location by setting at startup)).
ON mysql 8.0.14 or higher, the monitoring tables are loaded/unloaded with the thread pool plugin. However, this plugin cannot be dynamically loaded and requires restart.
Trying to install this plugin dynamically gives this error:
Command: install plugin thread_pool soname 'thread_pool.so';
Error: ERROR 1721 (HY000): Plugin 'thread_pool' is marked as not dynamically installable. You have to stop the server to install it.
To install:
Inside my.cnf
[mysqld]
plugin-load-add=thread_pool.so
To verify installation:
SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'thread%';
(plugin status must be active, or execute show plugins; directly)
To verify the performance schema monitoring tables:
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'performance_schema' AND TABLE_NAME LIKE 'tp%';
Also, successful installation changes thread_handling variable to ‘dynamically-loaded’.
In case of error, check the mysql server logs for diagnostic messages.
Operations:
Thread pool consists of a number of thread groups, each group manages a set of client connections. As connections are established, thread pool assigns them to group in round-robin fashion.
The thread pool exposes system variables that are used to configure mysql thread pooling:-
thread_pool_algorithm: Controls which algorithm the thread pool plugin uses:
0(default) - uses conservative low-concurrency algo, it is well tested, produces very good results.
1 - increases concurrency, and is more aggressive. At times known to perform 5-10% better results on optimal thread counts, but has degrading performance as the number of connection threads increases. (only experimental for now).
(Global, static)
2) thread_pool_dedicated_listeners: Dedicates a thread listener in each thread group to listen for incoming statements from connections assigned to the group.
OFF (default) - disables dedicated listener thread
ON: dedicates a listener thread for each thread group. Dedicated listener threads do not execute queries.
Enabling this variable is only useful when a transaction limit is defined by thread_pool_max_transactions_limit, else shouldn’t be enabled.
(static, global)
3) thread_pool_high_priority_connection: Defines how to schedule statement execution for a session.
If the value is 0 (default), statement queuing uses both high and low priority queues.
If the value is 1 (true), queued statements always go to the high-priority queue.
(Dynamic, global, session)
4) thread_pool_max_active_query_threads : How many active(running) threads per group to permit. Default 0 - permits as many threads as are available.
(Dynamic, Global)
5) thread_pool_max_transactions_limit : Max number of transactions permitted by the plugin. When we define a transaction limit, enabling thread_pool_dedicated_listners creates a dedicated listener thread in each thread group.
Default value = 0, max value = 10,00,000 (10L). If current value is 0, cannot set it to higher or if current value > 0, cannot set it to 0. (enable/ disable cannot be done dynamically). However, can dynamically be changed if > 0.
When the limit has been reached, new connections appear to hang until one or more existing txns are completed. Same occurs when attempting to start a new transaction on an existing connection. However a privileged connection may override the limit. (tp_connection_admin privilege)
(Partially dynamic, Global)
6) thread_pool_max_unused_threads : The maximum number of unused threads in the pool. This makes it possible to limit the memory used by sleeping threads.
Default value=0 (no limit on sleeping threads), max value=4096. If set to N, there exists 1 consumer thread, and N-1 reserve threads. If a thread is ready to sleep, but the number of sleeping threads is already at the max, the thread exists rather than sleeping. When a thread must be woken up, the consumer thread wakes up at first(if available). Reserver threads are woken up if no consumer thread is available.
(Dynamic, Global)
7) thread_pool_prio_kickup_timer : How long before the thread pool moves a statement awaiting execution from low priority queue to high priority queue. Specified in milliseconds.
Default 1000 (1 second), max: 4294967294 (can be set to 49 days for some reason 😀)
(Dynamic, Global)
8) thread_pool_query_threads_per_group : Number of query threads permitted per group. Default 1 (one query thread per group, used when thread_pool_algorithm=0). You can increase it when using algorithm=1.
Max value = 4096, but if thread_pool_max_transactions_limit is set, thread_pool_query_threads_per_group must not exceed it (most probably because if this is greater than max transactions, all transactions are permitted through a single group at once, transactions not evenly distributed, doesn’t make sense).
(Dynamic, Global)
9) thread_pool_size : Number of thread groups. Most important parameter controlling thread pool performance.
Default: 16, max: 512
(Static, Global)
10) thread_pool_stall_limit : Amount of time a statement has to finish executing after which it is considered stalled, after which the pool permits another thread to be created within the same group to begin executing another statement. Measured in units of 10 milliseconds.
Default: 6 (60ms), max 600 (6000ms/ 6 seconds)
(Dynamic, Global)
11) thread_pool_transaction_delay : The delay period before starting execution of a new transaction. Workers thread sleep for a specified number of milliseconds before executing a new transaction. However this setting doesn’t affect queries issued from a privileged connection (from admin thread group)
Default: 0s, max: 3,00,000s (5 minutes)
(Dynamic, Global)
12) thread_stack : Defines stack size for each thread. The default is large enough for normal operation. If too small, limits the complexity of sql statements, recursion depth of stored programs, and other memory consuming actions. Should be multiple of 1024.
Default value: 1048576, max value: 18446744073709550592 (10^64 - 1) for 64 bit platform
(Static, Global)
By default, thread pool tries to ensure that a single thread executes in each group at a time, but sometimes may permit more threads to execute temporarily for best performance.
Each group has a listener thread, when a statement arrives, same listener thread starts execution immediately
This immediate execution behavior can be changed using thread_pool_transaction_delay variable.
If the execution finishes early (defined by thread_pool_stall_limit), executing thread returns to listening for statements, else the thread is considered stalled and starts another thread as a listener thread.
Thread pool uses a dedicated background thread that regularly monitors thread group stats. Also, since the same listener thread is used regularly (as long as statements execute immediately), it promotes caching.
We can limit the number of active threads per group by using thread_pool_max_active_query_threads variable.
If a query gets blocked by an IO operation or user level locks (row lock or table lock), there are callbacks to the thread pool that ensures that the thread pool immediately can start a new thread in this group to execute another statement.
There are 2 queues, high priority and low priority
All statements for myisam table goes to low priority queue, all statements for innodb goes to high priority queue, but if autocommit is enabled, it too goes to low priority queue.
When a thread group selects a queued statement for execution, it first selects from a high priority queue, then goes to low priority.
If a statement stays in the low priority queue for long, it will be moved to the high priority queue after thread_pool_prio_kickup_timer seconds.
Thread Pool Tuning
Thread_pool_size:
16-36 optimal for Innodb, 24-36 is mostly used
4-8 optimal for myisam
Thread_pool_stall_limit
Monitor average execution time for the database. Eg. if 99% of queries execute within 100ms, rest takes time > 100ms, then it will be optimal to set 10 (10*10ms)
We can determine the fraction of stalled statements as:
Select sum(stalled_queries_executed) / sum (queries_executed) from performance_schema.tp_thread_group_stats.
Thread pool Monitoring
Mysql provides 3 tables under perfomance_schema database for monitoring the status/performance of thread pools. The tables are listed below:
tp_thread_group_state: Information about thread pool thread group states.
tp_thread_group_stats: Thread group statistics.
tp_thread_state: Information about thread pool individual thread states.
Comments
Post a Comment