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

Master Slave Replication Issue - Seconds behind master

atluriatluri EntrantCurrent User Role Beginner
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.

Comments

  • scott.nemesscott.nemes MySQL Sage Current User Role Patron
    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
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.