Databases Reference
In-Depth Information
max_connections
variable, you need to remember that clients can include application
web pages that interact with the database. This variable affects the number of people
who can concurrently load the database-enabled web pages; each request to load such
a page counts as a separate connection. Of course, these connections are short, typically
lasting only a few seconds while the page is generated and served to the web browser.
Some of the more important variables control how memory and files are managed.
MySQL databases are stored in files in the data directory, and the server needs to open
and close these files. However, opening and closing files is a relatively slow operation,
so the fewer times we need to do this, the better. The MySQL server variable
table_cache
specifies the maximum number of tables that can be open at once. The
larger this number, the fewer times we need to close open files and open closed ones.
You also need to consider how the
max_connections
value influences the value you
choose for
table_cache
. If you allow 100 concurrent connections, and your application
has queries that perform join operations on three tables, then your
table_cache
should
be at least 300. Note that operating systems impose their own limitations on the max-
imum number of files that can be held open by any program, as well as for the whole
system overall, so you may run into operating system limits if you set some MySQL
variable values too high.
We mentioned earlier that opening and closing files is a relatively costly process. It's
also far more costly to access files on disk than to access memory; if the server can keep
most of what it needs handy in memory, things will generally be much faster. In “Keys
and Indexes” in Chapter 6, we explored how an index can help MySQL to quickly find
data in a large table, just as an index page allows us to quickly find text in a book. When
data is requested from a database table that has an index, the server first looks up the
data location using the index file, then reads the data from the appropriate location in
the table file. This means that the server has to access the disk twice; if it can keep the
index file in memory, it has to read the disk only once to fetch the data, which is much
more efficient.
The MySQL server variable
key_buffer_size
controls the amount of memory set aside
for MyISAM table indexes. The default value is 8 MB, but you can set it to any value
up to 4 GB. Of course, you should actually have the required amount of memory on
your system, and you should leave enough memory for the operating system and other
processes. If you're using a dedicated MySQL server, you might want to set this value
as high as 20 to 40 percent of total system memory.
Some queries can't use an existing index. For example, entries in a telephone directory
are typically sorted by surname, then by given name. We can easily find all the people
with a surname starting with the letter “S,” but to find all the people with a first name
starting with “S,” we'd need to look at every entry in the directory. For such operations,
a thread needs to read through all the data in a database table, which involves lots of
disk reads. It's faster to read a small number of large data chunks, so it's good to allocate
a large value for the
read_buffer_size
for such whole-of-table operations. Similarly, the
