[Note] WSREP: Unable to report last applied write-set to cluster. Will try later. (gcs_sm_enter()): 110 seqno: XXXXXX

[R Gagnon]

Under high load to the cluster (50,000 to 80,000 inserts per second) on MariaDB v11.4.3 (galera-4) this note appears in the mysql error.log file every 20-60 seconds.

A search of the Internet found nothing at all for this "note".  Can anyone explain what this means, or how to resolve it?

We are running on CentOS 7 in AWS EC2 with 25,000 IOPS to a 5TB storage system on each of 4 nodes.

Is there some statistic that would help determine the issue so we can modify our config to apply more RAM to some kind of cache or something?

Thanks

[Alexey Yurchenko]

It just means that replication gets choked at times. The statistics you may want to look at is 'wsrep_flow_control_%'. If it is high then you may want to increase gcs.fc_limit and/or wsrep_applier_threads or speed up the servers in some other way. Basically it all boils down to replicas not being able to keep up.

[R Gagnon]

So here's what comes back for us...  Would 16 be too low?

> show status like 'wsrep_flow_control%';

+-----------------------------+----------------+

| Variable_name                | Value          |

+-----------------------------+----------------+

| wsrep_flow_control_paused_ns | 20287104985032 |

| wsrep_flow_control_paused    | 0.108083       |

| wsrep_flow_control_sent      | 9128                         |

| wsrep_flow_control_recv      | 29953                       |

| wsrep_flow_control_active    | false                       |

| wsrep_flow_control_requested | false                       |

+-----------------------------+----------------+

> show variables like 'wsrep_provider_gcs_fc_limit';

+----------------------------+-------+

| Variable_name               | Value |

+----------------------------+-------+

| wsrep_provider_gcs_fc_limit | 16         | 

+----------------------------+-------+

[Alexey Yurchenko]

Well, there you have it: wsrep_flow_control_paused    | 0.108083

means that about 10% of the time replication is paused because some replicas can't keep up. And what exactly "keeping up" means is determined by gcs.fc_limit - if the event queue on replica exceeds that much it is not keeping up. For transaction rates in the tens of thousands per second it is of course peanuts. Most likely you get hundreds of concurrent connections and hundreds of events in flight at any given moment and it is impossible to keep the queue length under 16. I would even say that you're doing quite good there with only 10% pause. The problem is that the individual pauses turn out to be quite lengthy and so you can see a rather jagged performance and uneven response latencies.

That's not all though. You also may have naturally big transactions. E.g. a transaction 1Gb in size naturally may take quite some time to be applied on replicas. That also can lead to replication pause. So you need to use streaming replication for those, look at setting wsrep_trx_fragment_size to something reasonable, like 10M or so.

[R Gagnon]

Thanks for the tips... Will look into that