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

Network Latency effecting reads

cfinnsoncfinnson EntrantLegacy User Role Beginner
Hi,

I'm currently attempting to configure Percona Cluster over WAN. I'm running into the problem though where reads seem to be slowed down because of the increased network latency. If writes take a bit longer that isn't too big of a deal for me but the longer reads are a killer.
The reads aren't always slow. I would say about 50% of the time it is no different than if the servers were connected only over the LAN. Other times though it takes way longer.
Every guide that I have seen has said that reads will not be effected by the increased network latency so I'm not really sure what is going on.


To test this I have 3 servers in a cluster. 2 are on a LAN together, and 1 is across the WAN. On average there's about a 2ms ping.
The tables that I'm reading from in my tests are never being written to. Only ever read from.
My test script does 200 separate selects of the same data from that table every time. Usually it takes around 0.15s to do this. On WAN I have seen it reach 5s.
I have a separate gmcast.segment assigned to the LAN side and the WAN side.

The servers are running CentOS 6. Our percona version is: 5.6.43-84.3-56-log Percona XtraDB Cluster
WSREP: version 28.32

Here is my my.cnf:

#############################
# GENERAL MYSQL
#############################
[client]
port=3306
socket=/var/lib/mysql/mysql.sock
[safe_mysqld]
port=3306
socket=/var/lib/mysql/mysql.sock
log_error=/var/log/mysql/mysql_error.log
[mysql]
port=3306
socket=/var/lib/mysql/mysql.sock

#############################
# DIRECTORY
#############################
[mysqld]
datadir=/var/lib/mysql
user=mysql
log_error=/var/log/mysql/mysql_error.log
general_log_file=/var/log/mysql/mysql.log
general_log=0
innodb_buffer_pool_size=1GB
thread_handling=pool-of-threads
max_connections=1024
max_allowed_packet=16M
innodb_log_file_size=256M

#############################
# MASTER SETTINGS
#############################
server_id=1
log_bin=percona-bin
log_slave_updates
enforce_gtid_consistency=1
gtid_mode=on

#############################
# CLUSTER CONNECTION URL
#############################
wsrep_cluster_address=gcomm://172.18.1.101,172.18.1.102,192.168.1.101

#############################
# CLUSTER NAME
#############################
wsrep_cluster_name=devcluster

#############################
# GENERAL PERCONA SETTINGS
#############################
wsrep_provider=/usr/lib64/libgalera_smm.so
binlog_format=ROW
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
wsrep_sst_method=xtrabackup-v2
max_binlog_files=8
max_binlog_size=1G

#############################
# WAN SETTINGS
#############################

wsrep_provider_options="gmcast.segment=0; gcs.max_packet_size=1048576; evs.send_window=512; evs.user_send_window=256; gcs.fc_limit=128; gcache.size=2G"
wsrep_slave_threads=16
binlog_row_event_max_size=2M
binlog_cache_size=2M

#############################
# IP OF THE LOCAL SERVER
#############################
wsrep_node_name=dev-db1
wsrep_node_address=172.18.1.101

#############################
# CLUSTER REPLICATION USERNAME AND PASSWORD
#############################
wsrep_sst_auth="sstuser:*****************"

Any ideas would be greatly appreciated.

Thank you
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.