Master Slave Replication Issue - Seconds behind master

We have Master Slave Mysql Setup, Master & slave hardware configuration is same (8 Core,30 GB Memory). These servers are Hosted on AWS.

My slave frequently is getting behind master . The current value for Seconds_Behind_Master is 114701, Not sure on how to get this sync master & slave Immediately. I can see that .

Master status - mysql-bin.000262 | 514064392
Slave Status - mysql-bin.000258 | 285395655

Below is my my.cnf configuration from Mysql Slave.

[mysql]

CLIENT

port = 3306
socket = /var/lib/mysql/mysql.sock

[mysqld]

GENERAL

user = mysql
default-storage-engine = InnoDB
socket = /var/lib/mysql/mysql.sock
pid-file = /srv/mysql/mysql.pid
skip_name_resolve

MyISAM

key-buffer-size = 32M
myisam-recover = FORCE,BACKUP

SAFETY

max-allowed-packet = 16M
max-connect-errors = 1000000

DATA STORAGE

datadir = /srv/mysql/

BINARY LOGGING

log-bin = /srv/mysqllogs/mysql-bin
max_binlog_size = 512M
expire-logs-days = 4
sync-binlog = 1

REPLICATION

server-id = 2
read-only = 1
log-slave-updates = 1
relay-log = /srv/mysqllogs/relay-bin
slave-net-timeout = 60
sync-master-info = 1
sync-relay-log = 1
sync-relay-log-info = 1
slave-skip-errors = 1062

CACHES AND LIMITS

tmp-table-size = 128M
max-heap-table-size = 128M
query-cache-type = 1
query-cache-size = 512M
max-connections = 4096
thread-cache-size = 100
open-files-limit = 65535
table-definition-cache = 4096
table-open-cache = 10240
#slave_parallel_workers = 3

INNODB

innodb-flush-method = O_DIRECT
innodb-log-files-in-group = 2
innodb-log-file-size = 256M
innodb-flush-log-at-trx-commit = 0
innodb-file-per-table = 1
innodb-buffer-pool-size = 24G

LOGGING

log-error = /srv/mysqllogs/mysql-error.log
#general_log_file = /srv/mysqllogs/mysql.log
#general_log = 1
log_warnings = 2
#log-queries-not-using-indexes = 1
#slow-query-log = 1
#slow-query-log-file = /srv/mysqllogs/mysql-slow.log

#tmpdir = /media/ephemeral0/tmp/

Could some one please help me in identifying & resolving the issue.

Hi atluri;

From your description, it sounds like the slave is getting behind due to MySQL replication being single-threaded. Your master is likely running large UPDATE/DELETE statements or DML that is taking a while to go through replication and blocking everything else behind it. So I would identify this type of traffic on your master, and the most likely fix will be to split up the UPDATE/DELETE statements into smaller chunks if that is the issue. If it is DML, then the only real work around would be using something like pt-online-schema-change or to run the DML first on your slave and then on the master with binary logging disabled for the session (note that would only work for non-destructive changes, like adding columns though).

-Scott