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 http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/

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 http://www.interworx.com/support/docs/nodeworx/mysql/howto-mysql-options

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 http://www.learn-mysql-tutorial.com/TuneMySQL.cfm

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