MySQL Forums
Forum List  »  Replication

Daisy Chained Replication Lag
Posted by: Jessica Kearney
Date: June 23, 2015 10:43AM

Good Morning,

I've hit wits end with my MySQL setup and need help from some people that actually know what they're doing. Anything I know I learned from google and that's only gotten me so far.

I have a 3 Node setup, Primary (in house), HA (disaster location) and Backup (in hosue). Primary replicates to HA and HA Replicates to Backup.

At first this worked fine but as we started deploying the application to more people the Backup machine was no longer able to keep up and just gets further and further behind never even coming close to catching up.

I thought the machine was too weak to keep up with the other more powerful machines so I bought a more powerful server to host it on and it helped... slightly but not nearly enough. Looking at the 'Seconds behind master' number it seems like every hour it's only processing 15 minutes of commands because every hour it gets 44 minutes more behind.

I'm losing my mind and any help or advice or any information at all really would be greatly appreciated!

The machine specs are as follows:
Primary - VM - 4 CPU cores - 16 GB RAM - 1TB HDD - 1TB Swap
HA - VM - 4 CPU cores - 16 GB RAM - 1TB HDD - 1TB Swap
Backup - Not VM - 3.2Ghz Quad Core Xeon - 12GB RAM - 1TB HDD

Config Files:

Primary my.cnf
[mysqld]
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
innodb_buffer_pool_size = 10752M
datadir=/opt/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
default-storage-engine = InnoDB
tmpdir = /opt/mysqltmp
# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[mysqld]
#REPLICATION SPECIFIC - GENERAL
server-id=101
#REPLICATION SPECIFIC - MASTER
log-bin=binlog
max_binlog_size=200M
binlog-format=mixed
binlog-do-db=mysql
binlog-do-db=mcscrmQA
binlog-do-db=sugarcrmQA
binlog-do-db=dbSTQA
binlog-do-db=test
binlog-do-db=dbSTProd
binlog-do-db=mcscrmProd
binlog-do-db=sugarcrmProd
binlog-do-db=testProd
expire-logs-days=5
log-slave-updates=1
relay-log=SugarSQLHA-relay-bin
max_relay_log_size=0
slave-exec-mode=STRICT
bind-address=172.17.1.39
max_allowed_packet = 16M
open_files_limit=100000

HA my.cnf
[mysqld]
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
innodb_buffer_pool_size = 10752M
datadir=/opt/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
#4/6-4/7 Test Variables
default-storage-engine = InnoDB
# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[mysqld]
server-id=102
binlog-format=mixed
log-bin=binlog
max_binlog_size=200M
max_relay_log_size=0
log-slave-updates=1
relay-log=SugarSQLbkup-relay-bin
slave-exec-mode=STRICT
expire-logs-days=5
slave_max_allowed_packet = 32M
max_allowed_packet = 16M
open_files_limit=100000
#Test Variables
innodb_io_capacity=350
innodb_print_all_deadlocks=on
innodb_random_read_ahead=on
innodb_read_io_threads=25
innodb_write_io_threads=35
slave_parallel_workers=20
replicate-ignore-table=sugarcrmProd.tracker
replicate-ignore-table=sugarcrmQA.tracker

Backup my.cnf
[mysqld]
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
innodb_buffer_pool_size = 8193M
datadir=/opt/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
#5/19 Test Variables
default-storage-engine = InnoDB
# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[mysqld]
server-id=203
binlog-format=mixed
log-bin=binlog
log-slave-updates=1
slave-exec-mode=STRICT
expire-logs-days=5
max_allowed_packet = 16M
slave_max_allowed_packet = 32M
open_files_limit=100000
max_binlog_size=200M
#5/19 Test
innodb_io_capacity=350
innodb_print_all_deadlocks=on
innodb_random_read_ahead=on
innodb_read_io_threads=25
innodb_write_io_threads=35
slave_parallel_workers=20
replicate-ignore-table=sugarcrmProd.tracker
replicate-ignore-table=sugarcrmQA.tracker
replicate-ignore-table=sugarcrmProd.wv_lead_view
replicate-ignore-table=sugarcrmQA.wv_lead_view

Options: ReplyQuote


Subject
Views
Written By
Posted
Daisy Chained Replication Lag
2220
June 23, 2015 10:43AM
1204
June 30, 2015 05:28PM
936
July 08, 2015 07:38PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.