Сегодня мы поговорим о тонкой настройке базы данных. Сразу оговоримся, что описанные настройки применимы к 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.
