Как оптимизировать сложный запрос на нагруженной базе MYSQL?

Question

[Александр]

Друзья всем привет! Имеем проект, в нем есть 2 динамически меняющиеся таблицы. По ним нужно делать селект с джойном.
Проблема в том, что проект загружен, этот запрос выполняется до 20к раз в секунду.
Проблема следующая, при невысокой нагрузке имеем продочную загрузку сервера и в целом живем. Но когда количество запросов подваливает к 10к в секунду, процессор начинает сильно нагружаться (до 100% по всем ядорам), селекты начинаю выполняться по несколько секунд, и в итоге происходит отказ в обслуживании.
Железо топовое, настроена репликация по 2м серверам, запись идет на мастер, все чтения со слейва:
Размеры таблиц - TaskFlow 200-300gb, Tasks 20gb
Сам запрос:
$acc - каждый запрос разный
Индексы все есть, до 5к запросов в секунду держит впринципе без проблем

SELECT 
                task.id,
                task.dc,
                task.provider,
                task.type,
                task.url,
                task.cost,
                task.cost_bot,
                task.last_start_msec,
                task.url_id,
                task.remains,
                task.cat
            FROM Task task
         LEFT JOIN TaskFlow flow ON flow.url_id = task.url_id AND flow.executor_acc_id = $acc
            WHERE task.status = 'active'
             AND task.type = 'follow_profile'  AND task.provider = 'insta'  AND task.cat = 3  AND flow.dt_start IS NULL

explain:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	task	ref	stat_task,getlist,getlist_bot	getlist_bot	6	const,const,const,const	8625	Using index condition
1	SIMPLE	flow	ref	user_url_executor	user_url_executor	8	admin_insta.task.url_id,const	1	Using where; Using index; Not exists

Что делать подскажите? Кеширования на уровне редис или других средств пока откладываем в сторону. Слишком много придется для этого переделывать.
Спасибо!

Comments

[ThunderCat]

Слишком много придется для этого переделывать.

э? 4 строчки + настройка 10 минут...

[rPman]

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

[Akina]

Задавать вопрос и при этом не предоставить полные структуры всех таблиц? ну-ну...

Про статистику данных я даже напоминать боюсь.

[Михаил]

Настроить еще один слейв и читать с двух слейвов?

Answer 1

[Slava Rozhnev]

Так как из второй таблицы данные не выбираются - попробуйте (NOT EXISTS):

SELECT 
  task.id, 
  task.dc, 
  task.provider, 
  task.type, 
  task.url, 
  task.cost, 
  task.cost_bot, 
  task.last_start_msec, 
  task.url_id, 
  task.remains, 
  task.cat 
FROM 
  Task task 
WHERE 
  task.status = 'active' 
  AND task.type = 'follow_profile' 
  AND task.provider = 'insta' 
  AND task.cat = 3 
  AND NOT EXISTS(
	SELECT 1 FROM  TaskFlow flow 
	WHERE 
		flow.url_id = task.url_id 
		AND flow.executor_acc_id = $acc 
  );

Comments

[Александр]

Здравствуйте. Думаете такая схема меньше жрет ресурса?

[Slava Rozhnev]

teranq, Проверьте. Тут нельзя сказать однозначно.

[Slava Rozhnev]

teranq, Проверите отпишитесь

[Александр]

Slava Rozhnev, Здравствуйте. Проверили. Скорость выполнения одинаковая, нужно будет смотреть под нагрузкой завтра.
Есть ли еще варианты в целом?

[Akina]

teranq,

Скорость выполнения одинаковая

Значит, нет подходящего индекса. Нужны:

Task ( { status, type, provider, cat } , url_id )
TaskFlow (executor_acc_id, url_id )

В первом - первые 4 поля, что в фигурных скобках, могут быть в любом порядке. Желательно - в порядке снижения селективности поля.

Ну и - я бы использовал такую форму условия:

WHERE (task.status, task.type, task.provider, task.cat) = ('active', 'follow_profile', 'insta', 3)

Серверу пофиг, но как бы нагляднее.

[Александр]

Akina, здравствуйте. Разумеется индексы есть. У меня нет проблем со скоростью. Проблемы с нагрузкой на железо

[Akina]

teranq, гм... а какие именно? Добавьте в текст вопроса вывод SHOW CREATE TABLE.

[Александр]

Akina, те самые что вы и написали) мы уж не совсем делетанты.

[Александр]

Slava Rozhnev, В итоге с вашим вариантом нагрузка стала стабильной а не волнообразной. Спасибо!

[Slava Rozhnev]

Александр, пожертвования принимаются на https://SQLize.online

[Александр]

Akina, кстати индесы так как вы и написали сделаны

[Александр]

Akina, а для чего для таск индекс по url id? он же не используется в выборке по этой таблице? Только по task flow2

[Akina]

Александр,

для чего для таск индекс по url id?

для нормальной обработки NOT EXISTS. Хотя бы в режиме сканирования.

[Александр]

Akina, Мы добавили это поле в индекс, в итоге mysql его перестал использовать. Я могу с вами связаться вне форума? Соответственно не бесплатно, спасибо.
Пришлите Telegram или Skype

[Александр]

Akina, Правильно же понял, его нужно добавить в составной индекс вместе со всем? Название_составного_индекс ( status, type, provider, cat , url_id ). Также в запросе есть Order BY по task.id

Answer 2

[rPman]

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

так же хорошим подходом является кеширование результатов на бакэнде, не важно как, главное в оперативной памяти, и инвалидировать кеш на update-ах.

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

Comments

[Akina]

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

Думаю, объём - не главная проблема автора.

лучше всего enum

В MySQL тип ENUM лучше не использовать. Даже SET - и то более вменяем. Связанная таблица-словарь обеспечит более высокую производительность. Тем более что таблица получится компактная, и будет всегда горячая.

[Александр]

Добрый день. Кеш бесполезен. Таблицы динамичные, очень много апдейтов и чтений. Проблема у меня в высокой нагоузке на железо, а не в скорости выполнения запросов..

[Александр]

И да, апдейты не напрямую летят, а через редис кроном.

[Александр]

Бро еще проблемы у нас и апдейты летят нонстоп и инсерты в таблицы эти

[rPman]

а решить проблему грубой силой? отключив fsync в настройках базы? это может на порядок поднять скорость работы, если упирается все в update-ы

это жуть как опасно, но у вас уже есть slave база, она пусть будет бакап репликой, паралельно можно в самодельном логе все команды на изменения данных складировать в логфайл на бакап сервер, помрет мастер, недостоющую разницу на слейве добить запросами из лога

[Александр]

rPman, с мастером проблем нет, чтения идут со слейва. Запись на мастер, но чтений и записей в эти таблицы по 90к+ в минуту каждой комменты (то есть 90к селектов и 90к инсертов)

[Александр]

rPman, у нас стоит O_DSYNC