Optimization of MySQL thread_stack connection thread

MySQL can be connected not only through the network, but also through named pipes. Regardless of which method is used to connect to MySQL, all client requests are managed by threads in MySQL. Each client connection will have a corresponding connection thread. A Thread Cache pool is implemented in MySQL to store idle connection threads instead of destroying them after completing the request. In this way, when there is a new connection request, MySQL will first check whether there is an idle connection thread in the Thread Cache. If so, it will be taken out and used directly. If there is no idle connection thread, a new connection thread will be created. Specific parameters:
Thread_cache_size: The number of connection threads that should be stored in the Thread Cache pool.
Thread_stack: The memory size that MySQL allocates to each connection thread when it is created. When MySQL creates a new connection thread, it needs to allocate a certain amount of memory stack space to it in order to store the client's requested query and its own various status and processing information.
View the setting values ​​of system variables related to connection threads: show variables like 'thread%';

mysql> show variables like 'thread%';
+-------------------+--------+
| Variable_name | Value |
+-------------------+--------+
| thread_cache_size | 32 |
| thread_stack | 196608 |
+-------------------+--------+
2 rows in set (0.00 sec)

As shown in the figure, the system sets the Thread Cache pool to cache up to 25 connection threads. When each connection thread is created, the system allocates 192KB of memory stack to it.
View the number of times the system has been connected and the status value of the connection thread in the current system

mysql> show status like 'connections';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections | 620 |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show status like '%thread%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| Delayed_insert_threads | 0 |
| Slow_launch_threads | 0 |
| Threads_cached | 3 |
| Threads_connected | 4 |
| Threads_created | 7 |
| Threads_running | 1 |
+------------------------+-------+
6 rows in set (0.00 sec)

Since the system was started, it has received 620 client connections and created 7 connection threads. Currently, 1 connection thread is connected to the client, and only one of the 3 connection threads is in the active state, that is, only one is processing the request submitted by the client. A total of 3 connection threads are cached in the Thread Cache pool.

Thread Cache Hit Rate:
Thread_Cache_Hit = (Connections - Threads_created) / Connections * 100%;
Generally, after the system has been running stably for a period of time, the Thread Cache hit rate should be maintained at around 90% to be considered normal.

Practical Application:

For 16G/32G machines, 512K is generally set

Of course, if you encounter the following error message, you should consider increasing this value.

mysql-debug: Thread stack overrun

Bug info

Error message:
java.sql.SQLException: Thread stack overrun: 5456 bytes used of a 131072 byte stack, and 128000 bytes needed. Use 'mysqld --thread_stack=#' to specify a bigger stack.

Official corresponding information:

The default (192KB) is large enough for normal operation. If the thread stack size is too small, it limits the complexity of the SQL statements that the server can handle, the recursion depth of stored procedures, and other memory-consuming actions

Can be used

show variables where `variable_name` = 'thread_stack';

Query the default thread stack size of the current database. Generally, it can be used normally. However, when the query statement or stored procedure is complex, a Thread stack overrun error will be reported. In this case, just modify the default configuration.

solve

Windows: Modify the my-small.ini or my.ini file in the MySQL installation directory to 256k or larger, then restart the service.

[mysqld]
thread_stack = 256k
linux: You also need to modify the configuration file, but! ! ! , not the configuration file in the installation directory, but /etc/my.cnf. Only this file can take effect, and then restart the service service mysql restart

[mysqld]
thread_stack = 256k