Подробности: Категория: Базы данных; Просмотров: 364

Не все базы данных по-умолчанию настроены правильно и надо знать как происходит...

Кэширование запросов MySQL (настройки)

(обратите внимание: кэш запросов устарел с версии MySQL 5.7.20 и удален в MySQL 8.0)

Для начала нужно авторизоваться в базе данных через "mysql -u root -p" и далее выполнять запросы или сразу в phpmyadmin вбить sql запрос:

SHOW VARIABLES LIKE 'query_cache_%';

Который покажет что-то такое:

query_cache_limit 1048576
query_cache_min_res_unit 4096
query_cache_size 67108864
query_cache_strip_comments OFF
query_cache_type ON
query_cache_wlock_invalidate OFF

Эти данные не меняются динамически, они просто устанавливаются и через 5 часов тут будут такие же данные.

где:

1) query_cache_limit – максимальный размер данных (результаты запросов) который попадет в кэш

2) query_cache_min_res_unit минимальный размер выделяемого единичного блока памяти для хранения результатов кешированного запроса.

MySQL не хранит кеш в одном большом куске памяти, вместо этого по требованию выделяются блоки с минимальным размером query_cache_min_res_unit (=4KB по умолчанию , то есть 4096 байт как видно выше). Последний такой блок обрезается до размера данных, а оставшаяся память освобождается.

3) query_cache_type должна иметь значение ON (1) или DEMAND (2) и выключить кэширование OFF (0).

Если установлено значение 0, кэш запросов отключается (хотя буфер из байтов query_cache_size по-прежнему выделяется). Если задано значение 1 - all, запросы SELECT будут кэшироваться, если не указан SQL_NO_CACHE. Если установлено значение 2(или DEMAND), будут кэшироваться только запросы с предложением SQL CACHE. Обратите внимание, что если сервер запускается с отключенным кэшем запросов, он не может быть включен во время выполнения.

Предупреждение: Начиная с MariaDB 10.1.7, для параметра query_cache_type автоматически устанавливается значение ON, если сервер запускается с ненулевым (и отличным от значения по умолчанию) значением query_cache_size. Это произойдет, даже если для query_cache_type явно задано значение OFF в конфигурации.

4) query_cache_size - размер в байтах, доступных для кэша запросов. Для структур кэша запросов требуется около 40 КБ, поэтому установка размера ниже этого приведет к появлению предупреждения. 0, значение по умолчанию до MariaDB 10.1.7, эффективно отключает кэш запросов.

На хабре:

128M ограничение. Не
рекомендуется ставить выше 256M , т.к это может привести к блокировке.

Так как у нас большеInnoDB таблиц, то зануляем cache _ size .
С версии MySQL 5.6 query_cache_size отключен, а с версии 8.0 удален

кто знает напишите, что там по этому поводу? я позже проверю...

Кзш в Mysql образован таким образом, что, результаты запросов сохраняются В КЭШЕ под определенным хэшом, и если хэш совпадает, то не будет обращение к базе данных и будет выведен хэшированный результат - и количество таких попаданий в хэшированный результат называется (Qcahe_hits) - его мы увидим в выводе ниже.

Выполним следующий sql запрос:

SHOW STATUS LIKE 'Qcache%';

В отличие, от предыдущего запроса, тут все меняется каждую секунду практики (выделяются блоки, заполняются и тд)

Здесь мы увидим что-то такое:

Qcache_free_blocks   629
Qcache_free_memory 45027528
Qcache_hits 4145
Qcache_inserts   20304
Qcache_lowmem_prunes   0
Qcache_not_cached 339
Qcache_queries_in_cache 7487
Qcache_total_blocks   15748

где:

1) Qcache_free_blocks показывает сколько свободных блоков есть в кеше (уменьшается по мере увеличения скэшированных запросов).;

2) Qcache_free_memory — показывает свободную «доступную» память для кэширования;

3) Qcache_hits — количество запросов, результаты которых были взяты из кеша, без обращения к базе данных, то есть нашлись по хэшу - зачем снова мучить базу, если результаты уже сохранены;

4) Qcache_inserts — количество запросов, которые были добавлены в кеш;

5) Qcache_lowmem_prunes — количество запросов, которые были удалены из кеша из-за нехватки памяти; / обратите внимание на этот пункт, если мало RAM.

6) Qcache_not_cached — количество запросов, которые не были записаны в кеш из-за использования функций работы со временем и тд

7) Qcache_queries_in_cache — количество запросов, которые находятся в кеше.

8) Qcache_total_blocks — количество занятых блоков;

Любую переменную можно посмотреть так:

SHOW VARIABLES LIKE 'query_cache_size';

Оценка эффективности кэша базы данных

1 способ)

 qcache_hit_ratio = qcache_hits / (qcache_hits + qcache_inserts + qcache_not_cached)

Если это значение > 0.8, то значит 80% ваших запросов попадают в кэш, это очень хороший показатель. Если % попадания в кэш низкий, то возможно стоит увеличить значение query_cache_size. (но Mariadb не рекомендует его более 50Мб)

Какие запросы не кэшируются

Запросы генерирующие предупреждения(warnings);
Запросы которые являются подзапросами внешнего запроса;
Подготовленные запросы (Prepared statements);
Запросы с SQL_NO_CACHE
Запросы с временными таблицами или вовсе не использующие таблицы;
Запросы внутри хранимых процедур и функций;
Запросы имеющие следующие формы:
SELECT ... IN SHARE MODE
SELECT ... FOR UPDATE
SELECT ... INTO OUTFILE ...
SELECT ... INTO DUMPFILE ...
SELECT * FROM ... WHERE autoincrement_col IS NULL
Запросы использующие хранимые функции, пользовательские переменные или ссылающиеся на таблицы в системных базах mysql или INFORMATION_SCHEMA;
Запросы в которых используются функции:
BENCHMARK (), CONNECTION_ID (), CONVERT_TZ (), CURDATE (), CURRENT_DATE (), CURRENT_TIME (), CURRENT_TIMESTAMP (), CURTIME (), DATABASE (), ENCRYPT () с одним аргументом, FOUND_ROWS (), GET_LOCK (), LAST_INSERT_ID (), LOAD_FILE (), MASTER_POS_WAIT (), NOW (), RAND (), RELEASE_LOCK (), SLEEP (), SYSDATE (), UNIX_TIMESTAMP () без аргументов, USER (), UUID ();

Как видим обычные запросы сюда практически не попадают., но обратите внимание, что есть запросы с NOW() и RAND() , которыми любят баловаться программисты.

Кстати, есть интересный калькулятор по настройкам MySQL