xtrabackup always fails after 16GB of data

[RG]

Ever since my data directory grew past 16GB, if the SST method is xtrabackup-v2, when I see the transfer reach about 16GB, I always get this on the receiving node:

WSREP_SST: [INFO] Evaluating socat -u TCP-LISTEN:4444,reuseaddr stdio | xbstream -x; RC=( ${PIPESTATUS[@]} ) (20150929 11:26:21.208)
xb_stream_read_chunk(): my_read() failed.

donor logs don't seem to have an error around that time.

This is in CentOS 6.5 in a server with 4GB of main memory. What more data do you need related to this?

rsync seems to work instead, but I'd rather use xtrabackup-v2, if possible.

What should I be looking for in the configuration?

P.S. Can rsync SST be run in compressed mode? where should I set the parameters for it?

Thanks

[James Wang]

what is your innodb_log_file_size please?

[RG]

[MYSQLD]
innodb_force_recovery = 3
default-time-zone='America/Chicago'
performance_schema=on
user=mysql
basedir=/usr/
datadir=/var/lib/mysql/data_dir
socket=/var/lib/mysql/mysql.sock
pid_file=mysqld.pid
port=3306
log_error=/var/log/mysqld.log
log_warnings=2
open_files_limit = 1024000
#log_output=FILE
### INNODB OPTIONS
innodb_buffer_pool_size=2642M
innodb_open_files = 1024000
innodb_flush_log_at_trx_commit=2
innodb_file_per_table=1
innodb_data_file_path = ibdata1:100M:autoextend
## You may want to tune the below depending on number of cores and disk sub
innodb_read_io_threads=4
innodb_write_io_threads=4
innodb_io_capacity=200
innodb_doublewrite=1
innodb_log_file_size=512M
innodb_log_buffer_size=64M
innodb_buffer_pool_instances=4
innodb_log_files_in_group=2
innodb_thread_concurrency=0
#innodb_file_format=barracuda
innodb_flush_method = O_DIRECT
innodb_autoinc_lock_mode=2
## avoid statistics update when doing e.g show tables
innodb_stats_on_metadata=0
default_storage_engine=innodb

# CHARACTER SET
#collation_server = utf8_unicode_ci
#init_connect='SET NAMES utf8'
#character_set_server = utf8

# REPLICATION SPECIFIC _ GENERAL
#server_id must be unique across all mysql servers participating in replication.
#server_id=SERVERID
# REPLICATION SPECIFIC
binlog_format=ROW
#log_bin=binlog
#relay_log=relay-bin
#expire_logs_days=7
#log_slave_updates=1
#gtid_mode=ON
#log_bin=binlog
#enforce_gtid_consistency=1
# OTHER THINGS, BUFFERS ETC
key_buffer_size = 24M
tmp_table_size = 64M
max_heap_table_size = 64M
max_allowed_packet = 512M
#sort_buffer_size = 256K
#read_buffer_size = 256K
#read_rnd_buffer_size = 512K
#myisam_sort_buffer_size = 8M
skip_name_resolve
memlock=0
sysdate_is_now=1
max_connections=200
thread_cache_size=512
query_cache_type = 0
query_cache_size = 0
table_open_cache=120000
lower_case_table_names=0
# 5.6 backwards compatibility
explicit_defaults_for_timestamp=1
##
## WSREP options
##

# Full path to wsrep provider library or 'none'
wsrep_provider=/usr/lib64/galera/libgalera_smm.so

wsrep_node_address=10.87.85.112
# Provider specific configuration options
# Original wsrep_provider_options="gcache.size=128M; gmcast.segment=1; evs.keepalive_period=PT3S; evs.inactive_check_period=PT10S; evs.suspect_timeout=PT30S; evs.inactive_timeout=PT1M; evs.install_timeout=PT1M; evs.send_window=1024; evs.user_send_window=512;"
wsrep_provider_options="gcache.size=4096M; gmcast.segment=1; evs.keepalive_period=PT3S; evs.inactive_check_period=PT10S; evs.suspect_timeout=PT30S; evs.inactive_timeout=PT1M; evs.install_timeout=PT1M; evs.send_window=1024; evs.user_send_window=512;"

# Logical cluster name. Should be the same for all nodes.
wsrep_cluster_name="my_wsrep_cluster"

# Group communication system handle
wsrep_cluster_address=gcomm://10.87.85.111:4567,172.16.14.226:4567,172.16.14.227:4567,10.87.85.112:4567

# Human_readable node name (non-unique). Hostname by default.
wsrep_node_name=10.87.85.112

# Address for incoming client connections. Autodetect by default.
#wsrep_node_incoming_address=

# How many threads will process writesets from other nodes
wsrep_slave_threads=8

# DBUG options for wsrep provider
#wsrep_dbug_option

# Generate fake primary keys for non-PK tables (required for multi-master
# and parallel applying operation)
wsrep_certify_nonPK=1

# Location of the directory with data files. Needed for non-mysqldump
# state snapshot transfers. Defaults to mysql_real_data_home.
#wsrep_data_home_dir=

# Maximum number of rows in write set
wsrep_max_ws_rows=131072

# Maximum size of write set
wsrep_max_ws_size=1073741824

# to enable debug level logging, set this to 1
wsrep_debug=0

# convert locking sessions into transactions
wsrep_convert_LOCK_to_trx=0

# how many times to retry deadlocked autocommits
wsrep_retry_autocommit=1

# change auto_increment_increment and auto_increment_offset automatically
wsrep_auto_increment_control=1

# replicate myisam
wsrep_replicate_myisam=1
# retry autoinc insert, which failed for duplicate key error
wsrep_drupal_282555_workaround=0

# enable "strictly synchronous" semantics for read operations
wsrep_causal_reads=0

# Command to call when node status or cluster membership changes.
# Will be passed all or some of the following options:
# --status  - new status of this node
# --uuid    - UUID of the cluster
# --primary - whether the component is primary or not ("yes"/"no")
# --members - comma-separated list of members
# --index   - index of this node in the list
#wsrep_notify_cmd=

##
## WSREP State Transfer options
##

# State Snapshot Transfer method
# ClusterControl currently DOES NOT support wsrep_sst_method=mysqldump
#wsrep_sst_method=xtrabackup-v2
wsrep_sst_method=rsync

# Address on THIS node to receive SST at. DON'T SET IT TO DONOR ADDRESS!!!
# (SST method dependent. Defaults to the first IP of the first interface)
#wsrep_sst_receive_address=

# SST authentication string. This will be used to send SST to joining nodes.
# Depends on SST method. For mysqldump method it is root:<root password>
wsrep_sst_auth=root:*******

# Desired SST donor name.
#wsrep_sst_donor=

# Protocol version to use
# wsrep_protocol_version=
[MYSQL]
socket=/var/lib/mysql/mysql.sock
#default_character_set=utf8
[client]
socket=/var/lib/mysql/mysql.sock
#default_character_set=utf8
[mysqldump]
max_allowed_packet = 512M
socket=/var/lib/mysql/mysql.sock
#default_character_set=utf8
[MYSQLD_SAFE]
pid_file=mysqld.pid
log_error=/var/log/mysqld.log
basedir=/usr/
datadir=/var/lib/mysql/data_dir

[RG]

In this config file, the SST method is rsync (after the failure to use xtrabackup)

On Wednesday, September 30, 2015 at 7:49:59 AM UTC-5, RG wrote:

[James Wang]

Here is URL may of use to you:

http://www.percona.com/blog/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/

Larger log file may make mysql restarting, stopping and starting longer.

So, suggestion: if SST using xtrabackup-2, do SST at less busy hours (out of hours?)

Otherwise, as you already did: use rsync for SST (but it blocks donor though it is now the default - Alexey advised in May London MariaDB show)

[RG]

Hi James,

I'm confused about the innodb_log_file_size versus xtrabackup "crashing" after 16GB? I can adjust the value, but that's only for start/stop. xtrabackup has run at many different hours and always with the same result. Thanks

On Wednesday, September 30, 2015 at 7:49:59 AM UTC-5, RG wrote:

[James Wang]

The URL I forwarded to you explains how to set the log file size.

You shall need a large log file if you application has large transactions.  

Do you have issue using rsync please?

[RG]

I kind of liked the idea of using xtrabackup to keep the donor node available. I tried to search to see if other people had the same problem with xtrabackup and I couldn't find out much stuff so I was wondered if my configuration is incorrect or something. rsync seems to work ok, but if possible, I'd like to have it running with compression enabled, but don't know how to set that up or if anybody has done that.

Thanks!

On Wednesday, September 30, 2015 at 7:49:59 AM UTC-5, RG wrote:

[James Wang]

post mysql command output here: 

show variables like 'innodb_file_per_table';

we used xtrabackup-v2 and our databases size total 40GB and no such a problem.

[James Wang]

Also,  you have set up some timeouts in

evs.inactive_check_period=
wsrep_provider_options=

We just leave these as default

[RG]

innodb_file_per_table is set to ON, per the config file (and double-checked the status)

We have 2 segments and we experience sometimes timeouts between them, so it was recommended to modify the evs.inactive_check_period and the wsrep_provider_options. The failure after 16GB is seen between 2 nodes of different segments and also in the same segment. I wonder if there's a configuration setting for xtrabackup that is affecting this since it happens every time in the same place.

Thanks for taking the time to check on this

On Wednesday, September 30, 2015 at 7:49:59 AM UTC-5, RG wrote:

[James Wang]

is the socat process still running in back ground?  if yes, kill it.  I remembered that I had some issue with xtrabackup-v2 14 months ago.  it was because somehow the socat process were running in background.

[James Wang]

Any update please?

On Thursday, 1 October 2015 14:53:43 UTC+1, RG wrote:

[RG]

I cannot try an SST at the moment since I'm in the middle of tests. As soon as I try one, I'll post the results, although in the meanwhile I switched to rsync

On Wednesday, September 30, 2015 at 7:49:59 AM UTC-5, RG wrote: