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.