High Performance MySQL [Electronic resources] نسخه متنی

1.2 Configuration Files

Configuring a MySQL server is often just a
matter of editing the configuration file to make any changes you need
and then restarting the server. While that sounds rather simple,
adjusting the server's configuration is something
you're not likely to do on a daily basis. More
likely, you've installed MySQL, configured it
minimally or with the defaults, and then let it run. Most users never
go back and adjust the server configuration until a problem arises.
As a result, it's easy to forget how to configure
MySQL.

Another possibility is that you didn't even know
there was a configuration file for MySQL. For the majority of
projects, MySQL's default configuration is more than
sufficient on modern hardware. It may not be as fast as it can be
(because you haven't optimized it), but it will
certainly meet your basic needs.

1.2.1 File Locations

When MySQL starts, it reads its
configuration files in a particular order, unless told otherwise. On
Unix, the order is:

/etc/my.cnf

datadir/my.cnf

~/.my.cnf

On Windows, the order:

%SystemRoot%/my.ini

C:\my.cnf

Three command-line arguments affect how MySQL reads its
configuration files:

--no-defaults

Tells MySQL not to read any configuration files.

--defaults-file=/path/to/file

Tells MySQL to read this file only, and any other files explicitly
declared with --defaults-extra-file.

--defaults-extra-file=/path/to/file

Tells MySQL to read this file after reading the
/etc/my.cnf global configuration file .

Files read later in the process override those set in previously read
files. If both /etc/my.cnf and
datadir/my.cnf
specify a value for the TCP port that MySQL should listen to, the
latter takes precedence.

This behavior can be quite helpful when you need to run
multiple servers either on the same host or
on several different hosts. You can give all servers an identical
copy of /etc/my.cnf that specifies all the
values that aren't specific to a single host. With
that out of the way, the few host-specific settings can be maintained
in a small supplemental file such as
datadir/my.cnf.

A similar strategy works if you'd like to run
multiple servers on a single host. By putting all the common settings
in /etc/my.cnf and the server-specific settings
in each
datadir/my.cnf,
it's easy to keep several servers running with a
minimum of effort.

For example, perhaps you want to run a couple different instances of
the MySQL server, one for each character set you plan to use (to make
your life easier). You might put all your
"common" settings in
/etc/my.cnf and the following in
/etc/my.english.cnf:

default-character-set=latin1
port=3306
socket=/var/lib/mysql/english.sock

Your /etc/my.german.cnf file has:

default-character-set=latin1_de
port=3307
socket=/var/lib/mysql/german.sock

You might even have /etc/my.korean.cnf with:

default-character-set=euc_kr
port=3308
socket=/var/lib/mysql/korean.sock

Now, when you start up the three servers, you want each to load all
the settings from the shared /etc/my.cnf file,
and then get settings from one of each of the previous language-based
configuration files. You can use a command like the following:

$ mysqld_safe --defaults-extra-file=/etc/my.german.cnf
$ mysqld_safe --defaults-extra-file=/etc/my.english.cnf
$ mysqld_safe --defaults-extra-file=/etc/my.korean.cnf

This command yields three different mysqld
instances, running on ports 3306 through 3308, each using the
language-specific configuration options mentioned in the file
indicated by the defaults-extra-file switch.

MySQL is usually installed as a service on Windows. As a result,
Windows users must call c:\mysql\bin\mysqld
directly to pass command-line arguments.

1.2.2 File Format

The configuration file format consists
of one or more sections, each of which may contain one or more lines.
Sections begin with a name in square brackets, such as
[mysqld];
this identifies the program to which the options should be applied.
Each line contains a comment, a key/value pair, a
set-variable directive, or a Boolean directive.
Blank lines are ignored.

Two special section names can occur in each
configuration file:
[server] and [client]. Items
listed in the [server] block apply to the MySQL
server process. Those in the [client] section
apply to all client programs that use the MySQL C client library,
including mysql,
mysqlhotcopy, and
mysqldump.

Comments
begin with # or ; and continue
to the end of the line:

# this is a comment
; so is this

There is no multiline comment format. You can't
place a comment at the end of an otherwise non-empty line:

key_buffer=128M # a comment can't go here

The key/value pairs are settings such as:

user = mysql
port = 3306

The set-variable statements look like key/value
pairs in which the value is a key/value pair itself:

set-variable = key_buffer=384M
set-variable = tmp_table_size=32M

Spaces aren't important in
set-variable lines. You can also write the two
previous lines as follows:

set-variable = key_buffer = 384M
set-variable=tmp_table_size=32M

Either way, MySQL will understand you. However, consider using some
space to enhance readability.

As of Version 4.1, the set-variable= portion of
the variable definition is no longer needed and is deprecated. In
current versions:

set-variable = key_buffer=384M

and:

key_buffer=384M

are both interpreted in an identical manner by the server at startup
time. If you are running a version that supports leaving out the
set-variable clause, it probably is best to do so
because it won't be supported forever.
We've chosen to use the older format here because
it's what you're likely to have
already, and the sample configuration files in the standard MySQL
distribution continue to use it.

The few boolean directives are just stated plainly:

skip-bdb

Individual lines in the configuration file are limited to 2 KB in
length. While it's rare that you'll
ever need to use a line that long, it can occasionally be a problem.

1.2.3 Sample Files

The support-files directory of the MySQL
distribution^[2] contains four sample configuration files:

^[2] These files aren't
included in the Windows distribution of older MySQL releases.

my-small.cnf

my-medium.cnf

my-large.cnf

my-huge.cnf

The names of the files are meant to
signify the size of the machine on which the MySQL server will run.
Each contains comments describing where the size comes from. For
example, my-medium.cnf says:

# This is for a system with little memory (32M - 64M) where MySQL plays
# a important part and systems up to 128M very MySQL is used together with
# other programs (like a web server)

To use a sample file, simply copy it to
/etc/my.cnf (or
systemdir\win.ini on
Windows) and making changes as necessary. While none is likely to be
ideal for any particular setup, each file is a good starting point
for setting up a new system. Failure to make adjustments to the
sample configuration can lead to worse performance in some cases.

Let's look at the sample
my-medium.cnf file from a newly installed system. Some
of the information may not make sense right away (depending on how
much experience you have), but the more examples you see, the more
you'll begin to understand them.

The file starts with some helpful comments about the type of system
this configuration is appropriate for and information needed to
install it:

# Example mysql config file for medium systems.
#
# This is for a system with little memory (32M - 64M) where MySQL plays
# a important part and systems up to 128M very MySQL is used together with
# other programs (like a web server)
#
# You can copy this file to
# /etc/mf.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is /usr/local/mysq/var) or
# ~/.my.cnf to set user-specific options.
#
# One can in this file use all long options that the program supports.
# If you want to know which options a program support, run the program
# with --help option.

Next are the options that apply to all the client tools you might run
on this host:

# The following options will be passed to all MySQL clients
[client]
#password       = your_password
port            = 3306
socket          = /tmp/mysql.sock

What follows next are the parameters specific to the server. The port
and socket options, of course, should agree with what the clients
were just told. The remaining settings allow MySQL to allocate more
RAM for various caches and buffers as well as enable some basic
replication options:

# Here follows entries for some specific programs
# The MySQL server
[mysqld]
port            = 3306
socket          = /tmp/mysql.sock
skip-locking
set-variable    = key_buffer=16M
set-variable    = max_allowed_packet=1M
set-variable    = table_cache=64
set-variable    = sort_buffer=512K
set-variable    = net_buffer_length=8K
set-variable    = myisam_sort_buffer_size=8M
log-bin
server-id       = 1

Next are a few options you probably don't need to
change if you have sufficient disk space:

# Point the following paths to different dedicated disks
#tmpdir         = /tmp/         
#log-update     = /path-to-dedicated-directory/hostname

The BDB options refer to the BDB
storage engine, which provide MySQL's first
transaction-safe storage. You'll learn more about
storage engines in Chapter 2.

# Uncomment the following if you are using BDB tables
#set-variable   = bdb_cache_size=4M
#set-variable   = bdb_max_lock=10000

InnoDB, another of
MySQL's storage engines, has numerous options that
must be configured before you can use them. Because it provides
transaction-safe tables with its own memory management and storage
system, you need to specify where the data files will live, as well
as how much RAM should be used. (InnoDB was briefly known as
Innobase, so you
may see that name in configuration files.)

# Uncomment the following if you are using Innobase tables
#innodb_data_file_path = ibdata1:400M
#innodb_data_home_dir = /usr/local/mysql/var/
#innodb_log_group_home_dir = /usr/local/mysql/var/
#innodb_log_arch_dir = /usr/local/mysql/var/
#set-variable = innodb_mirrored_log_groups=1
#set-variable = innodb_log_files_in_group=3
#set-variable = innodb_log_file_size=5M
#set-variable = innodb_log_buffer_size=8M
#innodb_flush_log_at_trx_commit=1
#innodb_log_archive=0
#set-variable = innodb_buffer_pool_size=16M
#set-variable = innodb_additional_mem_pool_size=2M
#set-variable = innodb_file_io_threads=4
#set-variable = innodb_lock_wait_timeout=50

The final option groups are for specific MySQL command-line
utilities, including the mysql shell:

[mysqldump]
quick
set-variable    = max_allowed_packet=16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[isamchk]
set-variable    = key_buffer=20M
set-variable    = sort_buffer=20M
set-variable    = read_buffer=2M
set-variable    = write_buffer=2M
[myisamchk]
set-variable    = key_buffer=20M
set-variable    = sort_buffer=20M
set-variable    = read_buffer=2M
set-variable    = write_buffer=2M
[mysqlhotcopy]
interactive-timeout

That file would be considerably larger and certainly more confusing
if all the possible settings were listed. For 90% (or more) of MySQL
users, there is simply never a need to adjust more than a few of the
settings listed in the sample files.

1.2.4 Reconfiguration

When
an administrator adjusts the server parameters, it's
common to go through an iterative process that involves making
changes, restarting the server, performing some tests, and repeating
the process. In fact, we'll look at doing just that
in Chapter 3. In the meantime,
it's worth mentioning that you should strongly
consider putting your MySQL configuration files into some sort of
revision control system (RCS, CVS, Subversion, etc.). Doing so gives
you an easy way to track changes and back out of a bad configuration
change.

As of MySQL 4.0, it's possible to change
server variables on the fly
at runtime. For example, if you wanted to increase the size of the
key buffer from what it was set to at startup, you might do the
following:

mysql> SET GLOBAL key_buffer=50M;

This sets the global value for key_buffer to 50 MB.

Some variables, such as sort_buffer_size, can be
set globally so that they affect all new threads on the server, or
they can be defined so that they apply only to the current MySQL
client session. For example, if you wish to make a series of queries
that might better use a large sort buffer, you can type:

mysql> SET SESSION sort_buffer_size=50M;

Variables set using the
SESSION syntax are thread-specific and
don't alter the values other threads use.

It's important to note that any change you make
here, using either
GLOBAL or SESSION syntax,
will not survive a restart of the MySQL server; it's
completely transient in that regard. Runtime changes like this are
excellent for testing scenarios such as, "If I
increase my key_buffer value, will it improve my query
performance?" Once you've found a
value that works for you, though, remember to go back to your
/etc/my.cnf file and put that value into your
configuration file, or you may find yourself wondering weeks or
months later why performance was so horrible after that reboot,
completely forgetting the variable change you made on the fly months
prior.

It's also possible to use arguments on the
mysqld_safe command line to override values
defined in the configuration files. For example, you might do
something like the following:

$ mysqld_safe -O key_buffer=50M

Like the earlier set-variable syntax, the
-O syntax is deprecated as of Version 4.0. Here is a
better way to issue that command:

$ mysqld_safe --key_buffer=50M

Command-line argument changes made in the
mysql.server startup script will, obviously,
survive from server restart to server restart, as long as that
startup script is used to disable and reenable the server.
It's important to point out, though, that
it's usually better to have all your configuration
declarations in a single place, so that maintenance
doesn't become a game of hide-and-seek with the
configuration options, trying to remember where you set which
values.