Кеш запросов размер – 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 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

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

Кэш планов и параметризация запросов. Часть 1. Анализ кэша планов.

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

Для начала давайте в общих чертах посмотрим, как происходит выполнение SQL-запросов сервером Microsoft SQL. Процессор запросов (query processor), который и занимается выполнением SQL-запросов, поступивших на SQL-сервер, и выдачей их результатов клиенту, состоит из двух основных компонентов:

1. Оптимизатор запросов (Query Optimizer).
2. Исполнитель запросов (Relational Engine).

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

1. Синтаксический анализатор (Parser) просматривает инструкцию SELECT и разбивает ее на логические единицы, такие как ключевые слова, выражения, операторы и идентификаторы, а также производит нормализацию запроса.
2. Из синтаксического анализатора данные попадают на вход компонента Algebrizer, который выполняет семантический анализ текста. Algebrizer проверяет существование указанных в запросе объектов базы данных и их полей, корректность использования операторов и выражений запроса, и извлекает из кода запроса литералы, для обеспечения возможности использования автоматической параметризации.
   Например, именно поэтому запрос, имеющий в секции SELECT поля, не содержащиеся ни в агрегатных функциях, ни в секции GROUP BY, пройдёт в SQL Server Management Studio (SSMS) проверку по Ctrl+F5 (синтаксический анализ), но свалится с ошибкой при попытке запуска по F5 (не пройдёт семантический анализ).
3. Далее Algebrizer строит дерево разбора запроса с описанием логических шагов, необходимых для преобразования исходных данных к желаемому результату. Для дерева запроса извлекаются метаданные объектов запроса (типы данных, статистика индексов и т.д.), производятся неявные преобразования типов (при необходимости), удаляются избыточные операции (например, ненужные или избыточные соединения таблиц).
4. Затем оптимизатор запросов анализирует различные способы, с помощью которых можно обратиться к исходным таблицам. И выбирает ряд шагов, которые, по мнению оптимизатора, возвращают результаты быстрее всего и используют меньше ресурсов. В дерево запроса записывается последовательность этих полученных шагов и из конечной, оптимизированной версии дерева генерируется план выполнения запроса.

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

club.directum.ru

MySQL Query Cache | MySQL | Статьи | Программирование Realcoding.Net

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

Автор: http://tuta_larson.habrahabr.ru/

www.realcoding.net

MySQL Query Cache | MySQL | Статьи | Программирование Realcoding.Net

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

Автор: http://tuta_larson.habrahabr.ru/

www.realcoding.net