MySQL® Administrator's Bible

Chapter 10. MySQL Server Tuning

IN THIS CHAPTER

• Choosing optimal hardware

• Tuning the operating system

• Tuning MySQL Server

There are four main areas of a server running mysqld that the database administrator can analyze and optimize for best performance. These areas are SQL tuning, schema and index tuning, mysqld tuning, and operating system tuning. SQL tuning and index optimizations are covered in Chapter 18. This chapter covers tuning mysqld for top performance. In addition to tuning mysqld, the operating system and hardware that the server runs on requires tuning for best performance. While these are not necessarily under the direct control of a database administrator, recommendations can be made to the system administrators for the best hardware and operating system to run mysqld on.

Choosing Optimal Hardware

Computer hardware changes very quickly. Because of this, specific hardware recommendations will not be made. However, there are some characteristics that you can look for when purchasing server hardware.

The first consideration is that you should always buy 64-bit hardware, because mysqld can take advantage of the larger amounts of per-process memory supported by 64-bit operating systems. On a 32-bit operating system, each process can only use about 2.4 Gb of RAM per process, and mysqld is currently single-threaded. Make sure that the motherboard can support a sufficient amount of RAM. If the server is going to be used in a high-load environment, we recommend that the motherboard ...