Larger is not always better.
We suggest that you tune
your configuration until it’s “good enough,” then leave it alone unless you have reason
to believe you’re forgoing a significant performance improvement. You might
also want to revisit it when you change your queries or schema.
we don’t have a one-size-fits-all
“best configuration file” for, say, a four-CPU server with 16 GB of memory and 12
hard drives. You really do need to develop your own configurations, because even a
good starting point will vary widely depending on how you’re using the server.
Be careful when setting variables. More is not always better, and if you set the values
too high, you can easily cause problems: you may run out of memory, causing your
server to swap, or run out of address space.
We suggest that you develop a benchmark suite before you begin tuning your server
(we discussed benchmarking )
The best way to proceed is to change one or two variables, a little at a time, and run
the benchmarks after each change. Sometimes the results will surprise you; you
might increase a variable a little and see an improvement, then increase it a little
more and see a sharp drop in performance. If performance suffers after a change, you
might be asking for too much of some resource, such as too much memory for a
buffer that’s frequently allocated and deallocated.
If you don’t know which files your server reads,
you can ask it:
$ which mysqld
/usr/sbin/mysqld
$ /usr/sbin/mysqld –verbose –help | grep -A 1 'Default options'
Default options are read from the following files in the given order:
/etc/mysql/my.cnf ~/.my.cnf /usr/etc/my.cnf
Here are some examples of the variety of behaviors of which you should be
aware:
• The query_cache_size variable is globally scoped.
• The sort_buffer_size variable has a global default, but you can set it per-session
as well.
• The join_buffer_size variable has a global default and can be set per-session,
but a single query that joins several tables can allocate one join buffer per join, so
there might be several join buffers per query.
In addition to setting variables in the configuration files, you can also change many
(but not all) of them while the server is running. MySQL refers to these as dynamic
configuration variables. The following statements show different ways to change the
session and global values of sort_buffer_size dynamically:
SET sort_buffer_size = <value>;
SET GLOBAL sort_buffer_size = <value>;
SET @@sort_buffer_size := <value>;
SHOW GLOBAL VARIABLES
Variables use different kinds of units, and you have to know the correct unit for each
variable. For example, the table_cache variable specifies the number of tables that
can be cached, not the size of the table cache in bytes. The key_buffer_size is specified
in bytes, whereas still other variables may be specified in number of pages or
other units, such as percentages.
key_buffer_size
Setting this variable allocates the designated amount of space for key buffer (or
key cache) all at once. However, the operating system doesn’t actually commit
memory to it until it is used. Setting the key buffer size to one gigabyte, for
example, doesn’t mean you’ve instantly caused the server to actually commit a
gigabyte of memory to it. (We discuss how to watch the server’s memory usage
in the next blog.)
MySQL lets you create multiple key caches, as we explain later in this blog. If
you set this variable to 0 for a nondefault key cache, MySQL moves any indexes
from the specified cache to the default cache and deletes the specified cache
when nothing is using it anymore. Setting this variable for a nonexistent cache
creates it.
Setting the variable to a nonzero value for an existing cache will flush the specified
cache’s memory. This is technically an online operation, but it blocks all
operations that try to access the cache until the flush is finished.
table_cache_size
Setting this variable has no immediate effect—the effect is delayed until the next
time a thread opens a table. When this happens, MySQL checks the variable’s
value. If the value is larger than the number of tables in the cache, the thread can
insert the newly opened table into the cache. If the value is smaller than the
number of tables in the cache, MySQL deletes unused tables from the cache.
thread_cache_size
Setting this variable has no immediate effect—the effect is delayed until the next
time a connection is closed. At that time, MySQL checks whether there is space
in the cache to store the thread. If so, it caches the thread for future reuse by
another connection. If not, it kills the thread instead of caching it. In this case,
the number of threads in the cache, and hence the amount of memory the thread
cache uses, does not immediately decrease; it decreases only when a new connection
removes a thread from the cache to use it. (MySQL adds threads to the
cache only when connections close and removes them from the cache only when
new connections are created.)
query_cache_size
MySQL allocates and initializes the specified amount of memory for the query
cache all at once when the server starts. If you update this variable (even if you
set it to its current value), MySQL immediately deletes all cached queries, resizes
the cache to the specified size, and reinitializes the cache’s memory.
read_buffer_size
MySQL doesn’t allocate any memory for this buffer until a query needs it, but
then it immediately allocates the entire chunk of memory specified here.
read_rnd_buffer_size
MySQL doesn’t allocate any memory for this buffer until a query needs it, and
then it allocates only as much memory as needed. (The name max_read_rnd_
buffer_size would describe this variable more accurately.)
sort_buffer_size
MySQL doesn’t allocate any memory for this buffer until a query needs to do a
sort. However, when there’s a sort, MySQL allocates the entire chunk of memory
immediately, whether the full size is required or not.
Tuning Memory Usage
Making
good use of the memory you can control is not hard, but it does require you to
know what you’re configuring.
You can approach memory tuning in steps:
1. Determine the absolute upper limit of memory MySQL can possibly use.
2. Determine how much memory MySQL will use for per-connection needs, such
as sort buffers and temporary tables.
3. Determine how much memory the operating system needs to run well. Include
memory for other programs that run on the same machine, such as periodic jobs.
4. Assuming that it makes sense to do so, use the rest of the memory for MySQL’s
caches, such as the InnoDB buffer pool.
32-bit Linux kernels limit the amount of memory any one process can address to
a value that is typically between 2.5 and 2.7 GB. Running out of address space is very
dangerous and can cause MySQL to crash.
Even on 64-bit servers, some limitations still apply. For example, many of the buffers
we discuss, such as the key buffer, are limited to 4 GB on a 64-bit server. Some of
these restrictions are lifted in MySQL 5.1, and there will probably be more changes
in the future because MySQL AB is actively working to make MySQL take advantage
of more powerful hardware. The MySQL manual documents each variable’s
maximum values.
By default MyISAM caches all indexes in the default key buffer, but you can create
multiple named key buffers. This lets you keep more than 4 GB of indexes in mem-
ory at once. To create key buffers named key_buffer_1 and key_buffer_2, each sized
at 1 GB, place the following in the configuration file:
key_buffer_1.key_buffer_size = 1G
key_buffer_2.key_buffer_size = 1G
You can use the CACHE INDEX command to map tables to caches. You
can also tell MySQL to use key_buffer_1 for the indexes from tables t1 and t2 with
the following SQL statement:
mysql> CACHE INDEX t1, t2 IN key_buffer_1;
Now when MySQL reads blocks from the indexes on these tables, it will cache the
blocks in the specified buffer. You can also preload the tables’ indexes into the cache
with the LOAD INDEX command:
mysql> LOAD INDEX INTO CACHE t1, t2;
You can place this SQL into a file that’s executed when MySQL starts up. The file-
name must be specified in the init_file option, and the file can include multiple
SQL commands, each on a single line (no comments are allowed). Any indexes you
don’t explicitly map to a key buffer will be assigned to the default buffer the first
time MySQL needs to access the .MYI file.
You can monitor the performance and usage of the key buffers with information
from SHOW STATUS and SHOW VARIABLES.
Calculate the number of misses incrementally over intervals of 10 to 100 seconds, so
you can get an idea of the current performance. The following command will show
the incremental values every 10 seconds:
$ mysqladmin extended-status -r -i 10 | grep Key_reads
When you’re deciding how much memory to allocate to the key caches, it might help
to know how much space your MyISAM indexes are actually using on disk. You
don’t need to make the key buffers larger than the data they will cache. If you have a
Unix-like system, you can find out the size of the files storing the indexes with a
command like the following:
$ du -sch `find /path/to/mysql/data/directory/ -name "*.MYI"`
Remember that MyISAM uses the operating system cache for the data files, which
are often larger than the indexes.
