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

Мое приложение очень интенсивно работает с базой данных, поэтому я очень старался убедиться, что приложение и база данных 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-кода.

Ответы

Ответ 1

Обычно предупреждения "слишком большого размера кеша" выпускаются в предположении, что у вас мало физической памяти, и сам кеш-память нуждается в замене или потребует ресурсы, которые требуются OS (например, кэш файлов).

Если у вас достаточно памяти, безопасно увеличивать query_cache size (я видел установки с кешем запросов 1GB).

Но вы уверены, что используете кеш запросов правильно? У вас есть много дословных повторяющихся запросов? Не могли бы вы разместить пример типичного запроса?

Ответ 2

Предупреждение, выданное mysqltuner.py, действительно актуально, даже если ваш кеш не подвержен обмену местами. Это хорошо объяснено в следующем: http://blogs.oracle.com/dlutz/entry/mysql_query_cache_sizing

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

Ответ 3

Вам нужно будет легко увеличивать кеш, это не только "не так много доступной памяти"!

В качестве примера прочитайте руководство:

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

Есть различные другие источники вы можете проверить

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

Поэтому не ставьте столько, сколько можете в этом кеше запросов!

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

Ответ 4

Будьте осторожны с установкой query_cache_size и ограничьте ее высотой. MySQL использует только один поток для чтения из кеша запросов.

С query_cache_size, установленным на 4G и query_cache_limit 12M, мы имели показатель кеша запросов 85%, но заметили повторяющиеся всплески в соединениях.

После изменения query_cache_size до 256M с 64K query_cache_limit коэффициент кэша запросов снизился до 50%, но общая производительность увеличилась.

Ответ 5

Кэш кэша Query составляет около 10%, поэтому я бы отключил кеширование запросов. Обычно, если вы не можете получить свой рейтинг хитов более 40 или 50%, возможно, кэш запросов не подходит для вашей базы данных.

У меня есть блог об этой теме... Здесь показана производительность Mysql query_cache_size.

Ответ 6

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