MySQL/MariaDB: тюнинг производительности (часть 4)

query_cache – это часть памяти, что выделяется MySQL во время запуска. В данной области осуществляется хранение запросов и их результаты типа SELECT.
Получая новый запрос, MySQL изначально проверяет его на уникальность. Если запрос не уникален – находит ответ в памяти, если же наоборот – осуществляет обращение к базе. Стоит также сделать акцент на следующем: select * FROM и SELECT * FROM для MySQL — различные запросы.
Запросы управляются в MySQL версий > 4.0:

MariaDB [(none)]> SHOW VARIABLES LIKE 'have_query_cache';
+------------------+-------+
| 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

 

Comments

comments