Как грамотно настроить query_cache_size в MySQL и MariaDB

Настройка query_cache_size в базе данных MySQL и MariaDBquery_cache – это область памяти, выделяемая MySQL при старте, в которой хранятся запросы и их результаты типа SELECT. При получении нового запроса SELECT – MySQL сначала проверяет нет ли такого же запроса в этом кэше и, если есть, возвращает значение из памяти, иначе – выполняет обращение к базе (и диску). Обратите внимание, что select * FROM и SELECT * FROM для кэша MySQL являются двумя различными запросами. Когда важно включать и настраивать  query_cache_size в базе данных? Всегда, когда у вас операции выборки (SELECT) преобладают над операциями вставки и модификации данных (INSERT, UPDATE). Когда же идет интенсивная вставка и обновление данных, кэш запросов всегда обнуляется при каждой операции и в этом случаем он может даже навредить производительности базы данных (стать бутылочным горлышком). Поскольку MySQL и MariaDB активно используются в сайтостроении и популярных CMS (Joomla, WordPress, Drupal и т.д.), где операции выборки преобладают над вставкой (материал на сайт добавляем 1 раз, а выборка его из базы может происходить сколько угодно раз), то настройка query_cache_size носит обязательный характер в этой сфере! Методику настройки кэша запросов на оптимальную производительность, определяемую параметром query_cache_size, мы и рассмотрим в данной статье блога.

Кэш запросов поддерживается в MySQL версий 4.0 и выше (в 5-ой и 7-ой версии он существенно доработан и улучшен):

MariaDB [(none)]> SHOW VARIABLES LIKE 'have_query_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | YES   |
+------------------+-------+

По умолчанию query_cache отключён, что можно проверить так:

MariaDB [(none)]> SHOW VARIABLES LIKE 'query_cache_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_size | 0     |
+------------------+-------+

Qcache может иметь три состояния:

MariaDB [(none)]> SHOW VARIABLES LIKE 'query_cache_type';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_type | ON    |
+------------------+-------+
  • 0 (OFF) – Qcache отключён;
  • 1 (ON) – Qcache включен;
  • 2 (DEMAND) – только для запросов с явным указанием его кэширования (SELECT SQL_CACHE).

Что бы включить его – достаточно установить query_cache_size в любое положительное значение, отличное от нуля.

В настройке Qcache главную роль играют три параметра:

  • query_cache_size – размер памяти, выделяемый под кэш;
  • query_cache_type – см. выше;
  • query_cache_limit – максимальный размер возвращаемого результата запроса, который будет хранится в кэше.

Устанавливаем query_cache_size = 16 МБ, query_cache_type = 1 (или в ON), а query_cache_limit – оставляем по умолчанию, 1 МБ:

MariaDB [(none)]> SET GLOBAL query_cache_size=16*1024*1024;
MariaDB [(none)]> SET GLOBAL query_cache_type=1;
Query OK, 0 rows affected (0.00 sec)

Проверяем:

MariaDB [(none)]> SHOW VARIABLES LIKE '%query_cache%';
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| have_query_cache             | YES      |
| query_cache_limit            | 1048576  |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 16777216 |
| query_cache_strip_comments   | OFF      |
| query_cache_type             | ON       |
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+

Через какое-то время, хотя бы час, проверяем статус:

MariaDB [(none)]> SHOW STATUS LIKE 'qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 176      |
| Qcache_free_memory      | 13628632 |
| Qcache_hits             | 9935     |
| Qcache_inserts          | 4537     |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 2614     |
| Qcache_queries_in_cache | 392      |
| Qcache_total_blocks     | 1069     |
+-------------------------+----------+

В последней выборке нас интересуют:

  • Qcache_free_memory – доступное место в кэше;
  • Qcache_hits – количество запросов, отработанных из кэша;
  • Qcache_inserts – количество добавлений запросов в кэш;
  • Qcache_not_cached – количество запросов, не подлежащих кэшированию;
  • Qcache_lowmem_prunes – количество высвобождений памяти из-за наполненности кэша.

Настройка query_cache_size в MySQL и MariaDB на оптимальную производительность

Теперь посчитаем % использования кэша:

Qcache_free_memory * 100 / query_cache_size – дадут нам % свободного места в query_cache_size:

>>> 13694488.0 * 100 / 16777216.0
81.625509262084961

81.6% свободно.

Или наоборот – % занятого места:

((query_cache_size-Qcache_free_memory)/query_cache_size)*100

>>> ((16777216.0 - 13694488.0) / 16777216.0) * 100
18.374490737915039

Т.е. занято 18.37%. Смысла в увеличении пока нет.

Что бы убедиться в этом – подсчитаем ещё такое значение как “Query Cache Hit Rate” по формуле:

((Qcache_hits/(Qcache_hits+Qcache_inserts+Qcache_not_cached))*100)

Получаем такое значение:

>>> (9935.0 / (9935.0 + 4537.0 + 2614.0)) * 100
58.147020952826878

Т.е. – MySQL обращался к кэшу 17086 раз (Qcache_hits+Qcache_inserts+Qcache_not_cached), и 9935 запросов были выданы из кэша, следовательно Query Cache Hit Rate = 58%.

Это не самое хорошее значение, однако – пока оно выше 50% – query_cache_size можно не менять. Если значение менее 50% – можно увеличить размер кэша, а если же значение ниже 10-20% – то, возможно, Qcache лучше не использовать вообще.

Ещё два важных значения – это соотношение значений Qcache_hits/Qcache_inserts и Qcache_inserts/Qcache_lowmem_prunes.

Из примера выше – для Qcache_hits/Qcache_inserts получаем такое значение:

>>> 11386.0 / 5697.0
1.9985957521502544

Т.е. – в среднем каждый добавленный в Кэш результат был выдан клиентам 2 раза. Чем ближе значение к соотношению 1:1 (или даже меньше, напрмиер – 0.5:1) – тем меньше эффективность работы кэша.

Второе значение Qcache_inserts/Qcache_lowmem_prunes – из примера выше получить не выйдет (т.к. Qcache_lowmem_prunes = 0), поэтому – возьмём значение с другой базы другого сервера:

mysql> SHOW STATUS LIKE 'qcache%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
...
| Qcache_inserts          | 2773956 |
| Qcache_lowmem_prunes    | 1417292 |

Результат:

>>> 2773956.0 / 1417292.0
1.957222647132701

Т.е. – фактически, каждый второй запрос и результат, добавленные в кэш, были удалены.

Можно посчитать процентное соотношение:

>>> 1417292.0 * 100 / 2773956.0
51.092807528309748

Итого – процент удалений кэша равен 51%, тогда как более-менее оптимальным считается значение 10-20% удалений.

Эти же данные можно получить в выводе утилиты mysqlreport:

$ mysqlreport --user root --password password | less
...
__ Query Cache _________________________________________________________
Memory usage    3.48M of   8.00M  %Used:  43.46
Block Fragmnt   4.13%
Hits            1.35M     0.8/s
Inserts         2.96M     1.8/s
Insrt:Prune    1.95:1     0.9/s
Hit:Insert     0.46:1
...

Изложенная в данной статье инструкция позволит Вам настроить кэш запросов (query_cache_size) в MySQL и MariaDB на оптимальную производительность и заставит летать Ваши сайты Jommla, WordPress, Drupal и многие многие другие!

Вас заинтересует / Intresting for you:

MySqlTuner - настраиваем базу ...
MySqlTuner - настраиваем базу ... 2716 просмотров Konstantin Tue, 21 Nov 2017, 13:17:28
Профилирование запросов MySQL ...
Профилирование запросов MySQL ... 3557 просмотров Светлана Комарова Sat, 02 Feb 2019, 14:25:04
Настройка базы данных MySQL в ...
Настройка базы данных MySQL в ... 2970 просмотров Боба Sun, 07 Oct 2018, 06:09:32
Конкретные способы тестировани...
Конкретные способы тестировани... 1893 просмотров Ирина Светлова Mon, 14 Jan 2019, 14:06:50
Войдите чтобы комментировать

anders7777 аватар
anders7777 ответил в теме #9479 01 окт 2019 06:59

apv пишет: Много неоднозначной и порой противоречивой информации относительно настройки query_cache_size. Кто-то полагает, что его не нужно активировать. Как и выставлено по умолчанию.

Да, все слишком индивидуально. Мне часто помогает настройка (не нулевая) query_cache_size. Все зависит от специфики текущей задачи.
apv аватар
apv ответил в теме #8935 22 фев 2018 08:07
Много неоднозначной и порой противоречивой информации относительно настройки query_cache_size. Кто-то полагает, что его не нужно активировать. Как и выставлено по умолчанию.