I apologize first for using Sync in the title, that may be the wrong term. I am using MySQL, but can switch to Postgress or other open source platform.

I have a service that when people subscribe to we receive a lot of data from them, around ~60,000 rows that gets split up across multiple tables using a http endpoint processed by php. After the initial sync, we only receive incremental updates.

Looking at server load, we became concerned that it would become an issue as we added more and more customers and so setup a separate server that would handle all the http requests, parse the data and place it in the right tables. As load increased, we could then section off customers to other identical servers.

The question is what should I do to effectively replicate the data on those finished tables over to the production server (same network)? I could setup triggers, but that would result in tens of thousands of individual connection requests for each insert.

If I setup were to setup mirroring / master-slave, I want it to ignore all existing data because I wouldn't want it to try comparing things that have already been sent on a regular basis since that could grow to 50 million rows quickly.

It would be more like :

SELECT @rowcount:=COUNT(*) FROM production.database.table1;
INSERT INTO production.table1 SELECT * FROM workhorse.database.table1 where `id` >@rowcount;

Something this simple could be run once a minute or so I would think and cut down on individual connections. I realize this is kind of messy since COUNT and ID aren't locked together, but it gets the idea across. Also, I don't know a way to run the query on a remote server.

Are there any tools/commands that would make quick work of this?

  • What database are you using? Have you looked into ETL tools? – CalZ Aug 28 '17 at 17:57
  • So sorry, I should have mentioned I was using MySQL, but can switch to Postgress or other open source platform. I will look into ETL tools, but would love an open source solution. It always makes me nervous when companies don't share pricing - but want to sign you up today :) – Alan Aug 28 '17 at 17:58
  • Fears justified: $1,000-$15,000/month news.ycombinator.com/item?id=12585791 – Alan Aug 28 '17 at 18:02
  • @Alan - edit your question with details in your comment above. – Max Vernon Aug 28 '17 at 18:05

Master-Slave (and various other forms of replication) provide keeping two (or more) hosts in sync continually. No ETL process needed.

Splitting the client processes and MySQL into separate servers is a good first step.

Putting different customers on different "shards" provides 'infinite' scalability in that dimension. You would need some form of proxy or home-grown directing to get from a client to the necessary server.

Ingesting 60K rows is pretty trivial unless, of course, the data format is always something different. At that point, I might contract a programmer who knows Perl (or other tool) to crank out ingestion scripts. (Years ago, I did such with news feeds.)

Does 50M rows at 60K per customer mean you have 1000 customers?

I would suggest you use sharding do deal with excessive write traffic and disk space. And use Slaves for excessive read traffic.

It is a never-ending pain to try to perfect "sync" scripts.

| improve this answer | |
  • Great ideas Rick, I am concerned about the master-slave scenario having too many small transactions as time goes on. I setup a federated db config which is allowing me to batch process them on a cron job, but I haven't figured out how to deal with a situation where the source data changes, such as if a customer phone# was updated. Currently working on finding a way to run a nested update query using the updated_at field. – Alan Aug 28 '17 at 21:33
  • 1
    Do the numbers. How many updates per day will you have? Number of active customers times average number of updates/inserts/deletes in 24 hours. MySQL can probably handle 1 million/day "out of the box". If you need more, we can talk about batching, ssds, slaves, sharing, and other techniques that can probably get you 10M/day with minimal effort. – Rick James Aug 28 '17 at 22:17
  • Thanks Rick, each customer will have 10,000-80,000 records depending on how long they have been in business. On a daily basis they should generate an additional 50ish inserts. I was noticing a server load of 0.8 with only 2 customers syncing which lasted about 30 minutes - here I mean the big initial sync. I haven't launched the product to the public yet, but imagine when I do I could have 100+ sign up and want to do the initial sync on the same day. I am trying to figure out how to process the data without it slowing down the user experience on the web server. – Alan Aug 29 '17 at 15:17
  • Some of that server load is naturally the apache service parsing incoming JSON data, the rest is mysql. I was thinking it would be better to do federated like on my other thread you commented on to let the server "rest" and focus on web traffic and then do a surge sync, but I didn't realize it was much slower. I just found I can do a delayed sync on the master-slave relationship, which might be better. Going to do some youtube to figure out how I want to develop the relationships. – Alan Aug 29 '17 at 15:21
  • 30 minutes to do a sync? That sounds unreasonable. What code do you use for that? Single-row INSERTs? Batch inserts? LOAD DATA INFILE? The last is what you should use. – Rick James Aug 29 '17 at 15:50

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.