webew
Войти » Регистрация
 
MySQL/MariaDB :: оптимизация

Кэширование запросов в MySQL

17 августа 2008, 21:02

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

Встроенный механизм кэширования запросов в MySQL.

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

mysql> show variables like 'query_cache%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 0       |
| query_cache_type             | ON      |
| query_cache_wlock_invalidate | OFF     |
+------------------------------+---------+

Чтобы включить кэш запросов и выделить под него 32 мегабайта памяти можно выполнить запрос set @@global.query_cache_size=32*1024*1024; с правами суперпользователя, а чтобы сделать эту настройку постоянной, следует добавить в my.cnf в подраздел [mysqld] строчку query_cache_size=32M. Второй полезный параметр - query_cache_limit задает максимальный объем результата выполнения запроса, который может быть помещен в кэш

После включения кэш работает автоматически:

  • При каждом запросе типа SELECT вычисляет хэш-сумму строки запроса и ищет ее в кэше. Если находит - возвращает рузельтат из кэша, если нет - выполняет запрос, а результат заносит в кэш (если результат не больше значения query_cache_limit).
  • При каждом запросе типа UPDATE, REPLACE, INSERT, DELETE, TRUNCATE или ALTER, удаляет из кэша все запросы, использующие таблицу, подвергшуюся обновлению.

Отметим следующие особенности работы кэша:

  • Различие запросов определяется буквально, сравнение чувствительно к реестру. Поэтому SELECT * FROM news и select * FROM news будут для кэша двумя разными запросами.
  • В кэш всегда попадает результат выполнения запроса целиком, результаты выполнения подзапросов не кэшируются.
  • Кэш работает одинаково для запросов к таблицам с различными механизмами хранения. MySQL также кэширует запросы SELECT к представлениям (VIEW).
  • Ряд запросов не подлежит кэшированию:
    • Запросы, содержащие одну из недетерминированных функций: NOW(), SLEEP(), RAND(), CURTIME(), LAST_INSERT_ID() и.др.
    • Запросы, использующие функции или хранимые процедуры, определенные пользователем.
    • Запросы, использующие значения пользовательских или локальных переменных.
    • Запросы, обращающиеся к базам данных mysql, INFORMATION_SCHEMA или performance_schema.
    • Запросы, обращающиеся к таблицам, разбитым на партиции (начиная с MySQL версий 5.1.63, 5.5.23, 5.6.5).
    • Запросы типа SELECT ... FOR UPDATE, SELECT ... IN SHARE MODE, SELECT ... INTO OUTFILE, SELECT ... INTO DUMPFILE, SELECT * FROM ... WHERE autoincrement_col IS NULL.
    • Запросы, использующие временные таблицы.
    • Запросы, не обращающиеся к таблицам.
    • Запросы, которые генерируют предупреждения (warnings).
    • В случае, если пользователь имеет права не на всю таблицу, а только на определенные колонки таблицы. Это исключение — следствие того, что кэш запросов один для всех пользователей, а права доступа средствами кэша проверяются лишь на уровне таблиц.

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

SELECT t.*, (SELECT body FROM entities WHERE id=t.lastid) body
                FROM
                  (SELECT e2.type,e2.id,e2.title,e2.active,
                  count(*)-IF(e2.type=3,1,0) cnt, MAX(e.id) lastid, MAX(e.created) lastcreated
                  FROM entities e
                  LEFT JOIN entities e2
                  ON e2.id=IF(e.foreparent,e.foreparent,e.id)
                  WHERE e.active = 1 AND e2.active = 1 AND e.type=3
                  GROUP BY e2.id
                  ORDER BY MAX(e.created) DESC LIMIT 10) t
ORDER BY lastcreated DESC

Запрос выполняет сортировку статей и тем по дате последнего комментария. Обращения к главной странице сайта происходят чаще, чем добавление комментариев или статей, поэтому кэш достаточно эффективен.

Замечание: если системная переменная query_cache_wlock_invalidate принимает значение OFF, то блокировка таблицы для записи не сбрасывает кэшированные запросы с участием данной таблицы. В этом случае через query cache будет возможно получение данных из заблокированной таблицы.

Текущее состояние кэша

Посмотреть состояние кэша можно с помощью запроса:

mysql> SHOW GLOBAL STATUS LIKE 'Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 973      |
| Qcache_free_memory      | 14282000 |
| Qcache_hits             | 3293750  |
| Qcache_inserts          | 252819   |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 66645    |
| Qcache_queries_in_cache | 1342     |
| Qcache_total_blocks     | 3709     |
+-------------------------+----------+
8 rows in set (0.00 sec)

Здесь:

  • Qcache_free_memory - объем свободной памяти, отведенной под кэш.
  • Qcache_hits - количество запросов, отработанных из кэша.
  • Qcache_inserts - количество вставок запросов в кэш.
  • Qcache_lowmem_prunes - количество высвобождений памяти из-за наполненности кэша.
  • Qcache_not_cached - количество запросов, не подлежащих кэшированию.
  • Qcache_queries_in_cache - количество запросов, находящихся в кэше в настоящее время.

Мерой эффективности кэша может служить отношение Qcache_hits / (Qcache_inserts + Qcache_not_cached).

Как избежать использования кэша?

В ряде случаев, желательно не использовать кэш. Наиболее распространены две ситуации:

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

Для выполнения запроса без использования кэша применяется директива SQL_NO_CACHE, которую помещают сразу после оператора SELECT. Например:

SELECT SQL_NO_CACHE id,title,author FROM news where message like '%швабра%';
Замечание. Если сервер исполняет большое количество быстрых запросов, кэш может оказывать негативное влияние на производительность. Так как кэш обрабытывает запросы только последовательно, связанная с ним часть исполнения запроса будет использовать только одно ядро процессора. Если кэш приводит к снижению производительности, его нужно отключить в конфигурационном файле.

Альтернативные способы кэширования

Несмотря на то, что встроенный кэш запросов представляет мощный и ясный механизм, в ряде случаев он неприменим. Опишем наиболее распространенные:

  • Запрос использует пользовательские функции, но тем не менее является детерменистическим.
  • Оптимизация работы подзапросов потребовала разбить запрос на два с использованием временных таблиц.
  • Таблица часто обновляется, аннулируя кэш, но не обязательно делать актуальную выборку.
  • В таблице часто обновляются поля, не использованные в запросе (что все равно аннулирует кэш запросов к данной таблице).

Последняя причина достаточно распространена в рунете и обычно является ошибкой проектирования. Например, многие реализуют простой счетчик просмотров статьи (каюсь, и я так делал, но сейчас использую Бревно) в виде запроса UPDATE articles SET viewcount=viewcount+1 WHERE id=542. Данный запрос при каждом просмотре статьи обновляет счетчик и вместе с тем удаляет все кэшированные запросы к таблице articles. В ряде случаев, данная ошибка приводит к потере производительности в десятки раз. Для эффективного использования кэша следует выносить часто обновляемые поля в отдельную таблицу.

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

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

SELECT (create_time > now() - INTERVAL 1 MINUTE) fresh FROM cache_mainpage_query LIMIT 1;

Анализируем значение fresh в полученной строке. Если fresh=0 или произошла ошибка, значит запрос следует выполнить и внести в MEMORY-таблицу:

DROP TABLE IF EXISTS 'cache_mainpage_query';
CREATE TABLE cache_mainpage_query ENGINE=MEMORY
        SELECT now() AS create_time, ... FROM ... WHERE ... ORDER BY ... LIMIT ...;

Затем, как в случае c=0, так и в случае c=1, выполняем запрос и возвращаем результат клиенту:

SELECT * FROM cache_mainpage_query ORDER BY ...;
Замечание. В рассмотренном примере время создания таблицы заносится в каждую строчку временной таблицы (колонка create_time). Это неэффективно по занимаемому месту в памяти, поэтому если объем памяти станет узким местом, следует перенести время кэширования в отдельную таблицу.

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

SET @c:=0;
CREATE TABLE cache_mainpage_query ENGINE=MEMORY
        SELECT now() AS create_time, @c:=@c+1 AS ord, ... FROM ... WHERE ... ORDER BY ... LIMIT ...;

Выборка в этом случае упрощается:

SELECT * FROM cache_mainpage_query ORDER BY ord;

С MEMORY-таблицами связано одно ограничение: в таких таблицах нельзя хранить поля типа BLOB/TEXT. Если Вам требуется хранить поля такого типа, можно либо создать MEMORY-таблицу явно с полями типа varchar() длиной до 65535 символов, либо использовать таблицы типа MyISAM.

Мы рассмотрели далеко не все варианты альтернативного кэширования, например, за кадром оказалось кэширование с помощью memcached и кэширование готовых кусков HTML. Напишите в комментариях, какие темы из области оптимизации производительности MySQL вам интересны.

Статья написана по материалам онлайн-курса «Оптимизация производительности MySQL».


© Все права на данную статью принадлежат порталу webew.ru. Перепечатка в интернет-изданиях разрешается только с указанием автора и прямой ссылки на оригинальную статью. Перепечатка в печатных изданиях допускается только с разрешения редакции.
Добавить комментарий
Отображение комментариев: Древовидное | Плоское

bur

Отличная подробная статья, автору респект!

Пара вопросов:
1) Какой реальный выйгрыш дает кэширование результата запросов, хотя бы в относительных величинах, т.к. в абсолютных, как я понимаю, всё зависит от нагруженности сервера, сложности запроса и др. факторов.

2) Не слишком ли сложна схема с MEMORY-таблицами? Неужели отдача от MEMORY-таблицы (хоть и готового результата) настолько быстрее, чем отдача некешированного результата от MyISAM-таблицы?

3) Я храню изображения в базе. Большую часть изображений можно кешировать на клиенте, с помощью Expires-заголовка HTTP-запроса. Однако, с некоторыми так не получается, например, с аватарками, или изображениями, которые часто меняются. Можно ли повысить скорость отдачи юзерпиков из базы с помощью MySQL-кеширования?
18.08.2008, 12:24
Ответить
NO USERPIC

rgbeast

1. Мы видели, что Qcache_hits / (Qcache_inserts + Qcache_not_cached) ~ 10, это по данным сервера webew.ru. Это значит, что только каждый десятый запрос реально выполняется. Выполнение запроса - это время от 1 мс до секунд в зависимости от сервера и запроса. Взять запрос из кэша - обычно не более 1-2 мс, просто взять данные и выдать. Далее все зависит от скорости запросов и работы других буфферов (innodb_buffer_pool, key_buffer). Если, скажем, запрос в среднем занимает 10 мс, то мы имеем выйгрыш в 10 раз, так как только каждый 10ый выполняется.

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

P.S. рекомендую смотреть не на длину SQL-запроса, а на время его выполнения и частоту выполнения. Необходимо учесть, что в таких запросах обычно ORDER BY + LIMIT (о чем я собираюсь написать отдельную статью скоро), то есть сортируется вся таблица - тысячи записей, а итог - десяток записей. Например, чтобы выбрать 10 лучших статей, нужно сложить все голоса за каждую из статей, затем отсортировать весь список по убыванию балла и только потом выбрать первые 10.

3. MySQL-кэширование будет работать здесь и может повысить производительность. Но в данном случае, можно обойтись без кэширования, так как выбор рисунков по ключу, а ОС должна закэшировать сам файл таблицы. Рисунки редко бывают причиной низкой производительности MySQL (я не говорю о сервисах типа fotki.yandex.ru, в этих случаях нужны индивидуальные решения). Замечу, что кэш запросов работает по умолчанию, если он включен, так что дополнительных действий выполнять не надо.
18.08.2008, 12:41
Ответить

bur

Спасибо!

Кстати, как правильно подобрать размер кеша? На каких нагрузках до каких пределов его следует увеличивать?
18.08.2008, 12:51
Ответить
NO USERPIC

rgbeast

Сейчас память недорогая. Можно выставить 64M, подождать день и посмотреть статус Qcache_free_memory и Qcache_lowmem_prunes. В примере выше видно, что кэш недозаполнен и не было нехватки памяти, значит он вместил все запросы, которые хотел (это нормальная ситуация). Скорость работы кэша практически не снижается при росте его объема (логарифмическая зависимость, так как поиск по хэшу запроса).
18.08.2008, 12:57
Ответить
NO USERPIC

timon1410

Спасибо за статью!

Кстати, насчет кэширования в memory таблицах: Вы в курсе, что запрос вида
CREATE TABLE cache_mainpage_query SELECT ... FROM T1 ...;

вызовет блокировку слдеющего ключа индекса таблицы T1 (где Т1 - innodb таблица, a cache_mainpage_query - таблица любого типа)?
(если кто не знал, то вот решение)
18.08.2008, 22:56
Ответить
NO USERPIC

rgbeast

Ситуация усложняется тем, что переменная innodb_locks_unsafe_for_binlog недоступна для изменения внутри сессси (может быть изменена только в my.cnf). Если выставить innodb_locks_unsafe_for_binlog=1, то может нарушиться репликация (в других запросах, не связанных с данной MEMORY-таблицей), если выставить 0, то будет блокировка о которой Вы писали. В случае, если T1 - MyISAM, блокировка будет на уровне таблицы T1 на чтение.
19.08.2008, 10:59
Ответить

1234ru

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

Хотел уточнить следующее:
Цитата:
Ряд запросов не подлежит кэшированию:
...
В случае, если пользователь имеет права не на всю таблицу, а только на определенные колонки таблицы. Это исключение — следствие того, что кэш запросов один для всех пользователей, а права доступа средствами кэша проверяются лишь на уровне таблиц.


Будет ли работать кэш в случае, если запрос от некоторого пользователя обращается к представлению (VIEW), на чтение которого пользователь имеет права, но само представление составлено из отдельных колонок таблицы, на чтение которой этот пользователь прав не имеет, а имеет права лишь на чтение некоторых её колонок?
То, что не убивает нас, делает нас инвалидами.
26.08.2008, 06:57
Ответить
NO USERPIC

rgbeast

Это можно легко проверить эмпирически на пустом сервере, наблюдая за значением статусной переменной Qcache_queries_in_cache;

Правда у меня в MySQL 5.0.45 пользователь, имеющий права только на некоторые колонки, не может вообще использовать VIEW (даже если VIEW использует только разрешенные колонки). Как видно, это одно из ограничений VIEW - проверка привилегий на уровне таблиц.
26.08.2008, 13:12
Ответить
NO USERPIC

akuba

присоединяюсь к благодарностям - реально дает ощутимый эффект, что было проверено на паре тяжелых проектов (http://all-ebooks.com)
18.11.2008, 14:02
Ответить
NO USERPIC

romank

в случае с MEMORY таблицами запрос
"SELECT count(*) c FROM INFORMATION_SCHEMA.TABLES
WHERE CREATE_TIME > NOW() - INTERVAL 1 MINUTE
AND TABLE_SCHEMA='webew' AND TABLE_NAME='cache_mainpagequery';"

никогда не вернет ни одной записи (по крайней мере на моей версии mysql 5.0.51a-24), так как для мемори-таблиц поле create_time всегда равно NULL. И поэтому никакого выигрыша в скорости выполнения не будет.
26.02.2009, 03:24
Ответить
NO USERPIC

macrob

Согласен с romank на счет create_time для мемори
Server version: 5.4.1-beta
Macrob
22.07.2009, 15:04
Ответить
NO USERPIC

mylogin

Подскажите пожалуйста. Если количество записей в таблице не превышает 100, и запросы обычные, снизит ли кэширование нагрузку на сервер или наоборот повысит? И насколько примерно?
05.10.2009, 19:33
Ответить

1234ru

Зависит от конкретного случая, но, как правило, операции с таблицей такого размера не оказывают заметного влияния на загруженность сервера и беспокоиться об их оптимизации нет необходимости.
То, что не убивает нас, делает нас инвалидами.
06.10.2009, 00:18
Ответить
NO USERPIC

mylogin

Спасибо. Но вообще даже хоть какой-то выигрыш был бы ценен, так как количество самих таблиц будет доходить до 300-400 и суммарная полезность могла бы быть значимой.
06.10.2009, 06:59
Ответить
NO USERPIC

rgbeast

Попробуйте ипользовать Innodb, сделать достаточно большой innodb_buffer_pool_size и отключить кэширование запросов.
06.10.2009, 10:29
Ответить
NO USERPIC

rgbeast

Большое спасибо за комментарий. Упустил это из виду. Модифицировал пример в статье.
26.10.2009, 01:01
Ответить
NO USERPIC

weec

имеет ли смысл использовать Qcache на SMP системах?
16.11.2009, 16:44
Ответить
NO USERPIC

rgbeast

Ответ сильно зависит от запросов. Если все запросы быстрые, то без qcache будет лучше работать многопоточность (см. доклад Констатина Осипова на Highload++ 2008). Если есть долгие запросы, то SMP не поможет, так как они часто требуют интенсивных дисковых операций, поэтому использование Qcache оправдано.
17.11.2009, 23:31
Ответить
Добавить комментарий
Отображение комментариев: Древовидное | Плоское
© 2008—2024 webew.ru, связаться: x собака webew.ru
Сайт использует Flede и соответствует стандартам WAI-WCAG 1.0 на уровне A.
Rambler's Top100

Реклама: