Getting a lot of Wsrep_rollback_local logs and failed transactions

MySQL & MariaDB Percona XtraDB Cluster 8.x
1

Hi, I have finally migrated my DB from 5.7 to 8.4. Everything nominally works… But I am frequently getting Wsrep_rollback_local log entries (1 per min) on each of my 3 nodes.

I did not have this problem on 5.7… It is causing problems in my application and DB transactions are failing… frequently as a result…

Any help much appreciated

2

Hi @karl123

Those are conflicts being resolved through brute-force (BF) aborts. Does your write traffic goes more than one node in the cluster?

Also, enabling debug log would be helpful for this kind of case to see more details: Index of wsrep system variables - Percona XtraDB Cluster

3

Hi Yunus, only just saw the reply. Apologies for the delay…

Writes are going to all three nodes. With a network load balancer in front of it… Was thinking about setting up ProxySQL. Would something like this help? IS writing to all three nodes making the problem worse?

4

Hello Karl,

As yunus said, “Those are conflicts being resolved through brute-force (BF) aborts. Does your write traffic goes more than one node in the cluster?”

Sending writes to all nodes will lead to deadlocks and rollbacks.

You need to enable debug logging to identify the issue. Index of wsrep system variables - Percona XtraDB Cluster

Yeah, but with the help of ProxySQL, I’d recommend routing all writes to a single node ( Writer hostgroup), and using the remaining nodes for reads/failover.

5

Writing to all three nodes simultaneously is the primary cause of Wsrep_rollback_local entries, and ProxySQL is the industry-standard way to fix this.

When you write to all nodes via a Network Load Balancer (NLB), you are essentially forcing the cluster to gamble on every transaction. As your traffic or row contention increases, the “winning” node forces the “losing” node to roll back the transaction to maintain data integrity across the cluster. This is exactly what you are seeing in your logs.

6

Thanks for the reply. I will give ProxySQL a go and report back in a few days