MySQL optimization parameter thread_cache_size

Thread_cache_size

Query process Usage

The code is as follows	Copy Code
Mysql> show global status like ' thread% '; + ——————-+ ———-+ | variable_name | Value | + ——————-+ ———-+ | threads_cached | 26 | | threads_connected | 510 | | threads_created | 35168165 | | threads_running | 459 | + ——————-+ ———-+ 4 rows in Set (0.01 sec)

Query Server Thread_cache_size Configuration

The code is as follows	Copy Code
Mysql> Show variables like ' thread_cache_size '; + ——————-+ ——-+ | variable_name | Value | + ——————-+ ——-+ | Thread_cache_size | 32 | + ——————-+ ——-+ 1 row in Set (0.00 sec)

If we set the thread_cache_size in the MySQL server configuration file, when the client disconnects,
The thread that the server handles this client will be cached in response to the next customer rather than destroyed (provided the cache count is not up to the upper limit).
Threads_created indicates the number of threads created, and if the threads_created value is found to be too large,
Indicates that the MySQL server has been creating threads, which is also a resource consuming and can appropriately increase the thread_cache_size value in the configuration file.

According to the investigation, the above server thread cache Thread_cache_size is not set, or is set too small, this value indicates that the number of stored threads in the cache can be recycled, and if there is room in the cache when disconnected, the client's thread will be placed in the cache if the thread is requested again , the request is read from the cache, and if the cache is empty or a new request, the thread will be recreated, and if there are many new threads, adding this value can improve system performance. By comparing the variables of connections and threads_created states, You can see the effect of this variable. (?> represents the value to be adjusted) set the rules according to physical memory as follows:

1G?> 8
2G?> 16
3G?> 32
>3G?> 64