Now you will learn

Basic hardware and software optimization tips for your MySQL server

Key start-up parameters for your MySQL server

How to use the OPTIMIZE command

How to use the EXPLAIN command

Building an Optimized Platform

Designing a well-structured, normalized database schema is just half of the optimization puzzle. The other half is building and fine-tuning a server to run this fine database. Think about the four main components of a server: CPU, memory, hard drive, and operating system. Each of these better be up to speed, or no amount of design or programming will make your database faster!

CPU?The faster the CPU, the faster MySQL will be able to process your data. There's no real secret to this, but a 750 MHz processor is significantly faster than a 266 MHz processor. With processor speeds now over 1 GHz, and with reasonable prices all around, it's not difficult to get a good bang for your buck.

Memory?Put as much RAM in your machine as you can. You can never have enough, and RAM prices will be at rock bottom for the foreseeable future. Having available RAM can help balance out sluggish CPUs.

Hard Drive?The proper hard drive will be both large enough and fast enough to accommodate your database server and its traffic. An important measurement of hard drive speed is its seek time, or the amount of time it takes for the drive to spin around and find a specific piece of information. Seek time is measured in milliseconds, and an average disk seek time is around 8 or 9 milliseconds. When buying a hard drive, make sure it's big enough to accommodate all the data you'll eventually store in your database and fast enough to find it quickly.

Operating System?If you use an operating system that's a resource hog, you have two choices: buy enough resources (that is, RAM) so that it doesn't matter, or use an operating system that doesn't suck away all your resources just so that you can have windows and pretty colors. Also, if you are blessed with a machine with multiple processors, be sure your operating system can handle this condition and handle it well.

If you put the proper pieces together at the system level, you'll have taken several steps toward overall server optimization.

Using the benchmark() Function

A quick test of your server speed is to use the benchmark() MySQL function to see how long it takes to process a given expression. You can make the expression something simple, such as 10 + 10, or something more extravagant, such as extracting pieces of dates.

No matter the result of the expression, the result of benchmark() will always be 0. The purpose of benchmark() is not to retrieve the result of the expression but to see how long it takes to repeat the expression for a specific number of times. For example, the following command executes the expression 10 + 10 one million times:

mysql> SELECT BENCHMARK(1000000,10+10);
+--------------------------+
| BENCHMARK(1000000,10+10) |
+--------------------------+
|            0 |
+--------------------------+

1 row in set (0.14 sec)This command executes the date extraction expression, also one million times:

mysql> SELECT BENCHMARK(1000000, EXTRACT(YEAR FROM NOW()));
+----------------------------------------------+
| BENCHMARK(1000000, EXTRACT(YEAR FROM NOW())) |
+----------------------------------------------+
|                      0 |
+----------------------------------------------+

MySQL Startup Options

MySQL AB provides a wealth of information regarding the tuning of server parameters, much of which the average user will never need to use. So as not to completely overwhelm you with information, this section will contain a few of the more common startup options for a finely tuned MySQL server.

When you start MySQL, a configuration file called my.cnf is loaded. This file contains information ranging from port number to buffer sizes but can be overruled by command-line startup options. At installation time, my.cnf is placed in the /etc/ directory, but you can also specify an alternate location for this file during start-up.

In the support-files sub-directory of your MySQL installation directory, you'll find four sample configuration files, each tuned for a specific range of installed memory:

my-small.cnf?For systems with less than 64MB of RAM, where MySQL is used occasionally.

my-medium.cnf?For systems with less than 64MB of RAM, where MySQL is the primary activity on the system, or for systems with up to 128MB of RAM, where MySQL shares the box with other processes. This is the most common configuration, where MySQL is installed on the same box as a Web server and receives a moderate amount of traffic.

my-large.cnf?For a system with 128MB to 512MB of RAM, where MySQL is the primary activity.

my-huge.cnf?For a system with 1GB to 2GB of RAM, where MySQL is the primary activity.

To use any of these as the base configuration file, simply copy the file of your choice to /etc/my.cnf (or wherever my.cnf is on your system) and change any system-specific information, such as port or file locations.

Key Startup Parameters

There are two primary start-up parameters that will affect your system the most: key_buffer_size and table_cache. If you get only two server parameters correctly tuned, make sure they're these two!

The value of key_buffer_size is the size of the buffer used with indexes. The larger the buffer, the faster the SQL command will finish and a result will be returned. Try to find the fine line between finely tuned and over-optimized; you may have a key_buffer_size of 256MB on a system with 512MB of RAM, but any more than 256MB could cause degraded server performance.

A simple way to check the actual performance of the buffer is to examine four additional variables: key_read_requests, key_reads, key_write_requests, and key_writes. You can find the values of these variables by issuing the SHOW STATUS command:

mysql> SHOW STATUS;NOTE

You'll learn more about the SHOW command in Hour 22, "Basic Administrative Commands."

A long list of variables and values will be returned, listed in alphabetical order. Find the rows that look something like this (your values will differ):

| Key_read_requests    | 602843 |
| Key_reads            | 151    |
| Key_write_requests   | 1773   |
| Key_writes           | 805    |

The other important server parameter is table_cache, which is the number of open tables for all threads. The default is 64, but you may need to adjust this number. Using the SHOW STATUS command, look for a variable called open_tables in the output. If this number is large, the value of table_cache should be increased.

The sample configuration files use various combinations of key_buffer_size and table_cache, which you can use as a baseline for any modifications you need to make. Whenever you modify your configuration, you'll be restarting your server in order for changes to take effect, sometimes with no knowledge of the consequences of your changes. In this case, be sure to try your modifications in a development environment before rolling the changes into production.

Optimizing Your Table Structure

An optimized table structure is different than a well-designed table. Table structure optimization has to do with reclaiming unused space after deletions and basically cleaning up the table after structural modifications have been made. The OPTIMIZE SQL command takes care of this, using the following syntax:

OPTIMIZE TABLE table_name[,table_name]For example, if you want to optimize the master_name table in your contact management database, use:

mysql> OPTIMIZE TABLE master_name;
+-----------------------+----------+----------+----------+
| Table                 | Op       | Msg_type | Msg_text |
+-----------------------+----------+----------+----------+
| contactDB.master_name | optimize | status   | OK       |
+-----------------------+----------+----------+----------+

mysql> OPTIMIZE TABLE master_name;
+-----------------------+----------+----------+-----------------------------+
| Table                 | Op       | Msg_type | Msg_text                    |
+-----------------------+----------+----------+-----------------------------+
| contactDB.master_name | optimize | status   | Table is already up to date |
+-----------------------+----------+----------+-----------------------------+

1 row in set (0.03 sec)Be aware that the table is locked while it is optimized, so if your table is large, optimize it during scheduled downtime or when little traffic is flowing to your system.

NOTE

You can use OPTIMIZE on only MyISAM and BDB tables.

Optimizing Your Queries

Query optimization has a lot to do with the proper use of indexes. The EXPLAIN command will examine a given SELECT statement to see whether it's optimized the best that it can be, using indexes wherever possible. This is especially useful when looking at complex queries involving JOIN. The syntax for EXPLAIN is

EXPLAIN SELECT statementThe output of the EXPLAIN command is a table of information containing the following columns:

table?The name of the table.

type?The join type, of which there are several.

possible_keys?This column indicates which indexes MySQL could use to find the rows in this table. If the result is NULL, no indexes would help with this query. You should then take a look at your table structure and see whether there are any indexes that you could create that would increase the performance of this query.

key?The key actually used in this query, or NULL if no index was used.

key_len?The length of the key used, if any.

ref?Any columns used with the key to retrieve a result.

rows?The number of rows MySQL must examine to execute the query.

extra?Additional information regarding how MySQL will execute the query. There are several options, such as Using index (an index was used) and Where (a WHERE clause was used).

The following EXPLAIN command output shows a non-optimized query:

mysql> EXPLAIN SELECT * FROM master_name;
+-------------+------+---------------+------+---------+------+------+-------+
| table       | type | possible_keys | key  | key_len | ref  | rows | Extra |
+-------------+------+---------------+------+---------+------+------+-------+
| master_name | ALL  | NULL          | NULL |  NULL   | NULL |  9   |       |
+-------------+------+---------------+------+---------+------+------+-------+

Think back to the example in Hour 11, "Advanced Usage of SELECT Statements," where you used a RIGHT JOIN on the master_name and email tables. Using EXPLAIN, you can see that this is an optimized query:

Figure 20.1 EXPLAIN output for an optimized query.

In the type column, you see the value eq_ref instead of ALL. When ALL is present, it means that all relevant tables are scanned during the course of the query for each combination of rows used. In other words, everything is looked at! On the other hand, eq_ref means that only one row will be read for each combination of rows; this indicates that indexes are being used properly and that the JOIN has done its job.

When using EXPLAIN on statements involving JOIN, a quick way to gauge the optimization of the query is to look at the values in the rows column. In the previous example, you have 2 and 1. Multiply these numbers together and you have 2 as your answer. This is the number of rows that MySQL must look at in order to produce the results of the query. You want to get this number as low as possible, and 2 is as low as it can go!

For a great deal more information on the EXPLAIN command, please visit the MySQL manual at http://www.mysql.com/doc/E/X/EXPLAIN.html.

Summary

Running an optimized MySQL server starts with the hardware and operating system in use. Your system's CPU should be sufficiently fast, and you should have enough RAM in use to pick up the slack when your CPU struggles. This is especially true if MySQL shares resources with other processes, such as a Web server. Additionally, the hard drive in use is important, as a small hard drive will limit the amount of information you can store in your database. The seek time of your hard drive is important?a slow seek time will cause the overall performance of the server to be slower. Your operating system should not overwhelm your machine and should share resources with MySQL rather than using all the resources itself.

Some key startup parameters for MySQL are the values of key_buffer_size and table_cache, among others. Baseline values can be found in sample MySQL configuration files, or you can modify the values of these variables and watch the server performance to see whether you hit on the right result for your environment.

Beyond hardware and software optimization is the optimization of tables, as well as SELECT queries. Table optimization, using the OPTIMIZE command, allows you to reclaim unused space. You can see how well (or not) optimized your queries are by using the EXPLAIN command. The resulting output will show if and when indexes are used, and whether you can use any indexes to speed up the given query.

With all optimization?hardware, software, or related to queries?try to perform the modifications and tests in a development environment rather than a production environment. Locked tables or degraded server performance should be worked out in the development environment and not during prime time!