MySQL. Увеличение производительности. Часть 1 – Кэширование данных

http://www.mysql.com/
http://www.mysql.com/

Мониторинг производительности СУБД – достаточно объемная задача и ориентироваться среди сотен индикаторов для начинающего системного администратора очень сложно. Основная задача – определение пороговых значений, при превышении которых нужно предпринимать активные действия. В этой статье я постараюсь пролить свет на вопрос мониторинга и анализа производительности кэширования данных и индексов, а также дать рекомендации по дальнейшим действиям.

Получить информацию о текущем состоянии переменных, отображающих показатели производительности, можно с помощью команды “SHOW STATUS”. Её полный синтаксис выглядит следующим образом: SHOW [GLOBAL | SESSION] STATUS [LIKE ‘pattern’ | WHERE expr] 1.

Перейдем к подробным описаниям показателей производительности.

MyISAM 2

Анализ показателей, которые будут рассмотрены ниже, актуален лишь в том случае, если вы используете MyISAM как низкоуровневую подсистему хранения данных. До версии MySQL 5.5 MyISAM использовалась по умолчанию.

Key_reads – количество физических операций чтения блоков ключей с диска;

Key_read_requests – операции чтения блоков ключей из кэша в оперативной памяти.
На большинстве ресурсов по оптимизации производительности MySQL рекомендуют обязательно отслеживать эти параметры вместе. Важно отношение Key_reads к Key_read_requests; если оно больше 99%, то все в порядке, если меньше 95%, то стоит проанализировать проблемы в производительности, возможно провести апгрейд оборудования или увеличить размер кэша.

Формула выглядит следующим образом:
Ratio(%)=(1-Key_reads/Key_read_requests)*100

В идеале объема выделенной для кэша памяти должно быть достаточно, чтобы разместить в ней все индексы 3.

При анализе отношения этих параметров необходимо иметь в виду также время сбора статистики, ведь если вы отслеживаете количество чтений записей с диска при только что перезагруженном сервере, то вы получите результат, который будет значительно ниже оптимальных значений. Некоторые авторы вообще утверждают что эти параметры отслеживать смысла большого нет 4.
Лично мое мнение – большое внимание придавать какому-либо одному параметру производительности не нужно, но принимать его в рассчет при общем анализе как минимум не будет лишним.

Key_writes – количество физических записей данных на диск;

Key_write_requests – количество обращений на запись данных в кэш.

Анализировать последние два параметра рекомендуется также вместе как отношение одного к другому. В идеале значение должно быть меньше 90%.

Ratio(%)=Key_writes/Key_write_requests*100

key_buffer_size – переменная, управляющая текущим размером кэша. Понять какой объем необходимо выставлять этой переменной можно из суммарного объема всех файлов с расширением .MYI, которые находятся в директории баз данных. Как это сделать применительно к определенным операционным системам в этой статье я рассматривать не планирую (при всем обилии информации в сети 5) Также я не буду затрагивать вопрос манипулирования размером кэша с помощью теплых и горячих очередей, что особо актуально для систем с 32-х разрядной архитектурой, поскольку использование в рабочей среде 32-х разрядных систем уже само по себе является проблемой, а для 64-х разрядных систем такой проблемы пока быть не должно.

InnoDB 6

Начиная с версии MySQL 5.6 движком по умолчанию стал InnoDB, параметры мониторинга которого будут рассмотрены ниже.

Innodb_buffer_pool_reads – количество операций чтения с диска, но применительно к другому движку СУБД – InnoDB;

Innodb_buffer_pool_read_requests – количество обращений к кэшу, также в контексте InnoDB.

В комплексе параметры имеют те же ограничения и формулу вычисления отношения (узнать сводную информацию можно также командой “SHOW ENGINE INNODB STATUS output”) 7: выше 99% – норма, ниже 95% – есть проблемы в производительности.

Ratio(%)=(1-Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests)*100

Для анализа использования кэша также не лишним будет обратиться к следующим параметрам:

Innodb_buffer_pool_pages_dirty – отображает количество “грязных” страниц – то есть которые были изменены, находясь в кэше, но эти изменения ещё не зафиксированы на диске;

Innodb_buffer_pool_pages_data – отображает сумму чистых и грязных страниц в кэше, а также индексных страниц;

Innodb_buffer_pool_pages_flushed – количество запросов на на сброс страниц из кэша на жесткий диск;

Innodb_buffer_pool_pages_free – количество свободных страниц;

Innodb_buffer_pool_pages_misc – страницы, используемые самой СУБД для различных нужд;

Innodb_buffer_pool_pages_total – общее количество страниц в памяти, образуется суммированием параметров pages_data, pages_free, pages_misc 8.

Из описанных выше пяти параметров особое внимание стоит уделить количеству свободных страниц (Innodb_buffer_pool_pages_free), т.к. оно может сказать об избытке или недостатке размера кэша 9. Однако если анализировать этот параметр в одиночку, то ничего внятного мы не узнаем, поскольку он представляется в абсолютном значении. Есть смысл связать этот параметр с общим количеством страниц в памяти и вычислить его процент. Зная, что объем страниц под нужды СУБД (pages_misc) обычно не превышает 10% 10, а количество свободных страниц (pages_free) должно быть минимальным, можно примерно определить границу, по превышении которой стоит задуматься об эффективности использования памяти. Это значение будет примерно в 10-15%,  при превышении 20-25% рекомендуется снижать объем кэша, поскольку количество свободных страниц становится значительным.

Rate(%)=(1-Innodb_buffer_pool_pages_data/Innodb_buffer_pool_pages_total) *100%

Задуматься о увеличении кэша нужно при падении значения свободных страниц ниже 5% (это исключительно мое личное мнение), либо отслеживать абсолютное значение и если оно равно 0, это говорит о сильной нехватке кэша. Формула будет выглядеть примерно так:

Rate(%)=Innodb_buffer_pool_pages_free/Innodb_buffer_pool_pages_total*100%

innodb_buffer_pool_size – отображает размер памяти, которая выделена для размещения данных и индексов. По идее чем это значение больше, тем лучше. Если речь идет исключительно о серверах баз данных без каких-либо других ролей, то рекомендуют выставлять его примерно в 70-80% от общего объёма оперативной памяти, но существуют и другие мнения 11. На совмещающих роли серверах будьте внимательны, надо точно понимать сколько оперативной памяти необходимо другим приложениям. К тому же на серверах с 32-х битной архитектурой есть некоторые ограничения 12. Рассчитать необходимый вам размер кэша InnoDB не представляется сложной задачей, в сети есть масса готовых примеров 13. Размер кэша вычисляется как произведение общего количества страниц на размер одной страницы (Innodb_buffer_pool_pages_total * Innodb_page_size).

Остальные параметры нас не интересуют (например, параметр innodb_additional_mem_pool_size вообще по умолчанию отключен 14). На этом обзор увеличения производительности за счет изменения параметров кэширования данных завершен.

Яндекс.Метрика