Mysql: Why is SELECT query slow/hanging in Percona XtraDB Cluster?

Question

linux

There is such a request.

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;

On a weak virtual machine (2 cores, 2 GB) with a deployed copy of the database, it runs in less than two minutes.
In a
Debian 9 combat cluster, Percona Xtradb Cluster 75.7.19,
2 iron servers (2*Intel Xeon E5-2650, 196Gb RAM, 2*SSD in RAID1)
+ a virtual machine with an arbitrator
, this is what happens with the request. If you perform it at night, it takes 3-4 minutes to complete. If executed during the day, under load, it runs indefinitely (now it has been hanging in the Sending data status for about 20 minutes). At the same time, the load on the hardware is insignificant, there is more than enough proca-memory-disk of the network.
Please advise where to look for the problem.

Answer 1

1. Try the query not to the cluster, but directly to one of the servers
2. Look at the query execution plan on the local copy and on the combat copy, compare
3. Check the locks in the database, transactions may hang
If the data is exactly the same, then either the statistics collected is so crooked ( but I don’t think so), or it’s a matter of the server (maybe the memory is running out there and everything goes to the swap, of course you have a lot of memory, but still? the disk is not full? the disks are generally alive, without errors?)