0

I have 1 server configured the galera, 4 servers with mariadb and 1 with haproxy.

But when I want to do an update on any server I get the error "Deadlock: wsrep aborted transaction" or "wsrep_max_ws_rows exceeded" (sometimes).

The servers are powerful with 32gb of memory and 24 CPUs (VmWare).

gard.conf (ip 1.1.1.110):

GALERA_NODES="1.1.1.111:4567 1.1.1.112:4567  1.1.1.113:4567  1.1.1.114:4567"
GALERA_GROUP="mariadb_cluster" 
GALERA_OPTIONS="pc.wait_prim=no"  
LOG_FILE="/var/log/garbd.log"

cluster.cnf (4 server mariadb ip 1.1.1.111 to 1.1.1.114):

[mysqld]
query_cache_size=0
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
query_cache_type=0
bind-address=0.0.0.0
innodb_file_per_table
collation-server = utf8_general_ci
init-connect = 'SET NAMES utf8'
character-set-server = utf8
join_buffer_size = 1M

wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
    
wsrep_cluster_name="mariadb_cluster"
wsrep_cluster_address="gcomm://1.1.1.111,1.1.1.112,1.1.1.113,1.1.1.114"

wsrep_sst_method=rsync
    
wsrep_node_address="1.1.1.111"
wsrep_node_name="mysqlcl0X"
wsrep_max_ws_rows=2048
wsrep_max_ws_size=8G
wsrep_provider_options = "gcs.fc_limit = 2048; gcs.fc_factor = 0.99; gcs.fc_master_slave = yes"

The error when doing an update is done directly on the client of each server, so the error is not the haproxy, I just put the configuration just in case.

haproxy.cfg:

listen galera
     bind *:3306
     mode tcp
     timeout client 60000ms
     timeout server 60000ms
     balance leastconn
     server 1.1.1.111 1.1.1.111:3306 check
     server 1.1.1.112 1.1.1.112:3306 check
     server 1.1.1.113 1.1.1.113:3306 check
     server 1.1.1.114 1.1.1.114:3306 check

What am I missing in the configuration?

From already thank you very much!

Kronnopio
  • 1
  • 1
  • 3
  • Are you inserting data on all nodes at the same time? If so, this is most likely expected behavior as Galera is not a synchronously replicating cluster and conflict detection is done at transaction commit. – markusjm Apr 28 '23 at 08:35

2 Answers2

0

https://mariadb.com/kb/en/galera-cluster-system-variables/#wsrep_max_ws_rows

Perhaps =0 would solve at least part of the problem.

If the problem really is "too many" rows in a transaction, then let's discuss the transaction and look for ways to be more efficient.

Rick James
  • 2,463
  • 1
  • 6
  • 13
0

I have solved it with this option:

wsrep-retry-autocommit = 100

I don't think it's the right thing to do, it takes a while to run, but it works.

Continue to fail..

1213 Deadlock: wsrep aborted transaction
Kronnopio
  • 1
  • 1
  • 3