Для использования оптимизации query cache, нужно понимать зачем ее использовать.
Кэш запросов устарел с MySQL 5.7.20 и удален в MySQL 8.0.
7.1 Кеширование на стороне БД
MySQL использует хорошо масштабируемые алгоритмы при работе с таблицами, что позволяет использовать его даже при малых объемах памяти. Однако для лучшей производительности требуется больше оперативной памяти.
Чтобы просмотреть текущие настройки, подключитесь к базе данных.
#mysq -u root -p
mysql> show variables like 'query_cache%';
+------------------------------+-----------+
| Variable_name | Value |
+------------------------------+-----------+
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 134217728 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+------------------------------+-----------+
5 rows in set (0.00 sec)
Проверим, что кеширование включено. Переменная:
query_cache_typeдолжно быть равноON(1) илиDEMAND(2).query_cache_limitопределяет максимальный размер результата, который будет помещен в кэш.query_cache_sizeдолжна быть отлична от нуля. При использовании режима DEMAND будут кешироваться только запросы, содержащие директивуSQL_CACHE.query_cache_min_res_unitопределяет минимальный размер блока памяти, который выделяется для хранения результатов кешированного запроса. По умолчанию размер равен 4 КБ. MySQL не хранит кеш в одном большом куске памяти. Вместо этого выделяются блоки с минимальным размером. Последний такой блок обрезается до размера данных, а оставшаяся память освобождается.
Эффект кэширования заключается в том, что сервер при получении запроса проверяет, есть ли хэш запроса в кэше. Если хэш совпадает, сервер сразу отдает результат, не производя разбор запроса, оптимизацию и т.д. Накладные расходы включают просмотр кэша, запись результата запроса в кэш и т.д.
Если много небольших запросов в кэше, это может привести к фрагментации памяти из-за большого количества свободных блоков. Это, в свою очередь, может вызвать удаление кэшированных записей из-за нехватки памяти. В таком случае рекомендуется уменьшить значение параметра query_cache_min_res_unit. Если большинство запросов порождают большой результат, увеличение этого параметра может повысить производительность.
Давайте посмотрим, какой эффект мы получим. Проверим значения счетчиков попаданий в кэш (Qcahe_hits), количество запросов, объявленных недействительными из-за нехватки памяти (Qcache_lowmem_prunes) и общее количество запросов типа SELECT (которые кэшируются).
#mysq -u root -p
mysql> show status like 'Qcache%';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| Qcache_free_blocks | 715 |
| Qcache_free_memory | 130369640 |
| Qcache_hits | 24209 |
| Qcache_inserts | 16215 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 444 |
| Qcache_queries_in_cache | 1740 |
| Qcache_total_blocks | 4225 |
+-------------------------+-----------+
8 rows in set (0.00 sec)
7.2 Текущий статус кеширования
Для мониторинга query cache используется команда SHOW STATUS.
mysql> SHOW STATUS LIKE 'Qcache_%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 10 |
| Qcache_free_memory | 16755496 |
| Qcache_hits | 49812 |
| Qcache_inserts | 103999 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 180 |
| Qcache_queries_in_cache | 6 |
| Qcache_total_blocks | 28 |
+-------------------------+----------+
8 rows in set (0.00 sec)
Qcache_free_blocksпоказывает, сколько свободных блоков находится в кеше. Этот параметр уменьшается по мере увеличения числа скэшированных запросов.Qcache_total_blocks— количество занятых блоков;Qcache_free_memory— показывает свободную «доступную» память для кэширования;Qcache_hits- это количество запросов, для которых результаты были получены из кеша без обращения к базе данных.Qcache_inserts— количество запросов, которые были добавлены в кеш;Qcache_lowmem_prunes— количество запросов, которые были удалены из кеша из-за нехватки памяти;Qcache_not_cached— количество запросов, которые не были записаны в кеш из-за использования функций работы со временем и т.п.;Qcache_queries_in_cache— количество запросов, которые находятся в кеше.
Можно посмотреть сколько запросов SELECT.
mysql> show status like 'Com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select | 16719 |
+---------------+-------+
1 row in set (0.00 sec)
7.3 Эффективность кеширования
Чтобы оценить эффективность кэша, разделите значение переменной Qcache_hits на Qcache_hits + Com_select. Это повышает значение счетчика Qcache_hits, если запрос берется из кэша, или Com_select, если запрос не кэшируется. Такой способ предлагается в книге «Mysql оптимизация производительности» от O’reilly.
В сети существует другой способ.
qcache_hit_ratio = qcache_hits / (qcache_hits + qcache_inserts + qcache_not_cached)
Если значение больше 0,8, то это значит, что 80% запросов попадают в кэш. Это очень хороший показатель.
Если процент попадания в кэш низкий, необходимо увеличить значение параметра query_cache_size. Текущее значение можно узнать так:
SHOW VARIABLES LIKE 'query_cache_size';
Вопрос все еще актуален: как выбрать адекватное значение query_cache_size?
В этом поможет Qcache_lowmem_prunes, в которой хранится число запросов, которые были удалены из кэша для кэширования новых запросов. Нужно стремиться к такому размеру кэша, при котором Qcache_lowmem_prunes будет минимальным. Для этого следует сравнить разницу значений Qcache_lowmem_prunes за час и количество запросов, поступивших на MySQL за этот же час.
«На практике для расчета query_cache_size можно использовать одну из двух формул:
query_cache_size = (число запросов за 10 минут)*(средний объем ответа на запрос) * 1,2
или
query_cache_size = (число запросов за 10 минут)*(средний объем ответа на запрос) * 1,2
query_cache_size = (объем трафика за 10 минут) * 1,2 »
Можно закэшировать запросы на 10 минут и добавить дополнительные 20% памяти для фрагментации кэша. Для подсчета количества и среднего объема ответа на запрос можно использовать переменную Bytes_sent.
Значение параметра query_cache_size мы увеличили. Теперь нужно обратить внимание на значения Qcache_total_blocks, Qcache_free_blocks и Qcache_queries_in_cache. MySQL хранит кэш в блоках. На один запрос нужно два блока: один для текста запроса, другой для результата.
В таблице со значениями Qcache% общее количество блоков кэша равно 28. Закешировано 6 запросов, что занимает 12 блоков. Свободных блоков 10. Чем больше незадействованных блоков, тем больше фрагментации кэша.
Если большинство запросов имеют небольшой объем результата, то стоит уменьшить минимальный размер блока кэша query_cache_min_res_unit (по умолчанию 4 Кб). Если же большинство запросов возвращают много данных, то стоит увеличить размер блока кэша.
Главное — достичь минимального значения Qcache_free_blocks. Если счетчик Qcache_not_cached велик, можно попробовать увеличить переменную query_cache_limita — это позволит увеличить лимит и поместить в кэш результаты запросов, которые «не помещаются». За использование кеша запросов отвечают следующие конфигурационные переменные.
query_cache_size— размер кеша запросов.query_cache_size = 0отключает использование кеша;query_cache_limit— размер максимальной выборки, хранимой в кеше;query_cache_wlock_invalidate— определяет будут ли данные браться из кеша, если таблица, к которым они относятся заблокирована на чтение.
Чтобы включить кэширование запросов mysql, добавьте строки в файл my.cnf (секция [mysqld]):
query_cache_size = 268435456
query_cache_type =1
query_cache_limit =1 048576
Перезапустить сервис.
7.4 Где кэш не используется
Не кэшируются:
- Запросы с
SQL_NO_CACHE - Подготовленные запросы
(Prepared statements); - Запросы, которые являются подзапросами внешнего запроса;
- Запросы внутри хранимых процедур и функций;
- Запросы, в которых используются функции:
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 (); - Запросы, использующие хранимые функции, пользовательские переменные или ссылающиеся на таблицы в базах данных MySQL или INFORMATION_SCHEMA, проходят проверку.
- Запросы имеющие следующие формы:
SELECT ... IN SHARE MODESELECT ... FOR UPDATESELECT ... INTO OUTFILE ...SELECT ... INTO DUMPFILE ...SELECT * FROM ... WHERE autoincrement_col IS NULL- Запросы с временными таблицами или вовсе не использующие таблицы;
- Запросы генерирующие предупреждения(warnings);
Дефрагментировать кэш можно командой:
mysql>flush query cache;
Очистить – командой:
mysql>reset query cache;
Никогда не работайте на production с настройками по умолчанию. Это может привести к неэффективному использованию ресурсов сервера. Правильная настройка MySQL позволит значительно увеличить производительность базы данных. Это поможет не только ускорить работу приложения, но и более эффективно обрабатывать большую нагрузку.
ПЕРЕЙДИТЕ В ПОЛНУЮ ВЕРСИЮ