Fine-Tuning the MySQL Server
There are several parameters that can be adjusted on a MySQL server to make it faster. The exact values that must be used for each parameter, however, will be specific to your system, based on the usage pattern of your MySQL server; therefore, it is not possible for us to publish recommended values that will be good for all readers.
Fortunately, MySQL stores statistics that will help you to determine the best values that you must use. Furthermore, there are two handy utilities that can be used to read these statistics and print them on an easy-to-understand format: tuning-primer.sh and mysqltuner.pl. In our examples, we will use mysqltuner.pl.
Download mysqltuner.pl to your server, chmod it to 755, and run it. It will give you several important statistics. In order for the data to be reliable, you must have the server running for at least 48 hours, and we particularly recommend that these 48 hours be within high-traffic days (e.g., during the week and not at a weekend or during a prolonged holiday).
The mysqltuner.pl script will, at the end of its report, give you a hint of the parameters you should adjust on your server. However, we will teach you how to make a more detailed analysis.
Parameters are usually configured at the my.cnf file, which is usually stored at /etc (so, to edit this file, run vi /etc/my.cnf). For the new parameters to take effect you will need to restart MySQL. Since you will need to wait 48 hours until your next fine-tuning session, we recommend you restart MySQL only after adjusting all parameters.
Let’s discuss the most important information provided by mysqltuner.pl, with some real examples. Ideally you want to see all parameters marked with a green “OK.”
Total fragmented tables: 30
This indicates that there are 30 tables that are fragmented, and we must run OPTIMIZE TABLE to defragment them. You can read this other tutorial to find out exactly what tables you need to defragment or use this script to defragment all tables. There is only one problem: on a very busy server with lots of data, defragmentation takes a while, and the table that is currently being defragmented will be locked, and this will affect the usability of your website or application. Therefore, we recommend you disable your web server while defragmenting.
In this section, mysqltuner.pl will make a list of possible security issues. You must correct all of them.
Maximum possible memory usage: 23.4G (149% of installed RAM)
Maximum possible memory usage: 12.2G (77% of installed RAM)
This option lists the maximum amount of RAM the MySQL server will use in the worst-case scenario, based on its current configuration. The two parameters that most influences the amount of RAM that will be required is max_connections and the amount of RAM reserved for the InnoDB buffer through the parameter innodb_buffer_pool_size.
On the first example, we have the server shown before in Figure 1. If you remember, that system had 16 GiB of RAM and was not using the swap file; however, if the server is to use all resources it is allowed based on its current configuration, it will require 23.4 GiB of RAM. In other words, it is not using the swap file right now, but under heavy traffic, it will, and the server will become slow.
So, in order to run this server correctly, we need to re-adjust all caching parameters and the max_connections parameter (more on this later). However, if even after adjusting these parameters the amount of recommended RAM is higher than the amount of RAM the server has installed, it is time to upgrade the amount of RAM. In this example, we would need to increase the amount of RAM from this server from 16 GiB to 32 GiB (24 GiB would be too close to the amount recommended, and we need to leave some room for the operating system and future use), if after adjusting the other parameters doesn’t decrease the amount of required RAM.
The second example is of another MySQL server also with 16 GiB of RAM. This server has a good amount of RAM installed, since MySQL can only use up to 12.2 GiB. Of course, after adjusting other parameters, we need to re-check to see if the maximum amount of RAM MySQL can use still “fits” the amount of RAM we have installed on this server.
Slow queries: 0% (5/5M)
How many queries take more than the number of seconds configured through the option long_query_time to execute. (If this option is not set in my.cnf, the default value is 10 seconds.) To see the current value of long_query_time, run:
SHOW VARIABLES WHERE variable_name=’long_query_time’;
In our example, only five queries took longer than 10 seconds (we kept the default value in our server) from a total of 5 million queries that were run since the last time we started the MySQL server.
You want this number to be zero or close to zero. If it is not, you will need to keep an eye on it to see what is wrong. You can adjust the other parameters, but usually to fix slow_queries (assuming that the load on the MySQL server is low and the amount of RAM is adequate), you will need to optimize the source code. Adding the parameter log_slow_queries=/var/log/mysql/log-slow-queries.log to the my.cnf file will create a log file containing all slow queries, and from there you can analyze what is going on.
Highest usage of available connections: 37% (938/2500)
This is the parameter that most impacts the amount of RAM you will need on your MySQL server, and it is configured through a parameter called max_connections in my.cnf.
A number of maximum available connections lower than the number of current connections will prevent users from accessing the database, making your website inaccessible and/or slow. A very high number of available connections to a very low number of actual connections will make MySQL server require more RAM than actually needed.
In our example, our my.cnf file was configured with max_connections=2500, which seems to be too high, since the maximum amount of connections we had so far was 938. So, it would be safe to reduce this parameter to a lower value, such as 1200. It is always important to leave some margin for future increase in traffic.
It is very important, however, to make sure you are running mysqltuner.pl after the server has already passed through periods of high traffic before adjusting this parameter, otherwise you may incorrectly configure a low number for max_connections.