Friday, October 11, 2019

MySQL InnoDB Cluster Network Reliability Parameters


MySQL InnoDB Cluster is easy to configure and deploy.  This article is written to provide 3 group replication parameter settings which may contribute to the stability and reliability of the Network in MySQL InnoDB Cluster.




With MySQL 8.0, there are at least 3 most important parameters in Network Reliability Settings.


1. group_replication_member_expel_timeout
3. group_replication_unreachable_majority_timeout

"group_replication_member_expel_timeout"- default = 0
When a node is considered to be disconnected, the majority group will wait for a time period in order to reform this majority group.   By default, there is a 5 seconds + "group_replication_member_expel_timeout" as time period to wait before kicking out the node member and reform the majority group.

Outcome : When one node is expelled, the reform takes place.  This is also the time period considered to promote a PRIMARY if (the expelled node is PRIMARY node).  

Setting considerations: Setting this value too large affect the duration for new Primary promotion so that applications cannot connect to New Primary if the expelled node is Primary.

Setting this value too low (0) as 5 seconds : Considering the following situations 
a.  Network reliability 
b.  VM Backup and the VM is frozen
c.  VM migration and the VM is forzen
d.  Backup data traffic
e.  Long Message / Transaction
d.  Node is too busy 

All those situations may delay the response.  If occurrence is high, the service  interruption will be greatly impacted.  If one node is failed, another node may also be suffered for the same reason.   So this value is important to be tuned correctly according to the environment.


"group_replication_autorejoin_tries" - default = 0
This setting allows the expelled / disconnected node to rejoin the Cluster.  The default is 0.  It means it does not rejoin.   

If a node is disconnected (network interruption or VM being too busy or being forzen - and the expel timeout is passsed), it does "autorejoin" at interval of 5 minutes.  For continuously 1 hour retry, the value can be 12.  For 3 days retry, it is 12 x 24 x 3.  

The setting of 0 requires manual intervention to bring the expelled node to the cluster.     A setting of > 0 allows the cluster to be more resilience to failure.  Otherwise, a node is failed at one time without rejoin recovery, sooner or later another node failure will be disaster.



"group_replication_unreachable_majority_timeout" - default = 0
This setting is in particularly visible to Primary node operation.    Considering a Primary node is committing a transaction, it tries to get consensus ("certification process") from other node members.   If at this time the "cluster network" with primary node is failed / broken, the "COMMIT" process is not able to get consensus.   The "group_replication_unreachable_majority_timeout as 0" defines the timeout to be forever.  It hangs and waits forever.   

Setting consideration : The value is too large where the application may hang for longer period before the database returns the error status.
This value is too small where the application may also receive "error" status and the transaction is rollback.