MySQL thread pool, mysql thread

MySQL thread pool, mysql thread

The MySQL thread pool is only available in Percona, MariaDB, and Oracle MySQL Enterprise Edition. Oracle MySQL Community edition is not available.

In the traditional mode, there are two thread Scheduling Methods for MySQL: one thread (one-thread-per-connection) and one thread (no-threads) for all connections ). In actual production, the former is generally used. That is, every time a client connects to the MySQL server, the MySQL server creates a separate thread for the client. The more connections, the more threads there will be.

If most of the threads are idle, the performance of the server will not be greatly affected. However, if too many threads are executed at the same time, the operating system will frequently switch context.

The thread pool is introduced to reduce the number of concurrent threads and the number of context switches.

The thread pool is first implemented by MariaDB. There are two implementation methods, corresponding to Windows and Unix operating systems respectively. On Windows, the local buffer pool function of the operating system is used directly, while on Unix systems, the function is implemented by itself. As a result, the system variables on the two operating systems are different.

 

The following describes the parameters of the thread pool based on Percona 5.6.31.

Thread_handling

The default value is one-thread-per-connection. To use the connection pool function, you must set it to pool-of-threads.

 

Thread_pool_size

Set the number of thread groups in the thread pool. The default value is the number of cores of the server CPU. The purpose of grouping is to map each group to each CPU core, so that each group can be executed by one thread at the same time point.

 

Thread_pool_max_threads

The maximum number of threads in the thread pool. If the value is 1000, the maximum number of threads that can be created in the thread pool cannot exceed 1000.

This variable can be used to limit the maximum number of threads in the pool. Once this number is reached no new threads will be created.

 

Thread_pool_oversubscribe

Controls the number of threads that are active at the same time for a single CPU core. It is similar to the concept of "overclock ".

The higher the value of this parameter the more threads can be run at the same time, if the values is lower than 3 it cocould lead to more sleeps and wake-ups

 

Thread_pool_stall_limit

When no thread is available in the thread pool, thread_pool_stall_limit determines how long it will take to create a new thread, in milliseconds. The default value is 500.

Within the appropriate range, the larger the value, the better the overall processing performance of the MySQL server, because a small number of threads will reduce the acquisition of system resources. However, the larger the value, the longer the thread creation time, and the more obvious the query latency.

The number of milliseconds before a running thread is considered stalled. when this limit is reached thread pool will wake up or create another thread. this is being used to prevent a long-running query from monopolizing the pool.

 

Thread_pool_idle_timeout

Sets the waiting time before Idle threads are destroyed. The unit is seconds. The default value is 60.

You can adjust the value of this parameter based on your business scenario. If it is set too short, the thread is frequently destroyed and created. If it is set too long, the number of threads in the thread pool does not decrease for a long time.

This variable can be used to limit the time an idle thread shoshould wait before exiting.

 

Extra_port

Set a port other than the MySQL service port for the Administrator to manage the server.

This variable can be used to specify additional port Percona Server will listen on. this can be used in case no new connections can be established due to all worker threads being busy or being locked when pool-of-threads feature is enabled.

 

Extra_max_connections

Used to set the maximum number of connections allowed by the extra_port. The connections created through the extra_port are one-thread-per-connection.

This variable can be used to specify the maximum allowed number of connections plus one extra SUPER users connection on the extra_port. this can be used with the extra_port variable to access the server in case no new connections can be established due to all worker threads being busy or being locked when pool-of-threads feature is enabled.

 

In addition, Percona also adds two parameters for implementing priority queues.

Thread_pool_high_prio_mode

Tasks to be processed in the thread pool group are placed in the task queue, waiting for processing by the worker thread.

Each group has two queues: a high-priority queue and a normal queue. The worker thread obtains the event from the high-priority queue for processing. The event is obtained from the normal queue only when the high-priority queue is empty.

Through priority queue, You can prioritize started transactions or short transactions, and promptly commit and release resources such as locks.

Three modes can be set for this parameter:

Transactions: by default, only one SQL statement that has enabled the transaction and thread_pool_high_prio_tickets is not 0 will enter the high-priority queue, after thread_pool_high_prio_tickets is placed in the priority queue, each connection is moved to the normal queue.

Statements: Separate SQL statements always enter the high-priority queue.

None: Disable the high-priority queue function. All connections are placed in the normal queue for processing.

 

Thread_pool_high_prio_tickets

Size of the tickets assigned to each new connection

This variable controls the high priority queue policy. Each new connection is assigned this variable tickets to enter the high priority queue. Setting this variable to 0 will disable the high priority queue. the default value is 4294967295.

 

Applicable scenarios of thread pools:

Applicable to business scenarios with a large number of short queries

In this scenario, when each thread is connected, excessive connections can easily reach the maximum number of connections. At the same time, too many active threads can cause frequent context switching. In this case, the thread pool can be used. Because it is a short query, no connection occupies the thread in the thread pool for a long time, so the response time of the client request is almost not affected, as the number of connections increases, the number of threads in the thread pool is controlled within a certain range, reducing the pressure on the system.

The thread pool is not suitable for business scenarios with a large number of long queries

In this scenario, long queries may occupy all the threads in the thread pool, resulting in low efficiency in the thread pool, and client settings cannot be connected.

Refer:

1. in-depth understanding of MariaDB and MySQL

2. MariaDB Principle and Implementation

3. http://www.tuicool.com/articles/7NveimQ

4. https://www.percona.com/blog/2013/03/16/simcity-outages-traffic-control-and-thread-pool-for-mysql/

5. http://mysql.taobao.org/monthly/2016/02/09/

6. http://www.cnblogs.com/cchust/p/4510039.html