Кеш запросов размер – MySQL Query Cache / Habr - Блог сумасшедшего сисадмина

Содержание

MySQL Query Cache / Habr

В MySQL есть очень полезная функциональность — кеш запросов. Кеш запросов доступен в MySQL начиная с версии 4.0.
Многие СУБД имеют подобную функциональность, но в отличие от MySQL они кешируют планы выполнения запросов, тогда как MySQL кеширует результаты запросов.
Дальше о том, как работает кеш запросов, как его настраивать и оптимально использовать.

Как работает кеш запросов.
Кеш запросов можно представлять себе как хеш, ключами которого являются запросы, а значениями — результаты запросов.
Если использование кеша запросов включено, то при получении запроса MySQL определяет, равны ли первые три символа запроса «SEL». Если да, то MySQL смотрит, есть ли в кеше запросов запись с ключом, равным запросу. Отсюда следуют два важных правила:

MySQL выполняет побайтовое сравнение, поэтому запросы, имеющие отличие хотя бы в одном символе (например, SELECT * FROM table и select * from table) будут рассматриваться как два разных запроса. Поэтому необходимо писать запросы в едином стиле;

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

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

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

Использование кеша запросов полностью прозрачно для клиента. Клиент не знает о том, выполнил ли 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 удаляет результаты выборок из кеша при любом изменении таблицы внутри транзакции (хотя мог бы не удалять до тех пор, пока транзакция не зафиксирована). Кроме этого, все выборки из этой таблицы не будут кешироваться до тех пор, пока транзакция не зафиксирована.

Использование кеша запросов.
Посмотреть статистику использования кеша запросов можно выполнив команду:
SHOW GLOBAL STATUS LIKE ‘Qcache%’

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_size. При выполнении запроса, как только получены первые строки результата сервер начинает кешировать их: он выделяет в кеше блок памяти, равный query_cache_min_res_unit, записывает в него результат выборки. Если не вся выборка поместилась в блок, то сервер выделяет следующий блок и так далее. После того, как весь результат записан в кеш, MySQL выделяет свободную часть последнего блока в отдельный блок и помечает его как свободный. Если вокруг такого блока есть свободный блок, то MySQL объединяет их в один.

В момент начала записи 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 Consulting.

P.S. пишите в личку темы статей по MySQL, которые вы хотели бы прочитать.

habr.com

query_cache_size параметр в Mysql

Эта настройка определяет количество памяти, которое Mysql выделит на кеш запросов. Кеш работает таким образом:

Mysql получает запрос и проверяет наличие его результата в кеше. Если данные есть, возвращает ответ.
Если данных нет, запрос выполняется и сохраняется в кеш.
Если таблица изменяется (любая из операций INSERT / UPDATE / DELETE) весь кеш таблицы чистится.

Это значит, что:

Эту настройку лучше использовать только тогда, когда имеет место большое количество чтений и небольшое количество записей (100 чтений на 1 запись).
Отключение этой настройки и использование индексов для получения высокой производительности более приемлемый вариант.

Если же вы решите использовать, сразу определяйте оба параметра (устанавливается в my.cnf):

query_cache_limit = 1M
query_cache_size  = 8M

Не используйте значения более 100…200Мб, т.к. с увеличением этого значения, производительность Mysql деградирует.

Для проверки состояние включения этого параметра, используйте запрос:

mysql> SHOW VARIABLES LIKE 'have_query_cache'

+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | YES   |
+------------------+-------+

Для мониторинга:

mysql> show status like '%Qcache%';

+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Qcache_free_blocks      | 165     |
| Qcache_free_memory      | 3893664 |
| Qcache_hits             | 4654886 |
| Qcache_inserts          | 352314  |
| Qcache_lowmem_prunes    | 301     |
| Qcache_not_cached       | 66691   |
| Qcache_queries_in_cache | 147     |
| Qcache_total_blocks     | 469     |
+-------------------------+---------+

Для очистки кеша:

FLUSH QUERY CACHE

#mysql #настройки ID: 620

ruhighload.com

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

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

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

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

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

rtfm.co.ua

Кэширование запросов MySQL: ограничено максимальным размером кеша 128 МБ? — performance

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

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

query_cache_size — это максимальный объем данных, которые могут храниться в кеше, а query_cache_limit — максимальный размер одного набора результатов в кеше.

Мой текущий кэш запросов MySQL настроен следующим образом:

query_cache_size=128M
query_cache_limit=1M

tuning-primer.sh дает мне следующие настройки подсказок о запущенной системе:

QUERY CACHE
Query cache is enabled
Current query_cache_size = 128 M
Current query_cache_used = 127 M
Current query_cache_limit = 1 M
Current Query cache Memory fill ratio = 99.95 %
Current query_cache_min_res_unit = 4 K
However, 21278 queries have been removed from the query cache due to lack of memory
Perhaps you should raise query_cache_size
MySQL won't cache query results that are larger than query_cache_limit in size

И mysqltuner.pl дает следующие подсказки настройки:

[OK] Query cache efficiency: 31.3% (39K cached / 125K selects)
[!!] Query cache prunes per day: 2300654

Variables to adjust:
    query_cache_size (> 128M)

Оба сценария настройки предполагают, что я должен поднять query_cache_size. Однако увеличение query_cache size более 128M может снизить производительность в соответствии с mysqltuner.pl (см. http://mysqltuner.pl/).

Как бы вы справились с этой проблемой? Не могли бы вы увеличить query_cache_size, несмотря на предупреждение mysqltuner.pl или попытаться каким-то образом настроить логику запросов? Большая часть доступа к данным обрабатывается Hibernate, но в приложении также используется довольно много ручного SQL-кода.

qaru.site

Включаю query_cache на InnoDB — разница в Таймингах Waiting (TTFB) заметна, убираю query_cache и жму F5 — отклик не уменьшается… Почему? — Toster.ru

Всем привет!

Есть у меня база данных, была MyISAM, стала InnoDB (560Мб где-то).
Недавно обновился с 5.5 до 5.7. На сайте в основном операции Select используются, в связи с чем я долгое время использовал:

query_cache_size = 128M
query_cache_type = ON
query_cache_limit = 1M

И радовался отсутствию тормозов даже на очень большой (1.5млн в день)
посещаемости. Там, понятно, еще настроенный Nginx и Xcache были
кстати.

Когда сменил движок на InnoDB, во многих источниках, и у вас
тоже, прочитал, что query_cache — отживший своё герой, да и не герой он
вовсе, т.к. тормозит систему при больших значениях, да и вообще в
InnoDB для кеша есть innodb_buffer_pool_size, делай его больше общего
размера твоей БД и норм. Я так и сделал (my.cnf):

query_cache_size = 0
query_cache_type = 0
query_cache_limit = 1M

innodb_file_per_table = 1
table_open_cache = 4096
innodb_open_files = 4096
innodb_buffer_pool_size = 4G # На сервере 32G оперативной памяти
innodb_buffer_pool_instances = 8
innodb_log_file_size = 512M
innodb_log_buffer_size = 2M
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 2
innodb_read_io_threads = 8
innodb_write_io_threads = 8

Но заметил, что возросли тайминги у Waiting (TTFB) и они не меняются
при простом обновлении страницы.

Например, у меня в админке есть достаточно тяжелые запросы (все
Select) — там выводится список из 500 разделов, для них еще
подразделы, кол-во продаж, статистических данных и т.д.

С включенным query_cache:

1-я загрузка страницы: 2.2 сек
2-я загрузка: 0.3 сек (что мне понятно, т.к. основную блокировку давали I/O
чтение с диска некэшированных данных, а теперь всё из оперативной памяти идёт)

С выключенным query_cache:

1-я загрузка страницы: 3.4 сек
2-я загрузка: 3.2 сек (а вот тут и заключается мой вопрос — WHY???)

Про устройство innodb_buffer_pool_size читал уже все что можно, но
почему он не кэширует Select запрос, не понимаю…

toster.ru

Настройка работы MySQL Query Cache

Использование кэширования запросов, является одним из ключевых факторов влияющих на производительность работы MySQL. Функционал Query Cache открывает дополнительные возможности для оптимизации базы данных и позволяет снизить время обработки запросов в несколько раз. Наилучшую эффективность работы Query Cache показывает на веб-серверах, у которых таблицы не обновляются слишком часто и присутствует много идентичных запросов.

Для подходящего запроса типа SELECT, MySQL автоматически сохраняет текст запроса и данные выборки в кэше. Все идентичные запросы в дальнейшем, будут обрабатываться в обход БД с помощью функции MySQL Query Cache. Таким образом, кэшированные запросы не выполняется вовсе.

Для работы Query Cache в значении переменной query_cache_type должно быть установлено ON или DEMAND, а query_cache_size быть отличной от нуля.

В противном случае, необходимо добавить соответствующие настройки в секцию [mysqld] конфигурационного файла MySQL:

nano /etc/mysql/my.cnf

[mysqld]
query_cache_type        = ON
query_cache_limit       = 1M
query_cache_size        = 16M

За настройку работы функции Query Cache отвечают системные переменные начинающиеся с 'query_cache_'.

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             | ON       |
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+
5 rows in set (0.00 sec)

query_cache_limit — размер максимальной выборки, которая будет записана в кэш. В качестве значения необходимо указать максимальный размер самого тяжелого запроса, но не стоит чрезмерно завышать значение данного параметра.
query_cache_min_res_unit — минимальный размер выделяемого блока памяти для хранения результатов кэшированного запроса. Для записи данных в кэш MySQL разбивает выборку на отдельные блоки с минимальным размером query_cache_min_res_unit. Последний такой блок обрезается до размера данных, а оставшаяся память освобождается. Для записи данных в кэш, MySQL по мере необходимости выделяет блоки размером query_cache_min_res_unit. В качестве значения необходимо указать среднее значение размера выборки от всех запросов. Примерное значение query_cache_min_res_unit можно вычислить по формуле query_cache_min_res_unit = (query_cache_size – Qcache_free_memory) / Qcache_queries_in_cache. Слишком большое значение будет способствовать фрагментации кэша, слишком маленькое может стать причиной снижения производительности.
query_cache_size — размер памяти выделяемый для хранения кэша запросов. Значение равное 0 отключает работу MySQL Query Cache. Устанавливаем значение исходя из количества свободной оперативной памяти в системе. Для выбора оптимального значения, в идеале переменная Qcache_lowmem_prunes должна равняться нулю. В противном случае, рекомендуется чтобы в процессе работы MySQL это значение увеличивалось незначительно.
query_cache_type — параметр отвечающий за работу кэша. Может принимать значения: ON, DEMAND и OFF. Опция включает или отключает работу MySQL Query Cache, если значение query_cache_type установлено равным DEMAND, MySQL будет кэшировать только запросы с директивой SQL_CACHE.
query_cache_wlock_invalidate — определяет будут ли данные браться из кэша, если таблица, к которым они относятся заблокирована на чтение. Если значение параметра query_cache_wlock_invalidate принимает значение OFF, то будет доступно получение данных заблокированной таблицы из Query Cache.

Для мониторинга MySQL Query Cache используется команда:

mysql> SHOW GLOBAL STATUS LIKE 'Qcache%';

+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 158      |
| Qcache_free_memory      | 16420704 |
| Qcache_hits             | 143791   |
| Qcache_inserts          | 21851    |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 12506    |
| Qcache_queries_in_cache | 215      |
| Qcache_total_blocks     | 598      |
+-------------------------+----------+
8 rows in set (0.00 sec)

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

Кратко механизм работы Query Cache выглядит следующим образом. Под кэширование запросов MySQL выделяет в памяти область размером query_cache_size. Для записи результатов запроса сервер создает в кэше свободный блок размером query_cache_min_res_unit. После заполнения блока, сервер создает новый пустой блок и так до тех пор, пока все данные выборки не будут записаны в кэш. После чего свободная область памяти последнего блока выделяется в новый свободный блок. В случае если размер выборки превышает установленное значение query_cache_limit, то запись прекращается, а занятое память освобождается.

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

Для дефрагментации кэша используется команда:

mysql> FLUSH QUERY CACHE;

Для оценки эффективности работы кэша используется формула Qcache_hits / (Qcache_hits + Com_select).

codebeer.ru

Memcached — стратегия кеширования / Habr

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

Memcached применяется для кеширования данных. Это делается для того, чтобы избежать лишних обращений к базе данных, т.е. в Memcached сохраняют результаты запросов. Это ускоряет работу сайта и уменьшают время выдачи страниц.
Кеш кроме преимуществ имеет свои недостатки. Одна из проблем кеша — это его актуальность. В режиме работы «только чтение» трудностей не возникает. Если же мы имеем дело с данными, которые изменяются, или изменяются часто, то эффективность кеширования резко падает. Чем чаще данные меняются, тем менее эффективен кеш. Обычно кеш сбрасывается после первого же изменения. Причем происходит сброс сразу всех закешированных данных. После сброса запросы идут к базе данных и по-новой происходит наполнение кеша. Если еще одно изменение — то кеш снова сбрасывается. Часто оказывается, что такая хорошая вещь как memcached не приносит никакой пользы для производительности сервера, и к тому же влечет за собой еще дополнительные расходы на память, процессорное время.
Один из методов решения данной проблемы — это логическое разделение кеша на независимые части. Если сброс кеша происходит, то только для той части, которая изменилась.

Рассмотрим один из таких подходов в связке Memcached — БД

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

Давайте разделим все обращения к базе по таблицам.

Допустим есть у нас запрос с обращением к нескольким таблицам. Мы берем запрос, анализируем какие в нем таблицы, смотрим, изменились ли в таблице данные. Если данные изменились, то кеш для запроса тоже обновляем. Звучит немного сложновато, возникают вопросы — каким образом это все делать, но в конечном счете реализация довольно простая.

Приступим к наброскам:

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

Вот и все сложности с таким подходом. Чтобы перейти к новой политике кеширования нам достаточно внести небольшие изменения в код. Пример, демонстрирующий этот подход, предоставлен ниже. Этот пример полностью самостоятельный и может быть выполнен если у вас есть PHP с поддержкой расширений mysql и memcache.
Такой подход увеличивает эффективность кеширования данных. При сбросе кеша удаляются только те данные, которые относятся к измененным таблицам. Если быть более конкретным, то слова «сброс кеша» теряют смысл, измененные данные становятся недоступными и продолжается наполнение кеша по новым ключам для тех же запросов. Если у вас есть «гадкая» таблица, из-за которой часто сбрасывается весь кеш, то теперь такая таблица не будет портить вам всю картину.

Метод жизнеспособен, он был опробован на одном из сайтов(http://www.skachatreferat.ru). Опыт показал, что не следует пренебрегать другими методами кеширования. Что для данных, чья актуальность не критична при частоте обновления раз в 5 минут, лучше применять самое простое кеширование с установкой времени жизни кеша в заданный период, в данном случае это 5 минут.

Возьмем habrahabr, который предоставляет доступ к статьям. Здесь каждая статья представляет собой текстовое поле и набор каких-то атрибутов. Текст меняется редко, в то время как атрибуты статьи меняются часто. По этой причине есть смысл поместить только текст статьи в кеш, а атрибуты независимо выбирать из таблиц. В результате скорость доступа к данным выростает на порядок.

Чем меньше столбцов мы выбираем, тем лучше для производительности. MySQL работает со столбцами с данными простого типа на порядок быстрее, чем со столбцам типа TEXT(где у нас хранится текст статьи). За счет использования этих особенностей достигается значительный выигрыш в производительности.

Ниже расположен скрипт для демонстрации метода разделения кеша по таблицам, исходник которого был вам обещан. Скрипт полностью самостоятельный и не требует каких-либо дополнительных модулей. Не забудьте указать данные для mysql и memcache в начале скрипта:

<? header('Content-type: text/html; charset=UTF-8'); $mysql_host='localhost'; $mysql_username='root'; $mysql_password='12345'; $mysql_database='test'; //укажите имена двух таблиц, эти таблицы не изменяются в этом примере $mysql_table1='table1'; $mysql_table2='table2'; $memcache_host='localhost'; $memcache_port=11211; $mysql=mysql_connect($mysql_host,$mysql_username,$mysql_password); if(!$mysql) die("Невозможно подсоединиться к MySQL: $mysql_username@$mysql_host/$mysql_password"); if(!mysql_select_db($mysql_database)) die("Невозможно подсоединиться к базе данных: $mysql_database"); $memcache = new Memcache; if(!$memcache->pconnect($memcache_host,$memcache_port)) die("Memcached не доступен: $memcache_host:$memcache_port"); function cacheGet($key) { global $memcache; return $memcache->get($key); } function cacheSet($key,$data,$delay) { global $memcache; return $memcache->set($key,$data,0,$delay); } function sqlExtractTables(&$query) { preg_match_all("/\\<\\<([A-Za-z0-9\\_]+)\\>\\>/",$query,$tables); if(!$tables[1]) die("Запрос не содержит таблиц, доступные для распознавания вида '<<table_name>>': $query"); $query=preg_replace("/\\<\\<([A-Za-z0-9\\_]+)\\>\\>/","\\1",$query); return $tables[1]; } function sqlQuery($query) { $resource=mysql_query($query); if(!$resource) die("Неправильный запрос: $query <br> ".mysql_error()); echo "<b>Запрос был выполнен:</b> $query<br>"; return $resource; } function sqlSet($query) { $tables=sqlExtractTables($query); foreach ($tables as $table) cacheSet($table,uniqid(time(),true),24*3600); return sqlQuery($query); } function sqlGet($query) { $tables=sqlExtractTables($query); foreach ($tables as $table) $appendix.=cacheGet($table); $appendix="/*".md5($appendix)."*/"; $query=$query.$appendix; $cache_key=md5($query); $result=cacheGet($cache_key); if($result!==false) { echo "<b>Попадание в кеш:</b> $query<br>"; return $result; } else echo "<b>Кеш не сработал:</b> $query<br>"; $resource=sqlQuery($query); $result=array(); while ($row = mysql_fetch_assoc($resource)) { $result[]=$row; } cacheSet($cache_key,$result,3600); return $result; } ?> <h3>Демонстрация. Разделение кешированных запросов по таблицам</h3> <h4>Делаем 2 запроса</h4> <? sqlGet("select * from <<$mysql_table1>> limit 1"); //обычно это селекты вида "select * from <<$mysql_table1>> where id=1", здесь так дано чтобы не надо было привязываться к конкретным столбцам ?><br><? sqlGet("select * from <<$mysql_table2>> limit 1"); ?> <h4>Меняем одну из таблиц</h4> <? sqlSet("delete from <<$mysql_table2>> where 1=0"); ?> <h4>Выполняем те же запросы опять</h4> <? sqlGet("select * from <<$mysql_table1>> limit 1"); ?><br><? sqlGet("select * from <<$mysql_table2>> limit 1"); ?> <h4>Результат: второй запрос должен быть выполнен снова, минуя кеш. Первый запрос продолжает браться из кеша</h4>

исходник здесь: www.skachatreferat.ru/demo.txt