Mysql: почему тормозит\зависает SELECT запрос в Percona XtraDB Cluster?

Question

[vlarkanov]

Есть вот такой запрос.

SELECT DATE(t1.starttime) AS day, sum(t1.sessiontime) AS calltime,
sum(t1.sessionbill) AS cost, count(*) as nbcall,
sum(t1.buycost) AS buy, sum(case when t1.sessiontime>0 then
1 else 0 end) as success_calls
FROM cc_call t1 LEFT OUTER JOIN cc_trunk t3 ON
t1.id_trunk = t3.id_trunk
LEFT OUTER JOIN cc_ratecard t4 ON t1.id_ratecard = t4.id
WHERE t1.starttime >= ('2017-12-1') AND t1.starttime <=
('2017-12-31 23:59:59')
AND (t1.terminatecauseid=1) GROUP BY day ORDER BY day;

На слабенькой виртуалке (2ядра, 2гб) с развернутой копией базы он выполняется менее чем за две минуты.

В боевом кластере
Debian 9, Percona Xtradb Cluster 75.7.19,
2 железных сервера (2*Intel Xeon E5-2650, 196Gb RAM, 2*SSD в RAID1)
+ виртуалка с арбитратором

с запросом происходит вот что. Если выполнять его ночью - выполняется минуты за 3-4. Если выполнять днем, под нагрузкой - выполняется неопределенно долго (сейчас уже около 20 минут висит в статусе Sending data). При этом нагрузка на железо незначительна, проца-памяти-диска сети более чем хватает.

Посоветуйте пожалуйста где искать проблему.

Answer 1

[Артем Советников]

1. Попробуйте запрос не в кластер, а напрямую в один из серверов
2. Посмотрите план выполнения запросов на локальной копии и на боевом, сравните
3. Проверьте блокировки в БД, может транзакции висят

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

Comments

[vlarkanov]

1)
Результат интересный:
В кластере (через maxscale запрос попал на node1):
31 rows in set (35 min 7.36 sec)

node1 (напрямую):
31 rows in set (59 min 3.40 sec)

Вероятно, связано с тем, что во время выполнения напрямую на node1 была бОльшая нагрузка со стороны пользователей.

Обратил внимание, что во время выполнения запросы значительно выросла нагрузка на CPU: в обычном "боевом" режиме суммарная нагрузка составляет около 100-140% (данные согласно atop, нагрузка плавно размазана по ядрам - 16 физическим\32 с учетом гипертрединга), а во время выполнения этого запроса поднимается до 200-240%, причем одно из ядер грузится на 90-100%, а остальные по чуть-чуть. Отмечу, что innodb_thread_concurrency = 32, если это важно;

2. Да, согласно explain, план совпадает.

3. Судя по processlist, никто из запросов не ждет блокировки - значит, ничего не заблокировано.

Свопа нет (отключен), памяти полно как в обычном режиме, так и во время выполнения запроса (~38гб), на дисках полно места и они здоровы

[Артем Советников]

4. А зачем вам в запросе cc_trunk и cc_ratecard в таком джойне? Вы из них данные не выбираете и LEFT OUTER JOIN cc_call не порежет
5. Интенсивность вставок какая?
6. Транзакции смотрели? Хотя innodb и лочит на уровне строк, но в клинч с select может встрять ...
Пока ваш запрос выполяется сделайте:
SHOW FULL PROCESSLIST
и
SHOW ENGINE INNODB STATUS
Скорее всего транзакции на боевой БД несколько мешают запросу, особенно если ночью всё работает нормально.
7. Можете READ UNCOMMITTED попробовать, я так понимаю всёравно у вас отчёт строится по промежутку времени в прошлом и новые данные не должны повлиять ...

[vlarkanov]

4. Критика по делу, но хочется понять почему же именно такой запрос так себя ведет;
5. INSERT около 20 qps под нагрузкой, UPDATE до 50 qps;
6. Не обнаружил ничего криминального;
7. Несколько раз провел запрос с READ UNCOMMITTED - выполняется менее чем за полторы минуты. Не понимаю - можно ли это считать решением проблемы? Если глобально задать эту переменную, возможно получение неточных данных, если они были изменены во время исполнения запроса.

[Артем Советников]

4. Транзакции мешают ... я не вижу большие ли они у вас или маленькие, сколько записей изменяется в каждой транзакции.
5. Не так много однако.
6. Ну так и 50 минут на запрос это не криминал :)
7. У вас запрос данные за декабрь 2017 выбирает, если они в процессе работы меняются ... тогда возможность применения READ UNCOMMITTED решайте сами, у вас видимо к этому запросу ещё в SELECT что-то добавится ... и будут ли рассчитанные показатели консистентными (сумма строк например может не сойтись ...) зависит от характера данных.
Если декабрьские данные не меняются, то READ UNCOMMITTED не должен навредить.
8. Индексы ведь все нужные созданы?

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

Чисто для косметики условие t1.starttime <= ('2017-12-31 23:59:59') я бы записал как t1.starttime < ('2018-01-01 00:00:00') ... так как-то спокойнее :)

[vlarkanov]

Артем Советников, спасибо за помощь! В процессе "расследования" узнал много нового и интересного, теперь информации достаточно чтобы двигаться дальше.

Answer 2

[Xilian]

Попробуй WITH (nolock). Возможно просто постоянные записи в таблицу.