Learning MySQL (2007)

Part III. Advanced Topics

Chapter 12. Configuring and Tuning the Server

The MySQL server has many features that can be configured to best fit the needs of your system hardware and application. The default settings are fine for most applications, but there are a few that you should be aware of. In this chapter, we look at how you can modify server configuration to suit your setup and for improved overall performance.

There are two types of MySQL program settings: options, which dictate what a program should do, and variables, which dictate the amount of resources that should be set aside for different tasks. Options and variables can be server-wide (global) or limited to a single client session. Some variables apply to the server, while others apply to individual processes, or threads, that handle queries. Resources are generally allocated to a thread only if it requires them. Options and variables can be specified in an options file, or from the command line when starting a program.

You don’t have to come up with optimal settings for each setting on your server. There are ready-to-use configuration files in the support-files directory under the MySQL directory. The my-medium.conf file includes recommended settings for most applications and server configurations; other distributed configuration file variants are tailored for “small,” “large,” and “huge” installations. You can use one of these files as a starting point for your customizations. On a Linux or Mac OS X system, you can copy the file you want to a standard location so that it will be read by the server; on a Windows system, you will have to explicitly tell the server to read in the file. Let’s look at the server options in more detail.

The MySQL Server Daemon

The main MySQL server program, or MySQL server daemon, is called mysqld. Under Windows, there are two main programs you can use: mysqld-nt.exe—which is optimized for Windows XP, 2000, and NT (and probably soon, for Vista)—and mysqld.exe, which can work on older versions of Windows.

On Linux or Mac OS X, the recommended way to start the mysqld program is by calling the mysqld_safe script. This in turn starts mysqld or, if it’s available, mysqld-max—a variant of the MySQL server that includes some more cutting-edge (and less commonly used) features. It also turns on server error logging, which you’d otherwise need to specify as an option to mysqld, and automatically restarts the server if it crashes. Prior to MySQL version 4, this was called safe_mysqld. On a Linux or OS X installation, you will still find a symbolic link called safe_mysqldpointing to mysqld_safe.

MySQL Server Options

The MySQL server is a complex piece of software and has many settings that you can tweak to make it better fit your needs. We’ll discuss some of the more useful server options here. mysqld_safe accepts a number of options of its own and passes on any options it doesn’t handle tomysqld. The options specific to mysqld_safe are probably not of interest to most readers of this book; you can find these by typing mysqld_safe --help at the command line:

basedir

This tells mysqld where MySQL is installed on the system. If you don’t specify this option, the program will try to use the location specified when the program was compiled.

datadir

This tells mysqld where the database files are stored.

defaults-file

This specifies the location of the options file to read; this is particularly useful if you want the server to read in options from a nondefault location.

enable-named-pipe

Allows a server running under Windows to use a named pipe. See skip-networking for more information.

init-file

This specifies a text file containing SQL commands that the server must execute when starting up. This is commonly used to reset a forgotten MySQL root password as discussed in Resetting Forgotten MySQL Passwords” in Chapter 9.

log

This tells mysqld to use the specified file to log every client connection and query.

log-bin

This specifies where you want the binary log of commands that attempt to modify data on the server.

log-error

This tells mysqld to use the specified file to log server startup, shutdown, and errors. By default, this is the file <hostname>.err in the data directory. For example, the log might show that we’ve run out of disk space:

060514 12:39:11 [ERROR] /usr/local/mysql/bin/mysqld: Disk is full writing

'/usr/local/mysql/data/Moodle/mdl_user.MYI' (Errcode: 49).

Waiting for someone to free space... Retry in 60 secs

log-slow-queries

This tells mysqld to log queries that take an unusually long time to process. You can use this information together with the EXPLAIN command to determine how best to tune the server or optimize the tables. Queries that take a time longer than the value of the long_query_time server variable are logged.

pid-file

For servers running on Linux and Mac OS X, this tells mysqld to save its process ID to the specified file; by default, this is <hostname>.pid (for Linux systems) or <hostname>.local.pid (for Mac OS X systems) and located in the MySQL data directory.

port

This is the port the MySQL server should listen to for incoming connections. The default MySQL port is 3306. If there’s already a server listening on that port, you’ll need to specify a different port. We described how to do this in Chapter 2. On a Linux or Mac OS X system, connections from a client on the same system go through a Unix socket file rather than through this TCP port.

shared-memory-base-name

For servers running on Windows, this tells mysqld to use the specified shared memory name. The default value is MYSQL. If you want to run multiple MySQL servers on a single Windows host, you’ll need to specify a different value for each server.

skip-networking

With this option, you can ask the server to not listen to a TCP port for incoming connections. This is more secure, as only connections from clients on the same system as the server (localhost) will be accepted. On a Linux or Mac OS X system, clients connect through the Unix socket file (described next). Under Windows, clients need to connect through a named pipe, so you’ll need to set the enable-named-pipe option for this to work.

socket

The absolute path to the Unix socket file on Linux and Mac OS X, or the named pipe under Windows, that the server uses for incoming connections from the local host. The default path is /tmp/mysql.sock for the Unix socket, and MYSQL for the Windows named pipe. You generally need to specify a different value only if there’s already a server using the default socket.

tmpdir

This tells mysqld where to store its temporary files.

user

On Linux and Mac OS X, the server tries to run under your account; if you start the server from the root account, the server will run with all the privileges of this superuser, which is dangerous. The user option tells mysqld what user account to run under. It’s a good idea to create an account with the name mysql, with access permissions for only the MySQL directories, and set the server to run under that username. Don’t forget that this user should be able to read and write files in the MySQL data and temporary directories. If you don’t specify the username, most MySQL scripts will automatically try to use your operating system account name as the value for the username.

Examples

Let’s look at how you might use these options in practice. Consider the case where we need to run multiple servers on a single host; each server must have a different port, socket, and process ID file. If we want the servers to keep logs, the logfiles for each server should be different as well. For example, if we’ve installed MySQL under Linux or Mac OS X in the directory /usr/local/mysql and want to run the server under the mysql account—with the database, log, and temporary files under the /tmp/mysql directory—we could start the server with the command (all on one line):

$ mysqld_safe \

--user=mysql \

--port=57777 \

--socket=/tmp/mysql/server1.sock \

--basedir=/usr/local/mysql \

--datadir=/tmp/mysql/data \

--tmpdir=/tmp/mysql/tmp \

--log=/tmp/mysql/logs/server1.main.log \

--log-error=/tmp/mysql/logs/server1.error.log \

--pid-file=/tmp/mysql/logs/server1.pid

Instead of typing in the settings at the command line, we can specify the required values in an options file as:

[mysqld]

user= mysql

port= 57777

socket= /tmp/mysql/server1.sock

basedir= /usr/local/mysql

datadir= /tmp/mysql/data

tmpdir= /tmp/mysql/tmp

# log server messages to:

log= /tmp/mysql/logs/server1.main.log

# log errors to this file:

log-error=/tmp/mysql/logs/server1.error.log

pid-file= /tmp/mysql/logs/server1.pid

We described how to use options files in Chapter 11. Note that since these are really options to the mysqld program, these options are listed under the mysqld group. Options specific to mysqld_safe can be listed under the mysqld_safe group.

Now consider the case where MySQL has been installed to the directory /home/adam/mysql-5.0.22. You can imagine the directory /Users/adam/mysql-5.0.22 being used for Mac OS X. For nonstandard installations, the mysqld_safe program should be called from the MySQL installation directory, so we first change to that directory:

$ cd /home/adam/mysql-5.0.22

and then start the server by typing (all on one line):

$ bin/mysqld_safe \

--port=57777 \

--socket=/home/adam/mysql-5.0.22/logs/mysqld-new.sock.file \

--basedir=/home/adam/mysql-5.0.22 \

--datadir=/home/adam/mysql-5.0.22/data \

--log=/home/adam/mysql-5.0.22/logs/main.log \

--log-error=/home/adam/mysql-5.0.22/logs/error.log \

--pid-file=/home/adam/mysql-5.0.22/logs/zahra.pid

The corresponding options file entries would be:

[mysqld]

port= 57777

socket= /home/adam/mysql-5.0.22/logs/mysqld-new.sock.file

basedir= /home/adam/mysql-5.0.22

datadir= /home/adam/mysql-5.0.22/data

log= /home/adam/mysql-5.0.22/logs/main.log

log-error=/home/adam/mysql-5.0.22/logs/error.log

pid-file= /home/adam/mysql-5.0.22/logs/zahra.pid

Finally, let’s look at an example for Windows, where we have MySQL installed in the directory C:\mysql-5.0.22-win32. We want to have the MySQL datafiles placed in C:\mysql\data, we want the logfiles placed in C:\mysql\logs, and we want the server to listen on port 13306. So we type (all on one line):

C:\>mysqld-net.exe \

--port=13306 \

--basedir=C:\mysql-5.0.22-win32 \

--datadir=C:\mysql\data \

--log-bin=C:\mysql\logs\mysql-binary.log

Note that the specified directories must exist, and the data directory must contain the mysql database files (the privilege tables); otherwise, MySQL will complain and abort.

Server Variables

Variables configure server resources and can be used to optimize the server settings to suit the hardware of the host computer, and to allocate resources for improved performance. For example, the variable max_connections specifies the maximum number of clients that can be connected to the server at any one time.

When choosing a value for a server variable, you need to think carefully about the nature of your application and your clients. For example, when setting the 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 maximum 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 sort_buffer_size variable controls the amount of memory available for queries that have an ORDER BY clause. The read_buffer_size and sort_buffer_size variables operate on a per-thread basis.

As with options, variables can be specified on the command line or in an options file. For example, the variable max_connections can be specified from the command line as:

$ mysqld --max_connections=200

or in an options file as:

[server]

max_connections=200

Some variables can also be set from within a client using the SET command; for example, you could write:

mysql> SET sort_buffer_size=2000000;

Query OK, 0 rows affected (0.00 sec)

To set a variable to apply across the server, rather than to the current client session, you need to add the GLOBAL keyword:

mysql> SET GLOBAL sort_buffer_size=2000000;

Query OK, 0 rows affected (0.01 sec)

To set GLOBAL variables, you need to have superuser privileges (in practice, you need to be logged in as the user root):

mysql> SET max_connections=200;

ERROR 1227 (HY000): Access denied; you need the SUPER privilege for this operation

Some variables are inherently related to the server, rather than to an individual session. MySQL will complain if you try to set a value for such variables without using the GLOBAL keyword:

mysql> SET max_connections=200;

ERROR 1229 (HY000): Variable 'max_connections' is a GLOBAL variable and should be

set with SET GLOBAL

The Slow Query Log

To determine what you should optimize, you should identify the frequently used queries that take a long time to complete. If you start the server with the log-slow-queries option, any queries that take more than 10 seconds to complete will be logged. You can change this duration by modifying the value of the long_query_time variable. You can add the log-queries-not-using-indexes option to ask the server to also log queries that don’t use an index. The default location of the slow queries log is in the data directory, with a name in the form <hostname>-slow.log.

Let’s look at an excerpt from a slow query log:

# Time: 060630 22:51:32

# User@Host: root[root] @ localhost []

# Query_time: 65 Lock_time: 0 Rows_sent: 8228 Rows_examined: 16577

USE LinkTracktclick;

SELECT DISTINCT * FROM Countries, clicktable ORDER BY CLICKS DESC;

This SELECT query took 65 seconds; if it’s a query that’s used often, we should add indexes to improve the query speed, or perhaps redesign the query in a manner that takes less time.

It can be hard to understand the entries in the slow-query logfile; you can use the mysqldumpslow script from the MySQL scripts directory to help summarize and organize this information. For example, we can ask for the two queries that took the longest time using the -t option:

$ scripts/mysqldumpslow -t 2

Reading mysql slow query log from ./log-slow.log

Count: 1 Time=65.00s (565s) Lock=0.00s (0s) Rows=8228.0 (8228), root[root]@localhost

select distinct * from tmpCountries, clicktable order by clicks desc

Count: 35 Time=12.00s (0s) Lock=0.00s (0s) Rows=3.8 (132),

RPUser[RPUser]@redback.cs.rmit.edu.au

select distinct id, surname, firstname, position_id from

student st, supervises s where st.id = s.student_id and s.status=N

and st.active=N and st.visible=N and supervisor_id = N

The Count is the number of queries that have been executed; from this, it would probably be better to focus on optimizing the second query, since it’s been run 35 times, rather than the top query, which has been run only once.

The script tries to process the slow-query logfile at the default location; if you’re using a nonstandard location, you should specify the logfile location:

$ mysqldumpslow path_to_your_slow_query_log_file

If you’re using Windows, you will need to follow the steps in Installing Perl modules under Windows” in Chapter 2 to use this Perl script.

Query Caching

Some applications require the database to repeatedly look up and return specific data. For example, the front page of an online store application might display all the products in stock that have been marked as being on sale. Every visitor to the online store will load this front page, and every page load will require the database server to look up all the products that are on sale.

It’s much more efficient for the database server to store, or cache, the result of this query, and simply return the cached result every time it sees the same query. If the data is changed, the database considers the cached result to be stale and runs the query again (and caches the new result). Query caching can have a huge effect on performance; the MySQL manual describes a speedup of more than two times as being typical.

You can configure the size of the server’s query cache by modifying the query_cache_size variable. The larger the cache, the more queries that can be cached. Like most other buffers, this follows the law of diminishing returns; doubling the query cache size is unlikely to double the effectiveness of the cache. You can check the server’s cache settings as follows:

mysql> SHOW VARIABLES LIKE '%query_cache%';

+------------------------------+---------+

| Variable_name | Value |

+------------------------------+---------+

| have_query_cache | YES |

| query_cache_limit | 1048576 |

| query_cache_min_res_unit | 4096 |

| query_cache_size | 3999744 |

| query_cache_type | ON |

| query_cache_wlock_invalidate | OFF |

+------------------------------+---------+

6 rows in set (0.00 sec)

Here, caching is available (have_query_cache is YES), and the query cache size is 399,360 KB. When the query cache size is nonzero, the query_cache_type setting determines which queries should be cached; with this set to ON, almost all SELECT queries are cached. There are main two exceptions: queries that explicitly disable caching with the SQL_NO_CACHE keyword immediately after the SELECT, and queries that use functions that vary with time and user—for example, queries that include the function CURRENT_TIMESTAMP().

The query_cache_limit variable indicates the largest result to store for any given query, while query_cache_min_res_unit specifies the allocation units in the cache (the default is generally fine). Finally, query_cache_wlock_invalidate determines whether an active write lock granted to one client will prevent other clients from reading cached results.

Of these settings, you will typically only need to ensure that caching is available (query_cache_type is ON) and set an appropriate value for query_cache_size:

mysql> SET query_cache_type = ON;

Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL query_cache_size = 40000000;

Query OK, 0 rows affected (0.01 sec)

You can then check on how queries are being read from the cache:

mysql> SHOW STATUS LIKE 'qcache%';

+-------------------------+----------+

| Variable_name | Value |

+-------------------------+----------+

| Qcache_free_blocks | 1 |

| Qcache_free_memory | 39826928 |

| Qcache_hits | 7 |

| Qcache_inserts | 128 |

| Qcache_lowmem_prunes | 0 |

| Qcache_not_cached | 10 |

| Qcache_queries_in_cache | 73 |

| Qcache_total_blocks | 178 |

+-------------------------+----------+

8 rows in set (0.00 sec)

Qcache_hits indicates how many queries have been answered directly from the cache. Over time, you should see a fair number of hits.

The Old Variables Format

You may encounter an older way of specifying variable values from the command line and in the options file. Under the old way, you’d use the set-variable= option from the command line, as in:

$ mysqld_safe --set-variable=sort-buffer-size=1048576

or in an options file, as in:

set-variable=sort_buffer_size=1048576

This format still works but has been deprecated since MySQL version 4.1. In the new format, you omit the set-variable=; we recommend you use the new method where possible.

Checking Server Settings

The SHOW VARIABLES command lists detailed server configuration settings, including things like the server version, paths to the different directories and files used by the server, and maximum concurrent connections. We’ll show only a few of them here; try them on your own server:

mysql> SHOW VARIABLES;

+---------------------------------+-----------------------------------------------+

| Variable_name | Value |

+---------------------------------+-----------------------------------------------+

| auto_increment_increment | 1 |

| auto_increment_offset | 1 |

| automatic_sp_privileges | ON |

| back_log | 50 |

| basedir | / |

| binlog_cache_size | 32768 |

| bulk_insert_buffer_size | 8388608 |

| character_set_client | latin1 |

| version_compile_os | mandriva-linux-gnu |

...

| wait_timeout | 28800 |

+---------------------------------+-----------------------------------------------+

185 rows in set (0.01 sec)

The mysqladmin variables command produces the same result from the command line:

$ mysqladmin --user=root --password=the_mysql_root_password variables

+---------------------------------+-----------------------------------------------+

| Variable_name | Value |

+---------------------------------+-----------------------------------------------+

| auto_increment_increment | 1 |

...

| wait_timeout | 28800 |

+---------------------------------+-----------------------------------------------+

From the monitor, you can view a subset of the variables by adding a LIKE clause:

mysql> SHOW VARIABLES LIKE 'k%';

+--------------------------+----------+

| Variable_name | Value |

+--------------------------+----------+

| key_buffer_size | 16777216 |

| key_cache_age_threshold | 300 |

| key_cache_block_size | 1024 |

| key_cache_division_limit | 100 |

+--------------------------+----------+

4 rows in set (0.00 sec)

The SHOW STATUS command shows you MySQL server status information:

mysql> SHOW STATUS;

+----------------------------+------------+

| Variable_name | Value |

+----------------------------+------------+

| Aborted_clients | 8 |

| Aborted_connects | 0 |

| Binlog_cache_disk_use | 0 |

| Binlog_cache_use | 0 |

| Bytes_received | 858887090 |

| Bytes_sent | 8535929437 |

...

| Com_insert | 318046 |

...

| Com_lock_tables | 126 |

...

| Com_select | 4541404 |

| Com_unlock_tables | 126 |

| Com_update | 153656 |

| Connections | 238544 |

| Created_tmp_disk_tables | 83154 |

| Created_tmp_files | 47 |

| Created_tmp_tables | 128857 |

...

| Key_blocks_not_flushed | 0 |

| Key_blocks_unused | 6119 |

| Key_blocks_used | 6698 |

| Key_read_requests | 45921497 |

| Key_reads | 35348 |

| Key_write_requests | 1612717 |

| Key_writes | 986186 |

| Max_used_connections | 15 |

...

| Open_files | 128 |

| Slave_retried_transactions | 0 |

| Slow_launch_threads | 0 |

| Slow_queries | 21 |

...

| Sort_scan | 212588 |

| Table_locks_immediate | 5831792 |

| Table_locks_waited | 185 |

| Threads_cached | 0 |

| Threads_connected | 1 |

| Threads_created | 238543 |

| Threads_running | 1 |

| Uptime | 1786334 |

+----------------------------+------------+

157 rows in set (0.00 sec)

We’ve omitted most of the rows here for space considerations; your instance may well show over 250 variable values.

You can also display the server status using mysqladmin status or mysqladmin extended-status commands:

$ mysqladmin --user=root --password=the_mysql_root_password status

Uptime: 12093 Threads: 1 Questions: 7160 Slow queries: 0 Opens: 76

Flush tables: 1 Open tables: 60 Queries per second avg: 0.592

The extended-status command produces the same output as the monitor’s SHOW STATUS command.

The SHOW PROCESSLIST command displays all running threads on the MySQL server and is a useful tool for diagnosing problems or understanding what users are doing. Try it on your server when you’re logged in as the root user:

mysql> SHOW PROCESSLIST;

+-------+------------+-----------------------------+--------+...

| Id | User | Host | db |...

+-------+------------+-----------------------------+--------+...

| 26534 | root | localhost | |...

+-------+------------+-----------------------------+--------+...

... +---------+------+-------+------------------+

... +---------+------+-------+------------------+

... | Sleep | 1 | | |

... | Query | 0 | | show processlist |

... +---------+------+-------+------------------+

2 rows in set (0.00 sec)

The output is fairly self-explanatory, and details are in the “SHOW syntax” section of the MySQL manual. The mysqladmin processlist command produces the same output:

$ mysqladmin --user=root --password=the_mysql_root_password processlist

+-------+------------+-----------------------------+--------+...

| Id | User | Host | db |...

+-------+------------+-----------------------------+--------+...

| 26534 | root | localhost | |...

+-------+------------+-----------------------------+--------+...

... +---------+------+-------+------------------+

... +---------+------+-------+------------------+

... | Sleep | 1 | | |

... | Query | 0 | | show processlist |

... +---------+------+-------+------------------+

You can end a problematic process using the KILL command with the number of the process. If you somehow kill your own connection, the monitor will establish a new connection to the server, resulting in a new process number. Here, we kill our own (the root user’s) connection — see how the new process number (26535) is different?:

mysql> KILL 26534;

Query OK, 0 rows affected (0.02 sec)

mysql> SHOW PROCESSLIST;

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id: 26535

Current database: *** NONE ***

+-------+------------+-----------------------------+--------+...

| Id | User | Host | db |...

+-------+------------+-----------------------------+--------+...

| 26535 | root | localhost | |...

+-------+------------+-----------------------------+--------+...

... +---------+------+-------+------------------+

... +---------+------+-------+------------------+

... | Sleep | 1 | | |

... | Query | 0 | | show processlist |

... +---------+------+-------+------------------+

2 rows in set (0.00 sec)

The mysqladmin kill command does the same thing:

$ mysqladmin --user=root --password=the_mysql_root_password kill 26534

Other Things to Consider

There are many other aspects of database and application design that you can look at when considering performance. For example, if you make large-scale changes to a table (for example, by deleting many entries), you are likely to get better performance if you run the OPTIMIZE TABLEcommand to reorganize the table file on disk. This is especially true if the table contains variable length fields:

mysql> OPTIMIZE TABLE artist;

+--------------+----------+----------+----------+

+--------------+----------+----------+----------+

+--------------+----------+----------+----------+

1 row in set (0.06 sec)

In most cases, the MySQL server handles this adequately, so you won’t need to use this command often, if at all.

Careful design of tables and indexes can also help improve performance. In Transactions and Locking” in Chapter 7, we saw how transaction support can be useful for some applications. However, transaction support adds overhead to database operations. If you need transaction support in some tables, but not in others, you can use different table types within a single database. In The EXPLAIN Statement” in Chapter 8, we saw how indexes can help increase the speed of queries. Try to minimize operations that scan all rows in a table, and try to add indexes that can be used by frequent queries. Shorter keys are generally faster to use, so try to keep the length of primary keys down.

We won’t discuss performance any further in this book, but if you’re setting up a production database site, it’s definitely worth looking at the resources listed in the next section.

Resources

Database server tuning is a complex art, and is largely beyond the scope of this book To learn more about tuning MySQL, we recommend the following resources:

§ The MySQL Manual: Optimization (http://dev.mysql.com/doc/refman/5.1/en/optimization.html)

§ MySQL Database Design and Tuning by Robert D. Schneider (MySQL Press)

§ High Performance MySQL by Jeremy D. Zawodny and Derek J. Balling (O’Reilly)

Exercises

1. Why is it important to allocate a large value to read_buffer_size?

2. What is the advantage of caching query results?

3. What does the OPTIMIZE TABLE command do?