thread_cache_size

Thread_cache_size is a variable that specifies the number of threads the server should cache for reuse. When a client disconnects, the client's threads are put into a cache if there aren't already thread_cache_size threads there.

Releem automatically tunes thread_cache_size and 44 other variables to improve MySQL performance. Try Releem for Free, or deepen your understanding of this variable by reading our documentation.

The thread_cache_size parameter is integral in the management of threads and caching in MySQL and MariaDB database servers. Here's some essential background information to better understand its usage:

• Threads in Database Systems – A thread is a sequence of executable commands that can be managed independently by the scheduler. Database servers use threads to handle multiple client connections simultaneously, allowing for concurrent processing of queries.

• Caching Threads – Caching threads means keeping a pool of threads ready for future use. When a new client connection is requested, the server can quickly allocate a thread from this pool, reducing the overhead of creating a new thread.

• Thread Creation Overhead – Creating and destroying threads can be resource-intensive, particularly under high load conditions. Thread creation involves allocating memory and other system resources, which can be a performance bottleneck if done frequently.

By caching threads, thread_cache_size reduces the frequency of thread creation and destruction, thus saving system resources and improving server efficiency.

During high-traffic periods, the demand for new client connections increases. If the server has to create and destroy threads for each new connection, it can significantly slow down response times. A well-configured thread cache allows the server to handle spikes in connections more smoothly, leading to better overall performance.

Although each cached thread consumes some memory, the overall memory footprint can be lower compared to frequently creating and destroying threads.

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.

The thread_cache_size variable can be configured using the command line or set at startup using the configuration file:
Command Line Configuration:
mysqld> set global thread_cache_size = XX

Replace XX with a value that suits your database needs. To verify that the variable has been changed:

mysqld> show global variables like ‘thread_cache_size’
Configuration File:
[mysqld]
thread_cache_size variable = XX

Replace XX with a value that suits your database needs. Must restart MySQL server to see changes applied.

When tuning the thread_cache_size variable, it's important to understand the potential impact on system performance. Here are a few key considerations:

• Performance Impact – The key is to balance the size of the thread cache against memory usage. Too small a cache leads to frequent thread creation, while too large a cache consumes more memory without proportional benefit.

• Threads_created variable – This metric in MySQL/MariaDB indicates the number of threads created since the server started. A high value suggests that the server frequently creates new threads, which can be a sign that thread_cache_size is too low.

• System Resources – Consider your specific system resources like memory and CPU. A larger cache requires more memory.

• Server Load – Higher traffic servers might benefit from a larger cache size.

• General Guideline – A common rule of thumb is to set thread_cache_size to a value that can handle the typical number of concurrent connections. For instance, if your server usually has around 100 concurrent connections, setting thread_cache_size to 100 or slightly more might be a good starting point.
  
  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.