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

delete A 
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 query end.

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.

  • looks like your innodb_log_file_size is too small. What is innodb_buffer_pool_size set to? Because your client timed out, the server was forced to roll back the transaction, which is expensive in terms of CPU. Crashing due to OOM is an overallocation of memory.
    – danblack
    Sep 12, 2018 at 23:08
  • Thanks for reply! innodb_log_file_size = '50331648' and innodb_buffer_pool_size = '104857600'. Never think about this.. Let me play with making innodb_log_file_size to 1G and see how it goes.
    – Yen
    Sep 13, 2018 at 1:50
  • See changing redo log file for changing the size.
    – danblack
    Sep 13, 2018 at 5:11
  • Changed innodb_log_file_size and innodb_buffer_pool_size to 1G it doesn't help. Query end state still very long. Sometimes the simple insert query runs several hundred second in query end state as well...
    – Yen
    Sep 20, 2018 at 23:50
  • Interesting thing (not sure if it is pertinent to this topic) is no matter 6446 or 6447 port I connect it will always connect to the write node. Totally no idea why it happens. Probably I should knock down everything and upgrade from 5.7.18 to 5.7.23.
    – Yen
    Sep 21, 2018 at 0:08


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.