MySQL database settings
Today we will talk about database tuning. At once we will make a reservation that the described settings are applicable to VPS with the minimum or average values of random access memory. For a more “pumped” project, most likely, an individual customization of the database configuration file will be required.
The main configuration file is located at /etc/my.cnf. We will work with him.
Let’s start with the max_connections directive. She is responsible for the number of simultaneously open “connections” (connections) to our MySQL / MariaDB database. The maximum value varies by version. So, for MySQL 5.5, it is equal to 100,000. But even on high-performance servers, such numbers are never displayed. The larger the value, the more likely the increase in server resource consumption. Together with other processes on the server, this may be a performance bottleneck. The max_connections parameter itself does not consume resources.
On servers with 1-2 GB of RAM, the best solution would be:
A good solution would be to specify max_user_connections with a value of 25.
max_user_connections – the number of open connections per user.
key_buffer is the buffer size used to write blocks of indexes. This is one of the values that must be specified for each server individually. In order not to reduce performance, it is recommended to expose from 15 to 25% of RAM memory under key_buffer . However, this is a rather controversial decision. key_buffer must be specified not only on the basis of the amount of RAM, but also paying attention to the distribution of RAM among all processes on the server. Within a server with 1-2 GB of RAM, the best option is the value key_buffer_size = 16K. If necessary, this parameter can be increased, but only for myisam. Therefore, if all the tables are on innodb, then the value should be smaller. MySQL itself uses myisam.
table_cache is the number of open tables for all threads. The higher the value, the greater the number of file descriptors required for MySQL to work. This parameter is described in detail in the http://www.mysql.ru/docs/man/Table_cache.html documentation .
The file descriptor * is an unsigned integer with which the process accesses the open file.
For a server with average performance, you can start with a value of 512.
You can check the variable Opened_tables, its value
mysql> show status LIKE "Opened_tables%"; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Opened_tables | 400 | +---------------+-------+
There are 400 open tables. In this case, the value of table_cache = 512 is justified.
If your database uses innodb or some tables, it is recommended to set the maximum possible value. The buffer in InnoDB caches not only data, but also indexes. In this case, the cache of the operating system itself is not used. Do not forget about the other MySQL settings and other processes on the server that also consume the RAM resource.
This parameter is responsible for how much memory is allocated for query caching. In this case, the smaller the value, the better. But it is selected individually. Start with 32M (megabytes) and increase until you find the configuration you need.
wait_timeout – the time (measured in seconds) for the server to wait for the connection activity before closing it. The optimal value for VPS with initial and average configuration is 10 seconds.
Search for bottlenecks in the MySQL database
If your site is slow, then the probability of failure of any action on your site is great. Often the problem of the slow work of the site is the large number of database queries that are executed for a long time, more than 1 second. Such queries can be tracked and optimized.
To determine which query to the MySQL database is “ slowing down”, you need to add two settings in the /etc/my.cnf file :
log_slow_queries = /var/log/mysql-slow.log long_query_time = 1
log _ slow _ queries – the file where the log will be written.
long_query_time – time in seconds, after which the request will be considered slow.
After adding these settings, you must create the file /var/log/mysql-slow.log and set the correct rights. This can be done with two commands:
touch /var/log/mysql-slow.log chown mysql:mysql /var/log/mysql-slow.log
After that, you need to restart the MySQL service. This can be done with the following commands:
/etc/init.d/mysql restart или /etc/init.d/mysqld restart
You can also enable the slow query log via set global
set global slow_query_log = 'ON'; set global log_queries_not_using_indexes = 'ON'; set global slow_query_log_file ='/var/log/mysql-slow.log'; set global long_query_time = 1; flush logs;
set global slow_query_log = ‘ON’; – enable logging of slow queries
set global log_queries_not_using_indexes = ‘ON’; – the parameter is responsible for adding to the log requests collected indices without using
set global slow_query_log_file = ‘/ var / log / mysql-slow.log’; – log file, in which we will write the log itself.
set global long_query_time = 1; – time in seconds, after which the request will be considered slow.
flush logs; – a command that closes and opens all log files.