MySQL-тюнинг. Настраиваем по-взрослому.

Итак, для начала благодарности. Выражается нереальная благодарность Олегу Копачовцу(он же Dr. Cop, http://www.kopachovets.com ), за собранный материал и анализ фактов, а также за удачную подачу материала.
Идея написания статьи витала в воздухе уже давно, вопрос правильной настройки сего загадочного зверька всегда вызывал у меня интерес. Мало кто знает, но правильно оттюненный MySQL может работать в 10-100 раз быстрее своего неоптимизированного собрата из базовой установки.
Я человек не жадный, именно поэтому данная статья увидела свет. Итак, приступимс …

Поучение 1. Вы еще не стартовали MySQL? – Тогда мы идем к вам …

Если вы думаете, что вы стартовали демона MySQL и на этом ваша работа закончилась – вы оптимист.
А уверены ли вы, что стартовали MySQL правильно? Да? Ну так давайте посмотрим, что стоило бы сделать в первую очередь …

Итак, предположим, что MySQL стартуется из init-скрипта, через демон mysqld_safe. Находим строку запуска MySQL, и что мы видим?

$bindir/mysqld_safe --datadir=$datadir --pid-file=$pid_file >/dev/null 2>&1 &

И чего? – спросите вы.

А вот чего, аккуратно добавляем к строке запуска следующие параметры:

–skip-name-resolve – Не производится разрешения имен хостов. Все значения в столбце Host в таблицах привилегий должны быть IP-адресами или значениями localhost
Это сильно увеличивая быстордействие запросов за счет выключения постоянных DNS запросов (до 1000% при “внешних” соединениях с mysql)
–skip-locking – Нужно запускать mysqld с опцией –skip-locking. Запрет внешней блокировки существенно повысит скорость работы. Редко когда с одной базой работают одновременно 2 сервера
Ограничение: при запрете внешней блокировки нельзя будет использовать несколько серверов для работы с теми же базами данных.
–low-priority-updates – INSERT/UPDATE в БД являются более низкоприоритетными, чем SELECT… Думаю для многих проектов это будет актуально, хотя пользоваться этим надо с умом.

$bindir/mysqld_safe --datadir=$datadir --pid-file=$pid_file --skip-name-resolve --low-priority-updates --skip-locking >/dev/null 2>&1 &

Поучение 2. Ну вроде бы запустились …

Мы конечно уже обрадовались, думается, ну вот же он – наш Database server, но-но, не тут то было, а как же my.cnf?
Открываем /etc/my.cnf, и начинаем стучать в бубен (то есть определять конфигурационные переменные MySQL):

thread_concurrency. Если у вас много памяти и много таблиц, то для увеличения производительности, при запуске сервера рекомендуется использовать следующие формулы, учитывающие специфику работы mysql под различные ОС:

• Для FreeBSD: thread_concurrency = (кол-во процессоров)*(кол-во ядер в одном процессоре)
• Для Linux: thread_concurrency = (кол-во процессоров)*(кол-во ядер в одном процессоре)*3

Почему на Linux можно давать можно и нужно давать больше потоков, чем на FreeBSD? Это связано с тем, что Linux умеет распределять и управлять потоками между ядрами, а FreeBSD не умеет, зато FreeBSD умеет эффективно распределять процессы, чего, в свою очередь, не умеет Linux.

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.
Пример:

mysql> show status LIKE "Key%";

+------------------------+------------+

| Variable_name | Value |

+------------------------+------------+

| Key_blocks_not_flushed | 0 |

| Key_blocks_unused | 818569 |

| Key_blocks_used | 287552 |

| Key_read_requests | 3012333357 |

| Key_reads | 2435564 |

| Key_write_requests | 668018001 |

| Key_writes | 70927911 |

+------------------------+------------+

Заметка: Key_buffer является общим для всех потоков, все остальные буфера выделяются для каждого потока конкретно

key_buffer = 0,25 * Объему ОЗУ - поскольку у меня 4Gb оперативной памяти, мне не жалко отдать 1Gb для индексов.

table_cache=1024 – Количество открытых таблиц для всех потоков. С увеличением этого значения увеличивается количество дескрипторов файлов, необходимых для mysqld. Чтобы узнать, необходимо ли изменять значение кэша таблиц, следует проверить значение переменной Opened_tables в вашем сервере MySQL.

mysql> show status LIKE "Opened_tables%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Opened_tables | 685 |
+---------------+-------+

Итак, сейчас у нас 685 открытых таблиц, есть смысл задать этот параметр для нашего сервера в 1024.

sort_buffer=128M – Каждый поток, которому необходимо произвести сортировку, выделяет буфер данного размера. Увеличение данного значения позволит ускорить выполнение операторов ORDER BY или GROUP BY. “Увлекаться” большим значением не стоит, а посчитать его можно исходя из среднего значения открытых потоков (Threads_running) и кол-ва ОЗУ сервера.

mysql> show status LIKE "Threads_running%";
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| Threads_running | 4 |
+-----------------+-------+

Текущее значение Threads_running равно 4, таким образом на буферы сортировки у нас выделяется в среднем 512Mb.

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, рекомендуется ограничиться этим значением.

mysql> show status LIKE "Max_used_connections%";
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| Max_used_connections | 62 |
+----------------------+-------+

В нашем случае, 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 млн записей.

Поучение 3. А не выпить ли нам таблеток от Склероза?

Кэш в MySQL называется QuickCache или он же QCache. Ошибочно мнение, что эффективность использования кэша это отношение хитов в кэш к инсертам в кэш. Все немного сложнее. Эффективность попадания в кэш можно оценить вот таким показателем:

qcache_hit_ratio = qcache_hits / (qcache_hits + qcache_inserts + qcache_not_cached)

Посмотреть эти значения можно следующим образом:

mysql> SHOW STATUS LIKE 'Qcache%';
+-------------------------+--------+
| Variable_name | Value |
+-------------------------+--------+
| Qcache_free_blocks | 36 |
| Qcache_free_memory | 138488 |
| Qcache_hits | 79570 |
| Qcache_inserts | 27087 |
| Qcache_lowmem_prunes | 3114 |
| Qcache_not_cached | 22989 |
| Qcache_queries_in_cache | 415 |
| Qcache_total_blocks | 912 |
+-------------------------+--------+

Если это значение > 0.8, то значит 80% ваших запросов попадают в кэш, это очень хороший показатель.
Если % попадания в кэш низкий, то необходимо увеличить значение query_cache_size.
Текущее значиние можно посмотреть так:

SHOW VARIABLES LIKE 'query_cache_size';

Опять же возникает вопрос: как выбрать адекватное значение query_cache_size?
В этом поможет Qcache_lowmem_prunes. В этой переменной хранится число запросов, которые были убраны из кэша из-за необходимости кэширования новых запросов. Необходимо стремится к такому размеру кэша, при котором Qcache_lowmem_prunes будет лишь незначительно увеличиваться. Для этого, рекомендуется сравнить разницу значений Qcache_lowmem_prunes за час и кол-во запросов, поступивших на mysql за этот же час.
На практике, для расчета query_cache_size можно использовать одну из 2-х формул:

query_cache_size = (число запросов за 10 минут)*(срений объем ответа на запрос) * 1,2
или
query_cache_size = (объем трафика за 10 минут) * 1,2

Это позволит закэшировать запросы на 10 минут + дать дополнительные 20% памяти на фрагментацию кэша и дополнительный резерв кэширования
Подсчитать количество и средний объем ответа на запроса можно использую переменные Bytes_sent соответственно

И так значения query_cache_size мы увеличили, после чего стоит обратить внимание на значения Qcache_total_blocks, Qcache_free_blocks и Qcache_queries_in_cache. MySQL хранит кэш в блоках. На 1 запрос необходимо 2 блока: один для самого текста запроса, второй для результата.
Если рассмотреть таблицу со значения Qcache%
Общее количество блоков кэша Qcache_total_blocks – 912
Закешировано сейчас 415 запрос, а значит занят 415*2 = 830 блоков
свободно блоков Qcache_free_blocks – 36. Чем больше незадействованных Qcache_free_blocks, тем больше степень “фрагментации” кэша.
Если большинство запросов имеют небольшой объем результирующих данных, то стоит уменьшить минимальный размер блока кэша query_cache_min_res_unit, который по умолчанию равен 4 Кб.
Если же большинство запросов возвращают много данных – то стоит увеличить размер блока кэша.
Главное – это добится минимального значения Qcache_free_blocks.

Если же случилось непоправимое, и ваш кеш вас подводит, рекомендуется не забыть про следующие команды MySQL:
FLUSH QUERY CACHE – дефрагментировать кэш
RESET QUERY CACHE – очистить кэш

Поучение 4. СМЕРШ. Ищем злодеев и вредителей.

Без комментариев:

–log-slow-queries=/var/log/slow_queries

Перед этим, стоит помочь MySQL (на всякий случай):

#touch /var/log/slow_queries
#chmod 777 /var/log/slow_queries

Рекомендуется ежедневно смотреть лог медленных запросов через less или же пользоваться mysqldumpslow.

Примечание: –log-long-format позволяет заносить в лог запросы, не использующие индексов. Это также полезная информация, для поиска внутренних врагов.

Поучение 5. Подсматриваем и вынюхиваем.

Даже если ваши руки настроили MySQL как конфетку, НЕ ЗАБЫВАЙТЕ О МОНИТОРИНГОВОМ программном обеспечении, – целее будете …

Поучение 6. Что делать и Кто виноват?

И так в один прекрасный момент мы видим, что очень медленно открываются страницы нашего драгоценного сайта…
При выяснении причин и при просмотри top сервера обнаруживаем, что паршивой овцой в нашей боевой linux-связке оказался таки MySQL. Главное спокойно и без паники.

Админы с поверхностным представлением об MySQL сделают “service mysql restart” и на этом успокоятся… Вот только, минут через 10-15, как правило, MySQL опять начнет бится в конвульсиях.

Админы с более продвинутой встроенной логикой начнут делать mysqladmin processlist и пытатся выловить кто же мучает их MySQL и найти причину. Не спорю метод эффективен, но может оказаться что сайт развился до такой степени что ему уже просто не хватает MySQL ресурсов.

Итак, что же мы будем делать в сутации когда наш верный конь MySQL захромал?

• таки да… “show processlist;” – и попытаться найти первопричину загрузки. По возможности ослабить её или устранить. Возможно какие-то боты “долбят” ваш сервер
• если таки причина не устранена пробуем сделать: mysqladmin flash-tables . Такое действие поможет закрыть устаревшие дескрипторы для таблиц и таким образом можно вправить МОСК своему любимому MySQL
• FLUSH QUERY CACHE – дефрагментировать кэш
• service mysql restart

Если ничего из вышеперечисленного не поможет, рекомендую пинать Системного Архитектора на тему построения более Масштабируемого решения.