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

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!