We help IT Professionals succeed at work.

MySQL Replication Issue (120 000 seconds Delay To Slaves)

Omega002
Omega002 asked
on
We have MySQL master and two slaves. Our issue is that there is a 120 000 seconds delay in the replication to slaves. Is there a parameter in the my.cnf that could resolve this issue?
Comment
Watch Question

Senior Developer
BRONZE EXPERT
Commented:
There is a command that can force to delay or maybe you can set it using:

mk-slave-delay --delay 1m --interval 15s --run-time 10m slavehost

as described here:http://lucato.it/mysql-time-delayed-replication

Author

Commented:

This is the my.cnf file for the master and slave MySQL instances. Do yo see something that needs to change that would speed up the replication?

--Master/Slave 006 --

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package).
old_passwords=1
max_connect_errors=10000

#skip-slave=1
#slave-skip-errors=1062
#skip-networking
#read-only=1
server-id=6

# slave-skip-errors = 1032, 1452, 1062

binlog_format=mixed
log-bin=mysql-bin
max_binlog_size=1000M
expire_logs_days=8
log-slave-updates
auto_increment_increment=10
auto_increment_offset=6

master-host=10.20.16.40
master-port=3306
master-user=repl
master-password=Am2rua0t
master-connect-retry=60
replicate-ignore-db=dba

relay-log=/var/lib/mysql/mysqld-relay-bin
relay-log-index=/var/lib/mysql/mysqld-relay-bin

set-variable=long_query_time=1
slow_query_log=1
slow_query_log_file=/var/log/mysql/mysql-slow.log
log-output=FILE,TABLE

#key_buffer_size = 2048M
#key_buffer = 1024M
#max_allowed_packet = 64M
#table_cache = 512

#sort_buffer_size = 64M
#join_buffer_size = 64M
#read_buffer_size = 32M
#read_rnd_buffer_size = 32M

# Phil 2010-07-12
key_buffer_size = 4096M
key_buffer = 2048M
max_allowed_packet = 128M
table_cache = 2048
sort_buffer_size = 128M
join_buffer_size = 128M
read_buffer_size = 64M
read_rnd_buffer_size = 64M

myisam_sort_buffer_size = 8M
thread_cache_size = 8
query_cache_size = 256M
tmp_table_size = 128M
max_connections = 512
myisam_max_sort_file_size=34358689792

# Innodb configuration
#innodb_data_file_path=ibdata1:10M:autoextend:max:10000M
# Phil 2010-07-11
#innodb_data_file_path=ibdata1:10M:autoextend
# Set buffer pool size to 50-80% of your computer's memory #innodb_buffer_pool_size=2048M #innodb_additional_mem_pool_size=160M
# Set the log file size to about 25% of the buffer pool size #innodb_log_file_size=512M #innodb_log_buffer_size=64M #
innodb_flush_log_at_trx_commit=1
# Phil 2010-07-12
innodb_buffer_pool_size=4096M
innodb_additional_mem_pool_size=320M
innodb_log_file_size=1024M
innodb_log_buffer_size=128M

# Try number of CPU's*2 for thread_concurrency thread_concurrency = 16 thread_stack=500000

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid



--Slave 010 ---


[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# misc params
old_passwords=1
max_connect_errors=10000
slave-skip-errors=1062,1146
slave_net_timeout=30
#log-warnings=1
server-id=7
#binlog_format=mixed
#log-bin=mysql-bin
#max_binlog_size=1000M
expire_logs_days=8
#log-slave-updates
auto_increment_increment=10
auto_increment_offset=7
relay-log=/var/lib/mysql/mysqld-relay-bin
relay-log-index=/var/lib/mysql/mysqld-relay-bin

set-variable=long_query_time=1
#slow_query_log=1
slow_query_log_file=/var/log/mysql/mysql-slow.log
#log-output=FILE,TABLE
table_cache=8096

key_buffer_size = 14096M
max_allowed_packet = 128M
sort_buffer_size = 128M
join_buffer_size = 128M
read_buffer_size = 64M
read_rnd_buffer_size = 64M

myisam_sort_buffer_size = 8M
thread_cache_size = 8
query_cache_size = 256M
tmp_table_size = 256M
max_heap_table_size=256M
max_connections = 512
myisam_max_sort_file_size=34358689792
skip-thread-priority
#innodb_flush_log_at_trx_commit=1
innodb_buffer_pool_size=4096M
innodb_additional_mem_pool_size=320M
innodb_log_file_size=1024M
innodb_log_buffer_size=128M

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid




Author

Commented:
your solution suggest to delay the replication process. I don't want to delay because it is currently behind as I stated. I would like it be synch much faster than it does right now.
Armand GSenior Developer
BRONZE EXPERT
Commented:
You should check if bin logs are making some delays and try to disable it. A whole lot of delay causes are explained here: http://www.mysqlperformanceblog.com/2007/10/12/managing-slave-lag-with-mysql-replication/

Explore More ContentExplore courses, solutions, and other research materials related to this topic.