Resource Groups в MySQL

Привет, Хабр!

Сегодня мы рассмотрим Resource Groups в MySQL — и перестанем жить на одной CPU.

Resource Groups — это контроль над CPU прямо из SQL. Вы создаёте логическую группу, говорите «эта группа может использовать только 2 CPU и работать на низком приоритете», и назначаете туда тяжелые, но второстепенные задачи. Всё. Дальше MySQL сам всё регулирует.

Прежде чем начать

Сначала проверим, включена ли вообще эта фича:

SHOW VARIABLES LIKE 'resource_group_enabled';

Если ON — отлично. Если OFF, скорее всего:

• У вас macOS (там это не работает в принципе).

• Или у вас включён thread pool (он конфликтует).

• Или вы сидите на древней версии MySQL.

Если всё норм, едем дальше.

По дефолту есть две группы

SELECT * FROM INFORMATION_SCHEMA.RESOURCE_GROUPS\G

Получите вот такое:

RESOURCE_GROUP_NAME: USR_default RESOURCE_GROUP_TYPE: USER VCPU_IDS: 0-3 THREAD_PRIORITY: 0

То есть, если вы никогда ничего не настраивали, весь ваш код, все ваши запросы, API, ETL, BI, всё работает в одном тазике USR_default. Без приоритета и без ограничений.

Наведём порядок

Допустим, у нас есть ETL-запросы или отчёты из Metabase, которым ни к чему мешать пользователям. Хотим их ограничить по CPU и поставить в конец очереди.

Создаём:

CREATE RESOURCE GROUP etl_group   TYPE = USER   VCPU = 2-3   THREAD_PRIORITY = 15;

Переводим на русский:

• VCPU = 2-3 — используем только два ядра.

• THREAD_PRIORITY = 15 — низкий приоритет (где 0 — норм, 19 — спи спокойно, малыш).

Назначаем сессию в группу

Узнали CONNECTION_ID() и назначаем поток:

SET RESOURCE GROUP etl_group FOR 123;

Или если хотим назначить себе прямо сейчас:

SET RESOURCE GROUP etl_group;

Теперь SQL будет отрабатывать их не мешая никому.

Но можно и точечно — только один запрос

SELECT /*+ RESOURCE_GROUP(etl_group) */ COUNT(*) FROM big_table;

Всё, что внутри запроса — будет выполнено с ограничениями группы.

Проверим, кто куда попал

SELECT THREAD_ID, RESOURCE_GROUP_NAME FROM performance_schema.threads WHERE PROCESSLIST_ID = CONNECTION_ID();

Если всё ок — будете видеть etl_group как текущую группу.

Как менять группу на ходу

Ночью можно дать больше приоритета:

ALTER RESOURCE GROUP etl_group   VCPU = 0-3,   THREAD_PRIORITY = 5;

Днём снова поджали:

ALTER RESOURCE GROUP etl_group   VCPU = 2,   THREAD_PRIORITY = 18;

А если не помогает — значит нет прав

Да, нужны привилегии. Вот как их выдать:

GRANT RESOURCE_GROUP_ADMIN ON *.* TO 'my_admin'@'%';

Тогда можно будет создавать и менять группы. А если просто использовать:

GRANT RESOURCE_GROUP_USER ON *.* TO 'etl_worker'@'%';

Некоторые проблемы

Список всех проблем:

• macOS не поддерживается вообще.

• FreeBSD и Solaris игнорируют приоритеты. Всё всегда на 0.

• Linux требует CAP_SYS_NICE. Без этого приоритеты игнорируются. Включить:

sudo setcap cap_sys_nice+ep /usr/sbin/mysqld

Лучше — через systemd:

sudo systemctl edit mysql

Добавьте:

[Service] AmbientCapabilities=CAP_SYS_NICE

• Thread Pool plugin ломает всё. Его надо отключить.

Три кейса использования

BI-отчёты на Metabase не душат REST-API

На одном сервере живут фронтовое API (latency < 100 мс) и Metabase, который в пик дня гоняет SELECT … GROUP BY на десятки секунд. Фронт-энд периодически замирает.

Решение

-- создаём группу под BI CREATE RESOURCE GROUP bi_low   TYPE = USER   VCPU = 3           -- одно «спокойное» ядро   THREAD_PRIORITY = 18;  -- почти самый низкий приоритет

В настройках подключения Metabase добавляем startup-query:

SET RESOURCE GROUP bi_low;

Теперь все BI-запросы ограничены одним ядром и низким приоритетом — API обслуживается без скачков latencies.

Ночные ETL-джобы Parallel Ingest против дневного OLTP

Cron в 02:00 поднимает Java-процесс, который заливает данные пачками INSERT … ON DUPLICATE KEY UPDATE. Днём всё ок, но если бэкап сместился и ETL стартовал в рабочий час — база становится резиновой.

Решение

-- заводим группу для ETL CREATE RESOURCE GROUP etl_batch   TYPE = USER   VCPU = 1-2          -- два ядра из восьми   THREAD_PRIORITY = 10;

Java-процессу в строку подключения пишем:

jdbc:mysql://db:3306/app?sessionVariables=resource_group=etl_batch

Днём ETL не вылазит за выделенные CPU, ночью при желании можно поднять приоритет:

ALTER RESOURCE GROUP etl_batch   VCPU = 0-3   THREAD_PRIORITY = 2;

Крутить ad-hoc-запросы

Иногда нужно быстро проверить гипотезу, и кто-то запускает EXPLAIN ANALYZE на таблицу в 200 М строк. Если соединение открыто под обычной учёткой, запрос лезет в ту же очередь, что и боевые платежи.

Решение

-- создаём песочницу для ручных запросов CREATE RESOURCE GROUP dev_lab   TYPE = USER   VCPU = 2-3   THREAD_PRIORITY = 16;

Даем разработчикам отдельный логин:

GRANT RESOURCE_GROUP_USER ON *.* TO 'dev'@'%'; GRANT USAGE          ON *.* TO 'dev'@'%'; GRANT SELECT, EXPLAIN ON prod_db.* TO 'dev'@'%';

И в .my.cnf этого логина пишем:

[client] user     = dev password = **** init_command = "SET RESOURCE GROUP dev_lab"

---

Итоги

Resource Groups — полезная фича MySQL: закрепили ядра, задали приоритеты, и тяжёлые BI- или ETL-потоки больше не топят прод; настройка занимает минуты, а выигрыш — стабильные миллисекунды отклика в пиковые часы. Если вам уже довелось приручать запросы через свои группы, поделитесь в комментарих вашим опытом — коллективный опыт всегда мощнее любой официальной документации.

Освоить MS SQL Server на профессиональном уровне и расширить свои возможности в IT можно на онлайн-курсе «MS SQL Server Developer».

Если интересно узнать свой уровень знаний для поступления на курс, пройдите вступительное тестирование.