Кэш запросов

Кэш запросов хранит результаты запросов SELECT, чтобы при получении идентичного запроса в будущем результаты можно было быстро вернуть.

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

Настройка кэша запросов

Если MariaDB не была специально скомпилирована без кэша запросов, кэш запросов всегда будет доступен, хотя и неактивен. Переменная сервера have_query_cache покажет, доступен ли кэш запросов.

SHOW VARIABLES LIKE 'have_query_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | YES   |
+------------------+-------+

Если это установлено в NO, вы не можете включить кэш запросов, пока не пересобираете или не переустановите версию MariaDB с доступным кэшем.

Чтобы увидеть, включен ли кэш, просмотрите переменную сервера query_cache_type. По умолчанию он включен в версиях MariaDB до 10.1.6, но отключен начиная с MariaDB 10.1.7 — при необходимости включите его, установив query_cache_type в 1.

Хотя он был включен в версиях до MariaDB 10.1.7, query_cache_size по умолчанию равен 0 КБ, что фактически отключает кэш запросов. Начиная с 10.1.7 размер кэша по умолчанию составляет 1 МБ. При необходимости установите размер кэша достаточно большим, например:

SET GLOBAL query_cache_size = 1000000;

Начиная с MariaDB 10.1.7, query_cache_type автоматически устанавливается в ВКЛ, если сервер запускается с query_cache_size установленным в ненулевое (и отличное от значения по умолчанию) значение.

См. Ограничение размера кэша запросов ниже для получения подробной информации.

Как работает кэш запросов

Когда кэш запросов включен и обрабатывается новый запрос SELECT, кэш запросов проверяется, чтобы узнать, есть ли этот запрос в кэше.

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

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

Запросы проверяются с учетом регистра, поэтому :

SELECT * FROM t

отличается от :

select * from t

Комментарии также учитываются и могут сделать запросы различными, поэтому :

/* retry */SELECT * FROM t

отличается от :

/* retry2 */SELECT * FROM t

См. переменную сервера query_cache_strip_comments для опции удаления комментариев перед поиском.

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

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

При использовании query_cache_type=ON, и запрос указывает SQL_NO_CACHE (без учета регистра), сервер не будет кэшировать запрос и не будет получать результаты из кэша запросов.

При использовании query_cache_type=DEMAND (после запроса функции MDEV-6631) и запрос указывает SQL_CACHE, сервер будет кэшировать запрос.

Важный момент из MDEV-6631 : переключение между query_cache_type=ON и query_cache_type=DEMAND может "выключить" кэш запросов старых запросов без строки SQL_CACHE, пока неясно, нужно ли включать еще одно значение query_cache_type (DEMAND_NO_PRUNE), или нет, чтобы разрешить использование старых запросов

Запросы, хранящиеся в кэше запросов

Если переменная сервера query_cache_type установлена в 1, или ON, все запросы, подходящие по размеру, будут храниться в кэше, если только они не содержат SQL_NO_CACHE клаузу или не являются запросами, для которых кэширование не имеет смысла, например, использование функции, возвращающей текущее время. Убедитесь, что SQL_NO_CACHE заставит сервер не использовать блокировки кэша запросов.

Если в запросе присутствуют какие-либо из следующих функций, он не будет кэшироваться. Запросы с этими функциями иногда называют «недетерминированными» — не путайте это использование термина с другими контекстами.

Запрос также не будет добавлен в кэш, если:

• Он имеет вид:
  • SELECT SQL_NO_CACHE ...
  • SELECT ... INTO OUTFILE ...
  • SELECT ... INTO DUMPFILE ...
  • SELECT ... FOR UPDATE
  • SELECT * FROM ... WHERE autoincrement_column IS NULL
  • SELECT ... LOCK IN SHARE MODE
• Использует временную таблицу
• Не использует таблицы вообще
• Генерирует предупреждение
• Пользователь имеет права на уровне столбца для любой таблицы в запросе
• Обращается к таблице из базы данных INFORMATION_SCHEMA, mysql или performance_schema
• Использует переменные пользователя или локальные переменные
• Использует хранимые функции
• Использует пользовательские функции
• Находится внутри транзакции с уровнем изоляции SERIALIZABLE
• Обращается к таблице внутри транзакции после того, как та же таблица выполнила отмену кэша запросов с помощью INSERT, UPDATE или DELETE

Сам запрос также может указать, что его не нужно хранить в кэше, используя атрибут SQL_NO_CACHE. Управление на уровне запроса — эффективный способ более оптимального использования кэша.

Также можно указать, что никакие запросы не должны храниться в кэше, если запрос этого не требует. Для этого переменная сервера query_cache_type должна быть установлена в 2, или DEMAND. Затем в кэш будут кэшироваться только запросы с атрибутом SQL_CACHE.

Ограничение размера кэша запросов

Существует два основных способа ограничения размера кэша запросов. Во-первых, общий размер в байтах определяется переменной сервера query_cache_size. Около 40 КБ требуется для различных структур кэша запросов.

Размер кэша запросов выделяется блоками по 1024 байта, поэтому его следует устанавливать кратным 1024.

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

Если режим строгий режим включен, установка размера кэша запросов в некорректное значение приведет к ошибке. В противном случае он будет установлен на ближайшее допустимое значение, и будет выведено предупреждение.

SHOW VARIABLES LIKE 'query_cache_size';
+------------------+----------+
| Variable_name    | Value    |
+------------------+----------+
| query_cache_size | 67108864 |
+------------------+----------+

SET GLOBAL query_cache_size = 8000000;
Query OK, 0 rows affected, 1 warning (0.03 sec)

SHOW VARIABLES LIKE 'query_cache_size';
+------------------+---------+
| Variable_name    | Value   |
+------------------+---------+
| query_cache_size | 7999488 |
+------------------+---------+

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

Второй способ ограничения кэша — установка максимального размера для каждого набора результатов запроса. Это предотвращает ситуацию, когда один запрос с огромным набором результатов занимает большую часть доступной памяти и выталкивает большое количество меньших запросов из кэша. Это определяется переменной сервера query_cache_limit.

Если вы попытаетесь установить слишком маленький размер кэша запросов (величина зависит от архитектуры), изменение размера не удастся, и кэш запросов будет установлен в ноль, например :

SET GLOBAL query_cache_size=40000;
Query OK, 0 rows affected, 2 warnings (0.03 sec)

SHOW WARNINGS;
+---------+------+-----------------------------------------------------------------+
| Level   | Code | Message                                                         |
+---------+------+-----------------------------------------------------------------+
| Warning | 1292 | Truncated incorrect query_cache_size value: '40000'             |
| Warning | 1282 | Query cache failed to set size 39936; new query cache size is 0 |
+---------+------+-----------------------------------------------------------------+

Просмотр кэша запросов

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

SHOW STATUS LIKE 'Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1158     |
| Qcache_free_memory      | 3760784  |
| Qcache_hits             | 31943398 |
| Qcache_inserts          | 42998029 |
| Qcache_lowmem_prunes    | 34695322 |
| Qcache_not_cached       | 652482   |
| Qcache_queries_in_cache | 4628     |
| Qcache_total_blocks     | 11123    |
+-------------------------+----------+

Qcache_inserts содержит количество запросов, добавленных в кэш запросов, Qcache_hits содержит количество запросов, которые использовали кэш запросов, а Qcache_lowmem_prunes содержит количество запросов, удаленных из кэша из-за нехватки памяти.

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

Обратите внимание, что до MariaDB 5.5 запросы, возвращаемые из кэша запросов, не увеличивали переменную состояния Com_select, поэтому для нахождения общего количества выполненных на сервере валидных запросов нужно добавить Com_select к Qcache_hits. Начиная с MariaDB 5.5, результаты, возвращаемые кэшем запросов, учитываются при подсчёте Com_select\xa0(см. MDEV-4981).

Плагин QUERY_CACHE_INFO создаёт таблицу QUERY_CACHE_INFO в схеме INFORMATION_SCHEMA, что позволяет вам изучить содержимое кэша запросов.

Дробление кэша запросов

Кэш запросов использует блоки переменной длины и со временем может стать фрагментированным. Высокое значение Qcache_free_blocks относительно Qcache_total_blocks может указывать на фрагментацию. FLUSH QUERY CACHE дефрагментирует кэш запросов без удаления каких-либо запросов:

FLUSH QUERY CACHE;

После этого останется только один свободный блок:

SHOW STATUS LIKE 'Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 6101576  |
| Qcache_hits             | 31981126 |
| Qcache_inserts          | 43002404 |
| Qcache_lowmem_prunes    | 34696486 |
| Qcache_not_cached       | 655607   |
| Qcache_queries_in_cache | 4197     |
| Qcache_total_blocks     | 8833     |
+-------------------------+----------+

Очистка и отключение кэша запросов

Чтобы очистить все результаты из кэша запросов, используйте RESET QUERY CACHE. FLUSH TABLES произведёт тот же эффект.

Установка значения query_cache_type или query_cache_size в 0 отключит кэш запросов, но для освобождения наибольшего количества ресурсов установите оба значения в 0 при необходимости отключения кэширования.

Ограничения

• Для использования OQGRAPH необходимо отключить кэш запросов.
• Кэш запросов не используется движком хранения Spider (и другими).
• Кэш запросов также необходимо отключить для кластеров MariaDB Galera версии до «5.5.40-galera», «10.0.14-galera» и «10.1.2».

LOCK TABLES и кэш запросов

Кэш запросов может использоваться при наличии блокировки записи таблиц (что может показаться непонятным, так как блокировки записи должны предотвращать чтение таблиц). Это поведение можно изменить, установив системную переменную query_cache_wlock_invalidate в значение ON, в этом случае каждая блокировка записи аннулирует кэш запросов таблицы. Установка значения OFF, по умолчанию, означает, что кешированные запросы могут возвращаться даже при удержании блокировки таблицы. Например:

1> SELECT * FROM T1
+---+
| a |
+---+
| 1 |
+---+
-- Here the query is cached

-- From another connection execute:
2> LOCK TABLES T1 WRITE;

-- Expected result with: query_cache_wlock_invalidate = OFF
1> SELECT * FROM T1
+---+
| a |
+---+
| 1 |
+---+
-- read from query cache


-- Expected result with: query_cache_wlock_invalidate = ON
1> SELECT * FROM T1
-- Waiting Table Write Lock

Транзакции и кэш запросов

Кэш запросов обрабатывает транзакции. Внутренне флаг (FLAGS_IN_TRANS) устанавливается в 0, когда запрос был выполнен вне транзакции, и в 1, когда запрос был выполнен внутри транзакции (BEGIN / COMMIT / ROLLBACK). Этот флаг является частью «хэша кэша запросов», другими словами, один запрос внутри транзакции отличается от запроса вне транзакции.

Запросы, изменяющие строки (INSERT / UPDATE / DELETE / TRUNCATE) внутри транзакции, аннулируют все запросы к таблице и отключают кэш запросов для изменённой таблицы. Транзакции, которые не завершаются командами COMMIT/ROLLBACK, проверяют, что даже без COMMIT/ROLLBACK, кэш запросов отключен для обеспечения блокировки на уровне строк и уровня согласованности.

Примеры:

SELECT * FROM T1 <first insert to query cache, using FLAGS_IN_TRANS=0>
+---+
| a |
+---+
| 1 |
+---+

BEGIN;
SELECT * FROM T1 <first insert to query cache, using FLAGS_IN_TRANS=1>
+---+
| a |
+---+
| 1 |
+---+

SELECT * FROM T1 <result from query cache, using FLAGS_IN_TRANS=1>
+---+
| a |
+---+
| 1 |
+---+

INSERT INTO T1 VALUES(2);  <invalidate queries from table T1 and disable query cache to table T1>

SELECT * FROM T1 <don't use query cache, a normal query from innodb table>
+---+
| a |
+---+
| 1 |
| 2 |
+---+

SELECT * FROM T1 <don't use query cache, a normal query from innodb table>
+---+
| a |
+---+
| 1 |
| 2 |
+---+

COMMIT;  <query cache is now turned on to T1 table>

SELECT * FROM T1 <first insert to query cache, using FLAGS_IN_TRANS=0>
+---+
| a |
+---+
| 1 |
+---+

SELECT * FROM T1 <result from query cache, using FLAGS_IN_TRANS=0>
+---+
| a |
+---+
| 1 |
+---+

Внутренняя структура кэша запросов

Внутри каждый флаг, который может изменить результат при использовании одного и того же запроса, — это другой запрос. Например, использование набора символов latin1 и использование набора символов utf8 с одним и тем же запросом рассматриваются кэшем запросов как разные запросы.

Некоторые поля, которые различают запросы (из внутренней структуры «Query_cache_query_flags»):

• запрос (строка)
• название текущей схемы базы данных (строка)
• флаг длинного клиента (0/1)
• протокол клиента 4.1 (0/1)
• тип протокола (внутреннее значение)
• существуют ли дополнительные результаты (флаг протокола)
• внутри транзакции (внутри транзакции или нет)
• автоподтверждение (autocommit системная переменная сессии)
• pkt_nr (флаг протокола)
• кодировка символов клиента (character_set_client системная переменная сессии)
• кодировка символов результатов (character_set_results системная переменная сессии)
• кодировка соединения (collation_connection системная переменная сессии)
• предел (sql_select_limit системная переменная сессии)
• часовой пояс (time_zone системная переменная сессии)
• sql_mode (sql_mode системная переменная сессии)
• max_sort_length (max_sort_length системная переменная сессии)
• group_concat_max_len (group_concat_max_len системная переменная сессии)
• default_week_format (default_week_format системная переменная сессии)
• div_precision_increment (div_precision_increment системная переменная сессии)
• lc_time_names (lc_time_names системная переменная сессии)

Дополнительную информацию можно найти, просмотрев исходный код (MariaDB 10.1):

• https://github.com/MariaDB/server/blob/10.1/sql/sql_cache.cc
• https://github.com/MariaDB/server/blob/10.1/sql/sql_cache.h

Таймаут и конкуренция мьютексов

При поиске запроса в кэше запросов функция try_lock ожидает с таймаутом 50 мс. Если блокировка не удаётся, запрос не выполняется через кэш запросов. Этот таймаут жёстко закодирован (MDEV-6766 включены две переменные для настройки этого таймаута).

Из sql_cache.cc, функция «try_lock» использует TIMEOUT:

        struct timespec waittime;
        set_timespec_nsec(waittime,(ulong)(50000000L));  /* Wait for 50 msec */
        int res= mysql_cond_timedwait(&COND_cache_status_changed,
                                      &structure_guard_mutex, &waittime);
        if (res == ETIMEDOUT)
          break;

При добавлении запроса в кэш запросов или прерывании добавления запроса в кэш запросов (например, с помощью команды KILL), функция try_lock ожидает, пока кэш запросов не вернёт результат; в этом случае таймаут не используется.

Когда два процесса выполняют один и тот же запрос, только последний процесс сохраняет результат запроса. Все остальные процессы увеличивают переменную состояния Qcache_not_cached.

SQL_NO_CACHE и SQL_CACHE

Для кэша запросов существуют два аспекта: размещение запроса в кэше и извлечение его из кэша.

1. Добавление запроса в кэш запросов. Это выполняется автоматически для кэшируемых запросов (см. (Запросы, хранящиеся в кэше запросов) когда системная переменная query_cache_type установлена в 1, или ON и запрос не содержит предложения SQL_NO_CACHE, или когда системная переменная query_cache_type установлена в 2, или DEMAND, и запрос содержит предложение SQL_CACHE.
2. Извлечение запроса из кэша. Это выполняется после получения запроса сервером и перед обработкой запроса парсером. В этом случае следует учесть один момент:

При использовании SQL_NO_CACHE, оно должно следовать за первым подсказкой SELECT, например:

SELECT SQL_NO_CACHE .... FROM (SELECT SQL_CACHE ...) AS temp_table 

вместо

SELECT SQL_CACHE .... FROM (SELECT SQL_NO_CACHE ...) AS temp_table 

Второй запрос будет проверен. Кэш запросов проверяет существование SQL_NO_CACHE/SQL_CACHE только после первого SELECT. (Дополнительная информация в MDEV-6631)