Optimizing and Tuning Your MySQL Database

• By Julie C. Meloni
• Sep 20, 2002
• Sample Chapter is provided courtesy of Sams

This chapter is from the book
Sams Teach Yourself MySQL in 24 Hours

Hour 20: Optimizing and Tuning Your Database

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, as well as the database design methods you've learned throughout this book.

In this hour, 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 |
+----------------------------------------------+
1 row in set (0.20 sec)

The important number is the time in seconds, which is the elapsed time for the execution of the function. You may want to run the same uses of benchmark() multiple times during different times of day (when your server is under different loads) to get a better idea of how your server is performing.