Кеш запросов MySQL: злейший враг или лучший друг?

database-mysql

Данная статья является переводом этого поста в блоге компании Percona.

В течение последних пары месяцев я участвовал в большом количестве проверок производительности приложений электронной коммерции работающих на платформе Magneto. Несмотря на то, что системы были разнообразны, они все имели одну общую особенность: кеш запросов (query cache) MySQL был очень полезен. Это было неожиданным для меня, так как я всегда считал кеш бутылочным горлышком. Я всегда придерживался политики отключения кеша запросов, чтобы уменьшить время отклика (response time) вне зависимости от специфики системы. Чтобы разобраться в данной ситуации и выяснить, когда кеш запросов MySQL может быть полезным, я решил провести серию тестов.

Про MySQL query cache

Кеш запросов MySQL известен своей конкурентностью. Глобальный мьютекс (mutual exclusion) должен быть получен для любой операции чтения или записи, т.е. любой доступ сериализуется. Это не было проблемой 15 лет назад, но сейчас, в эпоху многоядерных серверов, такая сериализация запросов приводит к значительной деградации производительности.

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

Простой тест

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

  • 1М записей были вставлены в 16 таблиц.
  • Средная нагрузка на запись (65 апдейтов в cекунду) была создана с помощью модифицированной версии скрипта тестирования производительности update_index.lua (код можно увидеть в конце статьи).
  • Скрипт select.lua был запущен с разными параметрами –num-threads.

Учтите, что тест разработан, чтобы не давать преимуществ кешу запросов, т.к. все данные помещаются в буфер (buffer pool) и SELECT запросы очень просты. Также заметьте, что я установил кеш запросов достаточно большим, чтобы записи не удалялись из кеша из-за нехватки памяти.

Результаты – кеш запросов MySQL ВКЛЮЧЕН

qcache_on

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

Результаты – кеш запросов MySQL ОТКЛЮЧЕН

qcache_off

Совсем другую картину мы видим при отключенном кеше запросов:

Пропускная способность хорошо масштабируется до 10-20 потоков (сервер, на котором проводилось тестирование был 16 ядерным). Но, что более важно, даже при большей конкурентности, общая пропускная способность продолжала увеличиваться: при 20 параллельных потоках MySQL обслуживал, примерно, в 3 раза больше запросов без использования кеша запросов.

Заключение

При использовании платформы Magneto(https://magento.com/) типичная нагрузка включает в себя малое количество записей в таблицы, очень малое количество потоков и довольно сложные SELECT запросы. Учитывая полученные нами результаты тестирования, не удивительно, что в таких условиях кеш запросов MySQL показал себя с хорошей стороны.

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

Приложение: тестовые скрипты