Mysql не использовать кэш

Обновлено: 28.06.2024

Кэш запросов – это функция MySQL, которая позволяет ускорить извлечение данных из БД. Это делается путем сохранения в памяти операторов SELECT с извлеченными записями; если позже клиент отправляет идентичный запрос, данные выводятся быстрее, поскольку не нужно повторно выполнять команды в БД.

По сравнению с данными, хранящимися на диске, кэшированные данные из RAM (Random Access Memory) можно получить быстрее, что улучшает операции ввода/вывода (I/O). Например, благодаря кэшу запросов производительность сайтов WordPress или e-commerce порталов с большим количеством операций чтения и почти не меняющимися данными может значительно вырасти.

В этом мануале вы научитесь настраивать кэш запросов MySQL. Сначала мы попробуем работать без кэша, чтобы оценить стандартное время обработки запроса. После настройки кэша запросов мы протестируем сервер MySQL и посмотрим на разницу в производительности.

Примечание: Кэш запросов устарел в версии MySQL 5.7.20 и был удален в MySQL 8.0, но он по-прежнему является мощным инструментом и его можно использовать в тех версиях MySQL, которые поддерживают его. В более новых версиях MySQL можно использовать альтернативные сторонние инструменты для оптимизации производительности базы данных MySQL (такие как ProxySQL).

Требования

Сервер Ubuntu 18.04, настроенный по этому мануалу.
Сервер MySQL (смотрите мануал Установка MySQL в Ubuntu 18.04).

1: Проверка кэша запросов

Перед настройкой кэша запросов нужно проверить, поддерживает ли ваша версия MySQL эту функцию. Сначала подключитесь по ssh к серверу Ubuntu 18.04:

Затем выполните следующую команду, чтобы войти на сервер MySQL как пользователь root:

sudo mysql -u root -p

При появлении запроса введите root пароль сервера MySQL, а затем нажмите Enter, чтобы продолжить.

Используйте следующую команду, чтобы проверить, поддерживается ли кэш запросов:

show variables like 'have_query_cache';

Вы должны получить такой вывод:

Если в have_query_cache установлено значение YES, это означает, что кэш запросов поддерживается вашей версией СУБД. Если ваша версия не поддерживает кэш, можно использовать альтернативные сторонние инструменты для оптимизации производительности MySQL (такие как ProxySQL).

Убедившись, что версия MySQL поддерживает кэш запросов, мы можем приступать к изучению переменных, которые управляют этой функцией.

2: Проверка стандартных переменных кэша запросов

В MySQL кэшем запросов управляет ряд переменных. Сейчас нам нужно проверить значения этих переменных, которые поставляются с MySQL по умолчанию, и разобраться, что контролирует каждая из них.

Чтобы проверить эти переменные, используйте следующую команду:

show variables like 'query_cache_%' ;

В выводе вы увидите переменные:

+------------------------------+----------+
| Variable_name | Value |
+------------------------------+----------+
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 16777216 |
| query_cache_type | OFF |
| query_cache_wlock_invalidate | OFF |
+------------------------------+----------+
5 rows in set (0.00 sec)

Значение query_cache_limit определяет максимальный размер отдельных результатов запроса, которые могут быть кэшированы. Значение по умолчанию составляет 1 048 576 байт, что эквивалентно 1 МБ.

MySQL не обрабатывает кэшированные данные целиком; он обрабатывает их блоками. Минимальный объем памяти, выделяемый каждому блоку, определяется переменной query_cache_min_res_unit. Значение по умолчанию составляет 4096 байт, или 4 КБ.

Переменная query_cache_size контролирует общий объем памяти, выделенной для кэша запросов. Если в ней установлено значение 0, это означает, что кэш запросов отключен. В большинстве случаев по умолчанию может быть установлено значение 16 777 216 (около 16 МБ). Кроме того, следует иметь в виду, что query_cache_size требует как минимум 40 КБ для размещения своих структур. Выделенное здесь значение выравнивается по ближайшему 1024-байтовому блоку – то есть фактическое значение может немного отличаться от установленного вами.

MySQL определяет запросы к кэшу, изучая переменную query_cache_type. Значение 0 или OFF отключает кэширование или извлечение кэшированных запросов. Вы также можете установить значение 1, чтобы включить кэширование для всех запросов, кроме тех, которые начинаются с SELECT SQL_NO_CACHE. Значение 2 позволяет кэшировать только те запросы, которые начинаются с SELECT SQL_CACHE.

Переменная query_cache_wlock_invalidate решает, должен ли MySQL извлекать результаты из кэша, если используемая в запросе таблица заблокирована. Ее значением по умолчанию является OFF.

Примечание: Переменная query_cache_wlock_invalidate устарела с версии MySQL 5.7.20. Вполне возможно, что вы не увидите ее в своем выводе (в зависимости от версии MySQL, которую вы используете).

Изучив системные переменные, которые управляют кэшем запросов MySQL, мы можем проверить, как работает MySQL без кэша.

3: Тестирование MySQL без кэша запросов

Цель этого мануала – оптимизировать сервер MySQL с помощью кэширования запросов. Чтобы увидеть разницу в скорости, мы оценим производительность обработки запросов до и после внедрения кэша.

Сейчас мы создадим тестовую БД и вставим туда какие-нибудь данные, чтобы посмотреть, как MySQL работает без кэша запросов.

Создайте базу данных и назовите ее sample_db, выполнив следующую команду:

Create database sample_db;
Query OK, 1 row affected (0.00 sec)

Перейдите в эту БД:

Use sample_db;
Database changed

Создайте здесь таблицу с двумя столбцами, customer_id и customer_name. Давайте назовем таблицу customers:

Create table customers (customer_id INT PRIMARY KEY, customer_name VARCHAR(50) NOT NULL) Engine = InnoDB;
Query OK, 0 rows affected (0.01 sec)

Запустите следующие команды, чтобы вставить тестовые данные:

Insert into customers(customer_id, customer_name) values ('1', 'JANE DOE');
Insert into customers(customer_id, customer_name) values ('2', 'JANIE DOE');
Insert into customers(customer_id, customer_name) values ('3', 'JOHN ROE');
Insert into customers(customer_id, customer_name) values ('4', 'MARY ROE');
Insert into customers(customer_id, customer_name) values ('5', 'RICHARD ROE');
Insert into customers(customer_id, customer_name) values ('6', 'JOHNNY DOE');
Insert into customers(customer_id, customer_name) values ('7', 'JOHN SMITH');
Insert into customers(customer_id, customer_name) values ('8', 'JOE BLOGGS');
Insert into customers(customer_id, customer_name) values ('9', 'JANE POE');
Insert into customers(customer_id, customer_name) values ('10', 'MARK MOE');
Query OK, 1 row affected (0.01 sec)
Query OK, 1 row affected (0.00 sec)
.

Теперь нужно запустить профайлер MySQL, аналитический сервис для мониторинга производительности запросов MySQL. Чтобы включить профиль для текущего сеанса, выполните следующую команду, установив значение 1 (что значит «включено»):

SET profiling = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

Затем запустите следующий запрос, чтобы извлечь список клиентов:

Select * from customers;

Вы получите следующий вывод:

+-------------+---------------+
| customer_id | customer_name |
+-------------+---------------+
| 1 | JANE DOE |
| 2 | JANIE DOE |
| 3 | JOHN ROE |
| 4 | MARY ROE |
| 5 | RICHARD ROE |
| 6 | JOHNNY DOE |
| 7 | JOHN SMITH |
| 8 | JOE BLOGGS |
| 9 | JANE POE |
| 10 | MARK MOE |
+-------------+---------------+
10 rows in set (0.00 sec)

Затем запустите команду SHOW PROFILES, чтобы получить информацию о производительности для только что выполненного запроса SELECT:

Вы получите примерно такой вывод:

Выходные данные показывают общее время, потраченное MySQL на извлечение записей из БД. Позже мы сравним эти данные с результатом, полученным при извлечении записей с включенным кэшем запросов. Для этого запишите текущее значение Duration. Предупреждение в выводе можно проигнорировать: оно просто сообщает, что команда SHOW PROFILES будет удалена в следующем релизе MySQL и заменена схемой производительности (Performance Schema).

Выйдите из интерфейса командной строки MySQL.

Только что вы запустили запрос в MySQL без поддержки кэша запросов и записали время, затраченное на извлечение записей. Теперь давайте включим кэш запросов и посмотрим, как изменится производительность при обработке того же запроса.

4: Настройка кэша запросов

Сейчас у нас есть тестовая БД, где мы уже запустили SELECT при отключенном кэше запросов. Давайте теперь включим этот кэш, отредактировав конфигурационный файл MySQL.

Откройте файл в редакторе:

sudo nano /etc/mysql/my.cnf

Добавьте следующие строки в конец файла:

.
[mysqld] query_cache_type=1
query_cache_size = 10M
query_cache_limit=256K

Мы включили кэш запросов, установив для параметра query_cache_type значение 1. Также мы установили индивидуальный предельный размер запроса в 256 КБ и выделили 10 мегабайт для кэша запросов (значение параметра query_cache_size равно 10 МБ).

Сохраните и закройте файл. Затем перезапустите сервер MySQL, чтобы изменения вступили в силу:

sudo systemctl restart mysql

Итак, мы включили кэш запросов. Давайте посмотрим, как изменится производительность при обработке того же запроса.

5: Тестирование MySQL с поддержкой кэша запросов

Теперь давайте повторим тот запрос, который мы выполнили в разделе 3, чтобы посмотреть, как кэш запросов оптимизировал производительность сервера MySQL.

Сначала подключитесь к серверу MySQL как root:

sudo mysql -u root -p

Введите свой root пароль для сервера базы данных и нажмите Enter, чтобы продолжить.

Теперь проверьте свой набор переменных, чтобы убедиться, что вы включили кэш запросов:

show variables like 'query_cache_%' ;

Вы увидите следующий вывод:

+------------------------------+----------+
| Variable_name | Value |
+------------------------------+----------+
| query_cache_limit | 262144 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 10485760 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+------------------------------+----------+
5 rows in set (0.01 sec)

Переменная query_cache_type имеет значение ON; это подтверждает, что вы включили кэш запросов с параметрами, которые определили на предыдущем этапе.

Перейдите в базу данных sample_db, которую вы создали ранее.

SET profiling = 1;

Затем выполните запрос, чтобы вывести список всех клиентов как минимум два раза (чтоб собрать достаточно информации для профилирования).

После того как вы выполните первый запрос, MySQL создаст кэш результатов, и, следовательно, вы должны повторить запрос еще раз, чтобы вызвать значение из кэша, если все работает правильно:

Select * from customers;
Select * from customers;

Затем запросите информацию о профилях:

Вы получите такой вывод:

Как видите, время, затраченное на выполнение запроса, резко сократилось с 0,00044075 (без кэша запросов ) до 0,00026000 (обратите внимание на второй запрос).

Вы можете оценить преимущества кэширования запросов с помощью детального профилирования первого запроса:

SHOW PROFILE FOR QUERY 1;
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000025 |
| Waiting for query cache lock | 0.000004 |
| starting | 0.000003 |
| checking query cache for query | 0.000045 |
| checking permissions | 0.000008 |
| Opening tables | 0.000014 |
| init | 0.000018 |
| System lock | 0.000008 |
| Waiting for query cache lock | 0.000002 |
| System lock | 0.000018 |
| optimizing | 0.000003 |
| statistics | 0.000013 |
| preparing | 0.000010 |
| executing | 0.000003 |
| Sending data | 0.000048 |
| end | 0.000004 |
| query end | 0.000006 |
| closing tables | 0.000006 |
| freeing items | 0.000006 |
| Waiting for query cache lock | 0.000003 |
| freeing items | 0.000213 |
| Waiting for query cache lock | 0.000019 |
| freeing items | 0.000002 |
| storing result in query cache | 0.000003 |
| cleaning up | 0.000012 |
+--------------------------------+----------+
25 rows in set, 1 warning (0.00 sec)

Выполните следующую команду, чтобы отобразить информацию о втором запросе:

SHOW PROFILE FOR QUERY 2;
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000024 |
| Waiting for query cache lock | 0.000003 |
| starting | 0.000002 |
| checking query cache for query | 0.000006 |
| checking privileges on cached | 0.000003 |
| checking permissions | 0.000027 |
| sending cached result to clien | 0.000187 |
| cleaning up | 0.000008 |
+--------------------------------+----------+
8 rows in set, 1 warning (0.00 sec)

Выводы профайлера показывают, что MySQL потратил меньше времени на обработку второго запроса, потому что он мог извлечь данные из кэша запросов, а не с диска. Вы можете сравнить два вывода для каждого из запросов. Если вы посмотрите информацию о профиле для QUERY 2, состояние sending cached result to client показывает, что данные были прочитаны из кэша и таблицы не открывались (статуса Opening tables не будет).

Если на вашем сервере включена функция кэширования запросов MySQL, вы сможете улучшить скорость чтения данных.

Заключение

Вы настроили кэш запросов для ускорения работы сервера MySQL в Ubuntu 18.04. Эта функция может повысить скорость работы вашего веб-сайта или приложения. Кэширование устраняет обработку лишних операторов SQL и является очень и популярным методом оптимизации базы данных.

Оптимизация для подобных таблиц — вынести часто изменяющиеся данные в отдельную таблицу. Например, есть таблица статей, в которой также хранятся и количество комментариев к ней. Посколько количество комментариев изменяется часто, а сама статья практически не меняется имеет смысл вынести количество комментариев в отдельную таблицу, позволяя при этом избегать частой инвалидации кеша статей.

Использование кеша запросов полностью прозрачно для клиента. Клиент не знает о том, выполнил ли MySQL запрос, или вернул его из кеша.

MySQL кеширует результаты только SELECT запросов. MySQL не кеширует запросы, результаты которых могут измениться. Например, запросы в которых используются функции, относящиеся к текущему времени (NOW(), CURDATE() и др.), к текущему соединению (CURRENT_USER(), CONNECTION_ID() и др.) и другие. Полный список таких функций можно найти в мануале. Кроме этого, MySQL не кеширует запросы, в которых есть использование пользовательских функций, хранимых процедур, выборки из баз mysql или INFORMATION_SCHEMA, выборки из таблиц, для которых определены привилегии для столбцов.

Оптимизация для запросов, использующих функции текущего времени (NOW(), CURDATE() и др.) — замена таких функций на строку с датой. Например: запрос
SELECT * FROM table WHERE create_date > NOW() — INTERVAL 1 DAY
, который не будет кешироваться можно заменить на запрос, который закешируется:
SELECT * FROM table WHERE create_date > '2009-10-14' — INTERVAL 1 DAY

Отдельно обрабатывается кеширование выборок от InnoDB таблиц. MySQL удаляет результаты выборок из кеша при любом изменении таблицы внутри транзакции (хотя мог бы не удалять до тех пор, пока транзакция не зафиксирована). Кроме этого, все выборки из этой таблицы не будут кешироваться до тех пор, пока транзакция не зафиксирована.

Qcache_free_blocks показывает сколько свободных блоков есть в кеше;
Qcache_total_blocks — количество занятых блоков;
Qcache_free_memory говорит о том, сколько свободной памяти осталось в кеше;
Qcache_hits — количество запросов, результаты которых были взяты из кеша;
Qcache_inserts — количество запросов, которые были добавлены в кеш;
Qcache_lowmem_prunes — количество запросов, которые были удалены из кеша из-за нехватки памяти;
Qcache_not_cached — количество запросов, которые не были записаны в кеш из-за использования функций работы со временем и т.п.;
Qcache_queries_in_cache — количество запросов, которые находятся в кеше.

query_cache_type = (ON, DEMAND, OFF) — определяет включено ли кеширование или нет(ON, OFF). При использовании DEMAND кешироваться будут только запросы, в которых есть директива SQL_CACHE;
query_cache_size — размер кеша запросов. query_cache_size = 0 отключает использование кеша;
query_cache_limit — размер максимальной выборки, хранимой в кеше;
query_cache_min_res_unit — минимальный размер блока, хранимого в кеше;
query_cache_wlock_invalidate — определяет будут ли данные браться из кеша, если таблица, к которым они относятся заблокирована на чтение.

В момент начала записи MySQL не знает о размере получившейся выборки. Если записанный в кеш размер выборки больше, чем query_cache_limit, то запись прекращается и занятое место освобождается (поэтому, если вы знаете наперед, что результат выборки будет большим, рекомендуется выполнять его с директивой SQL_NO_CACHE). В случае, если MySQL кеширует несколько запросов параллельно, блоки, выделяемые для разных запросов, могут чередоваться. Кроме этого, после того, как запрос удален из кеша, освободившееся место может быть недостаточным для записи новых запросов. Это приводит к фрагментации кеша. Для дефрагментации кеша можно выполнить команду FLUSH QUERY CACHE. (FLUSH QUERY CACHE переносит все запросы, хранящиеся в кеше в его начало и помечает оставшуюся память как один свободный блок). Кроме этого уменьшить фрагментацию кеша можно правильным подбором параметра query_cache_min_res_unit.Если значение query_cache_min_res_unit небольшое, то фрагментация будет уменьшаться, однако, MySQL будет вынужден создавать больше блоков в кеше. Если значение велико, то фрагментация будет большой.

Значение query_cache_min_res_unit должно быть равно среднему размеру кешируемого значения. Его примерное значение можно вычислить по формуле query_cache_min_res_unit = (query_cache_size – Qcache_free_memory) / Qcache_queries_in_cache. Однако для сайтов, размер выборки которых сильно меняется, рекомендуется использовать query_cache_type = DEMAND и явное указание на то, что запрос должен быть закеширован директивой SQL_CACHE. Кроме этого, необходимо ограничить запись в кеш больших выборок заданием переменной query_cache_limit или директивой SQL_NO_CACHE.

Определить то, насколько фрагментирован кеш, можно по значению переменной Qcache_free_blocks. Для идеального нефрагментированного кеша значение равно единице, в худшем случае — Qcache_total_blocks / 2. Так же можно определить, что ваш кеш запросов сильно фрагментируется, если значение Qcache_lowmem_prunes постоянно возрастает при том, что значение Qcache_free_memory далеко от нуля.

Оценить эффективность использования кеша можно по формуле Qcache_hits / (Qcache_hits + Com_select). О том, какое значение является достаточным для вашего сайта решать вам. Если для запросов хранимых в кеше требуется большое время, то эффективность даже в 10% может быть полезной. Однако если эффективность использования низкая и увеличить ее не удается, то возможно, что характер нагрузки вашей системы такой, что кеш запросов вовсе не эффективен для вас. В таких случаях бывает более полезным вообще отключить кеширование запросов и использовать кеширование на стороне клиентов.

MySQL при работе с таблицами использует хорошо масштабируемые алгоритмы, так что MySQL может работать даже при малых объемах памяти. Естественно для лучшей производительности нужно больше оперативной памяти.

Для просмотра текущих настроек подключаемся к базе

Во-первых, убедимся, что кеширование включено. Переменная

query_cache_type должна иметь значение ON (1) или DEMAND (2) и
query_cache_limit – определяет максимальный размер результата который попадет в кэш
query_cache_size быть отличной от нуля. При использовании DEMAND кешироваться будут только запросы, в которых есть директива SQL_CACHE;
query_cache_min_res_unit минимальный размер выделяемого блока памяти для хранения результатов кешированного запроса. MySQL не хранит кеш в одном большом куске памяти, вместо этого по требованию выделяются блоки с минимальным размером query_cache_min_res_unit (=4KB по умолчанию). Последний такой блок обрезается до размера данных, а оставшаяся память освобождается.

Эффект кэширования в том что сервер получая запрос смотрит есть ли хэш запроса в кэше. Если хэш совпадает – сервер сразу отдает результат – не производя разбор запроса, оптимизацию и т.д. накладные расходы – в сопровождении механизма кэширования – просмотр кэша, запись результата запроса в кэш и т.д.

И если у вас много небольших запросов в кеше, то это может привести к фрагментации памяти из-за большого количества свободных блоков. А это, в свою очередь, вызывает удаление кешированных записей из-за недостатка памяти. В таком случае имеет смысл уменьшить значение query_cache_min_res_unit. Если большинство ваших запросов порождают большой результат, то увеличение этого параметра может повысить производительность.

Попробуем оценить эффект. Смотрим как меняются показания счетчиков попаданий в кэш (Qcahe_hits), количество запросов объявленных недействительными из-за нехватки памяти (Qcache_lowmem_prunes), общее количество запросов типа SELECT (а кэшируются только они). Делаем:

Для мониторинга query cache используется SHOW STATUS:

Qcache_free_blocks показывает сколько свободных блоков есть в кеше (будет уменьшаться по мере увеличения скэшированных запросов).;
Qcache_total_blocks — количество занятых блоков;
Qcache_free_memory — показывает свободную «доступную» память для кэширования;
Qcache_hits — количество запросов, результаты которых были взяты из кеша, без реального обращения к базе данных;
Qcache_inserts — количество запросов, которые были добавлены в кеш;
Qcache_lowmem_prunes — количество запросов, которые были удалены из кеша из-за нехватки памяти;
Qcache_not_cached — количество запросов, которые не были записаны в кеш из-за использования функций работы со временем и т.п.;
Qcache_queries_in_cache — количество запросов, которые находятся в кеше.

Можно просмотреть общее количество запросов SELECT:

Оценивать эффективность кэша рекомендуют делением значения переменной 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.

Текущее значение можно посмотреть так:

Опять же возникает вопрос: как выбрать адекватное значение query_cache_size?
В этом поможет Qcache_lowmem_prunes. В этой переменной хранится число запросов, которые были убраны из кэша из-за необходимости кэширования новых запросов. Необходимо стремится к такому размеру кэша, при котором Qcache_lowmem_prunes будет лишь незначительно увеличиваться. Для этого, рекомендуется сравнить разницу значений Qcache_lowmem_prunes за час и кол-во запросов, поступивших на mysql за этот же час.

На практике, для расчета query_cache_size можно использовать одну из 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 хранит кэш в блоках. На 1 запрос необходимо 2 блока: один для самого текста запроса, второй для результата.
Если рассмотреть таблицу со значения Qcache%
Общее количество блоков кэша Qcache_total_blocks – 28
Закешировано сейчас 6 запрос, а значит занят 6*2 = 12 блоков
свободно блоков Qcache_free_blocks – 10. Чем больше незадействованных Qcache_free_blocks, тем больше степень “фрагментации” кэша.
Если большинство запросов имеют небольшой объем результирующих данных, то стоит уменьшить минимальный размер блока кэша query_cache_min_res_unit, который по умолчанию равен 4 Кб.
Если же большинство запросов возвращают много данных – то стоит увеличить размер блока кэша.
Главное – это добиться минимального значения Qcache_free_blocks.

Если счетчик Qcache_not_cached велик, можно попробоавть увеличить переменную query_cache_limit – она позволит увеличить лимит и помещать в кэш результаты запросов которые «не помещаются».

За использование кеша запросов отвечают следующие конфигурационные переменные:

Одним из инструментов повышения производительности MySQL, как и большинства других СУБД, является кэширование запросов. Однако преимущества использования кэша MySQL могут сойти на нет или даже может быть ухудшение производительности, если при настройке кэша не было полного представления о принципах и отличиях его работы по сравнению с другими СУБД . В этой статье я постараюсь как можно более подробно описать все преимущества, недостатки и нюансы использования кэширования sql-запросов.

Принцип работы

В отличие от других СУБД, MySQL кэширует не планы запросов, а сам текст запросов и результаты их выполнения, что четко прописано в официальном руководстве:

The query cache stores the text of a SELECT statement together with the corresponding result that was sent to the client. If an identical statement is received later, the server retrieves the results from the query cache rather than parsing and executing the statement again. The query cache is shared among sessions, so a result set generated by one client can be sent in response to the same query issued by another client.

При включении кэша, в памяти выделяется блок данных, равный по размеру значению переменной query_cache_size 1 (нулевой размер означает, что кэширование отключено). Размер переменной рекомендуется определять экспериментальным путем, но не менее 40КВ для записи служебной информации. На практике это означает, что с увеличением размера кэша также увеличиваются накладные расходы на его обслуживание и при переходе определенного порога, преимущества кэширования могут оказаться ниже, чем появившиеся недостатки. Обычно размер кэша в несколько сотен мегабайт уже будет являться избыточным 2 .

Данные в кэше хранятся блоками. При выполнении запроса, сервер последовательно выделяет блоки данных для записи результата, размер блока определяется переменной query_cache_min_res_unit 3 .

Важным моментом является то, что в кэш будут попадать далеко не все запросы, даже без каких-либо ограничений со стороны типа кэша (см. ниже). Ограничения зависят от использования определенных функций, UDF, локальных переменных, выборок из системных или временных таблиц и т.п., подробнее можно прочитать в официальном руководстве 4 .

Один и тот же запрос, данные из которого взяты напрямую из базы данных или выбраны из кэша, должен возвращать одинаковый результат. Поэтому если в таблице произошли любые изменения, то связанные с ней запросы должны быть непременно удалены из кэша, что и происходит при выполнении с таблицей одной или нескольких операций из: INSERT, UPDATE, DELETE, TRUNCATE TABLE, ALTER TABLE, DROP TABLE, или DROP DATABASE.

На этом о принципах функционирования все, переходим к следующей главе.

Кэшируйте правильно

Как было сказано выше, далеко не во всех ситуациях использование кэширования запросов будет давать выигрыш в производительности. Чтобы однозначно ответить на вопрос в каких случаях использовать кэш, надо понимать специфику работы именного вашего сервера баз данных. Запросы из таблиц с частым обновлением данных необходимо будет обновлять в кэше при каждом изменении хотя бы одной записи в таблице, что не имеет смысла в принципе и в конечном итоге обернется для вас снижением производительности. Однако практически в каждой базе данных, при условии что спроектирована она должным образом, найдутся таблицы, данные в которых не изменяются или изменяются достаточно редко. Таким образом, может возникнуть вполне логичная мысль кэшировать только запросы из определенных таблиц, число операций DML 5 с которыми на порядки ниже, чем DQL.

Благо в MySQL есть возможность избирательного кэширования и определяется она значением переменной query_cache_type 6 , которая может принимать три значения:

0 или OFF	Не кэшировать или не извлекать результаты из кэша запросов. Стоит отметить, что эта опция не отключает буфер кэша в оперативной памяти. Чтобы его отключить, вы должны установить переменную размера кэша в значение 0.
1 или ON	Кэшировать все результаты запросов, которые являются кэшируемыми, кроме запросов, начинающихся на SELECT SQL_NO_CACHE.
2 или DEMAND	Кэшировать только результаты запросов, которые, во-первых, поддаются кэшированию, а во-вторых, начинаются на SELECT SQL_CACHE.

Если есть необходимость кэшировать только определенные запросы, рекомендуется в них напрямую указать необходимость кэширования с помощью инструкции SELECT SQL_CACHE, а также выставить переменную типа кэша в значение 2 или DEMAND.

Важным моментом в использовании кэша является максимальное ограничение размера результата запроса в 1МВ (значение по умолчанию). Запросы, результат которых больше этого значения, не попадут в кэш. Поэтому если у вас такие запросы могут быть, рекомендуется увеличить значение переменной query_cache_limit 7 до необходимого.

Основные показатели производительности кэша

Таким образом, чтобы получить исчерпывающую информацию о фрагментированности кэша, необходимо обратить внимание на описанные ниже комплексные показатели.

Процент свободной памяти:

Free Cache Memory (%)=Qcache_free_memory/query_cache_size*100

Процент свободных блоков:

Free Cache Blocks (%)=Qcache_free_blocks 11 /Qcache_total_blocks 12 *100

Процент вытесненных из кэша запросов по отношению к общему количеству запросов к кэшу:

Prunes and Total Queries Ratio (%) 13 =Qcache_lowmem_prunes/Queries 14 *100

Также для оценки эффективности кэширования на некоторых ресурсах рекомендуют использовать формулу:

Select and Qcache Ratio (%) 17 =Qcache_hits 18 /(Qcache_hits+Com_select)*100

Cache Queries and Modification Statements(%) 19 =(Com_insert+Com_delete+Com_update+Com_replace) 20 /Qcache_hits

Какого-то оптимального значения у этого параметра не существует, но небольшое количество запросов в кэше по отношению к другим операциям может натолкнуть на размышления об увеличении объема кэша или о целесообразности его использования применительно к вашему окружению.

Читайте также: