problem replicating my pcx 5.7 on an AWS instance

Good morning guys

I am having a problem replicating my pcx 5.7 on an AWS instance.

I’ve tried replicating over the internet and the VPC, the problems continue. :frowning:

I can not get past these timeout errors

Does anyone have any ideas ?

Follow the logs.

Packets Pings
Loss% Snt Last Avg Best Wrst StDev
0.5% 3662 3.8 3.4 3.1 266.1 6.1

Transfer is at 15Mb.

Servers without firewall

joiner

/var/lib/mysql# du -h
1.5G .

joiner log

service mysql start

2017-07-20T13:39:01.620686Z 2 [Note] WSREP: State gap can’t be serviced using IST. Switching to SST
2017-07-20T13:39:01.627005Z 0 [Note] WSREP: Member 0.0 (ip-x.x.x.x) requested state transfer from ‘any’. Selected 1.0 (teste-mha-01)(SYNCED) as donor.
2017-07-20T13:39:01.627024Z 0 [Note] WSREP: Shifting PRIMARY -> JOINER (TO: 55690)
2017-07-20T13:39:01.627046Z 2 [Note] WSREP: Requesting state transfer: success, donor: 1
2017-07-20T13:39:01.627070Z 2 [Note] WSREP: GCache history reset: old(00000000-0000-0000-0000-000000000000:0) -> new(a8d37d7e-5c3b-11e7-9fbf-36d50cc17d69:55690)
2017-07-20T13:39:04.034731Z 0 [Note] WSREP: (c9e807ea, ‘tcp://0.0.0.0:4567’) connection to peer c9e807ea with addr tcp://x.x.x.x:4567 timed out, no messages seen in PT3S
2017-07-20T13:39:04.534737Z 0 [Note] WSREP: (c9e807ea, ‘tcp://0.0.0.0:4567’) turning message relay requesting off
Job for mysql.service failed because a timeout was exceeded. See “systemctl status mysql.service” and “journalctl -xe” for details.
root@ip-x.x.x.x:/var/lib/mysql# systemctl status mysql.service
● mysql.service - LSB: Start and stop the mysql (Percona XtraDB Cluster) daemon
Loaded: loaded (/etc/init.d/mysql; bad; vendor preset: enabled)
Active: failed (Result: timeout) since Thu 2017-07-20 13:44:00 UTC; 8min ago
Docs: man:systemd-sysv-generator(8)
Process: 1211 ExecStart=/etc/init.d/mysql start (code=killed, signal=TERM)
Tasks: 15
Memory: 1.5G
CPU: 26.864s
CGroup: /system.slice/mysql.service
├─1253 /bin/sh /usr/bin/mysqld_safe
├─2243 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --wsrep-provider=/usr/lib/libgalera_smm.so --log-error=/var/log/mysql/mysql-error.log --open-files-limit=10
├─2254 sh -c wsrep_sst_rsync --role ‘joiner’ --address ‘x.x.x.x’ --datadir ‘/var/lib/mysql/’ --defaults-file ‘/etc/mysql/my.cnf’ --defaults-group-suffix ‘’ --parent ‘2243’ ‘’
├─2255 /bin/bash -ue /usr/bin/wsrep_sst_rsync --role joiner --address x.x.x.x --datadir /var/lib/mysql/ --defaults-file /etc/mysql/my.cnf --defaults-group-suffix --parent 2243
├─2339 rsync --daemon --no-detach --port 4444 --config /var/lib/mysql//rsync_sst.conf
├─4403 rsync --daemon --no-detach --port 4444 --config /var/lib/mysql//rsync_sst.conf
├─4409 rsync --daemon --no-detach --port 4444 --config /var/lib/mysql//rsync_sst.conf
└─6973 sleep 1

Jul 20 13:45:07 ip-x.x.x.x rsyncd[4398]: receiving file list
Jul 20 13:45:07 ip-x.x.x.x rsyncd[4402]: receiving file list
Jul 20 13:45:07 ip-x.x.x.x rsyncd[4403]: receiving file list
Jul 20 13:45:07 ip-x.x.x.x rsyncd[4401]: receiving file list
Jul 20 13:45:08 ip-x.x.x.x rsyncd[4404]: receiving file list
Jul 20 13:45:10 ip-x.x.x.x rsyncd[4431]: connect from ip-10-10-10-101.sa-east-1.compute.internal (10.10.10.101)
Jul 20 13:45:10 ip-x.x.x.x rsyncd[4431]: rsync to rsync_sst/./performance_schema from ip-10-10-10-101.sa-east-1.compute.internal (10.10.10.101)
Jul 20 13:45:10 ip-x.x.x.x rsyncd[4431]: receiving file list
Jul 20 13:45:37 ip-x.x.x.x rsyncd[4401]: rsync: connection unexpectedly closed (10869 bytes received so far) [generator]
Jul 20 13:45:37 ip-x.x.x.x rsyncd[4401]: rsync error: error in rsync protocol data stream (code 12) at io.c(226) [generator=3.1.1]

joiner conf

cat /etc/mysql/my.cnf

[mysql]

CLIENT

port = 3306
socket = /var/run/mysqld/mysqld.sock

[mysqld]
federated
performance_schema

innodb_force_recovery=0

pxc_strict_mode = PERMISSIVE

GENERAL

user = mysql
default_storage_engine = InnoDB
socket = /var/run/mysqld/mysqld.sock
pid_file = /var/run/mysqld/mysqld.pid
bind_address = 0.0.0.0
skip-name-resolve

DATA STORAGE

datadir = /var/lib/mysql/
tmpdir = /var/lib/mysql/tmp

MyISAM

key_buffer_size = 32M
myisam_recover_options = FORCE,BACKUP

Path to Galera library

wsrep_provider = /usr/lib/libgalera_smm.so

Cluster connection URL contains the IPs of node#1, node#2 and node#3

wsrep_cluster_address = gcomm://10.10.10.101,x.x.x.x

In order for Galera to work correctly binlog format should be ROW

binlog_format = ROW

MyISAM storage engine has only experimental support

default_storage_engine = InnoDB

This changes how InnoDB autoincrement locks are managed and is a requirement for Galera

innodb_autoinc_lock_mode = 2

Node #1 address

wsrep_node_address = x.x.x.x

SST method

wsrep_sst_method = rsync

INNODB

innodb_flush_method = O_DIRECT
innodb_log_files_in_group = 2
innodb_log_file_size = 265Mb
innodb_flush_log_at_trx_commit = 0
innodb_file_per_table = 1
innodb_buffer_pool_size = 4G
thread_pool_size = 36
innodb_doublewrite = 1
innodb_thread_concurrency = 0
innodb_max_dirty_pages_pct = 80
innodb_thread_concurrency = 0
innodb_buffer_pool_instances = 20
innodb_log_buffer_size = 8M
innodb_lock_wait_timeout = 120
thread_pool_idle_timeout = 3600

binlog_row_event_max_size = 16M
binlog_cache_size = 16M

Gcache

#wsrep_provider_options = “gcache.size=300M; gcache.page_size=300M; gmcast.segment=2; evs.send_window=512; evs.user_send_window=256; gcs.fc_limit=128; evs.inactive_timeout = PT90S; evs.suspect_timeout = PT30S”
wsrep_provider_options = " gcs.max_packet_size=1048576; evs.send_window=512; evs.user_send_window=256; evs.inactive_timeout = PT100S; evs.suspect_timeout = PT60S"

#myisam
#wsrep_replicate_myisam = 1

Cluster name

wsrep_cluster_name = ev-cluster-teste

Node Name

wsrep_node_name = ip-x-x-x-x

Set to number of cpu cores.

wsrep_slave_threads = 8

to enable debug level logging, set this to 1

wsrep_debug = 0

#innodb_print_all_deadlock = 1

how many times to retry deadlocked autocommits

wsrep_retry_autocommit = 1

convert locking sessions into transactions

wsrep_convert_LOCK_to_trx = 1

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

wsrep_certify_nonPK = 1

Required for Galera

innodb_locks_unsafe_for_binlog = 1

BINARY LOGGING

log_bin = /var/lib/mysql/mysql-bin
expire_logs_days = 5
sync_binlog = 1
server_id = 1

SAFETY

max_allowed_packet = 16M
max_connect_errors = 1000000
skip_name_resolve
sql-mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE #,ONLY_FULL_GROUP_BY
sysdate_is_now = 1
innodb = FORCE
innodb_strict_mode = 1

CACHES AND LIMITS

tmp_table_size = 256M
max_heap_table_size = 256M
query_cache_type = 0
query_cache_size = 0
max_connections = 100
thread_cache_size = 120
open_files_limit = 100000
table_definition_cache = 4096
table_open_cache = 4096
read_buffer_size = 8M
query_cache_limit = 1M
join_buffer_size = 6M
#table_cache = 2000

LOGGING

log_error = /var/log/mysql/mysql-error.log
#log_queries_not_using_indexes = 1
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log

general_log_file = /var/log/mysql/mysql-general.log
general_log = 1

donor

/var/lib/mysql# du -h
64G .

donor log

2017-07-20T14:07:53.707628Z 2 [Note] WSREP: DONOR thread signaled with 0
2017-07-20T14:07:53.787297Z 0 [Note] WSREP: Flushing tables for SST…
2017-07-20T14:07:53.794344Z 0 [Note] WSREP: Provider paused at a8d37d7e-5c3b-11e7-9fbf-36d50cc17d69:55690 (5)
2017-07-20T14:07:53.797108Z 0 [Note] WSREP: Table flushing completed.
2017-07-20T14:07:54.004707Z WSREP_SST: [INFO] Starting rsync of data-dir…
2017-07-20T14:07:55.810592Z 0 [Note] WSREP: (c4b642ee, ‘tcp://0.0.0.0:4567’) turning message relay requesting off
rsync: read error: Connection timed out (110)
rsync error: error in socket IO (code 10) at io.c(802) [sender=3.1.0]
rsync: read error: Connection timed out (110)
rsync error: error in socket IO (code 10) at io.c(802) [sender=3.1.0]
rsync: read error: Connection timed out (110)
rsync error: error in socket IO (code 10) at io.c(802) [sender=3.1.0]

network

net.core.somaxconn = 1000
net.core.netdev_max_backlog = 5000
net.core.rmem_max = 16777216
net.core.wmem_max = 16777216
net.ipv4.tcp_wmem = 4096 12582912 16777216
net.ipv4.tcp_rmem = 4096 12582912 16777216
net.ipv4.tcp_max_syn_backlog = 8096
net.ipv4.tcp_slow_start_after_idle = 0
net.ipv4.tcp_tw_reuse = 1
net.ipv4.ip_local_port_range = 10240 65535

fs.file-max=200000
kernel.sem=250 32000 100 1024
kernel.shmmax=4294967295

net.ipv4.tcp_retries2 = 2
net.ipv4.tcp_keepalive_time = 12
net.ipv4.tcp_keepalive_intvl = 2
net.ipv4.tcp_keepalive_probes = 9

vm.swappiness = 0
vm.dirty_ratio = 80
vm.dirty_background_ratio = 5
vm.dirty_expire_centisecs = 12000

dpkg -l | grep percona

ii percona-release 0.1-4.xenial all Package to install Percona gpg key and APT repo
ii percona-xtrabackup-24 2.4.7-2.xenial amd64 Open source backup tool for InnoDB and XtraDB
ii percona-xtradb-cluster-5.7-dbg 5.7.18-29.20-1.xenial amd64 Debugging package for Percona XtraDB Cluster
ii percona-xtradb-cluster-client-5.7 5.7.18-29.20-1.xenial amd64 Percona XtraDB Cluster database client binaries
ii percona-xtradb-cluster-common-5.7 5.7.18-29.20-1.xenial amd64 Percona XtraDB Cluster database common files (e.g. /etc/mysql/my.cnf)
ii percona-xtradb-cluster-full-57 5.7.18-29.20-1.xenial amd64 Percona XtraDB Cluster with Galera
ii percona-xtradb-cluster-garbd-5.7 5.7.18-29.20-1.xenial amd64 Garbd components of Percona XtraDB Cluster
ii percona-xtradb-cluster-garbd-debug-5.7 5.7.18-29.20-1.xenial amd64 Debugging package for Percona XtraDB Cluster Garbd.
ii percona-xtradb-cluster-server-5.7 5.7.18-29.20-1.xenial amd64 Percona XtraDB Cluster database server binaries
ii percona-xtradb-cluster-server-debug-5.7 5.7.18-29.20-1.xenial amd64 Percona XtraDB Cluster database server UNIV_DEBUG binaries
ii percona-xtradb-cluster-test-5.7 5.7.18-29.20-1.xenial amd64 Percona XtraDB Cluster database test suite