Binlog MySQL Replication is a "Bag of Hurt". Are there any good alternatives?

Question

I've honestly tried this left and right and still find that my mirror server, set up as a replication slave still lags behind. My app's user base keeps growing and now Ive reached the point where I can't keep "shutting down" to "resync" databases (not even on weekends).

Anyways, my question: Are there any plausible, affordable, alternatives to binlog replication? I have two servers so wouldn't consider buying a third for load balancing just yet, unless its the only option.

Cheers,

/mp

Millicent

mysql replication is a stable, fast and slim solution, compared to the alternatives. booking.com uses unbelievably many mysql instances and cascaded replication setups from what i've heard. If your website is really too big, you might need a professional to help you out with troubleshooting, if your slave is not just too small hardware-wise. I personally have setups with 10k+ queries per second that have no issues with slave lag. Possibly your replication settings are not perfect, too. – Aloeswood

Answer 1

Your master executes in parallel and your slave executes in serial. If your master can process 1.5 hours of inserts/updates/executes in 1 real hour, your slave will fall behind.

If you can't find ways to improve the write performance on your slave (more memory, faster disks, remove unnecessary indexes), you've hit a limitation in your applications architecture. Eventually you will hit a point that you can't execute the changes in real time as fast as your master can execute them in parallel.

A lot of big sites shard their databases: consider splitting your master+slave into multiple master+slave clusters. Then split your customer base across these clusters. When a slave starts falling behind, it's time to add another cluster.

It's not cheap, but unless you can find a way to make binlog replication execute statements in parallel you probably won't find a better way of doing it.

Update (2017): MySQL now support parallel slave worker threads. There are still many variables that will cause a slave to fall behind, but slaves no longer need to write in serial order. Choosing to preserve the commit order of parallel slave threads is an important option to look at if the exact state of the slave at any point in time is critical.

Greengage

sometimes you just need to hear the harsh truth – Millicent

All applications hit some sort of scaling limitation. Most databases hit disk IO. It sounds like yours is replication. On the negative side, you can't buy bigger database servers to get the job done. On the plus side, you can buy smaller database servers :) – Greengage

Answer 2

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

Both will help to speed up your Slave with a small level of added risk if you have a server failure.

Eckel

These two commands will do wonders, performance-wise. Just keep in mind, if your master server crashes, you might lose transactions that have not yet found their way into the binlogs on disk from RAM. If that is fine, you should be happy. – Aloeswood

Answer 3

Adding memory to the slave would probably help. We went from 32 to 128 megs and the lagging more or less went away. But its neither cheap nor will it be enough in all situations.

Buying a third server will probably not help that much though, you will most likely just get another lagging slave.

Faludi