Databases Reference
In-Depth Information
tions you'll get, 500 is not an unreasonable starting point anyway. The default is
100, but that's not enough for a lot of applications.
Beware also of surprises that might make you hit the limit of connections. For
example, if you restart an application server, it might not close its connections
cleanly, and MySQL might not realize they've been closed. When the application
server comes back up and tries to open connections to the database, it might be
refused due to the dead connections that haven't timed out yet.
Watch the
Max_used_connections
status variable over time. It is a high-water mark
that shows you if the server has had a spike in connections at some point. If it
reaches
max_connections
, chances are a client has been denied at least once, and
you should probably use the techniques shown in
Chapter 3
to capture server
activity when that occurs.
thread_cache_size
You can compute a reasonable value for this variable by observing the server's
behavior over time. Watch the
Threads_connected
status variable and find its
typical maximum and minimum. You might want to set the thread cache large
enough to hold the difference between the peak and off-peak usage, and go ahead
and be generous, because if you set it a bit too high it's not a big problem. You
might set it two or three times as large as needed to hold the fluctuations in usage.
For example, if the
Threads_connected
status variable seems to vary between 150
and 175, you could set the thread cache to 75. But you probably shouldn't set it
very large, because it isn't really useful to keep around a huge amount of spare
threads waiting for connections; a ceiling of 250 is a nice round number (or 256,
if you prefer a power of two).
You can also watch the change over time in the
Threads_created
status variable. If
this value is large or increasing, it's another clue that you might need to increase
the
thread_cache_size
variable. Check
Threads_cached
to see how many threads
are in the cache already.
A related status variable is
Slow_launch_threads
. A large value for this status vari-
able means that something is delaying new threads upon connection. This is a clue
that something is wrong with your server, but it doesn't really indicate what. It
usually means there's a system overload, causing the operating system not to
schedule any CPU time for newly created threads. It doesn't necessarily indicate
that you need to increase the size of the thread cache. You should diagnose the
problem and fix it rather than masking it with a cache, because it might be affecting
other things, too.
table_cache_size
This cache (or the two caches into which it was split in MySQL 5.1) should be set
large enough to keep from reopening and reparsing table definitions all the time.
You can check this by inspecting the value of
Open_tables
and the change over time
in the value of
Opened_tables
. If you see many
Opened_tables
per second, your
table_cache
value might not be large enough. Explicit temporary tables can also
