Slave node stuck on Joining: receiving State Transfer
1,069 views
Skip to first unread message
Sander Groenen
Jul 6, 2017, 4:07:47 AM7/6/17
to codership
We have a docker setup with 2 nodes in master-slave configuration and for some reason after an initial SST the slave node gets stuck in Joining: receiving State Transfer and the wsrep_local_recv_queue keeps growing without ever reducing. Below are the mysql config for donor and slave nodes. We previously had a problem with IST not working due to docker container not binding to the correct address and after changing the ist.recv_bind to 127.0.0.1 this seemed to solve the problem, however this problem was introduced. Any help would be appreciated.
JOINER and DONOR logs are included as attachements
Output of status wsrep JOINER
My.cnf of DONOR
[client]
port = 3306socket = /var/run/mysqld/mysqld.sock
# Here is entries for some specific programs# The following values assume you have at least 32M ram
# This was formally known as [safe_mysqld]. Both versions are currently parsed.[mysqld_safe]socket = /var/run/mysqld/mysqld.socknice = 0
[mysqld]
# Settings for replicationserver_id=1bind-address=0.0.0.0
skip-host-cacheskip-name-resolve## * Basic Settings##user = mysqlpid-file = /var/run/mysqld/mysqld.pidsocket = /var/run/mysqld/mysqld.sockport = 3306basedir = /usrdatadir = /var/lib/mysqltmpdir = /tmplc_messages_dir = /usr/share/mysqllc_messages = en_USskip-external-locking
character-set-server=utf8default-storage-engine=INNODBsql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
## Instead of skip-networking the default is now to listen only on# localhost which is more compatible and is not less secure.#bind-address = 127.0.0.1## * Fine Tuning#max_connections = 251connect_timeout = 10000wait_timeout = 6000max_allowed_packet = 16Mthread_cache_size = 128sort_buffer_size = 4Mbulk_insert_buffer_size = 16Mtmp_table_size = 256Mmax_heap_table_size = 32M## * MyISAM## This replaces the startup script and checks MyISAM tables if needed# the first time they are touched. On error, make copy and try a repair.myisam_max_sort_file_size=100Gmyisam_recover_options = BACKUPkey_buffer_size = 8G#open-files-limit = 2000table_open_cache = 2000myisam_sort_buffer_size = 222Mconcurrent_insert = 2read_buffer_size = 2Mread_rnd_buffer_size = 1M## * Query Cache Configuration## Cache only tiny result sets, so we can fit more in the query cache.query_cache_limit = 100Mquery_cache_size = 1G# for more write intensive setups, set to DEMAND or OFF#query_cache_type = DEMAND## * Logging and Replication## Both location gets rotated by the cronjob.# Be aware that this log type is a performance killer.# As of 5.1 you can enable the log at runtime!#general_log_file = /var/log/mysql/mysql.log#general_log = 1
## Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.## we do want to know about network errors and suchlog_warnings = 2## Enable the slow query log to see queries with especially long durationslow_query_log=1slow_query_log_file = /var/lib/mysql/mariadb-slow.loglong_query_time = 2#log_slow_rate_limit = 1000#log_slow_verbosity = query_plan
relay-log-space-limit=6G
#log-queries-not-using-indexes#log_slow_admin_statements## The following can be used as easy to replay backup logs or for replication.# note: if you are setting up a replication slave, see README.Debian about# other settings you may need to change.#server-id = 1#report_host = master1#auto_increment_increment = 2#auto_increment_offset = 1#log_bin = /var/log/mysql/mariadb-bin#log_bin_index = /var/log/mysql/mariadb-bin.index# not fab for performance, but safer#sync_binlog = 1expire_logs_days = 10max_binlog_size = 100M# slaves#relay_log = /var/log/mysql/relay-bin#relay_log_index = /var/log/mysql/relay-bin.index#relay_log_info_file = /var/log/mysql/relay-bin.info#log_slave_updates#read_only## If applications support it, this stricter sql_mode prevents some# mistakes like inserting invalid dates etc.#sql_mode = NO_ENGINE_SUBSTITUTION,TRADITIONAL## * InnoDB## InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.# Read the manual for more InnoDB related options. There are many!default_storage_engine = InnoDB# you can't just change log file size, requires special procedureinnodb_log_file_size = 2Ginnodb_buffer_pool_size = 7Ginnodb_log_buffer_size = 8Minnodb_file_per_table = 1innodb_open_files = 400innodb_io_capacity = 400innodb_flush_method = O_DIRECT
innodb_additional_mem_pool_size=2Ginnodb_flush_log_at_trx_commit=1innodb_thread_concurrency=0innodb_read_io_threads=64innodb_write_io_threads=64innodb_autoextend_increment=64innodb_buffer_pool_instances=8innodb_concurrency_tickets=5000innodb_old_blocks_time=1000innodb_stats_on_metadata=0innodb_checksum_algorithm=0innodb_locks_unsafe_for_binlog = 1innodb_autoinc_lock_mode = 2
back_log=80flush_time=0max_connect_errors=100open_files_limit=4161table_definition_cache=1400sync_master_info=1000sync_relay_log=10000sync_relay_log_info=10000
## * Security Features## Read the manual, too, if you want chroot!# chroot = /var/lib/mysql/## For generating SSL certificates I recommend the OpenSSL GUI "tinyca".## ssl-ca=/etc/mysql/cacert.pem# ssl-cert=/etc/mysql/server-cert.pem# ssl-key=/etc/mysql/server-key.pem
## * Galera-related settings#[galera]# Mandatory settingswsrep_on=ON
#wsrep_debug=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_provider_options="gcache.size=2G; gcache.recover=yes; gcs.fc_limit=1000; gcs.fc_master_slave=yes; pc.weight=2;ist.recv_bind=127.0.0.1"wsrep_cluster_name=mariadb-cluster-nmb#on live node gcom address must be emptywsrep_cluster_address=gcomm://wsrep_node_address="192.168.114.39"wsrep_node_incoming_address="192.168.114.39"wsrep_node_name="nmb_main"wsrep_sst_receive_address="192.168.114.39"wsrep_sst_method=xtrabackup-v2wsrep_sst_auth=root:<correct-password>
binlog_format=row
wsrep_slave_threads=8
#innodb_flush_log_at_trx_commit=0
[mysqldump]quickquote-namesmax_allowed_packet = 64M
[mysql]#no-auto-rehash # faster start of mysql but no tab completion
[isamchk]key_buffer = 16M
!includedir /etc/mysql/conf.d/
My.cnf of JOINER
[client]
port = 3306socket = /var/run/mysqld/mysqld.sock
# Here is entries for some specific programs# The following values assume you have at least 32M ram
# This was formally known as [safe_mysqld]. Both versions are currently parsed.[mysqld_safe]socket = /var/run/mysqld/mysqld.socknice = 0
[mysqld]
# Settings for replicationserver_id=2bind-address=0.0.0.0
skip-host-cacheskip-name-resolve## * Basic Settings##user = mysqlpid-file = /var/run/mysqld/mysqld.pidsocket = /var/run/mysqld/mysqld.sockport = 3306basedir = /usrdatadir = /var/lib/mysqltmpdir = /tmplc_messages_dir = /usr/share/mysqllc_messages = en_USskip-external-locking
character-set-server=utf8default-storage-engine=INNODBsql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
## Instead of skip-networking the default is now to listen only on# localhost which is more compatible and is not less secure.#bind-address = 127.0.0.1## * Fine Tuning#max_connections = 251connect_timeout = 10000wait_timeout = 6000max_allowed_packet = 16Mthread_cache_size = 128sort_buffer_size = 4Mbulk_insert_buffer_size = 16Mtmp_table_size = 256Mmax_heap_table_size = 32M## * MyISAM## This replaces the startup script and checks MyISAM tables if needed# the first time they are touched. On error, make copy and try a repair.myisam_max_sort_file_size=100Gmyisam_recover_options = BACKUPkey_buffer_size = 8G#open-files-limit = 2000table_open_cache = 2000myisam_sort_buffer_size = 222Mconcurrent_insert = 2read_buffer_size = 2Mread_rnd_buffer_size = 1M## * Query Cache Configuration## Cache only tiny result sets, so we can fit more in the query cache.query_cache_limit = 100Mquery_cache_size = 1G# for more write intensive setups, set to DEMAND or OFF#query_cache_type = DEMAND## * Logging and Replication## Both location gets rotated by the cronjob.# Be aware that this log type is a performance killer.# As of 5.1 you can enable the log at runtime!#general_log_file = /var/log/mysql/mysql.log#general_log = 1
## Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.## we do want to know about network errors and suchlog_warnings = 2## Enable the slow query log to see queries with especially long durationslow_query_log=1slow_query_log_file = /var/lib/mysql/mariadb-slow.loglong_query_time = 4#log_slow_rate_limit = 1000#log_slow_verbosity = query_plan
relay-log-space-limit=6G
#log-queries-not-using-indexes#log_slow_admin_statements## The following can be used as easy to replay backup logs or for replication.# note: if you are setting up a replication slave, see README.Debian about# other settings you may need to change.#server-id = 1#report_host = master1#auto_increment_increment = 2#auto_increment_offset = 1#log_bin = /var/log/mysql/mariadb-bin#log_bin_index = /var/log/mysql/mariadb-bin.index# not fab for performance, but safer#sync_binlog = 1expire_logs_days = 10max_binlog_size = 100M# slaves#relay_log = /var/log/mysql/relay-bin#relay_log_index = /var/log/mysql/relay-bin.index#relay_log_info_file = /var/log/mysql/relay-bin.info#log_slave_updates#read_only## If applications support it, this stricter sql_mode prevents some# mistakes like inserting invalid dates etc.#sql_mode = NO_ENGINE_SUBSTITUTION,TRADITIONAL## * InnoDB## InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.# Read the manual for more InnoDB related options. There are many!default_storage_engine = InnoDB# you can't just change log file size, requires special procedureinnodb_log_file_size = 2Ginnodb_buffer_pool_size = 7Ginnodb_log_buffer_size = 8Minnodb_file_per_table = 1innodb_open_files = 400innodb_io_capacity = 400innodb_flush_method = O_DIRECT
innodb_additional_mem_pool_size=2Ginnodb_flush_log_at_trx_commit=1innodb_thread_concurrency=0innodb_read_io_threads=64innodb_write_io_threads=64innodb_autoextend_increment=64innodb_buffer_pool_instances=8innodb_concurrency_tickets=5000innodb_old_blocks_time=1000innodb_stats_on_metadata=0innodb_checksum_algorithm=0innodb_locks_unsafe_for_binlog = 1innodb_autoinc_lock_mode = 2
back_log=80flush_time=0max_connect_errors=100open_files_limit=4161table_definition_cache=1400sync_master_info=1000sync_relay_log=10000sync_relay_log_info=10000
#
# * Galera-related settings#[galera]# Mandatory settingswsrep_on=ON
#wsrep_debug=ON
#wsrep_sst_method: xtrabackup-v2 to prevent donor node not accessible during replication!
wsrep_dirty_reads=ONwsrep_provider=/usr/lib/galera/libgalera_smm.sowsrep_provider_options="gcache.size=2G; gcache.recover=yes; gcs.fc_limit=1000; gcs.fc_master_slave=yes; pc.weight=0; ist.recv_bind=127.0.0.1"wsrep_cluster_name=mariadb-cluster-nmbwsrep_cluster_address=gcomm://192.168.114.39,192.168.60.123wsrep_node_address="192.168.60.123"wsrep_node_incoming_address="192.168.60.123"wsrep_node_name="nmb_backup"wsrep_sst_receive_address="192.168.60.123"wsrep_sst_method=xtrabackup-v2wsrep_sst_auth=root:<correct-password>
binlog_format=row
# Optional setting
wsrep_slave_threads=69
#innodb_flush_log_at_trx_commit=0
[mysqldump]quickquote-namesmax_allowed_packet = 64M
[mysql]#no-auto-rehash # faster start of mysql but no tab completion
[isamchk]key_buffer = 16M
## * IMPORTANT: Additional settings that can override those from this file!# The files must end with '.cnf', otherwise they'll be ignored.#!includedir /etc/mysql/conf.d/JOINER and DONOR logs are included as attachements
output of DONOR wsrep status
Output of status wsrep JOINER
Reply all
Reply to author
Forward
0 new messages