November 27, 2012 at 12:25Automatic optimization of MySQL settings, PostgreSQL
Optimization of settings is always a delicate matter and setting exactly those parameters that will give maximum performance is often possible only during the application process, when there are already load statistics and bottlenecks are visible.
But it is very useful to make initial optimization when starting the DBMS. This post describes ways to automatically optimize MySQL and PostgreSQL with the mysqltuner and pgtune utilities.
For mysql optimization, there is a simple and easy-to-use mysqltuner utility.
You can get it for free use on github, namely here . Or load with one command:
It is simple to use: upload mysqltuner.pl to the server with mysql, set the launch rights for the file (or run this: perl mysqltuner.pl), give an account with privileged rights to the login / password request and see recommendations.
We put the recommendations in the config, restart mysql-server. Or we use “on the fly” through the mysql console if the project is already running and restarting is undesirable.
In addition to mysql configuration tips, the utility also displays information about indexes in tables and fragmentation if mysql has been used for some time.
Indexes are often forgotten, which greatly increases the consumption of system resources.
Index placement is best entrusted to those who designed the structure of the database, but you can do it yourself.
For defragmentation, run OPTIMIZE TABLE from the mysql console, but it is more convenient to do it for all tables at once through an interface like phpMyAdmin.
For further optimization in the process of the project we are already looking at the situation and increase the necessary parameters.
Useful information is described by my colleague in this article .
For greater performance, it is useful to use Percona Server to replace the standard MySQL Server.
The benefits can be judged from the performance graphs .
There are already a lot of articles about Percona Server on Habré, but in the future I will share my experience in using this assembly.
For tuning PostgreSQL settings there is also a useful utility called pgtune .
Unlike mysqltuner, the utility does not give recommendations, but immediately creates a configuration file postgresql.conf with parameters that are optimal for the system running PostgreSQL.
The usage pattern is as follows: where $ PGDATA is the path to the directory with the postgresql.conf server config. The output is 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 at your discretion:
-M or --memory - the total RAM size on the server, on the basis of which memory resources for PostgreSQL are allocated;
-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 no value is specified, it is determined based on the type of database;
-D or --debug - Enables debugging mode in PostgreSQL
-S or --settings - Sets to the directory where the configuration file is located.
After the utility finishes, edit the generated postgresql.conf.pgtune file if necessary (for example, set a non-standard port or configure 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 initial optimization when starting the DBMS. This post describes ways to automatically optimize MySQL and PostgreSQL with the mysqltuner and pgtune utilities.
MySQL
For mysql optimization, there is a simple and easy-to-use mysqltuner utility.
You can get it for free use on github, namely here . Or load with one command:
wget https://raw.github.com/rackerhacker/MySQLTuner-perl/master/mysqltuner.pl
It is simple to use: upload mysqltuner.pl to the server with mysql, set the launch rights for the file (or run this: perl mysqltuner.pl), give an account with privileged rights to the login / password request and see recommendations.
We put the recommendations in the config, restart mysql-server. Or we use “on the fly” through the mysql console if the project is already running and restarting is undesirable.
In addition to mysql configuration tips, the utility also displays information about indexes in tables and fragmentation if mysql has been used for some time.
Indexes are often forgotten, which greatly increases the consumption of system resources.
Index placement is best entrusted to those who designed the structure of the database, but you can do it yourself.
For defragmentation, run OPTIMIZE TABLE from the mysql console, but it is more convenient to do it for all tables at once through an interface like phpMyAdmin.
For further optimization in the process of the project we are already looking at the situation and increase the necessary parameters.
Useful information is described by my colleague in this article .
For greater performance, it is useful to use Percona Server to replace the standard MySQL Server.
The benefits can be judged from the performance graphs .
There are already a lot of articles about Percona Server on Habré, but in the future I will share my experience in using this assembly.
PostgreSQL
For tuning PostgreSQL settings there is also a useful utility called pgtune .
Unlike mysqltuner, the utility does not give recommendations, but immediately creates a configuration file postgresql.conf with parameters that are optimal for the system running PostgreSQL.
The usage pattern is as follows: where $ PGDATA is the path to the directory with the postgresql.conf server config. The output is 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 -i $PGDATA/postgresql.conf -o $PGDATA/postgresql.conf.pgtune
#------------------------------------------------------------------------------
# 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 at your discretion:
-M or --memory - the total RAM size on the server, on the basis of which memory resources for PostgreSQL are allocated;
-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 no value is specified, it is determined based on the type of database;
-D or --debug - Enables debugging mode in PostgreSQL
-S or --settings - Sets to the directory where the configuration file is located.
After the utility finishes, edit the generated postgresql.conf.pgtune file if necessary (for example, set a non-standard port or configure logging), replace the postgresql.conf configuration file with it, and restart PostgreSQL-server.
I will be glad to any questions / comments / additions!