1

Every day, there will be a set of new data that I will need to write and update to the database. This set of data can contain 8-10million records.

This new set of data comes from Service A and it writes directly into a database which is used by Service B. I don’t maintain Service A but I do maintain Service B.

The problem is that whenever Service A has to do such a massive load to the database, it consumes all the IOPS and bogs down the database which in turn affects Service B. And because there are so many records, it takes more than 10-15 hours to complete the process. This means the load of the database remains high for that number of hours too!

I’m considering to provide an endpoint for Service A to send its new data from and I will write to the database in a more “graceful” manner so that it doesn’t affect Service B.

However, I’m not sure how I should do it without still bogging down the database even with an endpoint so that Service A doesn’t have to write directly to the database. I’m thinking, even if I provide an endpoint for Service A to write data to my database, I would still have to somehow handle that same amount of load that will now just be Service B writing directly to the database and resulting in the same load issue.

I’m currently using AWS Postgres RDS as our database. All our services are hosted on AWS and we have a "microservice" architecture.

In a scenario like this, how should I design my endpoint or what AWS services should I be using so that I can handle the huge amount of data more gracefully?

  • This really depends on the nature of data being written into the database. You cannot just "ad hoc" add support for such a volume of data. You need to design your database and it's read/write patterns towards such use-case. You might have serious architecture problem on your hands right now. – Euphoric Oct 11 at 13:19
  • @Euphoric So if I have to handle such a load and use case, what kind of patterns and architectural design should I be looking at? – xenon Oct 11 at 14:15
  • If you have this much data please look into bulk importing. One can easily do a few million records in just a few minutes using a staging table and merge approach. Different databases use different techniques, but this is really the way to handle lots of records at once (millions). I haven't worked with Postgres but they may support this kind of feature. With SQL Server and Oracle, 8 to 10 million records is just a few minute process using their bulk import features. – Jon Raynor Oct 11 at 17:03
  • Consider a write-optimized database, such as Cassandra or MyRocks. I found the former kept up well when the task was to add nearly a billion records a day, and I did it with leftover rack hardware. – Chuck Adams Oct 13 at 1:47
  • Amending: MyRocks is optimized for storage, Cassandra for write speed. – Chuck Adams Oct 13 at 2:04
2

Update

In the comment thread you clarified that the database belongs to service B and is managed by you.

This gives you the authority to control access to the database. This does mean you have to expose an endpoint and route this traffic through your service, but it also gives you the authority to throttle requests as you see fit.

If you draw the line at X requests per second (you can figure this out based on what your db can handle while remaining usable by service B), simply reject any additional request from that source during that second. If that's a problem for the service A devs, then that's a problem the service A devs will have to solve.

You're currently letting someone DDOS your database and seem to think you have no other response than to acquiesce and let the db be dragged down. This is not the right response.

Open an endpoint for them, deny service A direct access to the database (revoke the service credentials, or if A and B share the same credentials, change the password and do not give them the new password), force them to use your service, inform them of the throttling you've implemented. The onus is on them to comply with your service.

An alternative solution when the service A devs complain about the throttling is to approach management and justify a need for upscaling service B to have the appropriate resources to deal with the workload. Management gets to decide what's more important, keeping costs down or making sure service A gets to work at top speed.


In a scenario like this, how should I design my endpoint so that I can handle the huge amount of data more gracefully?

It all boils down to what you have ownership over. And by you, I mean service B.

If the database is considered to be service B's database, then all access to the db should go through service B. Don't give direct access to others because you can't enforce any quality control on the data operations they perform.

If the database is a shared database not owned by a particular service, then it is not your problem (if you're only a service B dev). The issue needs to be tackled either by whoever maintains the database (e.g. enforced request throttling), or the service A devs (fixing their own service).

I’m considering to provide an endpoint for Service A to send its new data from and I will write to the database in a more “graceful” manner so that it doesn’t affect Service B.

If it's service B's database, then you should've done this from the start (regardless of performance issues). If it's not service B's database, then this is a case of "not my circus, not my monkeys" (from the perspective of service B, that is).

Suppose you do take on becoming a reasonable middle man between A and the db, and ignore the implementation details. So what happens if Service C is created tomorrow which imports different (but equally sizeable data)? Are you also going to incorporate service C now? What about Service D? E? F?

Trying to cover for other faulty mechanisms just leads to more effort and an inferior solution. You're muddying the water, A and B are separate for a reason.

What you're trying to do is akin to preventing a DDOS attack on your own database server (by Service A). If it were your own managed server, I would suggest taking this up with the IT support staff for your database server to handle the issue on a larger scale. DDOS attack are not (efficiently) prevented by programmers, but by server/network admins.

As this is AWS related, I'm not quite sure how exactly to go about this, but the principle of the approach is the same.
Rather than trying to fix the faulty actor (service A), fix the database server. Even if you fix service A, you're going to have to fix every actor's misbehavior. But if you fix the database server by e.g. enforcing request throttling for every actor, then you prevent any future services from making the same mistake and pulling the whole system down again.

  • The real solution might be to eliminate Service A and replace it with a more efficient means for loading data. That will depend on the database vendor. – Greg Burghardt Oct 11 at 11:58
  • 1
    @GregBurghardt: I agree from a company perspective. My answer is written for OP's point of view, where he is a B dev and not an A dev. The resolution can't come from B as B is not the cause of the issue. – Flater Oct 11 at 11:59
  • @GregBurghardt I don’t really have much control over Service A since I’m the developer for Service B. I was think what else could I do on my end if I have to handle such a load of data from Service A and that Service A cannot be changed. Of course, if Service A could be changed, it would be easier. But in my case, I can’t. – xenon Oct 11 at 14:14
  • @xenon Does "I have to handle such a load" imply that the database is owned and maintained by service B? If yes, you should have service A come through your service (and you are well within your rights to flatout throttle their requests if that's what you need - if it breaks their application that's their problem). If no, then you (as a service B dev) should not be the one fixing the issue between service A and the db. – Flater Oct 11 at 14:19
  • @Flater Yes, the database is used and maintained by Service B. That’s why I was thinking I have to do something about it by providing an endpoint. However, I’m not sure what’s the best way to do it. If my endpoints were just merely taking the request from Service A and writing it to the database, then the database will still have the same load issue. – xenon Oct 11 at 14:23
0

The architectural solution to most bulk load problems is to eliminate the bulk load. Go back to the source of the events and consume them in a stream.

However. Where this is not possible you need a tactical solution and following strict rules about service B owning its own DB might not help you.

Amazon has some guidance on bulk imports

https://aws.amazon.com/premiumsupport/knowledge-center/rds-import-data/

But you can see this isn't geared to being a regular business process. You are advised to switch off backups etc.

If you are at the level of data where you are forced to use these 'one off' methods just to get the work done in a reasonable amount of time then I would consider spinning up new databases for each import and having service B aware of multiple RDS instances.

That way you can have Service A create a whole new DB, add the data then message service B : "a new DB is ready for you"

Having everything in the cloud helps with this, as you can use the AWS api to spin up and access services without having to worry about physical hardware

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.