-3

I have a simple MySQL Master-Slave replication setup, but always get an unaccaptable huge slave lag. That means that the slave lag will get higher than the expire_logs_days of 14 and the replication will crash.

So, here are some facts:

  • MySQL 5.5
  • Datasize: 120GB
  • Master Server: SSD, 64GB RAM
  • Slave Server: SATA, 32GB RAM

The network connection is not a problem. I also droped some indexes on the slave. There is nothing else running on the slave server besides the nightly dumps.

Could the slower disk and the smaller RAM on the slave could cause the slave lag? Should I replace the slave server with an equivilant master server?

How could I determine if this is a hardware problem and not a misconfiguration of the slave?

sme
  • 1
  • 2
  • Please add more details. How big is your lag? How do you test it? What is the ping between servers? – Glueon Oct 16 '14 at 08:37
  • I add the info about the slave lag. There are some newer binlogs on the slave server than the one which is currently processed. That is why I dont think that the connection is a problem. – sme Oct 16 '14 at 08:54

1 Answers1

1

The slave replication lag is usually caused by the fact, that in mysql master server makes changes using multiple threads, and on slave only one thread is used to roll in the binlogs. Yes, starting from 5.6 it's multiple threads too, but only one thread per database. Considering sufficient amount of RAM comparing to the size of your database, you should really consider adding more fine tuning to your database server config. Like - increasing buffer pools, tuning caches, checkig open tables limit, innodb_flush_log_at_trx_commit value and innodb_io_capacity value. I guess you should check the last value first, basicly for SATA the defaut value of 200 is way much, try setting it to 100 and see if that will help.

drookie
  • 8,625
  • 1
  • 19
  • 29
  • Thanks for those hints. I didnt heard of the io_capacity config. So I will try to monitor the IO-wait and adjust this value. – sme Oct 16 '14 at 12:42
  • I change the innodb_io_capacity setting to 100 and it seems to solve the problem. – sme Oct 20 '14 at 08:31