Load balancing incoming data using offset / increment vs group replication

Question

I have a service that accepts large quantities of incoming JSON data from multiple sources. A single client will put a load of about 3.0 on a server, primarily from the Apache service, but also Mysql.

To balance this, I created a master-master relationship between 2 servers where they were each masters of each other, and slaves of each other. They both had auto_increment set to 2 with server one having an offset of 1, and server 2 having an offset of 2.

This resulted in server1 handling all incoming requests and writing odd number rows, and server2 doing the same and writing even number rows but now I have twice the processing power for Apache and can offload the non-time sensitive processes to servers not powering the main website.

The thought was that as we grew beyond 2 servers, we could increase the increment value to 10 and do offsets of 1-10 for each server. I could then adjust the MASTER_DELAY to reduce total connections so it could batch things in groups instead of individual queries.

I tasked a new employee to improve historical backups and in the process he started working on reworking the entire system to do group replication, which sounds great for redundant data, but he is telling me it will also be a solution for load balancing.

Is this a realistic solution / improvement that scales with 2-10+ servers simultaneously receiving hundreds of thousands of rows of content with foreign key pairs or should I go back to my previous setup? I don't understand how it would keep things straight without adjusting the autoincrement like I did with so much simultaneous writing.

Answer 1

Any multi-master setup has this limitation: Every row inserted on the original master must also be inserted on each other Master (and each Slave, if you have them).

That is, without "Sharding", you cannot get much more write scaling than with a single Master. (Read scaling is a different topic.)

Let me backtack a little bit. If the Master needs to do more prep work than actual INSERTing, then having more Masters is beneficial. (This does not sound like your situation.)

Yes (to one thing) -- you can do some load balancing with any multi-Master setup (Group Repl, Galera, M-M) -- both writes and reads. It usually requires a proxy between the clients and Masters. I strongly recommend a simple round-robin algorithm; other algorithms can't measure the "load" fast enough to make a difference.

All(?) multi-Master setups (assuming you use AUTO_INCREMENT) depend on auto_increment_increment being at least the number of Masters. (So, 'yes' to your note about offsets and increments.) But, that does not necessarily deal with other unique keys, or with normalization ids.

Give us some numbers. You make it sound like you are inserting a terabyte every few weeks? There are a lot things that need discussing is such a situation (SSDs, network bandwidth, contention, RAID, etc). How many rows per second? How much prep work? Are you already "batching" the INSERTs?

Here are my recommendations High Speed Ingestion .