0

I have a 3 node setup and the 2 read only nodes are way behind (like 24 hours) from the rw node..

I saw here: https://bugs.mysql.com/bug.php?id=86797 to restart the lagging node and will pick up by itself.. since I was not very convinced I did this in just one node.. restarted ok and came to the cluster in "RECOVERING" mode such good... But now I am checking and the Q is super big getting bigger and when I compare the data in one table to the one in RW is updating.. just very slow like one a sec .. so is not picking up any slack.. it should go faster to pick up with the RW node right?

CHANNEL_NAME: group_replication_applier VIEW_ID: 15255041(deleted) MEMBER_ID: 47dbb651-XXXXX (deleted) COUNT_TRANSACTIONS_IN_QUEUE: 218417 COUNT_TRANSACTIONS_CHECKED: 0 COUNT_CONFLICTS_DETECTED: 0 COUNT_TRANSACTIONS_ROWS_VALIDATING: 0 TRANSACTIONS_COMMITTED_ALL_MEMBERS: 1667fe4c-5033-11e8-b942-(Deleted)-3140512, 617916a6-5032-11e8-ad5b-(Deleted):1-14 LAST_CONFLICT_FREE_TRANSACTION:

note: I added the deleted for privacy.
on my other node is also behind around 24 hours says Q is 0

select * from replication_group_member_stats\G *************************** 1. row *************************** CHANNEL_NAME: group_replication_applier VIEW_ID: 1525504100(Deleted):13 MEMBER_ID: 617916a6-5032-11e8-(deleted) COUNT_TRANSACTIONS_IN_QUEUE: 0 COUNT_TRANSACTIONS_CHECKED: 6846224 COUNT_CONFLICTS_DETECTED: 0 COUNT_TRANSACTIONS_ROWS_VALIDATING: 5921012 TRANSACTIONS_COMMITTED_ALL_MEMBERS: 1667fe4c-5033-11e8-b942-0cc(deleted):1-3173566, 617916a6-5032-11e8-ad5b-(deleted):1-14 LAST_CONFLICT_FREE_TRANSACTION: 1667fe4c-5033-11e8-b942-0(deleted) 1 row in set (0.0004 sec) I am researching but is confusing some is for mysql 8.x like this solution: http://lefred.be/content/mysql-innodb-cluster-how-to-handle-fc/

and other info I found is for master/slave so is not related..

Also looking into the parallel slave options but not sure if this will make any difference.

NOTE: the hardware is exactly the same on all 3 nodes.

thanks

0

Hello for those out there with similar issues I fixed it thanks to this amazing blog post.
https://thoughts.t37.net/fixing-a-very-lagging-mysql-replication-db6eb5a6e15d basically my changes:

STOP SLAVE; SET GLOBAL slave_parallel_workers=40; START SLAVE; set GLOBAL sync_binlog=0; SET GLOBAL innodb_flush_log_at_trx_commit=2; SET GLOBAL innodb_flush_log_at_timeout=1800;`

I already had the MT setup so just needed to add this and 40 workers. Hope this will help other people..

  • 40 seems excessive. While it was catching up, did you saturate the CPU and/or I/O? Were user unable to get much done on that Slave? – Rick James May 22 '18 at 23:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.