1

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.

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 .

| improve this answer | |
  • Thank you Rick, that is very helpful. I am going through your High Speed Ingestion link and it looks great. To answer you about the numbers, when a new office signs up with us, they generate about 100,000 rows of data initially sent in blocks of 500. After that it is just the incremental changes from day to day but we have been concerned about what this will look like with 20,000+ offices, especially if you get 100 that sign up in the same afternoon. I did some initial load testing that went very well though: dba.stackexchange.com/questions/200137 – Alan Mar 13 '18 at 22:21
  • A single INSERT with 500 rows (or LOAD DATA with 500 rows) should take a few seconds. 100K: a few minutes. 100 offices: a few hours. Note: In this last case, there is the potential for some parallelism. 100K*20K = 2B rows (initially) --> need to be sure indexes and queries are well designed. – Rick James Mar 14 '18 at 4:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.