Кэширование запросов в MYSQL

MySQL при работе с таблицами использует хорошо масштабируемые алгоритмы, так что MySQL может работать даже при малых объемах памяти. Естественно для лучшей производительности нужно больше оперативной памяти.

Для просмотра текущих настроек подключаемся к базе

#mysql -u root -p

mysql> SHOW VARIABLES LIKE 'query_cache_%';

+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| query_cache_limit            | 1048576  |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 16777216 |
| query_cache_type             | ON       |
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+
5 rows in set (0.00 sec)

Во-первых, убедимся, что кеширование включено. Переменная

• query_cache_type должна иметь значение ON (1) или DEMAND (2) и
• query_cache_limit – определяет максимальный размер результата который попадет в кэш
• query_cache_size быть отличной от нуля. При использовании DEMAND кешироваться будут только запросы, в которых есть директива SQL_CACHE;
• query_cache_min_res_unit минимальный размер выделяемого блока памяти для хранения результатов кешированного запроса. MySQL не хранит кеш в одном большом куске памяти, вместо этого по требованию выделяются блоки с минимальным размером query_cache_min_res_unit (=4KB по умолчанию). Последний такой блок обрезается до размера данных, а оставшаяся память освобождается.

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

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

Попробуем оценить эффект. Смотрим как меняются показания счетчиков попаданий в кэш (Qcahe_hits), количество запросов объявленных недействительными из-за нехватки памяти (Qcache_lowmem_prunes), общее количество запросов типа SELECT (а кэшируются только они). Делаем:

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

mysql> SHOW STATUS LIKE 'Qcache_%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 10       |
| Qcache_free_memory      | 16755496 |
| Qcache_hits             | 49812    |
| Qcache_inserts          | 103999   |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 180      |
| Qcache_queries_in_cache | 6        |
| Qcache_total_blocks     | 28       |
+-------------------------+----------+
8 rows in set (0.00 sec)

• Qcache_free_blocks показывает сколько свободных блоков есть в кеше (будет уменьшаться по мере увеличения скэшированных запросов).;
• Qcache_total_blocks — количество занятых блоков;
• Qcache_free_memory — показывает свободную «доступную» память для кэширования;
• Qcache_hits — количество запросов, результаты которых были взяты из кеша, без реального обращения к базе данных;
• Qcache_inserts — количество запросов, которые были добавлены в кеш;
• Qcache_lowmem_prunes — количество запросов, которые были удалены из кеша из-за нехватки памяти;
• Qcache_not_cached — количество запросов, которые не были записаны в кеш из-за использования функций работы со временем и т.п.;
• Qcache_queries_in_cache — количество запросов, которые находятся в кеше.

Можно просмотреть общее количество запросов SELECT:

mysql> show status like 'Com_select';

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 16719 |
+---------------+-------+
1 row in set (0.00 sec)

Оценивать эффективность кэша рекомендуют делением значения переменной Qcache_hits на Qcache_hits + Com_select, поскольку при обработке запроса увеличивается счётчик Qcache_hits (если запрос обработан из кэша) или Com_select (если запрос не кэширован). Такой способ предлагают в «Mysql оптимизация производительности» O’reilly

В сети есть другой способ – qcache_hit_ratio = qcache_hits / (qcache_hits + qcache_inserts + qcache_not_cached)

Если это значение > 0.8, то значит 80% ваших запросов попадают в кэш, это очень хороший показатель.
Если % попадания в кэш низкий, то необходимо увеличить значение query_cache_size.

Текущее значение можно посмотреть так:

SHOW VARIABLES LIKE 'query_cache_size';

Опять же возникает вопрос: как выбрать адекватное значение query_cache_size?
В этом поможет Qcache_lowmem_prunes. В этой переменной хранится число запросов, которые были убраны из кэша из-за необходимости кэширования новых запросов. Необходимо стремится к такому размеру кэша, при котором Qcache_lowmem_prunes будет лишь незначительно увеличиваться. Для этого, рекомендуется сравнить разницу значений Qcache_lowmem_prunes за час и кол-во запросов, поступивших на mysql за этот же час.

На практике, для расчета query_cache_size можно использовать одну из 2-х формул:
query_cache_size = (число запросов за 10 минут)*(средний объем ответа на запрос) * 1,2
или
query_cache_size = (объем трафика за 10 минут) * 1,2

Это позволит закэшировать запросы на 10 минут + дать дополнительные 20% памяти на фрагментацию кэша и дополнительный резерв кэширования
Подсчитать количество и средний объем ответа на запроса можно использую переменные Bytes_sent соответственно

И так значения query_cache_size мы увеличили, после чего стоит обратить внимание на значения Qcache_total_blocks, Qcache_free_blocks и Qcache_queries_in_cache. MySQL хранит кэш в блоках. На 1 запрос необходимо 2 блока: один для самого текста запроса, второй для результата.
Если рассмотреть таблицу со значения Qcache%
Общее количество блоков кэша Qcache_total_blocks – 28
Закешировано сейчас 6 запрос, а значит занят 6*2 = 12 блоков
свободно блоков Qcache_free_blocks – 10. Чем больше незадействованных Qcache_free_blocks, тем больше степень “фрагментации” кэша.
Если большинство запросов имеют небольшой объем результирующих данных, то стоит уменьшить минимальный размер блока кэша query_cache_min_res_unit, который по умолчанию равен 4 Кб.
Если же большинство запросов возвращают много данных – то стоит увеличить размер блока кэша.
Главное – это добиться минимального значения Qcache_free_blocks.

Если счетчик Qcache_not_cached велик, можно попробоавть увеличить переменную query_cache_limit – она позволит увеличить лимит и помещать в кэш результаты запросов которые «не помещаются».

За использование кеша запросов отвечают следующие конфигурационные переменные:

• query_cache_size — размер кеша запросов. query_cache_size = 0 отключает использование кеша;
• query_cache_limit — размер максимальной выборки, хранимой в кеше;
• query_cache_wlock_invalidate — определяет будут ли данные браться из кеша, если таблица, к которым они относятся заблокирована на чтение.

Чтобы включить кэширование запросов mysql достаточно добавить строки в my.cnf (Секция [mysqld]):

query_cache_size = 268435456
query_cache_type=1
query_cache_limit=1048576

И перезапустить сервис.

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

• Запросы с 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);

Дефрагментировать кэш можно командой:

mysql>flush query cache;

Очистить – командой:

mysql>reset query cache;

Самое важное
Никогда не работайте с настройками по умолчанию. Это приведет к неиспользованию большинства ресурсов сервера. Правильная настройка MySQL может повысить производительность базы данных в несколько раз. Это позволит не только ускорить работу приложения но и справиться с большой нагрузкой.

В качестве примера конфигурации вы также можете использовать примеры конфигурации MySQL (/usr/share/mysql/my-innodb-heavy-4G.cnf).