20.
Multi-Master Conflicts
●
●
Galera uses optimistic concurrency control
If two transactions modify same row on
different nodes at the same time, one of
the transactions must abort
➔
●
Victim transaction will get deadlock error
Application should retry deadlocked
transactions, however not all applications
have retrying logic inbuilt
www.codership.com
23

21.
Database Hot-Spots
●
●
Some rows where many transactions want to
write to simultaneously
Patterns like queue or ID allocation can be hotspots
www.codership.com
24

23.
Diagnosing Multi-Master Conflicts
●
●
●
●
In the past Galera did not log much information
from cluster wide conflicts
But, by using wsrep_debug configuration, all
conflicts (...and plenty of other information) will
be logged
Next release will add new variable:
wsrep_log_conflicts which will cause each cluster
conflict to be logged in mysql error log
Monitor:
●
●
wsrep_local_bf_aborts
wsrep_local_cert_failures
www.codership.com
26

24.
wsrep_retry_autocommit
●
●
●
●
Galera can retry autocommit transaction on
behalf of the client application, inside of the
MySQL server
MySQL will not return deadlock error, but will
silently retry the transaction
wsrep_retry_autocommit=n will retry the
transaction n times before giving up and
returning deadlock error
Retrying applies only to autocommit
transactions, as retrying is not safe for multistatement transactions
www.codership.com
27

26.
Multi-Master Conflicts
1) Analyze the hot-spot
2) Check if application logic can be changed to
catch deadlock exception and apply retrying
logic in application
3) Try if wsrep_retry_autocommit configuration
helps
4) Limit the number of master nodes or change
completely to master-slave model
if you can filter out the access to the hotspot table, it is enough to treat writes
only to hot-spotwww.codership.com master-slave
table as
29

28.
State Transfer
Joining node needs to get the current
database state
➢ Two choices:
➢ IST: incremental state transfer
➢ SST: full state transfer
➢ If joining node had some previous state
and gcache spans to that, then IST can
be used
➢
www.codership.com
31

36.
SST Donor
All SST methods cause some disturbance
for donor node
● By default donor accepts client
connections, although committing will be
prohibited for a while
● If wsrep_sst_donor_rejects_queries is set,
donor gives unknown command error to
clients
➔ Best practice is to dedicate a reference
node for donor and backup activities
●
www.codership.com
39

50.
Backup by SST
●
●
●
Donor mode provides isolated processing
environment
A special SST script can be written just to
prepare backup in donor node:
wsrep_sst_backup
Garbd can be used to trigger donor node to run
the wsrep_sst_backup
www.codership.com
53

58.
Total Order Isolation
●
DDL is replicated up-front
Each node will get the DDL statement
and must process the DDL at same slot
in transaction stream
Galera will isolate the affected
table/database for the duration of DDL
processing
●
●
www.codership.com
61

59.
Rolling Schema Upgrade
●
●
●
●
●
DDL is not replicated
Galera will take the node out of replication
for the duration of DDL processing
When DDL is done with, node will catch up
with missed transactions (like IST)
DBA should roll RSU operation over all
nodes
Requires backwards compatible schema
changes
www.codership.com
62

60.
wsrep_on=OFF
●
●
●
wsrep_on is a session variable telling if this
session will be replicated or not
I tried to hide this information to the best I can,
but somebody has leaked this out
And so, yes, it is possible to run
“poor man's RSU” with wsrep_on set to OFF
●
such session may be aborted by replication
●
Use only, if you are really sure that:
planned SQL is not conflicting
● SQL will not generate inconsistency
●
www.codership.com
63

71.
SSL or VPN
●
●
●
Bundling several nodes through VPN
tunnel may cause a vulnerability
When VPN gateway breaks, a big part of
cluster will be blacked out
Best practice is to go for SSL if VPN does
not have alternative routes
www.codership.com
74

72.
UDP Multicast
●
●
●
●
Configure with gmcast.mcast_addr
Full cluster must be configured for
multicast or tcp sockets
Multicast is good for scalability
Best practice is to go for multicast if
planning for large clusters
www.codership.com
75