If you have a MySQL server, chances are that it is not running at the top of the performance it can deliver. In this tutorial, we will teach you how to optimize a MySQL server, so you can make your application (i.e., your website) run as fast as possible.
Before talking specifically about the MySQL server, we need to discuss three topics often overlooked when setting up a MySQL server: the hardware configuration of the server, the networking setup of the server, and the optimization of the source code of the application that will access the MySQL server.
In our explanations, we are assuming that you have a dedicated MySQL server.
When selecting the hardware configuration of the MySQL server, you should focus on RAM. The more, the better, since MySQL can use RAM to store frequently-accessed data (i.e., caching), which improves performance. Of course, you will need to correctly enable and adjust the caching mechanism of the MySQL server, and this is one of the goals of this tutorial.
Usually MySQL servers don’t require a lot of processing power, meaning that the CPU load will usually stay very low, usually close to zero (the exception is when you are running InnoDB tables; more on this ahead).
If the CPU load is above “1” (which means the server is queuing processing jobs), this either means that the MySQL server is using InnoDB tables or that the server ran out of usable RAM and it is using virtual memory, i.e., using a swap file in the hard drive to simulate more RAM.
If you already have a MySQL server up and running, we recommend you run the utility top to check the server load, the amount of RAM available, and whether the server is using the swap file.
click to enlarge
Figure 1: The top utility
In Figure 1, which represents the MySQL server of a very busy website, you can see that the server load is very low. We have 16 GiB of RAM installed, and the swap file is not in use. This means the amount of RAM for this server seems to be “perfect.” However, by enabling and adjusting MySQL’s parameters, you actually may need more RAM even if the swap file is not currently in use; we will talk about this later.
If you see that the server is using the swap file, this means you need to add more RAM to the server – urgently. In this case, if the CPU load is above “1”, it will drop below “1” as soon as you add more RAM.
If the CPU load is above “1” and the server is not using the swap file, this probably means you are using InnoDB tables, which require more processing power. In this case, there are two options: to configure the database tables to use the MyISAM engine or to replace the server with a model with more processing power. We will give you a more in-depth explanation of this issue on the MyISAM vs. InnoDB page.