We would like to find guidance on managing large write transactions (MBs/thousands of rows) that are impacting Percona XtraDB Cluster performance. Could you briefly explain the mechanism inside Percona XtraDB cluster that causes these large writes to trigger Flow Control (FC) and throttle the cluster?
Which status variables should we check to confirm FC activity and the replication queue size? Is it - wsrep_flow_control_paused, wsrep_local_recv_queue_avg? Finally, what are the most important settings or general practices we can change to stop large transactions from slowing down the cluster?
Have a look at streaming replication. Set wsrep_trx_fragment_unit to bytes, rows, or statements and then tune wsrep_trx_fragment_size to match whatever unit you picked. Also check wsrep_applier_threads and set this to 1/2 the number of CPUs.
Thank you so much to both @matthewb and @CTutte! You have pointed me to valuable and helpful resources.
To sum up, large write transactions cause Flow Control activity (the mechanism that allows a node to hit the pause button on replication for the whole cluster), which throttles and slows down the cluster. To monitor such situations, there is a variable called wsrep_flow_control_status, which tells the user if the node is currently in Flow Control or not. Other valuable variables for monitoring Flow Control are wsrep_flow_control_sent/recv or wsrep_flow_control_paused.
As advised here, nowadays it is possible to use streaming replication to split the big transaction into smaller chunks. In general, it is also possible to tune Flow Control using variables such as gcs.fc_limit (controls when Flow Control engages) or gcs.fc_factor (addresses when it is released).