Not the answer you need?
Register and ask your own question!

problem replicating my pcx 5.7 on an AWS instance

bdelmedicobdelmedico ContributorCurrent User Role Beginner
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. :(

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.
[email protected]:/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

Comments

  • bdelmedicobdelmedico Contributor Current User Role Beginner
    # 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
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.