Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Master:

Slave:

Both are running 5.5.28.

For the incremental backup purpose, I need to enable the log-slave-updates option. But Seconds_Behind_Master keeps increasing immediately after restarting MySQL. A simple statement get... 2 minutes to updated on the slave at spare time. At the busy time, it seems that the Slave cannot catch up with the Master, so I have to comment it out and restart MySQL.

Is there any way to improve this?

share|improve this question
    
It's very strange, because your slave is stringer then your master, can you provide the my.ini from both master and slave ? and also, are you tables mostly innoDB or MyISAM ? –  altmannmarcelo May 1 '13 at 19:15
    
Almost of tables are InnoDB. Updated my question to provide my.cnf. –  quanta May 1 '13 at 20:08
    
To clarify... you are saying that enabling log-slave-updates or not is the single factor in your environment that makes the difference between whether your slave can keep up or not.. correct? –  Michael - sqlbot May 2 '13 at 22:59
    
@Michael-sqlbot: yes. With my current settings: innodb_max_dirty_pages_pct = 0, sync_binlog = 1, the slave can keep up normally. But if I enable the log-slave-updates, the slave may lag behind the master for a long time. Looks like the sync_binlog is the main culprit: mysqlperformanceblog.com/2009/01/21/… –  quanta May 3 '13 at 3:14
add comment

2 Answers

As your main concern is to have an incremental backup solution, you can change the follow variables:

Changes:

innodb_max_dirty_pages_pct - set it to 75, then innoDB will cache some changes and flush it to disk at once.

innodb_doublewrite - Disable the innodb double write

sync_binlog - Disable syn binlog

long_query_time - increase the long query time or disable it (at the moment you are logging every single query)

Why slave delays?

Other think is, it looks to be a busy master, on master you are able to handle 768 connections at the same time, it means that 768 thread can be writing data to MySQL, and the server (if not having looking issues) will execute all these queries at same time. In the other end(slave), you have a single replication thread to execute all queries received from master, and if a query take 10 seconds to execute on slave, the only thread available to execute changes from master will be busy, in this case, some delay are expected. In your case you also use the slave as a read only copy of the master, then the replication thread may also need to wait some other thread release lock on some rows to apply the changes.

share|improve this answer
add comment

You can try to comment out these two variables on your slave to use default values - they may cause high disk I/O:

max_binlog_size
sync_binlog
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.