query_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– количество высвобождений памяти из-за наполненности кэша.
Теперь посчитаем % использования кэша:
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 и многие многие другие!
