Geek Answers Handbook

MySQL Binlog Replication is the Hurt Bag. Are there any good alternatives?

I honestly tried this on the left and still find that my mirror server, configured as a slave replicator, is still behind. My user application database continues to grow, and now I have reached the point where I cannot "close" the resync database (even on weekends).

Anyway, my question is: are there any plausible, affordable alternatives to binlog replication? I have two servers, so I will not consider buying a third one for load balancing, if only its only option.

Greetings

/ tr

+6
mysql replication
source share
3 answers

Your master runs in parallel, and your subordinate runs it in sequence. If your master can handle 1.5 hours of attachments / updates / executions in 1 hour, your subordinate will be behind.

If you cannot find ways to improve the write performance of your slave device (more memory, faster disks, delete unnecessary indexes), you have imposed a restriction on the architecture of your applications. In the end, you will find yourself at a point where you cannot make changes in real time as fast as your wizard can execute them in parallel.

Many large sites scald their databases: consider splitting your master + slave into multiple master + slave clusters. Then divide your customer base into these clusters. When the subordinate begins to lag, you need to add another cluster.

It's not cheap, but if you can't find a way to do binlog replication operations in parallel, you probably won't find a better way to do this.

Update (2017) . MySQL now supports parallel workflow threads . There are many more variables that will make the slave device lag, but the slaves no longer need to write in sequential order. The choice of preserving the fixing order of parallel slave flows is an important option if it is important that the exact state of the slave at any time is critical.

+10
source share

Have you tried: 1) SET innodb_flush_log_at_trx_commit = 0 2) SET sync_binlog = 0

Both will help speed up your Slave with little added risk if you have a server crash.

+1
source share

Adding memory to the slave is likely to help. We went from 32 to 128 megabytes, and the lag more or less left. But it is neither cheap nor sufficient in all situations.

Buying a third server will probably not help, but most likely you will get another lagging slave.

0
source share

More articles:


All Articles