Automatic optimization of MySQL, PostgreSQL settings
Optimization of settings is always a delicate matter, and it is often possible to set the parameters that will give maximum performance only when the application is running, when load statistics are already present and bottlenecks are visible.
But it is very useful to make the primary optimization when running the database. This post describes the ways to automatically optimize MySQL and PostgreSQL using the mysqltuner and pgtune utilities.
To optimize mysql, there is a simple and easy to use mysqltuner utility.
You can get it for free use on github, namely here . Or download one command:
')
It’s easy to use: upload mysqltuner.pl to the server with mysql, set launch permissions for a file (or start like this: perl mysqltuner.pl), we ask for login / password request with privileges and see recommendations.
We put the recommendations into the config, restart mysql-server. Or use “on the fly” via the mysql console if the project is already running and restarting is undesirable.
In addition to tips on setting up mysql, the utility also shows information about indexes in tables and fragmentation, if mysql has been in use for some time.
About indexes are often forgotten, which greatly increases the consumption of system resources.
It is better to entrust the indexing to those who designed the structure of the base, but you can do it yourself.
To defragment, we run OPTIMIZE TABLE from the mysql console, but it is more convenient to do for all the tables at once through an interface like phpMyAdmin.
For further optimization in the course of the project, we are already looking at the situation and increasing the necessary parameters.
Useful information is described by my colleague in this article .
For better performance, it is useful to use Percona Server to replace the standard MySQL Server.
The benefits can be judged from the performance graphs .
About Percona Server there are already a lot of articles on Habré, but in the future I will share my experience using this assembly.
For tuning PostgreSQL settings there is also a useful utility called pgtune .
Unlike mysqltuner, the utility does not provide recommendations, but immediately creates a configuration file postgresql.conf with parameters that are optimal for the system on which PostgreSQL is running.
The scheme of use is as follows:
where $ PGDATA is the path to the directory with the postgresql.conf server config. At the output we get the file postgresql.conf.pgtune, in which the parameters selected by the utility are set. The utility writes these parameters to the end of the file after the block.
You can use additional parameters to set the parameter values ​​not based on automatically determined server characteristics, but on your own:
-M or --memory - full size of RAM on the server, based on which memory resources are allocated for PostgreSQL;
-T or --type - Specifies the type of database: DW, OLTP, Web, Mixed, Desktop;
-c or --connections - The maximum possible number of connections to the database; If not specified, it is determined based on the type of the base;
-D or --debug - Turns on debug mode in PostgreSQL
-S or --settings - Set to the directory in which the configuration file is located.
After the utility completes, edit the generated postgresql.conf.pgtune file if necessary (for example, set up a non-standard port or set up logging), replace the postgresql.conf configuration file with it and restart PostgreSQL-server.
I will be glad to any questions / comments / additions!
But it is very useful to make the primary optimization when running the database. This post describes the ways to automatically optimize MySQL and PostgreSQL using the mysqltuner and pgtune utilities.
Mysql
To optimize mysql, there is a simple and easy to use mysqltuner utility.
You can get it for free use on github, namely here . Or download one command:
wget https://raw.github.com/rackerhacker/MySQLTuner-perl/master/mysqltuner.pl ')
It’s easy to use: upload mysqltuner.pl to the server with mysql, set launch permissions for a file (or start like this: perl mysqltuner.pl), we ask for login / password request with privileges and see recommendations.
We put the recommendations into the config, restart mysql-server. Or use “on the fly” via the mysql console if the project is already running and restarting is undesirable.
In addition to tips on setting up mysql, the utility also shows information about indexes in tables and fragmentation, if mysql has been in use for some time.
About indexes are often forgotten, which greatly increases the consumption of system resources.
It is better to entrust the indexing to those who designed the structure of the base, but you can do it yourself.
To defragment, we run OPTIMIZE TABLE from the mysql console, but it is more convenient to do for all the tables at once through an interface like phpMyAdmin.
For further optimization in the course of the project, we are already looking at the situation and increasing the necessary parameters.
Useful information is described by my colleague in this article .
For better performance, it is useful to use Percona Server to replace the standard MySQL Server.
The benefits can be judged from the performance graphs .
About Percona Server there are already a lot of articles on Habré, but in the future I will share my experience using this assembly.
PostgreSQL
For tuning PostgreSQL settings there is also a useful utility called pgtune .
Unlike mysqltuner, the utility does not provide recommendations, but immediately creates a configuration file postgresql.conf with parameters that are optimal for the system on which PostgreSQL is running.
The scheme of use is as follows:
pgtune -i $PGDATA/postgresql.conf -o $PGDATA/postgresql.conf.pgtune
where $ PGDATA is the path to the directory with the postgresql.conf server config. At the output we get the file postgresql.conf.pgtune, in which the parameters selected by the utility are set. The utility writes these parameters to the end of the file after the block.
#------------------------------------------------------------------------------ # pgtune wizard run on YYYY-MM-DD # Based on XXXXXXX KB RAM in the server #------------------------------------------------------------------------------ You can use additional parameters to set the parameter values ​​not based on automatically determined server characteristics, but on your own:
-M or --memory - full size of RAM on the server, based on which memory resources are allocated for PostgreSQL;
-T or --type - Specifies the type of database: DW, OLTP, Web, Mixed, Desktop;
-c or --connections - The maximum possible number of connections to the database; If not specified, it is determined based on the type of the base;
-D or --debug - Turns on debug mode in PostgreSQL
-S or --settings - Set to the directory in which the configuration file is located.
After the utility completes, edit the generated postgresql.conf.pgtune file if necessary (for example, set up a non-standard port or set up logging), replace the postgresql.conf configuration file with it and restart PostgreSQL-server.
I will be glad to any questions / comments / additions!
Source: https://habr.com/ru/post/160395/
All Articles