Пользователь
0,0
рейтинг
29 сентября 2008 в 11:12

Разработка → MySQL Query Cache

В 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, которые вы хотели бы прочитать.

Евгений @tuta_larson
карма
52,3
рейтинг 0,0
Реклама помогает поддерживать и развивать наши сервисы

Подробнее
Спецпроект

Самое читаемое Разработка

Комментарии (60)

  • +1
    MySQL определяет, равны ли первые три символа запроса «SEL»

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


    Не расскажете откуда такая информация? Первый раз про такое слышу.
    Даже проверил на всякий случай — у меня на сервере это утверждение не соответствует действительности, по крайней мере запрос

    /* test */ SELECT * FROM `prod` WHERE prodname LIKE 'пиво%' LIMIT 10


    благополучно забирается из кэша
    • +1
      хм… возможно mysql умнее чем вы думаете: он вырезает комментарии и обрамляющие пробелы в запросах.
      хотя это всего лишь мои домыслы:)
      • +2
        Возможно. А еще это может делать клиент, скорее всего в нем и дело.
    • 0
      Действительно странное дело. Информация эта была в выступлении Baron Schwartz'а на одной из конференций и нескольких других публикациях.
      Сам проверил сейчас — тоже работает все как нужно. Но это притоворечит логике — MySQL смотрит в кеш ДО парсинга запроса. Возможно, что это проделки клиента. Как выясню точно, напишу.
      • 0
        Проверил. В бинлог валится полный запрос вместе с начальным комментарием. Так что сомнения остаются и клиент ничего не делает (и правильно что не делает).

        Насчет логики, тут может быть все проще. Если упрощенно, то при поступлении запроса он очищается от конечных проблелов, потом от него берется какой либо ключ, допустим MD5, смотрится есть ли такой ключ в кэше, если есть — отдаем результат, если нет — процессим запрос (проверяем что он select, что нет now() etc), вычисляем, кладем в кэш вместе с ключем. Как то так. По крайней мере я был уверен что mysql именно так и делает :)
        • 0
          не бинлог, а query-лог, конечно
        • +1
          Заглянул в код. В коде все по написаному:
          /*
          Test if the query is a SELECT
          (pre-space is removed in dispatch_command)

          First '/' looks like comment before command it is not
          frequently appeared in real life, consequently we can
          check all such queries, too.
          */

          if ((my_toupper(system_charset_info, sql[i]) != 'S' ||
          my_toupper(system_charset_info, sql[i + 1]) != 'E' ||
          my_toupper(system_charset_info, sql[i + 2]) != 'L') &&
          sql[i] != '/')
          {
          DBUG_PRINT(«qcache», («The statement is not a SELECT; Not cached»));
          goto err;
          }

          При выборке из кеша идет именно проверка на эквивалентность первых символов запроса строке «SEL». Так что, все-таки — это происки клиента и комментарии в начале запроса писать не стоит.
          • +3
            Может у меня глаза уже замылилсь, но тут вроде написано, что
            если первые три буквы не SEL и первый символ не '/', то «The statement is not a SELECT; Not cached».

            Т. е. если первый символ '/' — то видимо обработка запроса где то дальше все таки произойдет, разве нет?

            В моем вольном переводе —
            начальный '/' указывающий на комментарий перед запросом не часто встречается в реальной жизни, поэтому мы проверяем все такие запросы тоже.


            Вообще стиль программирования — высший класс, один «goto err» чего стоит :)
          • +2
            кстати и про начальный пробел тоже явно указано — что он предварительно удаляется (а вы в статье пишете об обратном — что он влияет)
    • +2
      На самом деле Вы абсолютно правы.
      Такое поведение, которое описывает автор было характерно для MySQL версии до 4.1

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

      Чтобы не быть голословным, нашел линку, доказывающую мои слова — www.mysqlperformanceblog.com/2008/03/20/mysql-query-cache-whitespace-and-comments/
      • 0
        Ну вот, только мне пришла в голову мысль, что мы с комрадом tuta_larsen правы оба, и дело в версиях mysql и вы это подтвердили.
        Спасибо.

        Статью бы поправить (поставить ссылку на версию), а то хорошая же статья, а начинается с неактуальной для многих информации
        • 0
          На самом деле тут нужно еще кое-что про кеш добавить:

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

          SELECT
          p.*, c.*
          FROM
          (
          SELECT
          *
          FROM
          `products` as p
          WHERE
          p.category = 10
          ORDER BY
          p.price DESC
          LIMIT 100, 10
          ) as p
          JOIN customer as c
          ON p.cus_id = c.id

          И у вас в кеше есть результат для запроса
          SELECT
          *
          FROM
          `products` as p
          WHERE
          p.category = 10
          ORDER BY
          p.price DESC
          LIMIT 100, 10

          то, здесь для выполнения подзапроса кеш использоваться не будет.

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

          А что Вы где-то видели, что комментарии к статье и тело статьи хранят в одной таблице? Если кто-то так делает, то нужно начинать писать статьи не с темы кеширования запросов, а с темы нормализации БД. :-)

          • +1
            Так это вроде и понятно, но большой запрос то сам закэшируется и потом его будет отдаваться результат уже из кэша.
            Я так понимаю внутренний будет вычислятся потому, что он во первых может зависеть от внешнего, и во-вторых потому, что к моменту синтаксического анализа этого запроса, стадия отдачи из кэша уже пройдена. Причем второе еще можно как то поправить теоретически, а вот первое, имхо, нельзя — так как этого приведет к серьезному усложнению логики.

          • 0
            Ну да и последнее, что хотел добавить, и что обязательно нужно сказать — кеш мускула это не «серебрянная пуля» и не стоит на него очень сильно расчитывать, точнее, при правильной стратегии кеширования и конфигурационных параметров вы можете получить прирост производительности, но это не панацея.
            Также нужно всегда учитывать, что кеш мускула это централизованное хранилище, а не распределенное, т. е. на каждом сервере БД, свой кеш.
            Притом большой кеш мускула и большое кол-во запросов в нем, могут достаточно серьезно замедлить опперации на изменение данных (UPDATE/INSERT/DELETE)

            Поэтому использования Memcached вам как-правило не избежать :-)

            А MySQL кещ всего лишь один из уровней многоуровневого кеширования в веб приложениях.
            • 0
              А можно подробнее про «Притом большой кеш мускула и большое кол-во запросов в нем, могут достаточно серьезно замедлить опперации на изменение данных (UPDATE/INSERT/DELETE)»?

              Насколько я понимаю, удалить запись из кеша на запрос — это операция сложности O(1).
          • 0
            > А что Вы где-то видели, что комментарии к статье и тело статьи хранят в одной таблице?
            Я видел, когда вместе со статьями хранят счетчики комментов к ней (об этом и написано).
  • 0
    Замечательная статья на русском языке, очень странно, что разработчики популярных движков (CMS) совершенно об этом забывают.
    Потому постоянно встречаю в одной таблице и кол-во просмотров (то есть кеш запроса вообще отсутствует) и сумма рейтинга статьи, и кол-во комментариев, etc. Исправить, конечно, можно, но тогда и забываем об обновлении используемой системы.
    • 0
      Не пойму смысла выносить в отдельную таблицу post_id | comments_count | rating_points | что бы потом при показе поста объединять 2 таблицы…

      Или я чего то не понимаю?
      • 0
        когда на каждый просмотр страницы идет запрос «UPDATE article SET count_view = новое значение» — то кеширование запроса, который собственно выбирает текущую статью для просмотра пользователем — сбрасывается. Кеширования данных отсутствует как факт. Да и при некотором кол-ве посетителей нагрузка при JOIN comments_count (к примеру) будет лучше, нежели отсутствия кеша на глобальную таблицу article
        • +1
          А вы когда джойните вторую таблицу, но она была измененена, тоесть при многотаблицном селекте, когда одна (все) таблицы были изменены запрос разве закешируется?
          • 0
            Признаю свою ошибку, действительно, если хоть в одной таблице кешированного запроса что-либо меняется — кеш сбрасывается.
            Тогда имеет смысл рассмотреть два запроса (один для articles+кеш, второй только count), но по поводу производительности не скажу — надо будет потестировать на нагруженных системах
            • НЛО прилетело и опубликовало эту надпись здесь
          • –1
            Вот именно на это я и намекал :)
  • НЛО прилетело и опубликовало эту надпись здесь
    • НЛО прилетело и опубликовало эту надпись здесь
  • –7
    P.S. пишите в личку темы статей про MySQL, которые вы хотели бы прочитать.


    Разрешения спросить? :-)
  • НЛО прилетело и опубликовало эту надпись здесь
    • НЛО прилетело и опубликовало эту надпись здесь
  • –3
    «Многие СУБД имеют подобную функциональность, но в отличие от MySQL они кешируют планы выполнения запросов, тогда как MySQL кеширует результаты запросов.»

    Что-то как-то тут не срастается. Я про Oracle. Я думаю, что негоже писать о том, в чем не разбираешся.
    • –1
      В чем не срастается-то? «Многие» != «все»
      • +1
        В общем конечно справедливо, но для меня есть RDBMS Oracle, ну и всякие прочие… поделки. :)
  • –2
    А в чем соль статьи?

    Или это просто перевод?

    Если перевод, то где значок перевода?

    Я думал, тут что-то оригинальное…
  • 0
    Стало интересно насколько часто данные берутся из кеша у меня, запустил Putty:

    [root@v5311 ~]# mysql -u root -p
    Enter password:
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 238460
    Server version: 5.0.45 Source distribution
    
    Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
    
    mysql> SHOW GLOBAL STATUS LIKE 'Qcache%';
    +-------------------------+-------+
    | Variable_name           | Value |
    +-------------------------+-------+
    | Qcache_free_blocks      | 0     |
    | Qcache_free_memory      | 0     |
    | Qcache_hits             | 0     |
    | Qcache_inserts          | 0     |
    | Qcache_lowmem_prunes    | 0     |
    | Qcache_not_cached       | 0     |
    | Qcache_queries_in_cache | 0     |
    | Qcache_total_blocks     | 0     |
    +-------------------------+-------+
    8 rows in set (0.00 sec)

    Судя по всему кеш не работает. Подскажите, где и как его можно «включить».
    • НЛО прилетело и опубликовало эту надпись здесь
    • 0
      У меня такая же табличка по нулям, но кэш вроде как работает, потому что первый тяжелый запрос выполяняется за 10с, последующие такие же за 0.01
      • НЛО прилетело и опубликовало эту надпись здесь
        • +1
          fulltext поиск по 2 полям из ~2млн записей
          • НЛО прилетело и опубликовало эту надпись здесь
      • 0
        Просто первый раз mysql засасывает в буфер (в ОЗУ) страницы с диска, сколько может, Второй раз уже «бежит» по тем страницам, что попали в буфер. А еще и операционка кеширует файлы.
    • –1
      перечитайте статью, там написано :)
    • +1
      query_cache_type = (ON, DEMAND, OFF) — определяет включено ли кеширование или нет(ON, OFF). При использовании DEMAND кешироваться будут только запросы, в которых есть директива SQL_CACHE;

      т. е.

      query_cache_type = 1
      у вас написанно в my.cnf?
  • НЛО прилетело и опубликовало эту надпись здесь
  • 0
    а что такое Com_select? Первый раз встречается в формуле расчета эффективности, больше нигде не упоминается
    • 0
      Количество SELECT-запросов надо полагать
    • +1
      show global status like 'Com_select'; — показыват сколько запросов было непосредственно выполнено сервером, а не взято из кеша
      • 0
        Спасибо, теперь понятно.
        У мена получилось 34%, лучше чем ожидалось
  • 0
    Кэш запросов mysql живет своей жизнью, и, надеяться, как-то его переборость — бессмысленно. Удобнее кэшировать именно то, что нужно на уровне приложения или memchache. В таком случае, зная специфику обновления данных, можно гораздо точнее рулить кэшированием.
  • 0
    Сегодня с утра экспериментирую с кешем, по статье прирост страничка грузиться на от 0.5-1 сек. быстрее
    +-------------------------+-----------+
    | Variable_name           | Value     |
    +-------------------------+-----------+
    | Qcache_free_blocks      | 309       |
    | Qcache_free_memory      | 270714864 |
    | Qcache_hits             | 261275    |
    | Qcache_inserts          | 255794    |
    | Qcache_lowmem_prunes    | 0         |
    | Qcache_not_cached       | 1149609   |
    | Qcache_queries_in_cache | 577       |
    | Qcache_total_blocks     | 1551      |
    +-------------------------+-----------+
    8 rows in set (0.00 sec)
    
    

    вроде все понятно, но возможно будут какие-то коментарии?
    • 0
      смущает очень большое количество Qcache_not_cached и большое кол-во незанятой памяти
  • 0
    Хм, значит нет необходимости кэшировать запросы на уровне веб-приложения?
    • 0
      Вы не можете гарантировать, что запрос не будет вытеснен из кеша, тогда как на уровне приложения можете. Если это тяжелая выборка, и вы хотите, чтобы результат был в обязательно в кеше, то кешируйте на стороне приложения.
      • 0
        звучит вполне убедительно, спасибо
  • 0
    Одна из самых бесполезных вещей в мускуле. Вроде бы даже слышал что ее уберут в 6 или 7.
    Для проектов с большой нагрузкой мускуль кеш использовать бессполезно, ООЧЕНЬ не эффективно. Для остальных особой разницы и востребованности нет.
    • 0
      Не хотите обосновать данное утверждение?
      • 0
        Хотя бы по тому, что они достаточно глупые. Пример, мы в селекте выбираем А, создался кеш, потом пришел запрос на изменение Б(таже таблица), кеш селекта А испарился. В подавляющем большинстве проектов, высоконагруженых, такая инвалидация кеша не приемлима. Как Вы понимаете, все это можно и нужно делать гораздо умнее.
        • 0
          не убедили. разные задачи — разные решения.
          • 0
            даже не пытался, если у вас получиться на этой штуке что-нить построить, флаг, как говориться, в руки
  • 0
    Одна из самых бесполезных вещей в мускуле. Вроде бы даже слышал что ее уберут в 6 или 7.
    Для проектов с большой нагрузкой мускуль кеш использовать бессполезно, ООЧЕНЬ не эффективно. Для остальных особой разницы и востребованности нет.
    • 0
      сори, харб глючит
  • 0
    query_cache_limit — размер максимальной выборки, хранимой в кеше.

    А существует ли нижний порог? Или в кеш попадают любые резалт-сеты, пусть даже состоящие из 1 строки длиной в 1 символ?

Только зарегистрированные пользователи могут оставлять комментарии. Войдите, пожалуйста.