У Вас тормозит сайт. Вы не можете качественно отдать контент. Абоненты не могут нормально работать с закрытыми частями сайта. Служба поддержки получает звонки от разгневаных пользователей. Кто виноват и что делать? Ответ как правило в следующем:
- некачественный код
- не оптимизирована база
- не оптимально настроен сервер БД
- все вышесказанное вместе
- не справляется железо (этот пункт следует рассматривать только после оптимизации пп. 1-3)
Оставим рефакторинг кода на откуп программистам и посмотрим, как можно ускорить mysql сервер. Все написаное ниже верно и для mariadb
Настройка mysql
- Установить последнюю версию mysql
- Запустить mysqltunner и дать ему поработать 24+ часов. Выполнить его рекомендации
- Настроить query_cache_size, query_cache_limit
- Для движка MyISAM настроить key_buffer_size
- Для движка ARIA настроить aria-pagecache-buffer-size (только для mariadb)
- Значение innodb_buffer_pool_size должно быть 70-80% от обьема RAM
- Для innodb-таблиц установить innodb_file_per_table=1. Для изменений потребуется сдампить и удалить (переименовать) базу. Накатить базу из дампа по-новой
- Посмотреть на параметр innodb_flush_log_at_trx_commit. Возможно имеет смысл отключить сброс данных на диск
- Выяснить, что используется чаще SELECT или UPDATE. Если SELECT то low-priority-updates=1, если UPDATE то low-priority-updates=0. Есть смысл сделать два инстанса mysql на разных сокетах/портах и разделить базы по приоритету использования SELECT/UPDATE
- Возможно потребуется увеличить wait_timeout если у конечного пользователя наблюдаются ошибки соединения
- Для временных таблиц использовать tmpfs вместо дисковой фс
- Если возможно, для обмена данными приложения и БД использовать сокет. Такой обмен работает быстрее, чем обмен через tcp-соединение
Настройка БД
- Запустить mytop либо mtop и отследить медленные запросы
- Включить slow.log. Оптимизировать медленные запросы. Использовать EXPLAIN
- Проверить индексы в таблицах. Там, где используется поиск однозначно должен быть индекс
- Установить правильные тип и размер полей. Это уменьшит размер таблицы
- Использовать партиционирование если в таблице есть старые данные, которые нужны, но к ним редко обращаются
- Регулярный optimize table таблиц с движком ARIA/MyISAM
- Пробовать persistant connection в базу. Возможно полегчает, возможно нет. Это индивидуально
Железо и система
- Много памяти, чем больше — тем лучше. База любит память, дать ей разумный максимум, который только возможен
- Хороший, многоядерный сервер
- Дисковая система в raid10
- Мониторинг (munin, zabbix и тд). Важно понимать, чем занят сервер
- Кастомизация ядра и настройки sysctl.conf, limits.conf
- Отключить лишнее, максимально уменьшить работу с диском
- Если у Вас хайлоад-проект, то запланировать внедрение репликации на другой сервер
Помнить, что mysql с параметрами по-умолчанию не оптимизирован под высокие нагрузки. Оптимизация БД это комплекс работ. Каждое соединение, каждый байт — считаются
- Системный администратор с 2000 года
- Участник Freelancehunt, Хабр Q&A, cyberforum
- Кейсы
