Always is a sysadmin task, to verify the performance of the server and its services. Especially if the applications that your company uses are hosted in it. By default, almost all database managers have standard configurations to ensure as much as possible that the performance is good. However, not all computers are the same, much less the servers. So, in this post, I will help you optimize MySQL / MariaDB on Linux to increase performance.
The main objective of this post is to show you various parameters that you can modify to increase the performance of MySQL or MariaDB, but keep in mind that each server is different, then each one has its different needs and in the end, it depends on you.
So, let’s start.
The my.cnf file
The vast majority of the settings that apply to MySQL / MariaDB can be applied from this file. It can be found in the following path /etc/mysql/.
You have to know that in this file many things can be done, including damaging the server. So the first thing to do is to be careful what you do.
Some of the parameters that affect the performance of MySQL / MariaDB are:
- query_cache_type: Simply in this option, you can activate or deactivate the cache of the application. It is recommended to activate it by placing the value of 1.
- max_allowed_packet: It’s up to you here. This parameter allows you to define the maximum size that MySQL / MariaDB can work with. Example, if you want to import a very large database, then the value must be high.
- query_cache_size: Defines the size of the query cache. Very important, but it has a little trick, you should put 64Mb for every 1GB of RAM your server has.
- key_buffer_size: Specifies the size of the index cache. Note that if this value is too high you can cause the server to collapse. Normally, it should be 32Mb for every usable 1Gb. The higher the better, as long as you have the resources to do so.
- thread_cache_size: The maximum number of process threads that can be used. Here you must define between 32 and 64 in order not to saturate the processor.
- max_connections: Defines the number of total simultaneous connections the server will accept.
- wait_timeout: It is the waiting time that MySQL takes to close a connection.
Of course, there are many more parameters, however, I believe that these are the most basic ones to modify.
Optimize MySQL / MariaDB using MySQL Tuner
There is a fairly simple tool to apply that can help us with the MySQL / MariaDB configuration and optimization process. I’m talking about MySQLTuner.
MySQLTuner is a script written in Perl that allows you to review a MySQL installation quickly and make adjustments to increase performance and stability. The script has many indicators that will recommend the policies to apply to our server.
Let’s use it. First, download it.
:~$ wget http://mysqltuner.pl/ -O mysqltuner.pl
And run it.
:~$ perl mysqltuner.pl
It is recommended to execute this script when you have a real database so that the data you can collect is as real as possible. In this case, I’m doing a test.
SSD disks a good way to optimize
If it is in your possibilities it is good that you can acquire for your server a disk SSD. The SSD disks are rather expensive but compensate much in performance and this is important in their production server.
In some cases, when the hard disk is very fragmented its performance is up to 400% lower than an SSD. That is, it is too much difference.
Conclusion
It is not very difficult to optimize MySQL / MariaDB but it is very important to do it. For this, we have a tool like MySQLTuner to help us with the task.
On the other hand, it is good to improve the resources of our server as an SSD so that MySQL / MariaDB also improves performance.
