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.

I have a master I was going to start slaving to a new server to do some tests on. This master is currently being slaved to 3 other boxes.

After bringing the new slave online it became clear it wasn't able to keep up. I was hoping this was just an issue of caches needing to get warmed, but after a couple hours it was clear it was just falling farther and farther behind.

It's not as beefy hardware wise as the other slaves (less ram, slower disks) so that seemed like likely culprites.

However one thing that seemed odd was right around the time the new slave started going the other preexisting slave, which had no problem keeping up with the load for that time of day started falling farther and farther behind as well.

Any pointers on what to look at or why this might have happened. We're running MySQL 5.1. The new slave is in the same DC as the master and preexisting slave.

Edit: After we stopped slaving on the new slave to get some faster disks in, the preexistings slave's replication started to catch up again.

Edit: (Solution Found) I had copied a my.cnf from another slave (the one that was falling behind mysteriously). I updated some configs such as innodb buffer size to be more appropriate for the memory the new slave had but neglected to update the server-id. Apparently mysql does nothing to prevent this but everything described in http://bugs.mysql.com/bug.php?id=16927 was going on (Slave error's mentioned in the error log, an INSANE amount of relay logs getting generated).

I updated the server id on the new slave and things look to be going well. Well, insofar as it's not causing the other slaves to fall behind.

share|improve this question

2 Answers 2

Your new slave server can possibly be slow in some respect and not able to process the queries in the binary log as quickly as the other servers.

You should also look at the possibility that you may have run a very heavy query that caused the new slave to lag as well as the other slaves.

Regarding the new slave, you may have a performance problem that needs to be resolved. Possibilities could include:

  • Slow hard disks (you mentioned you are buying new ones)
  • Bad my.cnf configuration. Flushes too often to disk? flush method isn't O_DIRECT?
  • Long running queries from master server - consider using row-based replication if you feel it helps your overall system performance.
  • Caches on the new slave aren't warm for running the queries from the master - do you use INSERT INTO ... SELECT .. FROM statements? or non-deterministic insert statements like ones with sub-queries?
  • Raid card with battery - do the other servers have it and the slave doesn't?
share|improve this answer
    
This is a more poignant answer than mine because you directly address hardware and config as slave lag culprits. +1 !!! –  RolandoMySQLDBA Sep 14 '11 at 11:18

You mention that when you started the new slave, one of the other slaves (or the only other slave?) started falling behind and when you stopped the new slave, it started catching up again.

One possibility that would cause this is that your bandwidth can't handle the new slave. If you turned on the new slave and turned off the other slave, would the new one keep up?

As a reference, here is MySQL's FAQ on estimating performance of adding slaves.

share|improve this answer

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.