thread_pool_oversubscribe
parameter and decrease the value of the thread_pool_stall_limit
parameter to quickly enable the thread pool. After the backlog of SQL queries are processed, you can restore the parameters to their original values as needed.thread_handling_switch_mode
parameter to control whether to dynamically change the thread working mode. Parameter values are described as follows:Valid Value | Description |
disabled | The mode cannot be changed dynamically. |
stable | The mode can only be changed for new connections. |
fast | (Default value) The mode can be changed for new connections and new requests. |
sharp | Active connections will be killed in order to force a reconnection so that the mode can be changed quickly. |
show threadpool status
command displays the following new status:show full processlist
command displays the following new status:Parameter | Effective Immediately | Type | Default Value | Valid Values/Value Range | Description |
thread_pool_idle_timeout | Yes | uint | 60 | [1, UINT_MAX] | If there are no network events to handle, the worker thread will wait for thread_pool_idle_timeout seconds before being killed. |
thread_pool_oversubscribe | Yes | uint | 3 | [1,1000] | The maximum number of worker threads allowed in a worker thread group. |
thread_pool_size | Yes | uint | The number of CPUs on the current machine. | [1,1000] | Number of thread groups. |
thread_pool_stall_limit | Yes | uint | 500 | [10, UINT_MAX] | The timer thread checks all thread groups every thread_pool_stall_limit milliseconds.If a thread group has no listener, neither high nor low priority queue is empty, and no new I/O network event occurs, the thread group is considered stalled. The timer thread will wake up a sleeping thread or create a new thread to relieve the thread group's pressure. |
thread_pool_max_threads | Yes | uint | 100000 | [1,100000] | The total number of worker threads in the thread pool |
thread_pool_high_prio_mode | Yes, session | enum | transactions | transactions\\statement\\none | The high priority queue supports three modes: transactions: When there is only one statement that already starts a transaction and the ticket ( thread_pool_high_prio_tickets ) held by the connection of this statement is not zero, the connection can enter the high priority queue. After a connection has entered the priority queue thread_pool_high_prio_tickets times, it will be placed into the ordinary queue.statement: All connections enter the high priority queue. none: All connections enter the low priority queue. |
thread_pool_high_prio_tickets | Yes, session | uint | UINT_MAX | [0, UINT_MAX] | The value of the ticket assigned to each connection in transactions mode |
threadpool_workaround_epoll_bug | Yes | bool | false | true/false | Whether to bypass the epoll bug of Linux 2.x (which was fixed in Linux 3) |
show threadpool status
command displays the following status:Status | Description |
groupid | Thread group ID |
connection_count | The number of user connections in the thread group |
thread_count | The number of worker threads in the thread group |
havelistener | Whether the thread group has a listener |
active_thread_count | The number of active worker threads in the thread group |
waiting_thread_count | The number of worker threads calling wait_begin in the thread group |
waiting_threads_size | The number of sleeping worker threads waiting to be woken up in the thread group when there is no network event to handle (such worker threads will wait for thread_pool_idle_timeout seconds before being automatically killed). |
queue_size | The length of the ordinary queue of the thread group |
high_prio_queue_size | The length of the high priority queue of the thread group |
get_high_prio_queue_num | The total number of times that events in the thread group are removed from the high priority queue |
get_normal_queue_num | The total number of times that events in the thread group are removed from the ordinary queue |
create_thread_num | The total number of worker threads created in the thread group |
wake_thread_num | The total number of worker threads in the thread group awakened from the waiting_threads queue |
oversubscribed_num | The number of times that worker threads are ready to go to sleep because the thread group is oversubscribed |
mysql_cond_timedwait_num | The total number of times that worker threads in the thread group enter the waiting_threads queue |
check_stall_nolistener | The total number of times that no listener is detected in the thread group in the stall check performed by the timer thread |
check_stall_stall | The total number of times that the thread group is considered stalled in the stall check performed by the timer thread |
max_req_latency_us | The maximum time in milliseconds for a user connection to wait in the queue in the thread group |
conns_timeout_killed | The total number of times that user connections in the thread group are killed because there has been no new message on the client for the threshold period |
connections_moved_in | The total number of connections migrated from other thread groups to this thread group |
connections_moved_out | The total number of connections migrated from this thread group to other thread groups |
connections_moved_from_per_thread | The total number of connections switched from the one-thread-per-connection mode to this thread group |
connections_moved_to_per_thread | The total number of connections switched from this thread group to the one-thread-per-connection mode |
events_consumed | The total number of events processed by the thread group |
average_wait_usecs_in_queue | The average waiting time of all events in the queue in the thread group |
Was this page helpful?