MySQL thread entry-level analysis

Author: skate
Time: 2012/12/4

 

MySQL thread entry-level analysis

 

This afternoon, I discussed the thread pool of MySQL with my friends in the group. The discussion was very lively and I had a lot of GAINS. I would like to summarize the thread for a memo. The following figure shows the lidan image:

 

MySQL supports single-thread and multi-thread connection modes. If single-thread connection is used, only one connection can be connected to MySQL at the same time. Other connections will be suspended. For multithreading, multiple connections can be connected to the server at the same time. Which thread mode MySQL uses to control through the following variables.

Mysql> show variables like '% thread_handling % ';
+ ----------------- + --------------------------- +
| Variable_name | value |
+ ----------------- + --------------------------- +
| Thread_handling | one-thread-per-connection |
+ ----------------- + --------------------------- +
1 row in SET (0.02 Sec)

The client initiates a connection to the MySQL server, the MySQL server listens to the process, listens to new requests, and MySQL allocates a new thread for it to process the request. Starting from establishing a connection, the CPU divides it into thread stacks, authenticates the user identity, establishes context information, completes the request, closes the connection, and releases resources, in the case of high concurrency, it will bring great pressure to the system and cannot guarantee performance. Therefore, MySQL uses the thread cache to achieve thread reuse and reduce the consumption of this part. A connection is disconnected, instead of destroying the thread that carries the thread, but put the thread into the thread buffer, and in the suspended state. When the next new connection arrives, first go to the thread buffer to check whether there are Idle threads. If yes, use it. If not, create a new thread.

MySQL uses the thread_cache_size parameter to set the number of reusable threads. Its size can be set through the state variables threads_cached and threads_created.

Mysql> show status like 'thread % ';
+ ----------------- + ------- +
| Variable_name | value |
+ ----------------- + ------- +
| Threads_cached | 0 |
| Threads_connected | 2 |
| Threads_created | 1065 |
| Threads_running | 1 |
+ ----------------- + ------- +
4 rows in SET (0.13 Sec)

Threads_cached: Number of threads cached by the thread cache pool
Threads_created: Number of threads already created. The value of thread_cache_size can be determined based on the change trend.

Before 5.5.16, the thread processing mode is that each request corresponds to a thread mode, which means that when there are thousands of requests, tens of thousands of threads are required to respond to these requests. Now the problem is obvious, and the system resources are limited, ensure that thread_number * thread_stack cannot exceed the available memory resources, and consider the CPU scheduling and I/O processing capabilities. This is a very rough way to use resources, this uncontrolled processing method also leads to resource usage conflicts. When a large number of mutex locks occur, the performance decreases sharply. After 5.5.16, the thread pool is used to control the capacity of the server to avoid service failures and server downtime.

 

How does the thread pool dynamically control the number of concurrent threads? To be continued ....