Querries freeze forever in different statuses

Bug #1710568 reported by Dmitry on 2017-08-14
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Galera
Undecided
Unassigned

Bug Description

I have cluster 2 physical nodes (2x Intel Xeon E5-2670 2.6Ghz , 192Gb, RAID1 SSD, ОС Debian 8, mariadb-server-10.2.7) + arbitrator (on Proxmox). I write to only one node at time (and read from both). My cluster works fine from 1 hour to several hours and stops to apply querries. It doesn't show any errors in syslog or mysql-error.log
PROCESSLIST shows some queries (SELECT\INSTERT\UPDATE) in "Query End" or other status such as "Init". It cannot be killed (it just changes status to Killed. mysql -u root -e "SHOW STATUS LIKE 'wsrep_%' " shows that nodes are Synced, wsrep_local_recv_queue = 0 and so on.

This occures only if I write to 1st node (and read from both). If I write to the 2nd node (and read from both) - everything is OK. Both nodes are identical (hardware and software), I've complitely reinstaled OS on both of them, changed LAN cables and connected it to another CISCO - no effect. Here is my config file:

[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0

[mysqld]
#
# * Basic Settings
#
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc_messages_dir = /usr/share/mysql
lc_messages = en_US
skip-external-locking
performance_schema=ON

# MyISAM #
key-buffer-size = 32M
myisam-recover = FORCE,BACKUP

# SAFETY #
max-allowed-packet = 16M
max-connect-errors = 1000000
skip-name-resolve

# DATA STORAGE #
datadir = /var/lib/mysql/

# BINARY LOGGING #
log-bin = /var/lib/mysql/mysql-bin
expire-logs-days = 14
#sync-binlog = 0

# CACHES AND LIMITS #
tmp-table-size = 32M
max-heap-table-size = 32M
query-cache-type = 0
query-cache-size = 0
max-connections = 500
thread-cache-size = 50
open-files-limit = 65535
table-definition-cache = 4096
table-open-cache = 4096

# INNODB #
innodb-flush-method = O_DIRECT
innodb-log-files-in-group = 2
innodb-log-file-size = 2000M
innodb-flush-log-at-trx-commit = 0
innodb-file-per-table = 1
innodb-buffer-pool-size = 160G
innodb_buffer_pool_instances = 64

# LOGGING #
log-error = /var/lib/mysql/mysql-error.log
log-queries-not-using-indexes = 1
slow-query-log = 1
slow-query-log-file = /var/lib/mysql/mysql-slow.log

# GALERA part

binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
# Galera Provider Configuration
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
# Galera Cluster Configuration
wsrep_cluster_name="galera-cluster"
wsrep_cluster_address="gcomm://ip1,ip2"
# Galera Synchronization Configuration
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=user:password
# Galera Node Configuration
wsrep_node_address="ip1"
wsrep_node_name="galera-node1"

# Tuning
wsrep_retry_autocommit = 4
wsrep_slave_threads = 64
wsrep_provider_options="gcache.size=5G; gcs.fc_limit = 320; gcs.fc_factor=0.8;"

!includedir /etc/mysql/conf.d/

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers