Databases Reference
In-Depth Information
cause a growing number of opened tables even when the table cache isn't fully
used, though, so it might be nothing to worry about. Your clue would be that
Opened_tables
grows constantly even though
Open_tables
isn't as large as
table_
cache_size
.
Even if the table cache is useful, you should not set this variable too large. It turns
out that the table cache can be counterproductive in two circumstances.
First, MySQL doesn't use a very efficient algorithm to check the cache, so if it's
really big, it can get really slow. You probably shouldn't set it higher than 10,000
in most cases, or 10,240 if you like those powers of two.
18
The second reason to avoid setting this very large is that some workloads simply
aren't cacheable. If the workload isn't cacheable, and everything is going to be a
cache miss no matter how large you make the cache, forget the cache and set it to
zero! This helps you avoid making the situation worse; a cache miss is better than
an expensive cache check followed by a cache miss. What kinds of workloads aren't
cacheable? If you have tens or hundreds of thousands of tables and you use them
all pretty uniformly, you probably can't cache them all, and you're better off setting
this variable small. This is sometimes appropriate on systems that have a very large
number of collocated applications, none of which is very busy.
A reasonable starting value for this setting is 10 times as big as
max_connections
,
but again, keep it under 10,000 or so in most cases.
There are several other kinds of settings that you will frequently include in your con-
figuration file, including binary logging and replication settings. Binary logging is useful
for enabling point-in-time recovery and for replication, and replication has a few set-
tings of its own. We'll cover the important settings in the chapters on replication and
backups, later in this topic.
Safety and Sanity Settings
After your basic configuration settings are in place, you might wish to enable a number
of settings that make the server safer and more reliable. Some of them influence per-
formance, because safety and reliability are often more costly to guarantee. Some are
just sensible, however: they prevent silly mistakes such as inserting nonsensical data
into the server. And some don't make a difference in day-to-day operation, but prevent
bad things from happening in edge cases.
Let's look at a collection of useful options for general server behavior first:
18. Have you heard the joke about powers of two? There are 10 types of people in the world: those who
understand binary, and those who don't. There are also another 10 types of people: those who think
binary/decimal jokes are funny, and those who have sex. We won't say whether or not we think that's
hilarious.
