Тонкие настройки баз данных

Настройки базы данных MySQL

Сегодня мы поговорим о тонкой настройке базы данных. Сразу оговоримся, что описанные  настройки применимы к VPS с минимальными или средними значениями оперативной памяти. Для более "прокаченного" проекта, скорее всего, потребуется индивидуальная кастомизация файла конфигурации базы данных.

Основной файл конфигурации находится по адресу /etc/my.cnf. С ним мы и будем работать.вюл

Начнем, пожалуй, с директивы max_connections. Она отвечает за число одновременно открытых «коннектов» (подключений) к нашей базе данных MySQL/MariaDB. Максимальное значение варьируется в зависимости от версии. Так, для MySQL 5.5 оно равно 100000.  Но даже на высокопроизводительных серверах такие цифры никогда не выставляют. Чем больше значение, тем больше вероятность увеличения потребление ресурсов сервера. В совокупности с прочими процессами на сервере это может являться узким местом производительности.  Сам по себе параметр  max_connections ресурсы не потребляет.

На серверах с 1-2 Гб ОЗУ оптимальным решением будет:

max_connections=120

Неплохим решением будет указать и max_user_connections со значением 25

max_user_connections=25

max_user_connections — количество открытых соединений на одного пользователя.

key_buffer — размер буфера, который используется для записи блоков индексов. Это одно из значений, которое необходимо указывать для каждого сервера индивидуально. Чтобы не снижалась производительность, рекомендуется выставлять от 15 до 25% памяти ОЗУ под key_buffer. Однако это довольно спорное решение. key_buffer нужно указывать не только исходя из объема оперативной памяти, но и обращая внимание на распределение ОЗУ между всеми процессами на сервере. В рамках сервера с 1-2 Гб ОЗУ оптимальным вариантом будет значение key_buffer_size = 16K. При необходимости этот параметр можно увеличить, но только для myisam. Поэтому, если все таблицы на innodb, то значение должно быть меньшим. Сам по себе MySQL использует myisam.

table_cache — число открытых таблиц для всех потоков. Чем больше значение, тем больше количество дескрипторов файлов, необходимых для работы MySQL.  Данный параметр подробно описан в документации http://www.mysql.ru/docs/man/Table_cache.html

Дескриптор файла* — это целое число без знака, с помощью которого процесс обращается к открытому файлу. 

ru.bmstu.wiki/Файловый_дескриптор

Для сервера со средней производительностью можно начать со значения 512.

Можно проверить переменную Opened_tables, её значение

mysql> show status LIKE "Opened_tables%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Opened_tables | 400 |
+---------------+-------+

Открытых таблиц — 400. В данном случае значение table_cache=512 оправдано.

innodb_buffer_pool_size

Если у Вас база данных использует innodb или некоторые таблицы, то рекомендуется выставлять максимально возможное значение. Буфер в InnoDB кэширует не только данные, но и индексы. При этом кэш самой операционной системы не используется. Не стоит забывать о прочих настройках MySQL и других процессах на сервере, которые также потребляют ресурс оперативной памяти.

query_cache_size

Данный параметр отвечает за то, сколько выделить памяти на кэширование запросов. В данном случае, чем меньше значение — тем лучше. Но подбирается оно индивидуально. Начните с 32M (мегабайт) и увеличивайте до тех пор, пока не подберете необходимую конфигурацию.

wait_timeout — время (измеряется в секундах) ожидания сервером активности соединения, перед тем как закрыть его. Оптимальное значение для VPS с начальной и средней конфигурацией — 10 секунд.

Поиск узких мест в базе MySQL

Если Ваш сайт работает медленно, то вероятность отказа от какого-либо действия на Вашем сайте велика. Зачастую проблемой медленной работы сайта является большое число запросов к базе данных, которые выполняются долго, более 1 секунды. Такие запросы можно отследить и оптимизировать.

Чтобы определить, какой запрос к базе данных MySQL «тормозит», нужно в файле /etc/my.cnf добавить две настройки:

log_slow_queries = /var/log/mysql-slow.log

long_query_time = 1

log_slow_queries — файл, куда будет записываться лог.

long_query_time — время в секундах, по истечении которого запрос будет считаться медленным.

После добавления этих настроек необходимо создать файл /var/log/mysql-slow.log  и выставить корректные права. Это можно сделать двумя командами:

touch  /var/log/mysql-slow.log

chown mysql:mysql /var/log/mysql-slow.log

После этого нужно перезапустить сервис MySQL. Это можно сделать с помощью следующих команд:

/etc/init.d/mysql restart или /etc/init.d/mysqld restart

Так же лог медленных запросов можно включить через 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'; - включаем логирование медленных запросов
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; - команда, которая закрывает и открывает все файлы журналов.

Синтаксис команды flush: http://www.php.su/mysql/manual/?page=FLUSH

Если будет много запросов, которые исполняются более 1 секунды, лог будет разрастаться очень быстро. Необходимо следить за свободным местом на сервере. Попавшие в лог запросы необходимо оптимизировать для сокращения времени обработки и увеличения производительности.

Полезное для администратора:

http://mysqltuner.com/ — рекомендации по оптимизации,
http://www.mysql.ru/docs/ — документация MySQL,
https://php.net/book.mysql — MYSQL и PHP.