5 parameters to optimize MySQL in 2021

Hello everyone, today we are going to share with you 5 parameters to optimize MySQL . In MySQL on all operating systems, you have a configuration file. In this configuration file the parameters that will be applied on MySQL startup or on your next reboot . It is important to highlight this point since all the configuration made in this file will be applied at the next startup of the service, not immediately. With that said, let’s get started.

Where is the configuration file?

First of all, we must know where to find the configuration file. This file in Linux is usually found in /etc/mysql/my.conf Although it will depend on your distribution, the most common will be to find it in this location.

In Windows environments the file can be called my.ini or my.cnf. It can be located at:

  • BASEDIR \ my.ini
  • BASEDIR \ my.cnf
  • %WINDIR% \ my.ini
  • %WINDIR% \ my.cnf

%WINDIR% refers to the Windows installation (usually C: \ Windows) and BASEDIR is the installation directory. Normally in Program Files unless its location was changed at installation time.

If you have trouble locating it, you can look at this page: https://dev.mysql.com/doc/refman/8.0/en/option-files.html

Once located, we strongly recommend making a copy of the current file in case changes need to be reversed and MySQL continues to work without problem.

5 parameters to optimize MySQL

What are the 5 parameters to optimize MySQL?

Once the file is located and made a copy of it. We can modify the parameters. These parameters are general recommendations, but they cannot be valid for all environments. Therefore, always test these parameters in a non-production environment until their usefulness is proven.

innodb_buffer_pool_size

This parameter is what keeps the cached database. The larger this parameter, the less disk usage the database will require. This value “reserves” the amount that we indicate in RAM memory to be used by MySQL. Ideally, if we have small databases and enough RAM, they should occupy the entire database.

For example, if in total we have 2 GB of data in the MySQL database, we could indicate the value of innodb_buffer_pool_size in 3 or 4 GB.

As this is not always possible, we must use reasonable value. For example, we can indicate between 6 and 10 GB for this parameter in larger databases.

max_connections

This parameter defines the maximum number of recurring connections that we can have at the same time in MySQL. Therefore, it is convenient to modify this value in large environments where hundreds of concurrent connections are expected. Normally, with the default value (it depends on each version we use) it is usually enough to not touch this parameter. But if the “Too many connections” error appears, we must increase its value. If we modify it, we must use a prudent value according to our connections, since if we indicate a very high value, it will affect the performance of the database, by allowing many connections at the same time.

query_cache_size

This parameter is one of the most debated on the net and can cause many problems whether it is set too little or if a very low value is indicated. For it, our recommendation would be to disable it by setting its value to 0. In this sense, it is best to tune the queries instead of changing this parameter. At this point, we can help you examine the queries and try to optimize them so they are no longer a problem. Consult us without obligation.

innodb_flush_method

This value controls how data and logs are flushed from memory to disk. It is usually kept in O_DIRECT , to avoid “double buffering”. This double buffering parameter tends to perform worse than O_DIRECT.

We hope you liked this post. If you still want us to review your environment to correctly adjust your queries and parameters. Contact without obligation. We are experts in MySQL, SQL Server, Oracle databases, etc.

Feel free to subscribe to our monthly newsletter so as not to miss any of our publications. With just one email a month you will be informed and you can unsubscribe whenever you want.

Still do not know Query Performance ? Find out how it can help you in your Oracle environment. More information on their page LinkedIn .

Follow GPS on LinkedIn