Galera Cluster – Drawbacks of Enabling wsrep_log_conflicts and cert.log_conflicts

galeramariadbmy.cnfMySQLreplication

We have a HAProxy which balances reads and writes to only one (of three) Galera nodes.

If this balancing works fine, the parameters wsrep_log_conflicts and cert.log_conflicts should never log any conflicts and errors? It won't fill up /var/log filesystem with messages?

What is the drawback of enabling wsrep_log_conflicts and cert.log_conflicts? Why it's not enabled by default?

Please give more info, the only thing I found was DEALING WITH MULTI-MASTER CONFLICTS

Lastly, you can enable conflict logging features through
wsrep_log_conflicts and cert.log_conflicts.

# Enable Conflict Logging
wsrep_log_conflicts=ON
wsrep_provider_options="cert.log_conflicts=YES"

These parameters enable different forms of conflict logging on the
database server. When turned on, the node logs additional information
about the conflicts it encounters, such as the name of the table and
schema where the conflict occurred and the actual values for the keys
that produced the conflict.

7:51:13 [Note] WSREP: trx conflict for key (1,FLAT8)056eac38 0989cb96:
source: cdeae866-d4a8-11e3-bd84-479ea1a1e941 version: 3 local: 1 state:
MUST_ABORT flags: 1 conn_id: 160285 trx_id: 29755710 seqnos (l: 643424,
g: 8749173, s: 8749171, d: 8749171, ts: 12637975935482109) <--X-->
source: 5af493da-d4ab-11e3-bfe0-16ba14bdca37 version: 3 local: 0 state:
APPLYING flags: 1 conn_id: 157852 trx_id: 26224969 seqnos (l: 643423,
g: 8749172, s: 8749171, d: 8749170, ts: 12637839897662340)

Best Answer

In a Galera cluster, any node can play the part of a Master node.

Such a node can execute DML (INSERTs, UPDATEs, and DELETEs).

If DML statements are spread across multiple Master nodes and are targeting the same tables, you must expect log conflicts. Why ?

Galera wsrep libraries were designed to commit DML changes if all nodes in a cluster are ready to do so. If one node cannot do so, all nodes must abandon a commit. It's basically all-or-nothing commits.

If you look at the bottom of the document you referenced, there are suggestions to minimize log conflicts under the heading WORKING AROUND MULTI-MASTER CONFLICTS:

While Galera Cluster resolves multi-master conflicts automatically, there are steps you can take to minimize the frequency of their occurrence.

  • Analyze the hot-spot and see if you can change the application logic to catch deadlock exceptions.
  • Enable retrying logic at the node level using, wsrep_retry_autocommit.
  • Limit the number of master nodes or switch to a master-slave model.

Note If you can filter out the access to the hot-spot table, it is enough to treat writes only to the hot-spot table as master-slave.

So, to answer your question, your drawback would be implementing these suggestions since it may require some code rewrite when you catch deadlocks, some autocommit tuning, and writing to one Master node (which could introduce write bottlenecks on top of the fact that Galera Clusters do not write scale well past 3 nodes).

The options are probably not enabled by default because of the possibility of having a write-heavy cluster that must have the logs examined often (even if a conflict never occurs). Thus, enabling wsrep_log_conflicts and cert.log_conflicts become your tuning responsibility.

Related Question