5

The current situation

We're currently looking into a new product that will send device data back to us to interpret.

These are the numbers we're looking at:

  • Devices will most likely send data every 5 minutes
  • 26.000 devices by the end of next year
    • 26.000 INSERTS every 5 minutes. We will most likely have little control over the interval, so chances are that these 26.000 INSERTS are not evenly spread across these 5 minutes.
    • ~ 2.733.120.000 data entries per year
  • Each packet will be JSON-formatted, with a size between 300 - 500 bytes.
  • We'er expecting about 8.000 new devices every year.

We currently manage several databases for our internal systems, but have little experience with volumes like this. We use AWS Aurora right now, which, in theory, should support 100.000 INSERTS p/s.

How will this data be used?

The data will primarily be used to create reports in our customer portals:

  • Real time reports of device metrics
  • Historical reports, IE.:
    • How did device stats look on Feb 2th, 2019?
    • What did week 12 look like?
    • Give me a summary of January's metrics
    • Show a graph of a specific column sum, grouped by month

The problem

To be quite honest, I find it pretty hard to make a solid choice, considering I don't have any hands-on experience with data-volumes like this.

Our current stack

We use a combination of AWS EC2 machines and an AWS Aurora cluster to manage our data. The ideal solution would be AWS-oriented.

Infrastructure I am considering:

Option #1: To keep things simple, storing everything into Aurora directly could be a good solution.

Diagram of our theoretical infrastructure

Option #2: But, to create a separation of our "real time" data and interpreted data, perhaps, something like this is better.

An alternative diagram of our theoretical infrastructure

The actual questions

  • Is a MySQL-compatible database management system, like Aurora, suitable for something like this?
  • The incoming data will be used to generate "realtime" daily, weekly, monthly and yearly reports, aggregated per device. Would it be advisable to create separate tables for these different "perspectives" to make querying the data easier, or am I overcomplicating things and should I just store the measurements into one table?
  • Should we look into table partitioning?
  • Is there anything else that I did not mention, but we should look into?

If all of this is too vague, please let me know so that I can clarify the issue.

Would love to hear your thoughts.

  • It might help if you can explain what you plan on doing with the data after it is in the database as that can drive what solution you need – Joe W Mar 21 at 16:17
  • @JoeW good point. I added some examples in the post. Hope that clarifies it a bit. – derp Mar 21 at 16:21
  • 3
    I think this is still a huge project to tackle, maybe too big/broad in for DBA.SE. Odds are you can get by with any number of solutions, but putting some time into a bake-off to assess each one might be your best bet. – LowlyDBA Mar 21 at 17:20
  • Can you provide a link for the "in theory, should support 100,000 inserts p/s" statement? – Michael Kutz Mar 21 at 17:24
  • 6
    Whatever choice you make today, be prepared that you'll have to rethink it sooner or later. In any case don't let your devices or the API they call insert into the database directly; put a message queue in between. This way you can better manage the flow and swap out parts independently. – mustaccio Mar 22 at 13:54

Your Answer

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

Browse other questions tagged or ask your own question.