However, what is being experienced is
1) Data inconsistency, dirty reads are occurring so the record is not being updated properly (lost updates)
2) Connection lockup occurs, where the only way to unlock the client is the restart the DB node(s) for the locked connections.
The server version being used is Percona-XtraDB-Cluster-server-56-5.6.28-25.14.1.el7.x86_64 on CentOS 7.2, it is a 3-node cluster running over a local LAN connected via dual 1Gbps links, with a Linux IPVS load balancer doing round-robin in front from my application.
I have attached a test case that reproduces this issue consistently. This same test case works fine if pointing to only a single DB node in the cluster.
Config settings:
/etc/my.cnf:
[mysqld]
datadir = /var/lib/mysql
# move tmpdir due to /tmp being a memory backed tmpfs filesystem, mysql uses this for on disk sorting
tmpdir = /var/lib/mysql/tmp
My sequence of events is basically identical to this blog post, because this is directly from CoderShip, I am assuming this is intended to be supported: galeracluster. com/2015/ 09/support- for-mysql- transaction- isolation- levels- in-galera- cluster/
http://
However, what is being experienced is
1) Data inconsistency, dirty reads are occurring so the record is not being updated properly (lost updates)
2) Connection lockup occurs, where the only way to unlock the client is the restart the DB node(s) for the locked connections.
The server version being used is Percona- XtraDB- Cluster- server- 56-5.6. 28-25.14. 1.el7.x86_ 64 on CentOS 7.2, it is a 3-node cluster running over a local LAN connected via dual 1Gbps links, with a Linux IPVS load balancer doing round-robin in front from my application.
I have attached a test case that reproduces this issue consistently. This same test case works fine if pointing to only a single DB node in the cluster.
Config settings:
/etc/my.cnf:
[mysqld]
datadir = /var/lib/mysql
# move tmpdir due to /tmp being a memory backed tmpfs filesystem, mysql uses this for on disk sorting
tmpdir = /var/lib/mysql/tmp
[mysqld_safe] mysql.pid
pid-file = /run/mysqld/
syslog
!includedir /etc/my.cnf.d
/etc/my. cnf.d/base. cnf: gtid_consistenc y = 1 max_len = 102400 buffer_ pool_size = 10G log_file_ size = 64M file_per_ table = 1 storage_ engine = innodb flush_neighbors = 0
[mysqld]
bind-address = 0.0.0.0
key_buffer = 256M
max_allowed_packet = 16M
max_connections = 256
# Some optimizations
thread_concurrency = 10
sort_buffer_size = 2M
query_cache_limit = 100M
query_cache_size = 256M
log_bin
binlog_format = ROW
gtid_mode = ON
log_slave_updates
enforce_
group_concat_
innodb_
innodb_
innodb_
innodb_file_format = barracuda
default_
# SSD Tuning
innodb_
innodb_io_capacity = 6000
/etc/my. cnf.d/cluster. cnf: libgalera_ smm.so s3cretPass" options = "gcache.size = 5G; pc.recovery = true" address = gcomm:/ /10.30. 30.11,10. 30.30.12, 10.30.30. 13 autoinc_ lock_mode = 2 locks_unsafe_ for_binlog = 1 flush_log_ at_trx_ commit = 0
# Galera cluster
[mysqld]
wsrep_provider = /usr/lib64/
wsrep_sst_method = xtrabackup-v2
wsrep_sst_auth = "sstuser:
wsrep_cluster_name = cluster
wsrep_slave_threads = 32
wsrep_max_ws_size = 2G
wsrep_provider_
wsrep_cluster_
wsrep_sync_wait = 0
innodb_
innodb_
innodb_
sync_binlog = 0
innodb_support_xa = 0
innodb_flush_method = ALL_O_DIRECT
[sst]
progress = 1
time = 1
streamfmt = xbstream