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

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

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

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

MyISAM ²

Анализ показателей, которые будут рассмотрены ниже, актуален лишь в том случае, если вы используете 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

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

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

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

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

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

Ratio(%)=Key_writes/Key_write_requests*100

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

InnoDB ⁶

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

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

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

В комплексе параметры имеют те же ограничения и формулу вычисления отношения (узнать сводную информацию можно также командой “SHOW ENGINE INNODB STATUS output”) ⁷: выше 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 ⁸.

Из описанных выше пяти параметров особое внимание стоит уделить количеству свободных страниц (Innodb_buffer_pool_pages_free), т.к. оно может сказать об избытке или недостатке размера кэша ⁹. Однако если анализировать этот параметр в одиночку, то ничего внятного мы не узнаем, поскольку он представляется в абсолютном значении. Есть смысл связать этот параметр с общим количеством страниц в памяти и вычислить его процент. Зная, что объем страниц под нужды СУБД (pages_misc) обычно не превышает 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% от общего объёма оперативной памяти, но существуют и другие мнения ¹¹. На совмещающих роли серверах будьте внимательны, надо точно понимать сколько оперативной памяти необходимо другим приложениям. К тому же на серверах с 32-х битной архитектурой есть некоторые ограничения ¹². Рассчитать необходимый вам размер кэша InnoDB не представляется сложной задачей, в сети есть масса готовых примеров ¹³. Размер кэша вычисляется как произведение общего количества страниц на размер одной страницы (Innodb_buffer_pool_pages_total * Innodb_page_size).

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