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:
- 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. Thus, the thread loses simultaneously.
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.