MySQL Replication A->B->C

Question

I was setting the MySQL Replication for master -> slave/master -> slave and Replication for master -> slave its works fine but when i have enable this option in my.cnf

log-slave-updates=1

for updating the master bin log my replications is starting be slower and the time

Seconds_Behind_Master

is growing.

I use innodb engine but the DB is big. Any idea how i can improve the replication, looks like the network is not the issue. Also i was think to use

binlog_format=ROW

but master is using default setting for replication 'statement' and i cant reset master ;)

Thanks ...

Answer 1

try figuring out what's the bottleneck

• is it the cpu-limitation of the slave [mysql replication in 5.1 and 5.5 is single threaded, it'll use just once core]. if it is - think about putting faster cpu on the slave hosts.

• is it the io subsystem on the host? if so - think about more spindles, ssd or maybe raid card with larger bbu cache in write back mode to 'absorb' spike of writes

  you might also want to look for 'outside the [mysql] box' solutions like tungsten replicator.

Answer 2

Have you really got asynchrounous replication with 3 masters? This is a really bad idea. If you lose a node your remaining nodes will become increasingly out of sync.

Also i was think to use...binlog_format=ROW

It depends on what your queries look like, it might help, but MIXED mode might be a better solution.

I use innodb engine but the DB is big.

If the size of your database is impacting replication performance then you've obviously got HUGE scope for improving the performance via schema changes and query tuning.

but master is using default setting for replication 'statement' and i cant reset master

Why not?

Answer 3

Hi i solve the problem by improving the performance for innodb. Generally I improved io operations for it.

innodb_flush_log_at_trx_commit = 0
innodb_flush_method = O_DIRECT
innodb_write_io_threads = 32
innodb_read_io_threads = 32
innodb_thread_concurrency = 16

also i did change to btrfs

nospace_cache

after those changes all works fine ;) thx for help