Databases Reference
In-Depth Information
Completing the Basic Configuration
We're done with the tour of server internals—hope you enjoyed the trip! Now let's
return to our sample configuration file and see how to choose values for the settings
that remain.
We've already discussed how to choose values for the general settings such as the data
directory, the InnoDB and MyISAM caches, logs, and a few other things. Let's go over
what remains:
tmp_table_size
and
max_heap_table_size
These settings control how large an in-memory temporary table using the Memory
storage engine can grow. If an implicit temporary table's size exceeds either of these
settings, it will be converted to an on-disk MyISAM table so it can keep growing.
(An implicit temporary table is one that you don't create yourself; the server creates
it for you to hold an intermediate result while executing a query.)
You should simply set both of these variables to the same value. We've chosen the
value
32M
for our sample configuration file. This might not be enough, but beware
of setting this variable too large. It's good for temporary tables to live in memory,
but if they're simply going to be huge, it's actually best for them to just use on-disk
tables, or you could run the server out of memory.
Assuming that your queries aren't creating enormous temporary tables (which you
can often avoid with proper indexing and query design), it's a good idea to set these
variables large enough that you don't have to go through the process of converting
an in-memory table to an on-disk table. This procedure will show up in the process
list.
You can look at how the server's
SHOW STATUS
counters change over time to under-
stand how often you create temporary tables and whether they go to disk. You
can't tell whether a table was created in memory and then converted to on-disk or
just created on-disk to begin with (perhaps because of a
BLOB
column), but you can
at least see how often the tables go to disk. Examine the
Created_tmp_
disk_tables
and
Created_tmp_tables
variables.
max_connections
This setting acts like an emergency brake to keep your server from being over-
whelmed by a surge of connections from the application. If the application mis-
behaves, or the server encounters a problem such as a stall, a lot of new connections
can be opened. But opening a connection does no good if it can't execute queries,
so being denied with a “too many connections” error is a way to fail fast and fail
cheaply.
Set
max_connections
high enough to accommodate the usual load that you think
you'll experience, as well as a safety margin to permit logging in and administering
the server. For example, if you think you'll have 300 or so connections in normal
operations, you might set this to 500 or so. If you don't know how many connec-
