Basic Details
Thread_cache_size is the system variable that determines the value of thread cache size. These threads are freed after 5 minutes of idle time.
When a client disconnects, the threads are out in the cache if they are less than the system variable thread_cache_size. The requests are satisfied by reusing threads taken from the cache. But, it is only possible if the cache is empty, so it creates a new thread.
The thread_cache_size variable has a changeable value. It can be increased to improve performance, which is suitable for multiple new connections. However, you will not see an improvement in performance if your thread implementation is already good.
The value of thread_cache_size should be adjusted depending on the number of new connections. If there are hundreds of them, you should set the value high enough.
Releem automatically tunes thread_cache_size and 44 other variables to improve MySQL performance. Try Releem for Free, or deepen your understanding by reading our detailed article.
When a client disconnects, the threads are out in the cache if they are less than the system variable thread_cache_size. The requests are satisfied by reusing threads taken from the cache. But, it is only possible if the cache is empty, so it creates a new thread.
The thread_cache_size variable has a changeable value. It can be increased to improve performance, which is suitable for multiple new connections. However, you will not see an improvement in performance if your thread implementation is already good.
The value of thread_cache_size should be adjusted depending on the number of new connections. If there are hundreds of them, you should set the value high enough.
Releem automatically tunes thread_cache_size and 44 other variables to improve MySQL performance. Try Releem for Free, or deepen your understanding by reading our detailed article.
thread_cache_size – Usage
It is recommended to set thread_cache_size to a value that makes it easier for new connections to use threads from the cache. Otherwise, they have to create new threads, which can take some time.
Here's how you can set the thread_cache_size in MySQL:
Mysql> set global thread_cache_size = 16;
Query OK, 0 rows affected (0.00 sec).
You can also change it in the my.cnf file. Go through the variables to find:
Thread_cache_size = 16
Here's how you can set the thread_cache_size in MySQL:
Mysql> set global thread_cache_size = 16;
Query OK, 0 rows affected (0.00 sec).
You can also change it in the my.cnf file. Go through the variables to find:
Thread_cache_size = 16
When should thread_cache_size be changed?
The default value of thread_cache_size is based on this formula:
8 + (max_connections / 100)
The number of 100 can be increased or decreased, but the limit is capped to 100.
Here's how to initialize and change the thread_cache_size values:
The values of thread_cache_size must be set according to max_used_connections. It should neither be set higher nor lower. That's because you can not keep more threads in your cache than your server's requirement.
MySQL does not put or allow that many threads in the cache. The threads are not put pre-emptively in the cache. Instead, they are only put after a client creates a thread and disconnects.
For instance, if you do not have up to X clients connecting the server at the same time, you will not have X threads in the cache either.
To ensure efficient results, make sure to examine the difference between the Connections and Threads_created. These status variables will show you whether your thread_cache_size values are accurate or not.
With Releem, you don't have to worry about any of the nuances of thread_cache_size or other variables. Releem will assess your server, and recommend a configuration. Releem can automatically apply the recommended settings to improve MySQL Performance Score. And the whole time you don't have to do anything.
8 + (max_connections / 100)
The number of 100 can be increased or decreased, but the limit is capped to 100.
Here's how to initialize and change the thread_cache_size values:
- Show Global Status Like 'Connections';
- Show Global Status Like 'Threads_created';
- Show Global Status Like 'Max_Used_connections';
The values of thread_cache_size must be set according to max_used_connections. It should neither be set higher nor lower. That's because you can not keep more threads in your cache than your server's requirement.
MySQL does not put or allow that many threads in the cache. The threads are not put pre-emptively in the cache. Instead, they are only put after a client creates a thread and disconnects.
For instance, if you do not have up to X clients connecting the server at the same time, you will not have X threads in the cache either.
To ensure efficient results, make sure to examine the difference between the Connections and Threads_created. These status variables will show you whether your thread_cache_size values are accurate or not.
With Releem, you don't have to worry about any of the nuances of thread_cache_size or other variables. Releem will assess your server, and recommend a configuration. Releem can automatically apply the recommended settings to improve MySQL Performance Score. And the whole time you don't have to do anything.