MySQL - Thread Cache

thread_cache_size: Thread creation/destructions can be expensive, which happen at each connect/disconnect. I normally set this value to at least 16. If application has large jumps in amount of concurrent connections and I see fast growth of Threads_Created variable I boost it higher. The goal is not to have threads created in normal operation. See

The number of threads that are kept in the cache for reuse. This variable can be increased to improve performance if you have a lot of new connections. Increasing this value increases the number of file descriptors that mysqld requires. See

thread_cache_size: How many threads the server should cache for reuse. When a client disconnects, the client's threads are put in the cache if there are fewer than thread_cache_size threads there. Requests for threads are satisfied by reusing threads taken from the cache if possible, and only when the cache is empty is a new thread created. This variable can be increased to improve performance if you have a lot of new connections. Normally, this does not provide a notable performance improvement if you have a good thread implementation. However, if your server sees hundreds of connections per second you should normally set thread_cache_size high enough so that most new connections use cached threads. By examining the difference between the Connections and Threads_created status variables, you can see how efficient the thread cache is.

**Threads_created (status variable): The number of threads created to handle connections. If Threads_created is big, you may want to increase the thread_cache_size value. The cache miss rate can be calculated as Threads_created/Connections. **

Connections (status variable): The number of connection attempts (successful or not) to the MySQL server.

Threads_connected: The number of clients currently connected to the server. If its value is often close to the value of max_connections, it might be good to increase the value of max_connections to allow more connections. If clients that should be able to connect frequently cannot, that too is an indication that max_connections is too small. Each active connection handler / thread requires some memory, so you don't necessarily want to set max_connections to the number of threads allowed by the operating system.

Each thread that is used to manage client connections uses some thread-specific space. The following list indicates these and which variables control their size:

  • A stack (default 192KB, variable thread_stack)
  • A connection buffer (variable net_buffer_length)
  • A result buffer (variable net_buffer_length)

The connection buffer and result buffer both begin with a size given by net_buffer_length but are dynamically enlarged up to max_allowed_packet bytes as needed. The result buffer shrinks to net_buffer_length after each SQL statement. While a statement is running, a copy of the current statement string is also allocated. See

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License