[nextpage title=”Introduction”]
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.
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.
[nextpage title=”The Networking Setup”]
When ordering dedicated servers from? a hosting company without giving them any special instructions, they will probably set them up as shown in Figure 2.
Figure 2: Typical dedicated server setup
Using this configuration, the network interface of the web server will be used for both public (web) traffic and for the communications between the web server and the MySQL server. This is not the best configuration possible for several reasons.
Usually the switch of the hosting company works at 100 Mbps, so even if the servers have Gigabit Ethernet ports, the network interface will be limited to 100 Mbps, which can be easily reached on the communications between the web server and the MySQL server of a busy website. When this limit is reached, the website will be very slow.
Also because the network interfaces will be running at 100 Mbps, the latency (ping time) between the two servers will be higher than if the interfaces were configured as Gigabit Ethernet.
Another issue is that you will have database traffic circulating on the hosting company’s switch, which can pose a security risk.
The best approach to connect a web server to a MySQL server is to use a separate Gigabit Ethernet connection linking the second network interface (eth1) of the servers. We show this possibility in Figure 3. Since almost all servers have two Gigabit Ethernet ports, this configuration is very easily done, however you must ask the hosting company to perform it for you. (You must use a cross-over cable for the connection shown in Figure 3; another option is to use a Gigabit switch connecting the two servers.)
While the first network interface (eth0) will have a public IP address, the second network interface (eth1) must use a private IP address (such as 192.168.0.x). After setting up the physical connection, don’t forget to reconfigure your web applications (e.g., editing the application’s config.php file) to use the MySQL server’s eth1 IP address (192.168.0.x in our example), otherwise it will still use the public IP address.
Figure 3: Best server setup
With this configuration, the eth0 interface of the web server will be used only to carry public traffic; the traffic between the web server and the MySQL server will be moved to the private Gigabit connection, and it will be faster and more secure. The public IP address of the MySQL server must be used only for remote access to the server, using SSH.
Some system administrators paranoid with security simply disconnect the eth0 interface of the MySQL server from the hosting company’s switch; this way the MySQL server won’t be able to be publicly accessed. In this case, for you to access the MySQL server remotely using SSH, you will need to first open an SSH connection to the web server and then, from inside the web server, you can open an SSH connection the MySQL server using its private IP address (192.168.0.2, for example).
[nextpage title=”Source Code Optimization”]
The source code of the application that will access the MySQL server must be optimized to use the least amount of resources of the MySQL server as possible. Even if you are not a software developer, you should read this section, as there is one option that we will discuss (caching on the web server) that your software may support and it may not be enabled.
The MySQL caching mechanism is case-sensitive. This means that the queries “SELECT id,name FROM table” and “select id,name from table” are treated differently by the caching mechanism, even though both generate the same results. So, if the first query is cached when you run the second query, the MySQL server won’t use the query that is already cached; it will run the query again, which is not desirable. Therefore, the best practice is to standardize the way MySQL queries are written within the application source code, so the application can better use MySQL’s caching mechanism.
On SELECT statements, only select the rows you are really going to use, as this will save resources on the MySQL server. “SELECT *” statements are a big no-no.
Ideally, JOIN statements must use indexes.
When coding, try to use as few separate MySQL queries as possible; whenever possible combine MySQL queries.
Whenever possible, use a caching mechanism on the web server, so you don’t need to load from the MySQL server frequently-accessed data that doesn’t change very often. This caching must be done preferably in RAM, so when the web server needs data that is already cached, the data can be accessed almost instantly, meaning a faster load time for the page that the server must present.
Some smart software developers already added built-in support for caching mechanisms, such as Xcache, but this support is disabled by default, since the developer can’t know for sure if the web server where the software will be installed has the caching mechanism enabled or not.
Therefore, read the documentation of the software you are using on your web server to see if it supports any caching mechanism, and what to do to enable it. For example, on vBulletin, a popular forums software, you must uncomment the following line from its config.php file in order for it to use Xcache to store frequently-accessed data in RAM:
$config[‘Datastore’][‘class’] = ‘vB_Datastore_XCache’;
In this example, you need to comment out all other $config[‘Datastore’][‘class’] lines that may exist in config.php file.
Of course, you must have Xcache installed on your webserver and its “var_cache” correctly enabled in php.ini.
Even if your software doesn’t support a caching mechanism, we highly recommend you install Xcache on your web server, as it caches and optimizes PHP code, increasing the performance of your web server and lowering its CPU load.
The exact steps for installing Xcache are outside the scope of the present tutorial.
[nextpage title=”InnoDB vs. MyISAM”]
MySQL allows a few different storage engines for its tables. The two most common are InnoDB and MyISAM.
The main difference between the two is that MyISAM offers table-level locking, meaning that when data is being written into a table, the whole table is locked, and if there are other writes that must be performed at the same time on the same table, they will have to wait until the first one has finished writing data.
InnoDB, on the other hand, offers row-level locking, meaning that when data is being written to a row, only that particular row is locked; the rest of the table is available for writing.
At first, it seems that InnoDB is superior to MyISAM. However, the InnoDB engine uses more system resources (processing power, i.e., server load) than MyISAM, so you will need a more powerful server to run InnoDB. With InnoDB, the server load stays higher than with MyISAM. You may end up with a load that is so high that instead of increasing the performance of the server, you actually decrease the overall performance by switching from MyISAM to InnoDB.
The problems of table-level locking are only noticeable on very busy servers. For the typical website scenario, usually MyISAM offers better performance at a lower server cost.
Which engine you should use? Consult the documentation of the software you are using. Usually in the administrator’s guide, there is a specific section on this.
To check which engine the tables of a particular database is using, run the following query:
SHOW TABLE STATUS;
You will see the engine under “Engine.” Note that inside the same database you may have some tables using the MyISAM engine and some others using the InnoDB engine.
To change the engine of a table, run the following query, where you must replace “tablename” with the name of the table you want to reconfigure and “engine” with either “MyISAM” or “InnoDB”:
ALTER TABLE tablename ENGINE=engine;
If the load on the MySQL server is very high and the server is not using the swap file, before upgrading the server with a more expensive one with more processing power, you may want to try and alter its tables to use the MyISAM engine instead of the InnoDB to see what happens.
In the end, which engine you should use will depend on the particular scenario of the server, based on what we discussed above.
If you decide to use only MyISAM tables, you must add the following configuration lines to your my.cnf file:
default-storage-engine=MyISAM
default-tmp-storage-engine=MyISAM
If you only have MyISAM tables, you can disable the InnoDB engine, which will save you RAM, by adding the following line to your my.cnf file:
skip-innodb
Note, however, that if you don’t add the two lines presented above to your my.cnf file, the skip-innodb configuration will prevent your MySQL server from starting, since current versions of the MySQL server uses InnoDB by default.
[nextpage title=”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.
Security Recommendations
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.
[nextpage title=”Adjusting the Caches”]
Let’s now analyze the caching information given by mysqltuner.pl.
The two lines below give you the size and efficiency of the cache that stores the database keys, called “key buffer”:
Key buffer size / total MyISAM indexes: 64.0M/2.7G
Key buffer hit rate: 98.8% (243M cached / 3M reads)
In our case, the key buffer is configured with 64 MiB and this size seems to be excellent, because the buffer was used 98.8% of the time. If this percentage is below 90%, you should increase the size of the key buffer, which is done through the option key_buffer_size = 64M, where you should change “64M” with the amount of RAM you want to reserve for the key buffer.
The second most important cache available is the query cache, which stores queries that were already requested before. You can verify its efficiency through the following lines:
Query cache efficiency: 59.7% (2M cached / 3M selects)
Query cache prunes per day: 566279
The higher the percentage shown is, the better. Also, you want to have a low number of cache prunes. The “query cache prunes per day” indicates how many times the cache had to be deleted because more space was needed.
To increase this percentage and decrease the number of prunes per day, you must increase the size of the query cache.
If the line “query cache efficiency” has a “0%” on it, this means that you must turn on the query caching mechanism, because it is disabled (you are missing a query_cache_type=1 in your my.cnf file)!
The three configuration parameters for the query cache that you must have in your my.cnf file are:
query_cache_type = 1
query_cache_size = 4M
query_cache_limit = 1M
The first parameter enables the query cache. The second one configures the size of the query cache. And the third one configures the maximum size of the query to be cached. This is to prevent very big queries from being cached, which would delete several small queries that were previously inside the cache, if the query cache is not big enough to store all queries at the same time.
The next piece of information indicates the number of temporary tables that were created to deal with sorts. You want this number to be as low as possible.
Sorts requiring temporary tables: 0% (58 temp sorts / 641K sorts)
If the number of temporary tables required for sorts is high, you should increase the parameter sort_buffer_size = 4M (where you must replace “4M” with the size you want this cache to be) in the my.cnf file.
The efficiency of the join buffer can be checked through a line such as:
Joins performed without indexes: 86064
If you think the number shown is too high, you can try increasing the join buffer through the parameter join_buffer_size = 4M (where you must replace “4M” with the size you want this cache to be). However, this means JOIN statements on the source code of your website are not using indexes, and you should try to correct this on the source code.
Next we have the number of temporary tables created on disk. This should be as low as possible, since accessing data on a hard drive is much slower than accessing data that is in RAM.
Temporary tables created on disk: 6% (726 on disk / 11K total)
If this percentage is high, you should first optimize the structure of the database tables. Mainly, you must change all rows that are from the type TEXT and storing 255 characters or less to the type VARCHAR.
If after making this adjustment the number of temporary tables created on disk is still high, try increasing the amount of RAM configured through the parameters tmp_table_size = 4M and max_heap_table_size = 4M (where you must replace “4M” with the maximum size you want for tables created in memory to be). Both must be configured with the same value.
Then we have the thread cache, and its efficiency is given by a line such as:
Thread cache hit rate: 97% (938 created / 41K connections)
Here you want this percentage to be as high as possible. If the percentage shown is below 90%, you must increase the thread cache through the parameter thread_cache_size = 4M (where you must replace “4M” with the size you want this cache to be).
And, finally, we have the table cache, and its efficiency can be verified through the line:
Table cache hit rate: 96% (1K open / 1K opened)
Again, we want this percentage to be as high as possible. If this percentage is below 90%, you must adjust the parameter table_open_cache= 4096 (where you must replace “4096” with the size you want this cache to be) in your my.cnf file.
And the line below indicates how bad the table-locking issue is on your server:
Table locks acquired immediately: 99% (17M immediate / 17M locks)
As you can see, we are not having a problem with table locks on this particular server. However, if this percentage is low, you should consider migrating from MyISAM to InnoDB.
[nextpage title=”InnoDB Caches”]
If you have tables using the InnoDB engine, there are a couple of parameters you must adjust. The first is innodb_buffer_pool_size = 4G, where you must replace “4G” with the amount of RAM you want to dedicate to the InnoDB caching system. The MySQL manual recommends at least 70% of the available RAM on dedicated servers, however this is overkill if you are using a MySQL with both MyISAM and InnoDB tables. The size of the InnoDB buffer should be based on the amount of data stored in InnoDB tables. Check what the following output from mysqltuner.pl reports:
InnoDB data size / buffer pool: 304.0K/4.0G
As you can see, we are reserving 4 GiB of RAM to store only 304 kiB of data, so we could easily decrease (a lot) the size of the InnoDB buffer pool, and free a lot of RAM for other uses.
An important parameter that you must add to your my.cnf file is innodb_flush_method=O_DIRECT. This will prevent the operating system from caching data that is already cached.
Leave a Reply