A brief summary of mysql-parameter thread_cache_size optimization method _mysql

Description
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
Optimization method:
1, mysql> set global thread_cache_size=16
2. Edit/ETC/MY.CNF Change/Add
--------
Thread_concurrency = 16
--------

1, MySQL server number of threads to view the method:

Show global status like ' thread% ';

Threads_created: The number of threads created, if the threads_created value is found to indicate that the MySQL server has been creating threads, which is also more resource-consuming, can appropriately increase the thread_cache_size value in the configuration file

2, the optimization parameter thread_cache_size

Thread_cache_size: After the client disconnects, the server processing the client's thread will be cached in response to the next customer rather than destroyed (provided the cache count is not up to the upper limit)

That is, you can reuse the number of stored threads in the cache, if there is room in the cache when disconnected, the client's thread is placed in the cache, and 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 is recreated. If there are many new threads, adding this value can improve system performance.

Settings for thread_cache_size size:

If it is a short connection, set a larger, because the short connection often need to constantly create, constantly destroy, if the larger point, the connection thread are in the access state, do not need to re-create and destroy, so the performance is certainly a relatively large upgrade.
For long connections, the stability of the connection is not guaranteed, so set this parameter or there is a certain need, may be connected to the pool of problems, will lead to the instability of the database connection, there will be frequent creation and destruction, but this situation is relatively small, if it is long connection, can be set to a smaller, generally around 50-100.

Physical memory Settings rule: You can see the effect of this variable by comparing the variables of connections and threads_created states. (--> represents the value to be adjusted) set the rules according to physical memory as follows:

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

Query thread_cache_size Settings

Show global status like ' Thread_cache_size ';

Optimization method:

1, mysql> set global thread_cache_size=16
2. Edit/ETC/MY.CNF Change/Add
Thread_concurrency = 16

3. mysql Kill thread

Mysqladmin start slave stop slave Kill a thread connected to MySQLServer