MySQL/MariaDB — это надежная, мощная и популярная база данных. Однако без тонкой настройки она может неэффективно использовать ресурсы сервера. Узнайте, как оптимизировать производительность MySQL/MariaDB, чтобы улучшить время отклика базы данных и минимизировать накладные расходы сервера.
Требования
- Облачный сервер с ОС Linux.
- MySQL 5.7 или MariaDB 5.5, установленные и работающие.
MySQL/MariaDB по умолчанию установлена и работает на стандартной установке Linux. Если ваш сервер был создан с минимальной установкой, вам необходимо установить и настроить MySQL/MariaDB, прежде чем продолжить.
Оптимизация запросов MySQL/MariaDB
Частью процесса настройки MySQL/MariaDB для повышения производительности является проверка медленных или неэффективных запросов. Один плохо структурированный запрос MySQL/MariaDB может замедлить работу всей базы данных.
MySQL/MariaDB может быть настроена на запись в журнал любого запроса, который занимает больше заданного количества секунд. Это позволит вам отследить все медленные запросы и исправить их по мере необходимости.
Чтобы включить протоколирование медленных запросов, войдите в MySQL/MariaDB:
mysql -u root -pВведите следующую команду для включения протоколирования:
SET GLOBAL slow_query_log = 'ON';По умолчанию порог составляет 10 секунд. Используйте следующую команду, чтобы включить протоколирование для любого запроса, который занимает более 1 секунды:
SET GLOBAL long_query_time = 1;Запросы, выполнение которых занимает более 1 секунды, будут регистрироваться в /var/lib/mysql/hostname-slow.log.
Настройка параметров InnoDB
Существует три параметра InnoDB, которые можно настроить для повышения производительности MySQL 5.7 и MariaDB 5.5. Более подробную информацию о каждом из этих параметров можно найти в официальной документации MySQL.
innodb_buffer_pool_size
Это должно быть значение между 50% и 75% от общего объема оперативной памяти сервера. Он должен быть равен или меньше размера базы данных.
innodb_flush_log_at_trx_commit
Этот параметр обеспечивает значительный компромисс между производительностью и надежностью. Если установить значение 0, производительность базы данных значительно увеличивается. Однако при сбое может быть потеряно до 1 секунды транзакций.
Значение по умолчанию для этого параметра равно 1. Это значение необходимо для полного соответствия ACID. Если соответствие ACID является требованием для вашего проекта, не изменяйте значение для innodb_flush_log_at_trx_commit.
innodb_flush_method
Установите значение O_DIRECT, чтобы избежать двойной буферизации данных.
Добавьте новые настройки InnoDB
Чтобы изменить настройки InnoDB, откройте файл my.cnf для редактирования:
- CentOS 7:sudo nano /etc/my.cnf
- Ubuntu 16.04:sudo nano /etc/mysql/my.cnf
Только для CentOS 7: Оставьте первую строку файла ([mysqld]) нетронутой. Разместите конфигурации ниже этой строки.
Только Ubuntu 16.04: Добавьте следующее содержимое в верхнюю часть файла. Обязательно поместите [mysqld] в качестве первой строки.
Добавьте новые конфигурации, по одной на строку. Например:
[mysqld]
innodb_buffer_pool_size = 500M
innodb_flush_log_at_trx_commit = 0
innodb_flush_method = O_DIRECTСохраните и выйдите из файла. Перезапустите MySQL/MariaDB, чтобы изменения вступили в силу:
- CentOS 7:sudo systemctl restart mariadb
- Ubuntu 16.04:sudo systemctl restart mysql