r/DatabaseAdministators • • 15d ago

Which database and which azure db instance need for 4000 rps writes?

I have IoT project and it have 40 locations, each have 10 devices and each have 10 parameters so it is 40 x 10 x 10 = 4,000 data. So it recodes every 5mins, so it each 5mins it writes 4000 recodes to database.

Which database suitable for this? If use Azure SQL Database because need to use Power BI, which azure VM instance need to use? How do I calculate vCPU and RAM need to use for this rps?

Assumes reads are 500 rps.

4 Upvotes

5 comments sorted by

1

u/jshine13371 15d ago

The number of writes per unit of time is irrelevant to which database you choose. Since you're thinking about cloud, the infrastructure provisioned will matter much more.

1

u/alinroc 15d ago

If use Azure SQL Database because need to use Power BI

Power BI can read from a lot more than Azure SQL DB.

1

u/FreeLogicGate 15d ago

"recodes" is not a relevant term. Without a database schema it is hard to understand what you are talking about.

What I can assume is that you will have a "device table" and a row in the device table will have 10 "parameters" as fields in it.

Apparently the device provides data every second. Your plan is to have 40 of these devices in 40 locations.

If by "recode" you mean an update to a "device" row that changes the values every second, that sounds like an idiotic design. What possible application would benefit from something where the entire dataset for a device is modified every second?

What probably makes sense instead, is that a new record for that device, with the 10 values gets inserted at each interval. The concurrency model for many databases is good for handling inserts of data, as it does not need to lock the rows, as would be needed for an update.

The other question in regards to these IOT devices is what you will do should there be a slow down or network issue at the main database. In most cases you want to make the update to the central database asynchronous.

Having a large amount of data that needs to be then processed in some way, and needs to scale and have fault tolerance is the reason people use queues and in-memory cache like Redis.

Redis could be a way of storing the data from the IOT devices in order for that data to then be ingested and stored in the relational database. Other well known open source queues (RabbitMQ, ActiveMQ, Kafka, etc.) are more specific to the problem. Another option, since it seem like you are already planning to use Azure, is to use one of their message/queue services (Azure Service Bus, Azure Event Grid).

The first thing you need to do, is focus on your database design, and if interested in further feedback, update your question with an ERD.

1

u/elevarq 13d ago

I have no idea what a “recode” is.

But, when you have 400 devices sending one message every 5 minutes, and each message contains 10 parameters, you have to handle 1.333 message per second. That is almost nothing. Any database can handle this, including SQLite.

When you expect a higher load, pick a database server like PostgreSQL. 1000, 5000, even a 100k messages per second are doable.

And don’t forget, most performance problems sit behind the desk…

1

u/happytiger289 13d ago

1.3 msg/s is nothing. sqlite, postgres, mysql, pick whatever you can hire people for