MySQL и MariaDB являются наиболее широко используемыми системами управления реляционными базами данных (RDMS), когда речь идет о хостинге веб-сайтов и системах CMS, таких как Joomla, WordPress, Drupal и Typo 3.
Храните данные MySQL в отдельных разделах
Когда речь идет об оптимизации и обеспечении надежности, всегда лучше всего хранить данные базы данных в отдельном томе. Эти тома предназначены специально для быстрых накопителей, таких как SSD, NVMe. Даже если ваша система выйдет из строя, ваша база данных будет в безопасности. Поскольку том раздела состоит из быстрых томов хранения, производительность будет выше.
Установите максимальное количество соединений MySQL
MySQL/MariaDB использует инструкцию max_connections, которая определяет, сколько одновременных соединений в данный момент разрешено на сервере. Слишком большое количество соединений приводит к большому потреблению памяти, а также к высокой загрузке процессора. Для небольших сайтов количество соединений может быть определено в 100-200, а для крупных может потребоваться 500-800 и более. Значение max_connections можно динамически изменять с помощью SQL-запроса.
| 1 | mysql -u root -p |
| 1 | mysql> set global max_connections=200; |
Включить журнал медленных запросов MySQL
Ведение журнала запросов, выполнение которых занимает очень много времени, облегчает поиск и устранение проблем с базой данных. Журнал медленных запросов можно включить, добавив следующие строки в конфигурационный файл MySQL/MariaDB.
| 1 2 3 | slow-query-log=1 slow-query-log-file= /var/lib/mysql/mysql-slow-query.log long-query-time=1 |
Где первая переменная включает журнал медленных запросов
Вторая переменная определяет каталог файла журнала
Третья переменная определяет время выполнения запроса MySQL.
Перезапустите службу MySQL/MariaDB и проследите за журналом
| 1 | systemctl restart mysql |
| 1 | systemctl restart mariadb |
| 1 | tail -f /var/lib/mysql/mysql-slow-query.log |
Установите максимальный пакет, разрешенный MySQL
В MySQL данные разбиваются на пакеты. Max_allowed_packet определяет максимальный размер пакетов, которые могут быть отправлены. Установка слишком низкого значения max_allowed_packet может привести к слишком медленному выполнению запроса. Рекомендуется устанавливать значение packet равным размеру самого большого пакета.
Настройка емкости временной таблицы
Tmp_table_size - это максимальное пространство, используемое для таблицы встроенной памяти. Если размер таблицы превышает указанный предел, она будет преобразована в таблицу MyISAM на диске. В MySQL/MariaDB вы можете добавить следующие переменные в конфигурационный файл для настройки временного размера таблицы. Рекомендуется установить это значение на сервере 64M на Гб памяти.
| 1 2 3 | [mysqld] tmp_table_size=64M |
Перезапустите службу mysql
| 1 | systemctl restart mysql |
| 1 | systemctl restart mariadb |
Настройте максимальный объем таблицы памяти
Max_heap_table_size - это переменная, используемая в MySQL для настройки максимального объема таблицы памяти. Размер максимальной емкости таблицы памяти должен быть таким же, как и емкость временной таблицы, чтобы избежать записи на диск. Рекомендуется установить это значение на сервере равным 64M на ГБ памяти. Добавьте следующую строку в конфигурационный файл MySQL и перезапустите службу.
| 1 2 | [mysqld] max_heap_table_size=64M |
Чтобы применить изменения, перезапустите сервер базы данных.
| 1 | systemctl restart mysql |
| 1 | systemctl restart mariadb |
Отключение обратного поиска DNS для MySQL
При получении нового соединения MySQL/MariaDB выполняет поиск DNS для определения IP-адреса пользователя. Это может вызвать задержку, если конфигурация DNS недействительна или есть проблемы с DNS-сервером. Чтобы отключить поиск DNS, добавьте следующую строку в конфигурационный файл MySQL и перезапустите службу MySQL.
| 1 2 | [mysqld] skip-name-resolve |
Перезапустите службу:
| 1 | systemctl restart mysql |
| 1 | systemctl restart mariadb |
Избегайте использования свопинга в MySQL
Ядро Linux перемещает часть памяти в специальный раздел диска, называемый "swap" пространством, когда в системе заканчивается физическая память. В этом случае система записывает информацию на диск, а не освобождает часть памяти. Поскольку системная память быстрее дискового хранилища, рекомендуется отключить своппинг. Отключить своппинг можно с помощью следующей команды.
| 1 | sysctl -w vm.swappiness=0 |
Увеличение размера буферного пула InnoDB
MySQL/MariaDB имеет движок InnoDB, который имеет буферный пул для кэширования и индексирования данных в памяти. Буферный пул помогает MySQL/MariaDB запросам выполняться сравнительно быстрее. Выбор правильного размера буферного пула InnoDB требует определенных знаний о системной памяти. Лучше всего установить значение размера буферного пула InnoDB на 80% от объема оперативной памяти.
Пример.
Системная память = 4 ГБ
Размер буферного пула = 3,2 ГБ
Добавьте следующую строку в конфигурационный файл MySQL и перезапустите службу
| 1 2 | [mysqld] Innodb_buffer_pool_size=3.2G |
Перезапустите базу данных:
| 1 | systemctl restart mysql |
| 1 | systemctl restart mariadb |
Работа с размером кэша запросов
query_cache_size был удален в MySQL 8.
Директива Query cache в MySQL/MariaDB используется для кэширования всех запросов, которые постоянно повторяются с одними и теми же данными. Для небольших сайтов рекомендуется установить значение 64MB и со временем увеличивать его. Увеличивать размер кэша запросов до гигабайтов не рекомендуется, так как это может ухудшить производительность базы данных. Добавьте следующую строку в файл my.cnf.
| 1 2 | [mysqld] query_cache_size=64M |
Проверка неиспользуемых соединений
Неработающие соединения потребляют ресурсы, поэтому их необходимо завершить или обновить, если это возможно. Эти соединения находятся в состоянии "сна" и могут оставаться в нем в течение длительного периода времени. Проверьте простаивающие соединения с помощью следующей команды.
| 1 | mysqladmin processlist -u root -p | grep "Sleep" |
Запрос выведет список процессов, которые находятся в состоянии сна. Как правило, в PHP это событие может произойти при использовании mysql_pconnect. Это открывает соединение с MySQL, выполняет запросы, снимает аутентификацию и оставляет соединение открытым. С помощью директивы wait_timeout простаивающие соединения могут быть прерваны. Значение по умолчанию для wait_timeout равно 28800 секунд, которое можно уменьшить до минимального значения, например 60 секунд. Добавьте следующую строку в файл my.cnf
| 1 2 | [mysqld] wait_timeout=60 |
Восстановление базы данных MySQL
Если сервер неожиданно выключается, существует вероятность того, что таблицы в MySQL/MariaDB могут упасть. Существуют и другие возможные причины сбоя таблиц базы данных, например, доступ к базе данных во время выполнения процесса копирования, внезапный сбой файловой системы. На этот случай у нас есть специальный инструмент под названием "mysqlcheck", который проверяет, ремонтирует и оптимизирует все таблицы в базах данных.
Используйте следующую команду для выполнения действий по ремонту и оптимизации.
Для всех баз данных:
| 1 | mysqlcheck -u root -p --auto-repair --check --optimize --all-databases |
Для конкретной базы данных:
| 1 | mysqlcheck -u root -p --auto-repair --check --optimize dbname |
Замените dbname на имя вашей базы данных
Проверка производительности MySQL/MariaDB с помощью инструментов тестирования
Наилучшей практикой является регулярная проверка производительности баз данных MySQL/MariaDB. Это позволит легко получить отчет о производительности и точки улучшения. Существует множество инструментов, среди которых лучшим является mysqltuner.
Выполните следующую команду, чтобы загрузить инструмент
| 1 | wget https://github.com/major/MySQLTuner-perl/tarball/master |
Разархивируйте файл
| 1 | tar xvzf master |
Перейдите в каталог проекта и выполните следующий скрипт.
| 1 | cd major-MySQLTuner-perl-7aa57fa |
| 1 | ./mysqltuner.pl |
Про использование mysqltuner можно почитать в статье "Оптимизация работы MySQL"