Databases Reference
In-Depth Information
the cache, if there's room. If there isn't room, MySQL destroys the thread. As long as
MySQL has a free thread in the cache it can respond rapidly to connection requests,
because it doesn't have to create a new thread for each connection.
The
thread_cache_size
variable specifies the number of threads MySQL can keep in
the cache. You probably won't need to configure this value unless your server gets many
connection requests. To check whether the thread cache is large enough, watch the
Threads_created
status variable. We generally try to keep the thread cache large enough
that we see fewer than 10 new threads created each second, but it's often pretty easy
to get this number lower than 1 per second.
A good approach is to watch the
Threads_connected
variable and try to set
thread
_cache_size
large enough to handle the typical fluctuation in your workload. For ex-
ample, if
Threads_connected
usually stays between 100 and 120, you can set the cache
size to 20. If it stays between 500 and 700, a thread cache of 200 should be large enough.
Think of it this way: at 700 connections, there are probably no threads in the cache; at
500 connections, there are 200 cached threads ready to be used if the load increases to
700 again.
Making the thread cache very large is probably not necessary for most uses, but keeping
it small doesn't save much memory, so there's little benefit in doing so. Each thread
that's in the thread cache or sleeping typically uses around 256 KB of memory. This is
not very much compared to the amount of memory a thread can use when a connection
is actively processing a query. In general, you should keep your thread cache large
enough that
Threads_created
doesn't increase very often. If this is a very large number,
however (e.g., many thousand threads), you might want to set it lower because some
operating systems don't handle very large numbers of threads well, even when most of
them are sleeping.
The Table Cache
The table cache is similar in concept to the thread cache, but it stores objects that
represent tables. Each object in the cache contains the associated table's parsed
.frm
file, plus other data. Exactly what else is in the object depends on the table's storage
engine. For example, for MyISAM, it holds the table data and/or index file descriptors.
For merge tables it might hold many file descriptors, because merge tables can have
many underlying tables.
The table cache can help you reuse resources. For instance, when a query requests
access to a MyISAM table, MySQL might be able to give it a file descriptor from the
cached object. Although this does avoid the cost of opening a file descriptor, that's not
as expensive as you might think. Opening and closing file descriptors is very fast on
local storage; the server should be able to do it a million times a second easily (it's
different on network-attached storage, though). The real benefit of the table cache is
for MyISAM tables, where it lets the server avoid modifying the MyISAM file headers
to mark a table as “in use.”
7
