Optimizing and Tuning MySQL - Administration and Fine-Tuning - Sams Teach Yourself PHP, MySQL and Apache All in One (2012)

Sams Teach Yourself PHP, MySQL and Apache All in One (2012)

Part VI. Administration and Fine-Tuning

Chapter 31. Optimizing and Tuning MySQL


In this chapter, you learn the following:

Basic hardware and software optimization tips for your MySQL server

Key startup parameters for your MySQL server

How to use the OPTIMIZE TABLE command

How to use the EXPLAIN command

How to use the FLUSH command to clean up tables, caches, and log files

How to use SHOW commands to retrieve information about databases, tables, and indexes

How to use SHOW commands to find system status information


Proper care and feeding of your MySQL server will keep it running happily and without incident. The optimization of your system consists of proper hardware maintenance and software tuning.


Note

For additional methods of maintaining and administering your MySQL server, consider the MySQL Workbench product. You can find information and screenshots of this feature-rich graphical interface at http://www.mysql.com/products/workbench/.


Building an Optimized Platform

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

CPU—The faster the CPU, the faster MySQL can process your data. There’s no real secret to this, but a 3.0GHz processor is significantly faster than a 1.0GHz processor. With processor speeds consistently increasing, and with reasonable prices all around, it is 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 is cheap these days. 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 for desktop drives and 3 to 5 milliseconds for servers. When buying a hard drive, make sure that it is 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 (for example, Windows), you have two choices: Buy enough resources so that it doesn’t matter, or use an operating system that doesn’t suck away all your resources.

Whether you’re purchasing these components yourself to build a machine, or shopping for managed solutions of customized servers, if you put the proper pieces together at the system level, you’ll have taken several steps toward overall server optimization.


Note

The selection of MySQL table type—MyISAM or InnoDB—is also an optimization option. Depending on your selection, various additional optimizations will be available to you. I recommend taking a look at the table-specific optimization tips in the MySQL manual athttp://dev.mysql.com/doc/refman/5.5/en/optimization.html, as well as the MySQL Performance Blog at http://www.mysqlperformanceblog.com/.


Benchmarking Your Database Server

You can perform a quick test of your server speed using the benchmark() MySQL function to see how long it takes to process a given expression. For example, you can make the test expression something simple, such as 10+10, or something more extravagant, such as extracting pieces of dates and performing advanced calculations.

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 an 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:

select benchmark(1000000,10+10);

The result of this command on one of my test systems is this:

+--------------------------+
| benchmark(1000000,10+10) |
+--------------------------+
| 0 |
+--------------------------+
1 row in set (0.04sec)

This command also executes the date extraction expression one million times:

select benchmark(1000000, extract(year from now()));

The result of this command on one of my test systems is as follows:

+----------------------------------------------+
| benchmark(1000000, extract(year from now())) |
+----------------------------------------------+
| 0 |
+----------------------------------------------+
1 row in set (0.09sec)

The important number is the time in seconds, which is the elapsed time for the execution of the function; the first test took 0.04 seconds, and the second took 0.09 seconds. You might want to run the same uses of benchmark() multiple times during different parts of the day (when your server is under different loads) to get a better idea of how your server is performing, and to try some benchmarks that put your server through more heavier processing than these simple examples.

MySQL Startup Options

MySQL administrators can fine-tune a wealth of server parameters, much of which the average user never needs to use. And frankly, if you are using MySQL in a virtual hosting environment, you cannot use the information except to ask for changes in your server setup. So as not to completely overwhelm you with information, this section contains only a few of the common startup options for a finely tuned MySQL server.


Note

You can read more in the MySQL Manual at http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html.


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.

In the support-files subdirectory of your MySQL installation directory (or in the installation directory itself on Windows), you’ll find 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

Two primary startup parameters 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 finishes and a result is returned. Try to find the fine line between finely tuned and overoptimized; you might 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.

A long list of variables and values are returned, listed in alphabetic order. Find the rows that look something like this. (Your values will differ.)

| Key_read_requests | 10182771 |
| Key_reads | 9326 |
| Key_write_requests | 48487 |
| Key_writes | 2287 |

If you divide the value of key_reads by the value of key_read_requests, the result should be less than 0.01. Also, if you divide the value of key_writes by the value of key_write_requests, the result should be less than 1. Using the previous values yields results of 0.000915861721998 and 0.047167281951863, respectively; well within the acceptable parameters. You could try to get these numbers even smaller by increasing the value of key_buffer_size, but these numbers are fine as they are.

The other important server parameter is table_cache, which is the number of open tables for all threads. The default is 64, but you might 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 oftable_cache should be increased.

The sample configuration files included with your MySQL installation use various combinations of key_buffer_size and table_cache. You can use these combinations as a baseline for any modifications you need to make. Whenever you modify your configuration, you have to restart your server 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 from 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 TABLE SQL command takes care of this, using the following syntax:

OPTIMIZE TABLE table_name[,table_name]

For example, if you want to optimize the grocery_inventory table in the testDB database, use: OPTIMIZE TABLE grocery_inventory. You might see a status message that simply says “OK,” or one that says “Table does not support optimize, doing recreate + analyze instead.” These are both fine, as the outcome is the same—your table has been optimized.

Be aware that tables are locked while undergoing optimization, so if your table is large, perform the optimization during scheduled downtime or when little traffic is flowing to your system.

Optimizing Your Queries

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

EXPLAIN SELECT statement

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

id—The select identifier ID.

select_type—The type of SELECT statement, of which there are several.

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 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).

There’s not much optimizing you can do with a “select all” query except add a WHERE clause with the primary key. The possible_keys column would then show PRIMARY, and the Extra column would show Where used.

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. Suppose that you have 2 and 1 as results; multiply these numbers together and you have 2 as your answer. This is the number of rows that MySQL must look at 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, visit the MySQL Manual at http://dev.mysql.com/doc/refman/5.5/en/using-explain.html.

Using the FLUSH Command

Users with reload privileges for a specific database can use the FLUSH command to clean up the internal caches used by MySQL. Often, only the root-level user has the appropriate permissions to issue administrative commands such as FLUSH.

The FLUSH syntax is as follows:

FLUSH flush_option

The FLUSH command has nine different options, with these being the most common:

• PRIVILEGES

• TABLES

• HOSTS

• LOGS

You have used the FLUSH PRIVILEGES command before, after adding new users. This command simply reloads the grant tables in your MySQL database, enabling the changes to take effect without stopping and restarting MySQL. When you issue a FLUSH PRIVILEGES command, the Query OKresponse assures you that the cleaning process occurred without a hitch. For example, the process would look like this in the command-line interface:

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.10 sec)

The FLUSH TABLES command closes all tables currently open or in use and essentially gives your MySQL server a millisecond of breathing room before starting back to work. When your caches are empty, MySQL can better utilize available memory. Again, you’re looking for the Query OKresponse:

mysql> FLUSH TABLES;
Query OK, 0 rows affected (0.21 sec)

The FLUSH HOSTS command works specifically with the host cache tables. If you cannot connect to your MySQL server, a common reason is that the maximum number of connections has been reached for a particular host, and it is throwing errors. When MySQL sees numerous errors on connection, it assumes that something is amiss and simply blocks any additional connection attempts to that host. The FLUSH HOSTS command resets this process and again allows connections to be made:

mysql> FLUSH HOSTS;
Query OK, 0 rows affected (0.00 sec)

The FLUSH LOGS command closes and reopens all log files. If your log file is getting to be a burden, and you want to start a new one, this command creates a new, empty log file. Weeding through a year’s worth of log entries in one file looking for errors can be a chore, so try to flush your logs at least monthly:

mysql> FLUSH LOGS;
Query OK, 0 rows affected (0.04 sec)

For more information on FLUSH, visit the MySQL Manual at http://dev.mysql.com/doc/refman/5.5/en/flush.html.

Using the SHOW Command

The SHOW command has several different uses and produces output displaying a great deal of useful information about your MySQL database, users, and tables. Depending on your access level, some SHOW commands are unavailable to you or provide only minimal information. The root-level user can use all the SHOW commands, with the most comprehensive results. The common uses of SHOW include the following, which you soon learn about in more detail:

SHOW GRANTS FOR user
SHOW DATABASES [LIKE something]
SHOW [OPEN] TABLES [FROM database_name] [LIKE something]
SHOW CREATE TABLE table_name
SHOW [FULL] COLUMNS FROM table_name [FROM database_name] [LIKE something]
SHOW INDEX FROM table_name [FROM database_name]
SHOW TABLE STATUS [FROM db_name] [LIKE something]
SHOW STATUS [LIKE something]
SHOW VARIABLES [LIKE something]

The SHOW GRANTS command displays the privileges for a given user at a given host. This is an easy way to check on the current status of a user, especially if you have a request to modify a user’s privileges. With SHOW GRANTS, you can check first to see that the user doesn’t already have the requested privileges. For example, examine the privileges available to the joeuser user we created early in this book:

SHOW GRANTS FOR joeuser@localhost;

The results of this query are as follows:

+----------------------------------------------------------------+
| Grants for joeuser@localhost |
+----------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'joeuser'@'localhost' IDENTIFIED|
| BY PASSWORD ' *13883BDDBE566ECEFF0501CDE9B293303116521A' |
+----------------------------------------------------------------+

1 rows in set (0.00 sec)

If you’re not the root-level user or the joeuser user, you get an error; unless you’re the root-level user, you can see only the information relevant to yourself. For example, the joeuser user isn’t allowed to view information about the root-level user:

SHOW GRANTS FOR root@localhost;

This query results in the following error message:

ERROR 1044: Access denied for user:'joeuser@localhost' to database 'mysql'

Be aware of your privilege level throughout the remainder of this chapter. If you are not the root-level user, some of these commands are not available to you or display only limited information.

Some popular SHOW commands follow; for more information, see the MySQL Manual at http://dev.mysql.com/doc/refman/5.5/en/show.html.

Retrieving Information About Databases and Tables

You have used a few of the basic SHOW commands earlier in this book to view the list of databases and tables on your MySQL server. As a refresher, the SHOW DATABASES command does just that—it lists all the databases on your MySQL server. Here is one result example:

+-------------------+
| Database |
+-------------------+
| testDB |
| mysql |
+-------------------+
2 rows in set (0.00 sec)

After you’ve selected a database to work with, you can also use SHOW to list the tables in the database. This example is the result of running a SHOW DATABASES query after the testDB has been selected. (Your table listing may vary.)

+---------------------+
| Tables_in_testDB |
+---------------------+
| grocery_inventory |
| email |
| master_name |
| myTest |
| testTable |
+---------------------+
5 rows in set (0.01 sec)

If you add OPEN to your SHOW TABLES command, you get a list of all the tables in the table cache, showing how many times they’re cached and in use:

SHOW OPEN TABLES;

A result looks something like this:

+----------+-------------------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+-------------------+--------+-------------+
| mysql | procs_priv | 0 | 0 |
| mysql | db | 0 | 0 |
| mysql | host | 0 | 0 |
| testdb | grocery_inventory | 0 | 0 |
| mysql | user | 0 | 0 |
| mysql | tables_priv | 0 | 0 |
| mysql | columns_priv | 0 | 0 |
+----------+-------------------+--------+-------------+
7 rows in set (0.00 sec)

Using this information in conjunction with the FLUSH TABLES command you learned earlier in this chapter helps keep your database running smoothly. If SHOW OPEN TABLES shows that tables are cached numerous times but are not currently in use, go ahead and use FLUSH TABLES to free up that memory.

Retrieving Table Structure Information

A helpful command is SHOW CREATE TABLE, which does what it sounds like; it shows you the SQL statement used to create a specified table:

SHOW CREATE TABLE grocery_inventory;

The preceding command results in the following:

+--------------------+---------------------------------------------+
| Table | Create Table
|+————————---+---------------------------------------------+
| grocery_inventory | CREATE TABLE 'grocery_inventory' (
'id' int(11) NOT NULL auto_increment,
'item_name' varchar(50) NOT NULL default ",
'item_desc' text,
'item_price' float NOT NULL default '0',
'curr_qty' int(11) NOT NULL default '0',
PRIMARY KEY ('id')
) ENGINE=InnoDB DEFAULT CHARSET=latin1
+--------------------+---------------------------------------------+
1 row in set (0.00 sec)

This is essentially the same information you get if you dump the table schema, but you can use the SHOW CREATE TABLE command quickly if you’re just looking for a reminder or a simple reference to a particular table-creation statement.

If you need to know the structure of the table but don’t necessarily need the SQL command to create it, you can use the SHOW COLUMNS command, as follows:

SHOW COLUMNS FROM grocery_inventory;

This query results in something like this:

+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | | auto_increment |
| item_name | varchar(50) | NO | | | |
| item_desc | text | YES | | | |
| item_price | float | NO | | | |
| curr_qty | int(11) | NO | | | |
+------------+-------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)


Note

The SHOW COLUMNS and DESCRIBE commands are aliases for one another and, therefore, do the same thing.


The SHOW INDEX command displays information about all the indexes present in a particular table. The syntax is

SHOW INDEX FROM table_name [FROM database_name]

This command produces a table full of information, ranging from the column name to the cardinality of the index. Table 31.1 describes the columns returned from this command.

Table 31.1 Columns in the SHOW INDEX Result

image

Another command that produces a wide table full of results is the SHOW TABLE STATUS command, the syntax of which is as follows:

SHOW TABLE STATUS [FROM database_name] LIKE 'something'

This command produces a table full of information, ranging from the size and number of rows to the next value to use in an auto_increment field. Table 31.2 describes the columns returned from this command.

Table 31.2 Columns in the SHOW TABLE STATUS Result

image

Retrieving System Status

The SHOW STATUS and SHOW VARIABLES commands quickly provide important information about your database server. The syntax for these commands is simply SHOW STATUS or SHOW VARIABLES—nothing fancy.

More than 300 status variables will appear as the output of SHOW STATUS, but some of the most useful are as follows:

Aborted_connects—The number of failed attempts to connect to the MySQL server. Anytime you see an aborted connection, you should investigate the problem. It could be related to a bad username and password in a script, or the number of allowable simultaneous connections could be set too low for the flood of traffic to your site.

Connections—The aggregate number of connection attempts to the MySQL server during the current period of uptime.

Max_used_connections—The maximum number of connections that have been in use simultaneously during the current period of uptime.

Slow_queries—The number of queries that have taken more than long_query_time, which defaults to 10 seconds. If you have more than one slow query, it is time to investigate your SQL syntax!

Uptime—The total number of seconds the server has been up during the current period of uptime.

You can find a comprehensive list of SHOW STATUS variables and an explanation of their values in the MySQL Manual, at http://dev.mysql.com/doc/refman/5.5/en/server-status-variables.html.

The SHOW VARIABLES command produces 325 results that control the general operation of MySQL and include the following useful tidbits:

connect_timeout—Shows the number of seconds the MySQL server waits during a connection attempt before it gives up

max_connections—The allowable number of simultaneous connections to MySQL before a connection is refused

port—The port on which MySQL is running

table_type—The table type for MySQL

version—The MySQL version number

You can find a comprehensive list of the variables returned by the SHOW VARIABLES results and an explanation of their values in the MySQL Manual at http://dev.mysql.com/doc/refman/5.5/en/show-variables.html. After you know the values you have, you can change them in your MySQL configuration file or startup command.

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.

In addition, the hard drive in use is important because a small hard drive limits the amount of information you can store in your database. The seek time of your hard drive is important. A slow seek time causes 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 use all the resources itself.

Some key startup parameters for MySQL are the values of key_buffer_size and table_cache, among others. You can find baseline values 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, enables you to reclaim unused space. You can see how well (or not) optimized your queries are by using the EXPLAIN command. The resulting output shows if and when indexes are used and whether you can use any indexes to speed up the given query.

Paying attention to your MySQL server ensures that it continues to run smoothly. Basic administration commands, such as FLUSH and SHOW, help you to recognize and quickly fix potential problems. All these commands are designed to give MySQL a millisecond of rest time and breathing room if it is under a heavy load. Numerous SHOW commands display structural information about databases, tables, and indexes, as well as how the system is performing.

Q&A

Q. Can MySQL take advantage of multiple CPUs in a single server?

A. Absolutely. If your operating system supports multiple CPUs, MySQL takes advantage of them. However, the performance and tuning of MySQL using multiple processors varies, depending on the operating system.

Q. What permission level must I have to use the OPTIMIZE command?

A. Any user with INSERT privileges for a table can perform OPTIMIZE commands. If a user has only SELECT permissions, the OPTIMIZE command will not execute.

Workshop

The workshop is designed to help you review what you’ve learned and begin learning how to put your knowledge into practice.

Quiz

1. Which MySQL function enables you to run an expression many times over to find the speed of the iterations?

2. Which SQL command cleans up the structure of your tables?

3. Which FLUSH command resets the MySQL log files?

4. To quickly determine whether MySQL has support for InnoDB tables, would you use SHOW STATUS or SHOW VARIABLES?

5. Write a SQL statement that enables you to see the SQL statement used to create a table called myTable.

Answers

1. The benchmark() function

2. OPTIMIZE

3. FLUSH LOGS

4. SHOW VARIABLES

5. SHOW CREATE TABLE myTable

Activities

1. If you have root-level access to your server, change the values of key_buffer_size and table_cache, and run benchmark() functions after each change to see how the execution times differ.

2. Use OPTIMIZE on all the tables you have created in your database to clean up any structural issues.

3. Use the SHOW STATUS command to retrieve information about your MySQL server, and then issue FLUSH commands to clean up the server. After each command, use SHOW STATUS again to see which commands affect which results in the SHOW STATUS results display.