mysyslog.ru

Настройка производительности MySQL (MyISAM)

В свое время я достаточно долго искал в интернете внятное описание тюнинга производительности mysql и ничего подходящего не находил. На mysql.com главы документации о производительности носят скорее декларативный характер – “Сделайте так, и все будет хорошо” – без описание почему именно такие значения нужно выставить. Сейчас ситуация несколько меняется, появляются статьи о правильной настройке с объяснениями, похоже в основном благодаря книге High Performance MySQL. Этой публикацией я открываю небольшой цикл статей, призванных помочь системным администраторам и web-мастерам настраивать mysql.

Один из самых важных параметров с точки производительности MyISAM – key_buffer_size. Во всех рекомендациях советуют отдавать под него 30-40% процентов физической памяти. Правда почти нигде не рассказывается почему именно так. Попробую ответить на этот вопрос.
Переменная key_buffer_size определяет какой размер индексов можно поместить в оперативную память (кеш). MyISAM хранит индексы в отдельных файлах с расширением .MYI, т.е. для того, чтобы воспользоваться индексами mysql должна открыть и прочитать один из файлов .MYI. Нужно напомнит, что IO операции с дисками крайне медленны по сравнению, например, с оперативной памятью. Потому снижение числа дисковых IO операций – основная стратегия повышения производительности в любом высоконагруженном приложении, не только в СУБД. Mysql, следуя стратегии уменьшения IO операций, прочитав индекс из файла, помещает полученное значение в оперативную память. В случае если этот индекс потребуется еще раз, mysql его считает уже из кеша, что значительно быстрее. Если память под индексы достигла размера key_buffer_size, то mysql удалит старые данные из кеша. Т.е. если удаленные данные опять потребуются, их придется читать с диска. Из этого легко сделать вывод, что идеальным вариантом будет поместить все индексы в память. Но сколько памяти под индексы нужно? Индексы хранятся в файлах размер которых можно подсчитать. Конечно же нужно помнить, что размер индексов будет меняться во времени.
Подсчитать размер файлов индексов очень просто. Для этого нужно перебраться в каталог со всеми базами. Подсмотреть имя каталога можно так:

mysql> show variables like 'datadir';

Далее выполняем du:

# cd /var/lib/mysql/
# du -ch */*.MYI
...
54M    total

Т.е. сейчас под индексы хватит 64 Мб с небольшим запасом на рост. Это очень хороший результат, так как все индексы без проблем помещаются в память. 64М легко выделить почти на любой системе. Как же быть с базами, у которых индексы занимают несколько Гб? На 32х битных системах в этом плане все совсем плохо из-за ограничения по памяти в 4Гб. Для таких систем может пригодиться система разделения на горячий и холодный кеш. Представьте ситуацию, сервер выполнил редкий запрос, который потребовал считать достаточно много индексов с диска. Для их хранения пришлось вытеснить из памяти индексы других запросов, которые могут выполняться куда чаще. В результате, серверу пришлось прочитать с диска индексы для этого редкого запроса, да еще потом считать с диска все вытесненные им из кеша индексы…
Чтобы избежать такой ситуации можно разделить кеш на “горячий” и “холодный”. При первом чтении индекса он попадает в холодный кеш. Если индекс используется еще раз, то он перемещается в горячий кеш. Если памяти не хватает, то вытесняются только те значения индексов, которые находятся в холодной части кеша. Т.е. часто используемые индексы остаются в памяти. Соотношение между горячим и холодным кешами задается переменной key_cache_division_limit. По умолчанию, key_cache_division_limit = 100, т.е. существует только горячий кеш.
На 64x битной системе казалось бы проблемы с памятью под индексы не должно быть. Главное, чтобы было достаточно физической памяти. Но в mysql до версии 5.1 размер кеша ограничен 4Гб. Похоже это баг, который судя по всему так и не исправили в боле ранних версиях. Вы знаете много людей, перешедших в продакшене на mysql 5.1? Я нет. Многие продолжают использовать 4.1. Обойти это ограничение на 64х битных машинах можно. Для этого есть именованные индексы. Можно создать отдельные индексы для каких-то конкретных таблиц таким образом суммарный объем памяти под индексы быдет состоять из общего буфера и именованных кешей, которые в сумме могут быть больше чем 4Гб. Для создания именованного кеша key_buffer_1 нужно отредактировать my.cnf, добавив в него строчку:

key_buffer_1.key_buffer_size = "32M"

После этого нужно определить для каких таблиц будет использоваться этот кеш, в нашем примере это таблицы t1 и t2:

CACHE INDEX t1,t2 IN key_buffer_1;

Разделение на несколько кешей помимо основного буфера позволяет решить проблему с вытеснением индексов из памяти. Можно создать отдельные буфера в памяти для наиболее часто используемых таблиц.
Есть еще одна хитрость, любой индекс перед тем как попасть в кеш должен быть считан с диска, что может быть совершенно лишним в момент “боевой” работы сервера. Этого можно избежать, загрузив все индексы в момент старта сервера. Главное, чтобы памяти хватило. Делается это командой:

 LOAD INDEX INTO CACHE t1,t2

Как понять что все плохо? Т.е. плохо в данном случае – это когда слишком много чтений ключей с диска. Данные о чтении индексов из памяти или с дисков можно посмотреть так:

mysql> show global status like 'key_read%';
Variable_name Value

Key_read_requests 132
Key_reads 3

Key_read_requests – чтения из памяти.
Key_reads – чтение с дисков
Можно использовать эту формулу, чтобы определить процент запросов к дискам: 100 - ( Key_read_requests * 100) / ( Key_reads + Key_read_requests )
Чем ближе это значение к 0, тем лучше для производительности. Но с этими данными нежно обращаться осторожно, как и с любыми относительными вычислениями. 0,1 процента запросов к диску на разных системах могут очень сильно отличаться по своей сути. На одном сервер это будет одно обращение в час, а на другом десятки и сотни в секунду.
Потому нужно отслеживать и абсолютные значения. Хорошим вариантом будет отслеживать число запросов к дискам в секунду в моменты пиковой нагрузки на сервер. Для этого нужно посмотреть значение Key_reads в разные моменты времени. Пусть пиковыми часами нагрузки у нас является промежуток между 12тью и 15 часами дня. Тогда нужно замерить значение Key_reads в 12 часов, и еще раз в 15 часов. Их разница поделенная на число секунд в трех часах даст значение обращений к дискам в секунду. Далее полученное число можно сравнить производительностью дисков, но это уже совсем другая история.

–
Для подготовки этой стати использовались материалы из книги High Performance MySQL, сайтов sqlinfo.ru и mysqlperformanceblog.com

Tags: dba, mysql