I have an InnoDB cluster with three nodes running with 4GB ram and 80GB SSD on Linode.
I am trying to delete around 240000 of rows from MySQL InnoDB cluster in one go. However, it stuck at
query end state for more than 100 seconds then it timeout at MySQLWorkbench side. The worst thing is sometime after timeout master node MySQL started to use all memory and swap then crashed.
If I delete by small chunk with below 20000 rows it worked but still took 60s. I know I can just delete in a small chunk and that's how people suggest typically. But I think 20000 rows is too small. It may indicate other potential problems as most of the query time goes to
query end state.
The query is like this
from A left join B on fk_Btable_id=Btable_id
left join C on fk_Ctable_id=Ctable_id
where col1 = 'value';
The profile from delete around 20000 rows in one delete query.
sending data: 15.68s.
query end: 45.5s
When more than 30000 rows the delete query timeout at 180s at MySQLWorkbench side. But it still able to complete after some time. On slow query log, it said total 1488 seconds to complete the query. That's ridiculously long. Delete query itself completed within 20sec. Residual time goes to
To test without a cluster, I spinned up another instance on Linode with same data, ran the same delete query and it completed within 20s. So my guessing is because of InnoDB cluster, probably network or some kind of sync mechanism but I can't find any proof for this.
Any idea would be helpful...either guess or the direction I can look into. Thanks a lot.