Login

Back to Basics

This chapter from High Performance MySQL by Jeremy Zawodny and Derek J. Balling. (O’Reilly Media, ISBN: 0-596-00306-4, April 2004) talks about binary distributions, the sections in a configuration file, and some SHOW commands that provide a window into what’s going on inside MySQL. This book is for the MySQL administrator who has the basics down but realizes the need to go further.

Many MySQL users and administrators slide into using MySQL. They hear its benefits, find that it’s easy to install on their systems (or better yet, comes pre-installed), and read a quick book on how to attach simple SQL operations to web sites or other applications.

It may take several months for the dragons to raise their heads. Perhaps one particular web page seems to take forever, or a system failure corrupts a database and makes recovery difficult.

Real-life use of MySQL requires forethought and care—and a little benchmarking and testing. This book is for the MySQL administrator who has the basics down but realizes the need to go further. It’s a good book to read after you’ve installed and learned how to use MySQL but before your site starts to get a lot of traffic, and the dragons are breathing down your neck. (When problems occur during a critical service, your fellow workers and friendly manager start to take on decidedly dragon-like appearances.)

The techniques we teach are valuable in many different situations, and sometimes to solve different problems. Replication, for instance, may be a matter of reliability for you—an essential guarantee that your site will still be up if one or two systems fail. But replication can also improve performance; we show you architectures and tech niques that solve multiple problems.

We also take optimization far beyond the simple use of indexes and diagnostic ( EXPLAIN ) statements: this book tells you what the factors in good performance are, where bottlenecks occur, how to benchmark MySQL, and other advanced perfor mance topics.

We ask for a little more patience and time commitment than the average introduc tory computer book. Our approach involves a learning cycle, and experience con vinces us that it’s ultimately the fastest and most efficient way to get where you want.

After describing the problems we’re trying to solve in a given chapter, we start with some background explanation. In other words, we give you a mental model for under standing what MySQL is doing. Then we describe the options you have to solve the problem, and only after all that do we describe particular tools and techniques.

Before we dig into how to tune your MySQL system to optimum performance, it’s best if we go over a couple of ground rules and make sure everyone is on the same page.

If you’ve enjoyed what you’ve seen here, or to get more information, click on the “Buy the book!” graphic. Pick up a copy today!

There are two ways you can install MySQL. As a novice administrator, you may have simply installed a binary package that had precompiled executables, libraries, and configuration files, and placed those files wherever the maker of the binary package decided they should go.

It’s exceedingly rare for a Windows user to compile his own copy of MySQL. If you’re running MySQL on Windows, feel free to down load your copy from the MySQL web site and skip this discussion.

Alternatively, for any number of reasons, you might have decided to compile the MySQL binaries on your own by downloading a source tarball and configuring the installation to best meet your needs. However, don’t do so lightly. Compiling from source has led to countless hours of pain for some users, mostly due to subtle bugs in their compilers or thread libraries. For this very reason, the standard binaries pro vided by MySQL AB are statically linked. That means they are immune to any bugs in your locally installed libraries.

There aren’t too many places where the issue of “binary versus compiled-from-source” will come into play in the average MySQL tuning regimen, but they do hap pen. For example, in Chapter 10, our advice on chrooting your installation can be used only if every file MySQL needs is brought into a single directory tree, which might not be the case in a binary installation.

For a novice administrator on a simple installation, we recommend using a binary package (such as an RPM) to set up your system. However, once you progress to the point of really needing to tinker with the “guts” of MySQL, you will probably want to quickly go back, change a configure flag, and recompile.

If you’ve enjoyed what you’ve seen here, or to get more information, click on the “Buy the book!” graphic. Pick up a copy today!

One thing to keep in mind is that there are a number of sources for binary packages, and nearly all of them set up the system differently.

For example, you can download the binary installation from the MySQL.com web site. You can also install the binary distribution included by your Linux distribution vendor, or the one you grabbed from the FreeBSD ports collection. Finally, you can down load a binary for a platform that isn’t officially supported, but on which someone is keeping a MySQL version current, such as the Amiga architecture. * In any of these cases, you will end up with different directory layouts, compilation options, etc.

(* Note: At the time that sentence was written, it was entirely theoretical: the thinking was “I’m not aware of anything, but surely someone will do that!” In researching it, we found that MySQL for Amiga was, indeed, happening. For those who read German, there’s an article from Amiga Magazine at http://www.amiga-magazin.de/magazin/a08-01/mysql/ that describes how to do it, and a mailing list at http://groups.yahoo.com/group/Amiga_MySql/ for people working on it as well.)

If you use the binary distributions from anyone other than MySQL AB, your support options may be significantly decreased, simply by virtue of having limited yourself to seeking help from those who use that particular distribution. Even a question as sim ple as, “Where is the my.cnf file located on the FreeBSD port of MySQL?” is going to limit those who can respond to two groups: those who have run MySQL using the FreeBSD port, and those on the mailing list or newsgroup, etc. who have encoun tered that question before. On the plus side, if your distribution has automated secu rity announcements and updates, you probably never need to worry about patching MySQL if a security flaw is discovered.

Many binary distributors of MySQL mold it to fit “their” layout. For example, the Debian distribution places the config files in /etc/mysql/ , some language-specific files in /usr/share/mysql/ , the executables directly into /usr/bin/ , etc. It’s not “the Debian way” to segregate an application’s binaries; it incorporates them into the system as a whole. Likewise, in those places it does incorporate them, it does so in what may seem like an odd manner. For instance, you might expect config files to go directly into /etc/ , but instead they get put in /etc/mysql/ . It can be confusing if you’re trying to find everything you need to modify, or if you’re trying to later convert from one type of installation to the other.

The MySQL.com-supplied tarball binary packages, however, behave more like the source-compilation process. All the files—configuration files, libraries, executables, and the database files themselves—end up in a single directory tree, created specifi cally for the MySQL install. This is typically /usr/local/mysql , but it can be altered as needed at installation time. Because this behavior is much the same as a source-com-piled installation, the available support from the MySQL community is much greater. It also makes things easier if you decide later to instead use a MySQL instal lation you compile from source.

On the other hand, the MySQL-supplied binary packages that are distributed using package-management formats such as RPM are laid out similarly to the format of the system they are designed for. For example, the RPM installation you get from MySQL.com will have its files laid out similarly to the Red Hat-supplied RPM. This is so because it’s not uncommon for a Linux distribution to ship an RPM that hasn’t been thoroughly tested and is broken in fairly serious ways. The RPM files MySQL. com distributes are intended as upgrade paths for users with such a problem so they can have “just what they have now, but it works.”

Because of that, if you’re going to install a binary you download from MySQL.com, we highly recommend using the tarball formatted files. They will yield the familiar directory structure the average MySQL administrator is used to seeing.

If you’ve enjoyed what you’ve seen here, or to get more information, click on the “Buy the book!” graphic. Pick up a copy today!

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 configu ration 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.

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 lis ten 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 sup plemental file such as datadir / my.cnf .

A similar strategy works if you’d like to run multiple servers on a single host. By put ting 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 :

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:

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:mysqlbinmysqld directly to pass command-line arguments.

If you’ve enjoyed what you’ve seen here, or to get more information, click on the “Buy the book!” graphic. Pick up a copy today!

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 cli ent 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 = 384 M set-variable=tmp_table_size=32 M

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 configura tion 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.

If you’ve enjoyed what you’ve seen here, or to get more information, click on the “Buy the book!” graphic. Pick up a copy today!

(*Note: These files aren’t included in the Windows distribution of older MySQL releases.)

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 # play s a important part and systems up to 128M very MySQL is used # together wit h other programs (like a web server )

To use a sample file, simply copy it to /etc/my.cnf (or systemdirwin.ini on Windows) and making changes as necessary. While none is likely to be ideal for any par ticular 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 configura tion 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

# play s a important part and systems up to 128M very MySQL is

# used together wit h other programs (like a web server)

#

# You can copy this file t o

# /etc/mf.cnf to set global options ,

# mysql-data-dir/my.cnf to set server-specific options (in thi s

# installation this directory is /usr/local/mysq/var) o r

# ~/.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 progra m

# 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 client s

[client ]

#password = your_passwor d

port = 330 6

socket = /tmp/mysql.soc k

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 serve r

> [mysqld ]

> port = 330 6

> socket = /tmp/mysql.soc k

> skip-lockin g

> set-variable = key_buffer=16 M

> set-variable = max_allowed_packet=1 M

> set-variable = table_cache=6 4

> set-variable = sort_buffer=512 K

> set-variable = net_buffer_length=8 K

> set-variable = myisam_sort_buffer_size=8 M

> log-bi n

> 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 disk s

#tmpdir = /tmp /

#log-update = /path-to-dedicated-directory/hostnam e

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 table s

#set-variable = bdb_cache_size=4 M

#set-variable = bdb_max_lock=1000 0

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 table s

#innodb_data_file_path = ibdata1:400 M

#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=5 M

#set-variable = innodb_log_buffer_size=8 M

#innodb_flush_log_at_trx_commit= 1

#innodb_log_archive= 0

#set-variable = innodb_buffer_pool_size=16 M

#set-variable = innodb_additional_mem_pool_size=2 M

#set-variable = innodb_file_io_threads= 4

#set-variable = innodb_lock_wait_timeout=5 0

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

[mysqldump ]

quic k

set-variable = max_allowed_packet=16 M

[mysql ]

no-auto-rehas h

# Remove the next comment character if you are not familiar with SQ L

#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=20 M

set-variable = sort_buffer=20 M

set-variable = read_buffer=2 M

set-variable = write_buffer=2 M

[mysqlhotcopy ]

interactive-timeou t

That file would be considerably larger and certainly more confusing if all the possi ble 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.

If you’ve enjoyed what you’ve seen here, or to get more information, click on the “Buy the book!” graphic. Pick up a copy today!

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 con sider putting your MySQL configuration files into some sort of revision control sys tem (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 cur rent 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 wonder ing weeks or months later why performance was so horrible after that reboot, com pletely 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 val ues 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, obvi ously, 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.

If you’ve enjoyed what you’ve seen here, or to get more information, click on the “Buy the book!” graphic. Pick up a copy today!

MySQL users often wonder how to find out what their server is actually doing at any point in time—usually when things start to slow down or behave strangely. You can look at operating system statistics to figure out how busy the server is, but that really doesn’t reveal much. Knowing that the CPU is at 100% utilization or that there’s a lot of disk I/O occurring provides a high-level picture of what is going on, but MySQL can tell far more.

Several SHOW commands provide a window into what’s going on inside MySQL. They provide access to MySQL’s configuration variables, ongoing statistics, and counters, as well as a description of what each client is doing.

Show Variables

The easiest way to verify that configuration changes have taken effect is to ask MySQL for its current variable settings. The SHOW VARIABLES command does just that. Executing it produces quite a bit of output, which looks something like this:

mysql> SHOW VARIABLES;

Variable_name

Value

back_log

basedir

binlog_cache_size

character_set

concurrent_insert

connect_timeout

datadir

20

mysql

32768

latin1

ON

5

/home/mysql/data/

The output continues from there, covering over 120 variables in total. The variables are listed in alphabetical order, which is convenient for reading, but sometimes related variables aren’t anywhere near each other in the output. The reason for this is because as MySQL evolves, new variables are added with more descriptive names, but the older variable names aren’t changed; it would break compatibility for any program that expects them. * (* In the rare event they do change, MySQL retains the old names as aliases for the new ones.)

Many of the variables in the list may be adjusted by a set-variable entry in any of MySQL’s configuration files. Some of them are compiled-in values that can not be changed. They’re really constants (not variables), but they still show up in the out put of SHOW VARIABLES . Still others are boolean flags.

Notice that the output of SHOW VARIABLES (and all of the SHOW commands, for that matter) looks just like the output of any SQL query. It’s tabular data. MySQL returns the output in a structured format, making it easy to write tools that can summarize and act on the output of these commands. We’ll put that to good use in later chapters.

If you’ve enjoyed what you’ve seen here, or to get more information, click on the “Buy the book!” graphic. Pick up a copy today!

The other SHOW command we’ll look at is SHOW PROCESSLIST . It outputs a list of what each thread is doing at the time you execute the command.* It’s roughly equivalent to the ps or top commands in Unix or the Task Manager in Windows.

(* Not all threads appear in the SHOW PROCESSLIST output. The thread that handles incoming network connec tions, for example, is never listed.)

It’s common for the State and Info columns to contain more information that pro duces lines long enough to wrap onscreen. So it’s a good idea to use the G escape in the mysql command interpreter to produce vertical output rather than horizontal output:

I d The number that uniquely identifies this process. Since MySQL is a multi-threaded server, it really identifies the thread (or connection)and is unrelated to process IDs the operating system may use. As the operating system does with processes, MySQL starts numbering the threads at 1 and gives each new thread an ID one higher than the previous thread.

User:

The name of the MySQL user connected to this thread.

Host

The name of the host or IP address from which the user is connected. db The database currently selected. This may be NULL if the user didn’t specify a database.

Command

This shows the command state (from MySQL’s internal point of view) that the thread is currently in. Table 1 lists each command with a description of when you are likely to see it. The commands roughly correspond to various function calls in MySQL’s C API. Many commands represent very short-lived actions. Two of those that don’t, Sleep and Query , appear frequently in day-to- day usage.

Table 1. Commands in SHOW PROCESSLIST output

Command

Meaning

Binlog Dump

The slave thread is reading queries from the master’s binary log.

Change user

The client is logging in as a different user.

Connect

A new client is connecting.

Connect Out

The slave thread is connecting to the master to read queries from its binary log.

Create DB

A new database is being created.

Debug

The thread is producing debugging output. This is very uncommon.

Delayed_insert

The thread is processing delayed inserts.

Drop DB

A database is being dropped. Field List The client has requested a list of fields in a table.

Init DB

The thread is changing to a different database, typically as the result of a USE command.

Kill

The thread is executing a KILL command.

Ping

The client is pinging the server to see if it’s still connected. Processlist The client is running SHOW PROCESSLIST.

Query

The thread is currently executing a typical SQL query: SELECT, INSERT, UPDATE, DELETE. This is the most common state other than Sleep.

Quit

The thread is being terminated as part of the server shutdown process.

Refresh

The thread is issuing the FLUSH PRIVILEGE command.

Register Slave

A slave has connected and is registering itself with the master.

Shutdown

The server is being shut down.

Sleep

The thread is idle. No query is being run.

Statistics

Table and index statistics are being gathered for the query optimizer.

Time

The number of seconds that the process has been running the current com mand. A process with a Time of 90 and Command of Sleep has been idle for a minute and a half.

State

Additional human-readable information about the state of this thread. Here’s an example:

Slave connection: waiting for binlog update

This appears on the master server when a slave is actively replicating from it.

Info

This is the actual SQL currently being executed, if any. Only the first 100 charac ters are displayed in the output of SHOW PROCESSLIST . To get the full SQL, use SHOW FULL PROCESSLIST .

If you’ve enjoyed what you’ve seen here, or to get more information, click on the “Buy the book!” graphic. Pick up a copy today!

In addition to all the variable information we can query, MySQL also keeps track of many useful counters and statistics. These numbers track how often various events occur. The SHOW STATUS command produces a tabular listing of all the statistics and their names.

To confuse matters a bit, MySQL refers to these counters as variables too. In a sense, they are variables, but they’re not variables you can set. They change as the server runs and handles traffic; you simply read them and reset them using the FLUSH STATUS command.

The SHOW STATUS command, though, offers a lot of insight into your server’s perfor mance. It’s covered in much greater depth in Appendix A.

Show INNODB Status

The SHOW INNODB STATUS status command provides a number of InnoDB-specific sta tistics. As we said earlier, InnoDB is one of MySQL’s storage engines; look for more on storage engines in Chapter 2.

The output of SHOW INNODB STATUS is different from that of SHOW STATUS in that it reads more as a textual report, with section headings and such. There are different sec tions of the report that provide information on semaphores, transaction statistics, buffer information, transaction logs, and so forth.

SHOW INNODB STATUS is covered in greater detail along with SHOW STATUS in Appendix A. Also, note that in a future version of MySQL, this command will be replaced with a more generic SHOW ENGINE STATUS command.

If you’ve enjoyed what you’ve seen here, or to get more information, click on the “Buy the book!” graphic. Pick up a copy today!