Chapter 10. General Server Performance Parameters and Tuning

Before getting started, it's worth mentioning again: The most productive steps you can take to help most poorly performing database applications is to look at their indexing, followed by their SQL and application logic, and then their database design and other structural factors. At the very bottom of the list are the engine and other settings that you're about to discover. However, these do have an impact, so it's worth spending some time learning more about them.

To make things even easier for administrators, the past few years have seen MySQL AB bundling several prebuilt, configuration files for various processing profiles. While not necessarily optimized for your specific environment, they may be used as an effective base for further tuning efforts. They include those in the following list:

• Minimal memory, relatively infrequent MySQL usage: my-small.cnf

• Minimal memory combined with reliance on MySQL, or larger memory for a multipurpose system: my-medium.cnf

• Large memory server dedicated primarily to MySQL: my-large.cnf

• Very large memory server completely dedicated to MySQL: my-huge.cnf

It's worth your time to review these files, if for no other reason than to gain some ideas on the optimal ratios between the various server settings.

To help put the dozens of different general engine performance-impacting parameters and status variables into perspective, this chapter is broken into several portions:

• Connectivity

• Memory management

• Application control

• User resource control

Before exploring each of these topics, the following section first discusses the scope of these parameters and variables.