Оптимизируем MySQL Query Cache

Начиная с версии 4.0.1 в MySQL появилась функция кеширования запросов — «Query Cache«, которая открывает дополнительные возможности для задач по оптимизации баз данных.

Query Cache кеширует результаты запроса. Это значит, что кешированный запрос не выполняется вовсе. Более того, кеширование отрабатывает даже до разбора самого запроса. А это значит, что запрос должен быть точно таким же как в кеше, вплоть до байта.

Настройка Query Cache

За настройку кеширования отвечают системные переменные начинающиеся с ‘query_cache_‘.

Во-первых, убедимся, что кеширование включено. Переменная query_cache_type должна иметь значение ON(1) или DEMAND(2) и query_cache_size быть отличной от нуля.

При ‘query_cache_type = ON‘ кешируются все запросы, кроме содержащих хинт SQL_NO_CACHE и некоторых исключений.

При ‘query_cache_type = DEMAND‘ кешируются только запросы начинающиеся с SELECT SQL_CACHE.

query_cache_min_res_unit минимальный размер выделяемого блока памяти для хранения результатов кешированного запроса. MySQL не хранит кеш в одном большом куске памяти, вместо этого по требованию выделяются блоки с минимальным размером query_cache_min_res_unit(=4KB по умолчанию). Последний такой блок обрезается до размера данных, а оставшаяся память освобождается.

И если у вас много небольших запросов в кеше, то это может привести к фрагментации памяти из-за большого количества свободных блоков. А это, в свою очередь, вызывает удаление кешированных записей из-за недостатка памяти. В таком случае имеет смысл уменьшить значение query_cache_min_res_unit. Если большинство ваших запросов порождают большой результат, то увеличение этого параметра может повысить производительность.

Для мониторинга query cache используется SHOW STATUS:

Можно выделить несколько оценок производительности query cache.

Отношение кешированных запросов к их общему числу: Qcache_hits/(Qcache_hits+Com_select). Мы складываем Com_select и Qcache_hits, так как если результат запроса берется из кеша, то Com_select не инкрементируется.

Операция вставки в кеш тоже дает накладные расходы, посмотрим сколько кешированных запросов используется: Qcache_hits/Qcache_inserts.

Так как любое изменение таблиц, используемых в запросе, влечет за собой удаление записи из кеша, то имеем следующую оценку: (Com_insert+Com_update+Com_replace+Com_delete)/Qcache_hits

Исключения

Query Cache используется не всегда и не для всех типов запросов. Если размер результата запроса превышает query_cache_limit(=1MB по умолчанию), то он не кешируется. MySQL до версии 4.1.1 не поддерживает query cache в транзакциях.

Не кешируются:

• Запросы с SQL_NO_CACHE
• Подготовленные запросы (Prepared statements);
• Запросы которые являются подзапросами внешнего запроса;
• Запросы внутри хранимых процедур и функций;
• Запросы в которых используются функции:

  BENCHMARK(), CONNECTION_ID(), CONVERT_TZ(), CURDATE(), CURRENT_DATE(), CURRENT_TIME(), CURRENT_TIMESTAMP(), CURTIME(), DATABASE(), ENCRYPT() с одним аргументом, FOUND_ROWS(), GET_LOCK(), LAST_INSERT_ID(), LOAD_FILE(), MASTER_POS_WAIT(), NOW(), RAND(), RELEASE_LOCK(), SLEEP(), SYSDATE(), UNIX_TIMESTAMP() без аргументов, USER(), UUID();

• Запросы использующие хранимые функции, пользовательские переменные или ссылающиеся на таблицы в системных базах mysql или INFORMATION_SCHEMA;
• Запросы имеющие следующие формы:

  SELECT … IN SHARE MODE

  SELECT … FOR UPDATE

  SELECT … INTO OUTFILE …

  SELECT … INTO DUMPFILE …

  SELECT * FROM … WHERE autoincrement_col IS NULL

• Запросы с временными таблицами или вовсе не использующие таблицы;
• Запросы генерирующие предупреждения(warnings);