CODEWITHSUNDEEP
mysqliodatabase-replication
rabudde
rabudde

Reputation: 7722

Heavy replication write load on MySQL slave

We are using Percona MySQL 5.6 on Debian 8 for an ecommerce aggregator. For this there is a master backend server doing the whole ETL (processing product feeds from partners) and a slave MySQL server used by the frontend web servers. It's a single product database with about 600GB data.

Both machines have RAID10 with datacenter series SSDs. Master MySQL is a dual Xeon E5 with 128GB RAM and slave is a single Xeon E5 with 64GB RAM. Our problem is, that the ETL is so I/O heavy (with thousands of IOPS), that the master is able to handle the I/O load but the slave server cannot keep up with replication. Most work is done in a ramdisk and only real neccessary data is written to database, already.

The slave has more reads than writes, but replication cannot catch up. What options are there for scaling the replication writes (i.e. the I/O load) on the slave?

Edit 2016-11-18: With "options" I'm not only asking for optimizing MySQL, but for completely other techniques or software which could handle this situation better.

Upvotes: 1

Views: 805

Answers (1)

Sugu Sougoumarane
Sugu Sougoumarane

Reputation: 416

Upgrading to 5.7 should hopefully solve your immediate problem: http://mysqlhighavailability.com/multi-threaded-replication-performance-in-mysql-5-7/.

This is usually a precursor to the master running out of write capacity. You should start thinking about sharding soon. You can check out YouTube's vitess.io project, which I'm part of.

Upvotes: 1

Related Questions