Оптимизация MySQL
Так уж повелось, что в мире серверов, самой популярной базой данных, стала база mysql. Простота установки, бесплатность, высокая скорость работы (при должной настройки), сделали ее одной из наиболее часто используемых баз данных. Она может почти все, работает шустро, но, без должной оптимизации и настройки может стать очень проблемным сервисом. А отсюда может происходить и медленная загрузка сайта, и ошибки на страницах вида "Too many active connections". Оптимизация настроек всегда дело трудоемкое и найти и выставить именно те параметры, которые дадут максимальную производительность, можно только в процессе работы приложения, когда уже есть статистика нагрузки и видны проблемные места.
Итак, приступим, если вы думаете, что вы установили и запустили MySQL и на этом ваша работа закончилась – вы оптимист. Первое что надо сделать это правельно запустить.
Обычно (Ubuntu/Debian) MySQL стартуется из init-скрипта, через демон mysqld_safe. Смотрим /etc/init.d/mysql находим строку запуска MySQL это что то типа этого # Start MySQL! ниже видим /usr/bin/mysqld_safe > /dev/null 2>&1 &. MySQL запускается с настройками по умолчанию, но, есть еще некоторые параметры которые можно добавить к запуску:
–skip-name-resolve – Не производится разрешения имен хостов. Все значения в столбце Host в таблицах привилегий должны быть IP-адресами или значениями localhost
Это сильно увеличивая быстордействие запросов за счет выключения постоянных DNS запросов (до 1000% при “внешних” соединениях с mysql)
–skip-locking – Нужно запускать mysqld с опцией –skip-locking. Запрет внешней блокировки существенно повысит скорость работы. Редко когда с одной базой работают одновременно 2 сервера. Ограничение: при запрете внешней блокировки нельзя будет использовать несколько серверов для работы с теми же базами данных.
–low-priority-updates – INSERT/UPDATE в БД являются более низкоприоритетными, чем SELECT… Думаю для многих проектов это будет актуально, хотя пользоваться этим надо с умом.
–log-slow-queries=/var/log/slow_queries - записываем медленные запросы
Перед этим, стоит помочь MySQL (на всякий случай):
#touch /var/log/slow_queries
#chmod 777 /var/log/slow_queries
Рекомендуется ежедневно смотреть лог медленных запросов через less или же пользоваться mysqldumpslow.
Примечание: –log-long-format позволяет заносить в лог запросы, не использующие индексов. Это также полезная информация, для поиска внутренних врагов.
В итоге должно получится следующее:
/usr/bin/mysqld_safe --skip-name-resolve --low-priority-updates > /dev/null 2>&1 &
Хочу обратить внимание на то что в разных версиях MySQL не все параметры могут работать!
А что же дальше... а дальше нас ждет настройка my.cnf и мы рассмотрим несколько важных конфигурационных переменных:
thread_concurrency Если у вас много памяти и много таблиц, то для увеличения производительности, при запуске сервера рекомендуется использовать следующие формулы, учитывающие специфику работы mysql под различные ОС:
» Для Linux: thread_concurrency = (кол-во процессоров)*(кол-во ядер в одном процессоре)*3
Примечание: Хотя по поводу этой переменной споры не утихают до сих пор мы просто используем команду cat /proc/cpuinfo и смотрим сколько у нас процессоров и ядер а именно cpu cores: 1 у меня 8 процессоров по 4 ядра в итоге я выставил значение 96.
max_connections=4000 Разрешенное количество одновременно соединений. Ставим 4000, чтобы не было казусов с “Too many connections…”, но стараемся поставить все таки меньше, так как, Mysql 5.0 все ещё использует select() вызов, а если хотим держать большое число соединений, то необходимо ставить mysql 6.0, который построен на libevent (epoll).
Примечание: если вам хочется использовать больше 4000 коннектов к базе, вынужден вас огорчить, с этим есть проблема. Задать такое количество соединений, вы конечно сможете, но стандартные билды не позволяют использовать такое количество соединений. Вы конечно, можете скачать патчи, для повышения количества соединений, – но это не спасет Отца Русской демократии, даже в этом случае вы будете иметь ограничение порядка 7000 коннектов.
key_buffer=1024M Блоки индексов буферизированы и доступ к ним разрешен всем потокам. key_buffer – размер буфера, используемого для блоков индексов. Чтобы улучшить обработку индексов (для всех операций чтения и записи нескольких элементов), необходимо увеличить это значение настолько, насколько возможно. Рекомендуется выставлять это значение от 15% до 25% ОЗУ, чтобы система не начала сохранять временные файлы на диске, что значительно снизит производительность.
Производительность буфера ключей можно проверить, выполнив команду show status LIKE "Key%"; и проверив значения переменных Key_read_requests, Key_reads, Key_write_requests и Key_writes. Отношение значений Key_reads/Key_read_request обычно должно быть < 0,01.
key_buffer = 0,25 * Объему ОЗУ - поскольку у меня 24Gb оперативной памяти, мне не жалко отдать 5Gb для индексов.
Примечание: В общем можете выставить и большее значение на размер потребляемой ОЗУ это не играет он всего лишь её резервирует, но, если будит DDOS сервер уйдет в даун.
table_cache=1024 – Количество открытых таблиц для всех потоков. С увеличением этого значения увеличивается количество дескрипторов файлов, необходимых для mysqld. Чтобы узнать, необходимо ли изменять значение кэша таблиц, следует проверить значение переменной Opened_tables в вашем сервере MySQL.
sort_buffer=128M – Каждый поток, которому необходимо произвести сортировку, выделяет буфер данного размера. Увеличение данного значения позволит ускорить выполнение операторов ORDER BY или GROUP BY. “Увлекаться” большим значением не стоит, а посчитать его можно исходя из среднего значения открытых потоков (Threads_running) и кол-ва ОЗУ сервера.
record_buffer=32M – Каждый поток, осуществляющий последовательное сканирование, выделяет буфер указанного размера для каждой сканируемой таблицы. Если проводится много последовательных операций сканирования, это значение можно увеличить. Адекватно оценить/подсчитать размер этого буфера можно исходя из данных о количестве прочитанных строк из таблиц mysql и объема данных в таблицах… Обычно рекомендуется принять его в 4-6 раз меньшим чем sort_buffer.
query_cache_limit=2M – Результаты, превышающие это значение, не кэшируются (по умолчанию – 1Мб). Зависит от типа извлекаемых данных из mysql. Если запросов много и в то же время преимущественно извлекается небольшое количество данных (1 Mb), то данное значение лучше уменьшить.
max_join_size=1000000 Это защита от кривых рук программиста, способного join`ом на 10 миллионов записей похоронить даже 4-х процессорный сервер.
Объединения, которые потенциально могут считывать более max_join_size записей, будут возвращать ошибку. Это значение нужно задавать, если ваши пользователи осуществляют объединения, которым недостает оператора WHERE, – такие объединения занимают много времени, а затем возвращают миллионы строк.
max_sort_length=20 – Защита от кривых мозгов архитектора БД, когда не стоят адекватные лимиты по индексам сортировки текстовых полей
Параметр определяет, сколько байтов следует использовать при сортировке значений BLOB или TEXT (обрабатываются только первые max_sort_length байтов каждого значения, остальные игнорируются).
thread_cache_size=64 Определяет, сколько потоков должно сохраняться в кэше для повторного использования. После отключения клиента потоки клиента помещаются в кэш, если там не больше потоков, чем thread_cache_size. Все новые потоки сначала берутся из кэша, и только когда кэш становится пустым, создаются новые потоки. Значение этой переменной можно увеличить, чтобы повысить производительность, если создается много новых соединений (если потоки у вас хорошо организованы, обычно заметного улучшения производительности не наблюдается). Насколько эффективен текущий кэш потоков, можно определить по разнице между Connections и Threads_created. Если есть возможность, рекомендуется установить это значение не меньше, чем значение переменной Max_used_connections. Если значение этой переменной больше 128, рекомендуется ограничиться этим значением. В нашем случае, Max_used_connections = 62, поэтому установим этот параметр в 64.
myisam_sort_buffer_size=512М – Буфер, который выделяется для сортировки индексов при выполнении команды REPAIR или для создания индексов при помощи команд CREATE INDEX или ALTER TABLE. Рекомендуется не жадничать …
net_read_timeout=12 – Количество времени в секундах, на протяжении которого ожидаются дополнительные данные от соединения, пока не будет отменено чтение. Обратите внимание, что мы не ожидаем поступления данных от соединения, время ожидания определяется по write_timeout.
net_write_timeout=15 – Время ожидания записи блока через соединение, пока запись не будет прервана (в секундах).
wait_timeout=30 – Время в секундах, на протяжении которого сервер ожидает активности соединения прежде, чем закрыть его.
Примечание: мы, предполагаем, что наша система очень динамична, и висеть конекшенам по несколько часов не требуется, 30 секунд достаточно даже для очень медленных запросах от Web-приложения.
interactive_timeout=600 – Количество времени в секундах, на протяжении которого сервер ожидает активности со стороны интерактивного соединения, прежде чем закрыть его. Интерактивный клиент – это клиент, который использует параметр CLIENT_INTERACTIVE для mysql_real_connect(). См. также информацию по wait_timeout.
long_query_time=30 – Если обработка запроса отнимает больше указанного промежутка времени (в секундах), значение счетчика Slow_queries будет увеличено. Если используется параметр –log-slow-queries, запрос будет записан в журнал медленных запросов.
Значение этого параметра должно быть примерно равно time_limit скрипта php или временно лимиту операции выдачи, т.к. часто получаются ситуации когда PHP-скрипт уже вылетел по time_limit, а бендненькое умирающее животное MySQL все еще корчится в конвульсиях над запросом по группировке 10 млн записей.
Примечание: Хочу еще раз обратить внимание на то что в разных версиях MySQL не все переменные могут работать!
После настройки очень полезно сделать еще и автоматическую оптимизацию MySQL с помощью утилиты mysqltuner. Загрузить её можно командой:
Использовать её просто: загружаем mysqltuner.pl на сервер с mysql, ставим права на запуск для файла (или запускаем так: perl mysqltuner.pl), на запрос логина / пароля даем root или пользователя с привилегированными правами и смотрим рекомендации. Рекомендации заносим в конфиг, перезапускаем mysql-server. Или применяем «налету» через консоль mysql, если проект уже запущен и перезапуск нежелателен. Кроме советов по настройкам mysql, утилита так же показывает информацию о индексах в таблицах и фрагментации, если mysql уже какое-то время используется. Про индексы нередко забывают, что сильно повышает потребление ресурсов системы. Для дефрагментации запускаем OPTIMIZE TABLE из консоли mysql.
Оптимизация всех таблиц в базе данных.
mysqlcheck --user=uuuuuu --password=pppppp --optimize database_name
Оптимизация всех баз данных сразу.
mysqlcheck --user=uuuuuu --password=pppppp --optimize --all-databases
Если хотим выполнять оптимизацию всех баз ежедневно в 4 часа ночи, открываем файл /etc/crontab и добавляем в него строчку.
0 4 * * * root mysqlcheck --user=uuuuu --password=pppppp --optimize --all-databases > nul
По дальнейшей оптимизации в процессе работы проекта уже смотрим по ситуации и увеличиваем нужные параметры.