Link to home
Create AccountLog in
Avatar of Omega002
Omega002Flag for Afghanistan

asked on

MySQL Replication Issue (120 000 seconds Delay To Slaves)

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?
ASKER CERTIFIED SOLUTION
Avatar of Armand G
Armand G
Flag of New Zealand image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Omega002

ASKER


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




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.
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account