Как можно ускорить Mysql. Чек лист работ

У Вас тормозит сайт. Вы не можете качественно отдать контент. Абоненты не могут нормально работать с закрытыми частями сайта. Служба поддержки получает звонки от разгневаных пользователей. Кто виноват и что делать? Ответ как правило в следующем:

  • некачественный код
  • не оптимизирована база
  • не оптимально настроен сервер БД
  • все вышесказанное вместе
  • не справляется железо (этот пункт следует рассматривать только после оптимизации пп. 1-3)

Оставим рефакторинг кода на откуп программистам и посмотрим, как можно ускорить mysql сервер. Все написаное ниже верно и для mariadb

Настройка mysql

  1. Установить последнюю версию mysql
  2. Запустить mysqltunner и дать ему поработать 24+ часов. Выполнить его рекомендации
  3. Настроить query_cache_size, query_cache_limit
  4. Для движка MyISAM настроить key_buffer_size
  5. Для движка ARIA настроить aria-pagecache-buffer-size (только для mariadb)
  6. Значение innodb_buffer_pool_size должно быть 70-80% от обьема RAM
  7. Для innodb-таблиц установить innodb_file_per_table=1. Для изменений потребуется сдампить и удалить (перименовать) базу. Накатить базу из дампа по-новой
  8. Посмотреть на параметр innodb_flush_log_at_trx_commit. Возможно имеет смысл отключить сброс данных на диск
  9. Выяснить, что используется чаще SELECT или UPDATE. Если SELECT то low-priority-updates=1, если UPDATE то low-priority-updates=0. Есть смысл сделать два инстанса mysql на разных сокетах/портах и разделить базы по приоритету использования SELECT/UPDATE
  10. Возможно потребуется увеличить wait_timeout если у конечного пользователя наблюдаются ошибки соединения
  11. Для временных таблиц использовать tmpfs вместо дисковой фс
  12. Если возможно, для обмена данными приложения и БД использовать сокет.  Такой обмен работает быстрее, чем обмен через tcp-соединение

Настройка БД

  1. Запустить mytop/mtop и отследить медленные запросы
  2. Включить slow.log. Оптимизировать медленные запросы. Использовать EXPLAIN
  3. Проверить индексы в таблицах. Там, где используется поиск однозначно должен быть индекс
  4. Установить правильные тип и размер полей. Это уменьшит размер таблицы
  5. Использовать партиционирование если в таблице есть старые данные, которые нужны, но к ним редко обращаются
  6. Регулярный optimize table таблиц с движком ARIA/MyISAM
  7. Пробовать persistant connection в базу. Возможно полегчает, возможно нет. Это индивидуально

Железо и система

  1. Много памяти, чем больше — тем лучше. База любит память, дать ей разумный максимум, который только возможен
  2. Хороший, многоядерный сервер
  3. Дисковая система в raid10
  4. Мониторинг (munin, zabbix и тд). Важно понимать, чем занят сервер
  5. Кастомизация ядра и настройки sysctl.conf, limits.conf
  6. Отключить лишнее, максимально уменьшить работу с диском
  7. Если у Вас хайлоад-проект, то запланировать внедрение репликации на другой сервер

Помнить, что mysql с параметрами по-умолчанию не оптимизирован под высокие нагрузки. Оптимизация БД это комплекс работ. Каждое соединение, каждый байт — считаются

Александр Черных
системный администратор

Статьи по теме