Mysql replication lags

Question

I am 2 slaves and 1 master on aws, 1 slave is already in sync with master but 2nd slave create huge slave lg behind, after investigation i found , this slave create numerous relay log in Kb's but earlier it create relay log in 100MB, when relay log is in MB, it works fine but when it is in KB, it start lagging.

Slave description: RAM : 32 GB Binlog format : ROW on both side. data partition and relay log is in same drive.

Data transfer rate between master and slave is 40 MB's .

mysql-slave> SHOW SLAVE STATUSG
*************************** 1. row ***************************
Slave_IO_State: Queueing master event to the relay log

slave lagged.

My.cnf

# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
#basedir         = /usr
basedir = /opt/mysql/server-5.6
#datadir         = /var/lib/mysql/datadir
datadir         = /data/mysql/data/
#tmpdir          = /tmp
#lc-messages-dir = /usr/share/mysql
lc-messages-dir = /opt/mysql/server-5.6/share
skip-external-locking
#log-slave-updates
performance_schema=0
key_buffer_size         = 64M
##open-files-limit = 40000
#
#
max_allowed_packet      = 1G
thread_stack            = 192K
thread_cache_size       = 32
#query_cache_size        = 256M
#query_cache_type        = 1
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
##myisam-recover         = BACKUP
#table_cache            = 1024
table_open_cache       = 512
log_error = /data/mysql/data/mysqllog.err
slave-skip-errors = 1062
#
 read_only=on

########Newamit#################
skip-name-resolve
skip-federated
skip-slave-start
#max_allowed_packet=1G
max_connect_errors=100000
#query_cache_size=256M
#query_cache_type=1
sort_buffer_size=2M
join_buffer_size=2M
innodb_doublewrite=OFF
##myisam_sort_buffer_size=8M
myisam_sort_buffer_size=4M

read_buffer_size=512K
#sort_buffer_size=20M
read_rnd_buffer_size=512K
max_connections=200

tmp_table_size=64M
server_id=151

relay-log=/data/binlog/relayLog/relay-log
auto_increment_increment=1
auto_increment_offset=2


#max_binlog_size=100M
interactive_timeout=600
#read_only=on
#tmpdir = /var/lib/mysql/mytmp/
tmpdir = /data/mysql/mytmp

#innodb_log_group_home_dir =/var/lib/mysql/iblogs
innodb_log_group_home_dir = /data/mysql/iblogs
innodb_log_files_in_group=3
innodb_log_file_size=500M
innodb_log_buffer_size=32M
#innodb_buffer_pool_instances=3
#innodb_thread_concurrency=8
innodb_flush_log_at_trx_commit=2
innodb_flush_method=O_DIRECT
binlog_format=row
#innodb_flush_log_at_trx_commit=2
#innodb_buffer_pool_size=25G
#innodb_buffer_pool_size=11G
innodb_buffer_pool_size=12G
innodb_additional_mem_pool_size=84M
#innodb_file_io_threads=8
innodb_read_io_threads=10
innodb_write_io_threads=20
#innodb_thread_concurrency=2

#innodb_lock_wait_timeout=50
#lock_wait_timeout=400

lower_case_table_names=0
log_output=FILE
query_cache_min_res_unit=8192

innodb_file_per_table=1
##datadir=/var/lib/mysql/daatdir
query_cache_type        = 1
query_cache_size        = 64M
query_cache_limit       = 5M

[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer              =50M

!includedir /etc/mysql/conf.d/

Answer 1

You can try the following on the lagging slave:

STOP SLAVE IO_THREAD;

This will stop the IO_THREAD which usually causes latency

Also please check that all your mysql servers are of the same version.