Home » Как оптимизировать MySQL с помощью Query Cache на Ubuntu 24

Как оптимизировать MySQL с помощью Query Cache на Ubuntu 24

Если когда-либо задавались вопросом, почему ваш MySQL сервер работает медленнее, чем у соседа, то эта статья для вас. Сегодня разберём Query Cache — одну из самых недооценённых фич MySQL, которая может серьёзно ускорить работу вашего сервера на Ubuntu 24. Это не очередная теоретическая статья, а практическое руководство с реальными примерами и кодом.

Query Cache — это встроенный механизм кэширования запросов в MySQL, который сохраняет результаты SELECT-запросов и повторно их использует при идентичных обращениях. Звучит просто, но дьявол, как всегда, в деталях.

Как работает Query Cache: под капотом

Query Cache работает по принципу “запрос-ответ”. Когда MySQL получает SELECT-запрос, он сначала проверяет, есть ли уже готовый результат в кэше. Если есть — отдаёт его моментально, минуя весь процесс парсинга, оптимизации и выполнения.

Но есть нюансы:

Запросы должны быть абсолютно идентичными (даже пробел имеет значение)
Кэш инвалидируется при любом изменении таблиц
Не кэшируются запросы с функциями NOW(), RAND() и подобными
Не работает с временными таблицами и хранимыми процедурами

Пошаговая настройка Query Cache на Ubuntu 24

Начнём с проверки текущего состояния:

mysql -u root -p
SHOW VARIABLES LIKE 'query_cache%';
SHOW STATUS LIKE 'Qcache%';

Теперь редактируем конфигурационный файл MySQL:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Добавляем в секцию [mysqld]:

[mysqld]
query_cache_type = 1
query_cache_size = 256M
query_cache_limit = 4M
query_cache_min_res_unit = 2k

Перезапускаем MySQL:

sudo systemctl restart mysql
sudo systemctl status mysql

Проверяем, что всё работает:

mysql -u root -p -e "SHOW VARIABLES LIKE 'query_cache%';"

Настройка параметров: что и как

Параметр	Рекомендуемое значение	Описание
query_cache_type	1	0 = выключен, 1 = включен, 2 = по запросу
query_cache_size	64M-512M	Размер кэша в памяти
query_cache_limit	1M-8M	Максимальный размер кэшируемого результата
query_cache_min_res_unit	2k-8k	Минимальный размер блока в кэше

Мониторинг и оптимизация

Создаём скрипт для мониторинга эффективности:

#!/bin/bash
# query_cache_monitor.sh
mysql -u root -p -e "
SELECT 
    ROUND(Qcache_hits / (Qcache_hits + Com_select) * 100, 2) as hit_rate,
    Qcache_hits,
    Com_select,
    Qcache_inserts,
    Qcache_lowmem_prunes,
    Qcache_free_memory,
    Qcache_total_blocks
FROM 
    (SELECT VARIABLE_VALUE as Qcache_hits FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Qcache_hits') as h,
    (SELECT VARIABLE_VALUE as Com_select FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Com_select') as s,
    (SELECT VARIABLE_VALUE as Qcache_inserts FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Qcache_inserts') as i,
    (SELECT VARIABLE_VALUE as Qcache_lowmem_prunes FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Qcache_lowmem_prunes') as p,
    (SELECT VARIABLE_VALUE as Qcache_free_memory FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Qcache_free_memory') as f,
    (SELECT VARIABLE_VALUE as Qcache_total_blocks FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Qcache_total_blocks') as b;
"

Делаем скрипт исполняемым:

chmod +x query_cache_monitor.sh
./query_cache_monitor.sh

Практические примеры и кейсы

Положительный кейс: Интернет-магазин с каталогом товаров

Запросы типа “SELECT * FROM products WHERE category_id = 5” выполняются часто и результаты редко меняются. Query Cache даёт прирост производительности в 3-5 раз.

Отрицательный кейс: Высоконагруженное приложение с частыми UPDATE

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

Пример настройки для разных сценариев:

# Для читающих приложений (блоги, каталоги)
query_cache_size = 512M
query_cache_limit = 8M

# Для смешанных нагрузок
query_cache_size = 128M
query_cache_limit = 2M

# Для высоконагруженных OLTP
query_cache_type = 0  # лучше отключить

Альтернативные решения и сравнение

Решение	Преимущества	Недостатки
MySQL Query Cache	Встроенный, прозрачный	Устарел, удалён в MySQL 8.0
Redis	Гибкий, быстрый	Требует изменения кода
Memcached	Простой, надёжный	Только кэш, нет персистентности
ProxySQL	Продвинутое кэширование	Сложность настройки

Интеграция с другими инструментами

Query Cache отлично работает в связке с:

Nginx — кэширование на уровне веб-сервера
PHP OPcache — кэширование скомпилированного кода
Varnish — HTTP-кэширование

Пример настройки мониторинга через Prometheus:

# prometheus_mysql_exporter.yml
scrape_configs:
  - job_name: 'mysql'
    static_configs:
      - targets: ['localhost:9104']
    metrics_path: /metrics
    scrape_interval: 15s

Автоматизация и скрипты

Скрипт для автоматической настройки Query Cache:

#!/bin/bash
# auto_query_cache_setup.sh

# Определяем объём RAM
RAM=$(free -m | awk 'NR==2{print $2}')
QC_SIZE=$((RAM / 8))  # 12.5% от RAM

# Бэкапим конфиг
cp /etc/mysql/mysql.conf.d/mysqld.cnf /etc/mysql/mysql.conf.d/mysqld.cnf.bak

# Добавляем настройки
cat >> /etc/mysql/mysql.conf.d/mysqld.cnf << EOF

# Query Cache Settings (auto-generated)
query_cache_type = 1
query_cache_size = ${QC_SIZE}M
query_cache_limit = 4M
query_cache_min_res_unit = 2k
EOF

# Перезапускаем MySQL
systemctl restart mysql

echo "Query Cache настроен с размером ${QC_SIZE}MB"

Troubleshooting: частые проблемы

Проблема: Низкий hit rate (< 30%)

Решение: Увеличить query_cache_size или проверить частоту UPDATE-запросов

Проблема: Высокий Qcache_lowmem_prunes

Решение: Увеличить query_cache_size или уменьшить query_cache_limit

Проблема: Фрагментация кэша

Решение: Настроить query_cache_min_res_unit под размер ваших запросов

# Очистка кэша
FLUSH QUERY CACHE;
RESET QUERY CACHE;

Будущее Query Cache

Важно отметить, что Query Cache был удалён из MySQL 8.0 из-за проблем с масштабируемостью и конкуренцией потоков. Oracle рекомендует использовать:

InnoDB Buffer Pool для кэширования данных
Внешние решения вроде Redis или Memcached
Кэширование на уровне приложения

Для тех, кто планирует миграцию на MySQL 8.0, стоит заранее рассмотреть альтернативы.

Заключение и рекомендации

Query Cache — отличный инструмент для оптимизации MySQL 5.7 и более ранних версий, особенно для приложений с преобладающей читающей нагрузкой. Он может дать существенный прирост производительности при правильной настройке.

Рекомендации по использованию:

Используйте для блогов, каталогов товаров, новостных сайтов
Не используйте для высоконагруженных OLTP-систем
Регулярно мониторьте метрики эффективности
Планируйте миграцию на альтернативные решения при переходе на MySQL 8.0

Для экспериментов с Query Cache рекомендую взять VPS или выделенный сервер — так вы сможете безопасно тестировать настройки без риска для production-системы.

Полезные ссылки для дальнейшего изучения:

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

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