MySQL. Увеличение производительности. Часть 2 — Кэширование запросов

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

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

Принцип работы

В отличие от других СУБД, MySQL кэширует не планы запросов, а сам текст запросов и результаты их выполнения, что четко прописано в официальном руководстве:

The query cache stores the text of a SELECT statement together with the corresponding result that was sent to the client. If an identical statement is received later, the server retrieves the results from the query cache rather than parsing and executing the statement again. The query cache is shared among sessions, so a result set generated by one client can be sent in response to the same query issued by another client.

Сам запрос и результат его выполнения хранится в виде хэша и это накладывает некоторые ограничения использования, а именно: идентичные по структуре запросы должны быть одинаковыми по написанию “байт в байт”, иначе это будут разные запросы (запросы “SELECT * FROM tbl_name” и “Select * from tbl_name” являются разными – пример из руководства). В кэш могут попасть только запросы, начинающиеся на “SEL”. До версии MySQL 5.0 (в последующих версиях баг исправлен) запросы, начинающиеся на пробел или комментарий, в кэш попасть могут, но извлечь их не получится. Описанные выше особенности, главным образом, стоит учитывать разработчикам баз данных, иначе использование кэширования запросов уже будет неэффективным.

При включении кэша, в памяти выделяется блок данных, равный по размеру значению переменной query_cache_size ¹ (нулевой размер означает, что кэширование отключено). Размер переменной рекомендуется определять экспериментальным путем, но не менее 40КВ для записи служебной информации. На практике это означает, что с увеличением размера кэша также увеличиваются накладные расходы на его обслуживание и при переходе определенного порога, преимущества кэширования могут оказаться ниже, чем появившиеся недостатки. Обычно размер кэша в несколько сотен мегабайт уже будет являться избыточным ².

Данные в кэше хранятся блоками. При выполнении запроса, сервер последовательно выделяет блоки данных для записи результата, размер блока определяется переменной query_cache_min_res_unit ³.

Важным моментом является то, что в кэш будут попадать далеко не все запросы, даже без каких-либо ограничений со стороны типа кэша (см. ниже). Ограничения зависят от использования определенных функций, UDF, локальных переменных, выборок из системных или временных таблиц и т.п., подробнее можно прочитать в официальном руководстве ⁴.

Один и тот же запрос, данные из которого взяты напрямую из базы данных или выбраны из кэша, должен возвращать одинаковый результат. Поэтому если в таблице произошли любые изменения, то связанные с ней запросы должны быть непременно удалены из кэша, что и происходит при выполнении с таблицей одной или нескольких операций из: INSERT, UPDATE, DELETE, TRUNCATE TABLE, ALTER TABLE, DROP TABLE, или DROP DATABASE.

На этом о принципах функционирования все, переходим к следующей главе.

Кэшируйте правильно

Как было сказано выше, далеко не во всех ситуациях использование кэширования запросов будет давать выигрыш в производительности. Чтобы однозначно ответить на вопрос в каких случаях использовать кэш, надо понимать специфику работы именного вашего сервера баз данных. Запросы из таблиц с частым обновлением данных необходимо будет обновлять в кэше при каждом изменении хотя бы одной записи в таблице, что не имеет смысла в принципе и в конечном итоге обернется для вас снижением производительности. Однако практически в каждой базе данных, при условии что спроектирована она должным образом, найдутся таблицы, данные в которых не изменяются или изменяются достаточно редко. Таким образом, может возникнуть вполне логичная мысль кэшировать только запросы из определенных таблиц, число операций DML ⁵ с которыми на порядки ниже, чем DQL.

Благо в MySQL есть возможность избирательного кэширования и определяется она значением переменной query_cache_type ⁶, которая может принимать три значения:

0 или OFF	Не кэшировать или не извлекать результаты из кэша запросов. Стоит отметить, что эта опция не отключает буфер кэша в оперативной памяти. Чтобы его отключить, вы должны установить переменную размера кэша в значение 0.
1 или ON	Кэшировать все результаты запросов, которые являются кэшируемыми, кроме запросов, начинающихся на SELECT SQL_NO_CACHE.
2 или DEMAND	Кэшировать только результаты запросов, которые, во-первых, поддаются кэшированию, а во-вторых, начинаются на SELECT SQL_CACHE.

Если есть необходимость кэшировать только определенные запросы, рекомендуется в них напрямую указать необходимость кэширования с помощью инструкции SELECT SQL_CACHE, а также выставить переменную типа кэша в значение 2 или DEMAND.

Важным моментом в использовании кэша является максимальное ограничение размера результата запроса в 1МВ (значение по умолчанию). Запросы, результат которых больше этого значения, не попадут в кэш. Поэтому если у вас такие запросы могут быть, рекомендуется увеличить значение переменной query_cache_limit ⁷ до необходимого.

Основные показатели производительности кэша

Помочь определить, когда увеличение размера кэша будет полезным, может анализ данных индикатора Qcache_lowmem_prunes ⁸, который отображает количество удаленных из кэша запросов по причине нехватки памяти. Анализировать этот индикатор нужно совместно с Qcache_free_memory ⁹. Если происходит удаление запросов из памяти, в то время когда свободного места предостаточно, это может говорить о сильной фрагментированности памяти – невозможно записать запрос и результат его выполнения целиком, поскольку нет непрерывного свободного участка необходимой длины, а лишь мелкие свободные фрагменты. Это может наблюдаться когда есть большое количество запросов с небольшим по объему результатом. В этом случае наименее используемые кэшированные запросы постепенно вытесняются из кэша ¹⁰.

Таким образом, чтобы получить исчерпывающую информацию о фрагментированности кэша, необходимо обратить внимание на описанные ниже комплексные показатели.

Процент свободной памяти:

Free Cache Memory (%)=Qcache_free_memory/query_cache_size*100

Процент свободных блоков:

Free Cache Blocks (%)=Qcache_free_blocks ¹¹/Qcache_total_blocks ¹²*100

Процент вытесненных из кэша запросов по отношению к общему количеству запросов к кэшу:

Prunes and Total Queries Ratio (%) ¹³=Qcache_lowmem_prunes/Queries ¹⁴*100

Анализировать данные необходимо следующим образом: оптимальный размер свободной памяти, исключительно на мой взгляд, должен быть не менее 30% от общего объема. При этом процент свободных блоков минимально может опускаться к 50% от общего количество блоков кэша ¹⁵. Минимальное абсолютное значение свободных блоков равно 1 (сразу после выполнения дефрагментации или очистки кэша – FLUSH QUERY CACHE ¹⁶ и RESET QUERY CACHE соответственно).

Также для оценки эффективности кэширования на некоторых ресурсах рекомендуют использовать формулу:

Select and Qcache Ratio (%) ¹⁷=Qcache_hits ¹⁸/(Qcache_hits+Com_select)*100

Однако на мой взгляд эта характеристика является немного абстрактной и применимой далеко не во всех ситуациях (почему, постараюсь ответить ниже). Так, хоть и формула отображает процентный вес обращений к кэшированным запросам (Qcache_hits) к обращениям к диску (Com_select), но ответить на вопрос “что нужно сделать для оптимизации” она не может.

Окончательную точку в размышлениях “все же сколько запросов из общего числа потенциально могут попасть в кэш?” может поставить анализ отношения операций INSERT/UPDATE/DELETE/REPLACE к общему количеству запросов в кэше:

Cache Queries and Modification Statements(%) ¹⁹=(Com_insert+Com_delete+Com_update+Com_replace) ²⁰/Qcache_hits

Какого-то оптимального значения у этого параметра не существует, но небольшое количество запросов в кэше по отношению к другим операциям может натолкнуть на размышления об увеличении объема кэша или о целесообразности его использования применительно к вашему окружению.

Каких-то других важных индикаторов производительности и их производных я больше не вижу. Вы можете сколько угодно экспериментировать над комбинациями различных индикаторов, пробовать менять значения переменных и тому подобное, но основная задача отслеживания показателей производительности кэша – ответ на вопросы: эффективно ли кэширование в данный момент и в данном окружении; если нет, то можно ли его сделать эффективным; если да, то можно ли его ещё улучшить? В некоторых ситуациях использование кэша запросов, наряду с грамотно определенными параметрами, может дать серьезный прирост производительности – кто его знает, может быть у вас есть огромная база данных только для чтения, большинство необходимых запросов из которой (и их результатов) можно вполне поместить в оперативную память? Однако в большинстве случаев прирост производительности может оказаться минимальным, поэтому не стоит сильно заострять внимание только на кэшировании выборок, есть также масса других способов повышения производительности, о которых я расскажу в других статьях.