MySQL/MariaDB: тюнинг производительности #4: query_cache_size

Автор: | 01/16/2015
 

mariadb_logoСреди прочих данных tuning-primer сообщает что:

QUERY CACHE
Query cache is supported but not enabled
Perhaps you should set the query_cache_size

query_cache — это область памяти, выделяемая MySQL при старте, в которой хранятся запросы и их результаты типа SELECT. При получении нового запроса SELECTMySQL сначала проверяет нет ли такого же запроса в этом кеше и, если есть, возвращает значение из памяти, иначе — выполняет обращение к базе (и диску). Обратите внимание, что 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 отключён, что можно проверить так:

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 а 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
...

По теме:

http://www.techinfobest.com

http://haydenjames.io

http://goldapplesoftware.ca