query_cache – это часть памяти, что выделяется MySQL во время запуска. В данной области осуществляется хранение запросов и их результаты типа SELECT.
Получая новый запрос, MySQL изначально проверяет его на уникальность. Если запрос не уникален – находит ответ в памяти, если же наоборот – осуществляет обращение к базе. Стоит также сделать акцент на следующем: select * FROM и SELECT * FROM для MySQL — различные запросы.
Запросы управляются в MySQL версий > 4.0:
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| have_query_cache | YES |
+------------------+-------+
Обратите внимание, изначально query_cache не активен. Для проверки необходимо воспользоваться комбинацией:
1 | MariaDB [(none)]> SHOW VARIABLES LIKE ‘query_cache_size’; |
2 | +——————+——-+ |
3 | | Variable_name | Value | |
4 | +——————+——-+ |
5 | | query_cache_size | 0 | |
6 | +——————+——-+ |
Qcache может находиться в трёх режимах:
1 | MariaDB [(none)]> SHOW VARIABLES LIKE ‘query_cache_type’; |
2 | +——————+——-+ |
3 | | Variable_name | Value | |
4 | +——————+——-+ |
5 | | query_cache_type | ON | |
6 | +——————+——-+ |
- (OFF) – Qcache выключен;
- (ON) – Qcache включен;
- (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 а query_cache_limit – остаётся неизменным, 1 МБ:
1 | MariaDB [(none)]> SET GLOBAL query_cache_size=16*1024*1024; |
1 | MariaDB [(none)]> SET GLOBAL query_cache_type=1; |
2 | Query OK, 0 rows affected (0.00 sec) |
Выполняем проверку:
01 | MariaDB [(none)]> SHOW VARIABLES LIKE ‘%query_cache%’; |
02 | +——————————+———-+ |
03 | | Variable_name | Value | |
04 | +——————————+———-+ |
05 | | have_query_cache | YES | |
06 | | query_cache_limit | 1048576 | |
07 | | query_cache_min_res_unit | 4096 | |
08 | | query_cache_size | 16777216 | |
09 | | query_cache_strip_comments | OFF | |
10 | | query_cache_type | ON | |
11 | | query_cache_wlock_invalidate | OFF | |
12 | +——————————+———-+ |
Спустя ориентировочно пару часов необходимо проверить текущий статус:
01 | MariaDB [(none)]> SHOW STATUS LIKE ‘qcache%’; |
02 | +————————-+———-+ |
03 | | Variable_name | Value | |
04 | +————————-+———-+ |
05 | | Qcache_free_blocks | 176 | |
06 | | Qcache_free_memory | 13628632 | |
07 | | Qcache_hits | 9935 | |
08 | | Qcache_inserts | 4537 | |
09 | | Qcache_lowmem_prunes | 0 | |
10 | | Qcache_not_cached | 2614 | |
11 | | Qcache_queries_in_cache | 392 | |
12 | | Qcache_total_blocks | 1069 | |
13 | +————————-+———-+ |
В выведенной информации интерес представляют следующие данные:
- Qcache_free_memory – незанятая память;
- Qcache_hits – число обработанных из памяти запросов;
- Qcache_inserts – число запросов, направленных в память;
- Qcache_not_cached – число запросов, что не подвергаются сохранению;
- Qcache_lowmem_prunes – объём освобождений памяти, в связи с наполненностью кэша.
Далее необходимо произвести процентный расчёт эксплуатации памяти: Qcache_free_memory * 100 / query_cache_size – предоставляют процент незанятой памяти в query_cache_size:
1 | >>> 13694488.0 * 100 / 16777216.0 |
2 | 81.625509262084961 |
Данный результат указывает на то, что 81.6% является свободным.
Также получаем информацию о занятом объёме памяти:
((query_cache_size-Qcache_free_memory)/query_cache_size)*100
1 | >>> ((16777216.0 — 13694488.0) / 16777216.0) * 100 |
2 | 18.374490737915039 |
Согласно данным, он составляет 18.37%. Таким образом, делаем вывод, что на данном этапе осуществлять действия по увеличению памяти не стоит.
Для того, чтобы удостовериться в полученных расчётах, просчитаем “Query Cache Hit Rate” используя формулу:
((Qcache_hits/(Qcache_hits+Qcache_inserts+Qcache_not_cached))*100)
В результате получено данное значение:
1 | >>> (9935.0 / (9935.0 + 4537.0 + 2614.0)) * 100 |
2 | 58.147020952826878 |
Здесь мы видим, что MySQL делала запрос в память 17086 раз и 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 видим следующее значение:
1 | >>> 11386.0 / 5697.0 |
2 | 1.9985957521502544 |
Данные указывают на то, что практически все результаты, находящиеся в кеше были выданы повторно. Из этого можно сделать вывод, что с приближением показателя к отношению 1:1, работа кеша становится малоэффективной.
Рассмотрим следующий немаловажный показатель Qcache_inserts/Qcache_lowmem_prunes
1 | mysql> SHOW STATUS LIKE ‘qcache%’; |
2 | +————————-+———+ |
3 | | Variable_name | Value | |
4 | +————————-+———+ |
5 | … |
6 | | Qcache_inserts | 2773956 | |
7 | | Qcache_lowmem_prunes | 1417292 | |
…
В результате получаем такие числовые характеристики:
1 | >>> 2773956.0 / 1417292.0 |
2 | 1.957222647132701 |
Вышеуказанные цифры говорят о том, что по факту, каждый второй запрос и результат, что добавлялись в память – удалены.
Для того, чтобы подсчитать процентное соотношение, нам потребуется:
1 | >>> 1417292.0 * 100 / 2773956.0 |
2 | 51.092807528309748 |
Итак – доля удалений кеша составляет 51%, в то время, как рациональным является показатель 10-20% удалений.
Равнозначные показатели можно получить при условии обработки данных утилитой mysqlreport:
01 | $ mysqlreport —user root —password password | less |
02 | … |
03 | __ Query Cache _________________________________________________________ |
04 | Memory usage 3.48M of 8.00M %Used: 43.46 |
05 | Block Fragmnt 4.13% |
06 | Hits 1.35M 0.8/s |
07 | Inserts 2.96M 1.8/s |
08 | Insrt:Prune 1.95:1 0.9/s |
09 | Hit:Insert 0.46:1 |
10 | … |