DB for high writes?
Hi
We have an existing application which tracks cars
We use a sql server DB to store the data
We have a new customer who has a report rate of every 5 seconds, and 300,000 devices
We plan to keep each 1 min of data in SQL server for 12 months, and the every 5 seconds of data in another data base for 72 hours to comply with regulations
The "other" database would be 1 table with the following fields
Imei Number
DateTime
Speed
Lat
Lon
IotDeviceRawData
it will only contain 72 hours of data, and will probably be terrabytes worth of data
It will be written to a lot, and queried infrequently (perhaps 10 times a day) to pull out data of a car
I'm open to options for different databases, it needs to be a open source DB (free) and fast to write to.
We have an existing application which tracks cars
We use a sql server DB to store the data
We have a new customer who has a report rate of every 5 seconds, and 300,000 devices
We plan to keep each 1 min of data in SQL server for 12 months, and the every 5 seconds of data in another data base for 72 hours to comply with regulations
The "other" database would be 1 table with the following fields
Imei Number
DateTime
Speed
Lat
Lon
IotDeviceRawData
it will only contain 72 hours of data, and will probably be terrabytes worth of data
It will be written to a lot, and queried infrequently (perhaps 10 times a day) to pull out data of a car
I'm open to options for different databases, it needs to be a open source DB (free) and fast to write to.
David Johnson, CD
any db is able to handle this quantity of requests. your limitations are your disk speed and amount of ram for caching
Also the type of queries to be asked. You don;t want to search all values in a AV pair of database for a certain values. So a SQL type database can be useful.
Postgresql has handling for geo locations (lat/lon, etc.). besides native handling of JSON formatted data. So IMH it should be considered as well.
Postgresql has handling for geo locations (lat/lon, etc.). besides native handling of JSON formatted data. So IMH it should be considered as well.
The process that receives the data is based on SNMP, all?
Simply write twice, one to the 72hour, once to the warehouse db
Expire stuff older than 72 hours.
SQL partitions
Mariadb, much depends on whether you have enough Ram to keep data in memory.
Often though the data capture intervalonthedevice is every 5 seconds, the reporting is commonly in groups, every 15 seconds et.
Simply write twice, one to the 72hour, once to the warehouse db
Expire stuff older than 72 hours.
SQL partitions
Mariadb, much depends on whether you have enough Ram to keep data in memory.
Often though the data capture intervalonthedevice is every 5 seconds, the reporting is commonly in groups, every 15 seconds et.
ASKER
Thanks all
We will be inserting around 170 million records per day with 10,000 IOT devices
This can easily grow to 20,000 devices so we need to also plan for that
The query will be done by IMEI and DateTime, and we just display the other data like lat,lon - no queries will be done on lat lon
The querying doesn't have to be fast, just possible (for court cases of wreckless driving etc), and will be infrequent.
I was thinking a job would kick off over night to delete older data that is not required.
Hope that give more info
Maria DB is an option as long as it can keep up with the bulk inserts.
We will be inserting around 170 million records per day with 10,000 IOT devices
This can easily grow to 20,000 devices so we need to also plan for that
The query will be done by IMEI and DateTime, and we just display the other data like lat,lon - no queries will be done on lat lon
The querying doesn't have to be fast, just possible (for court cases of wreckless driving etc), and will be infrequent.
I was thinking a job would kick off over night to delete older data that is not required.
Hope that give more info
Maria DB is an option as long as it can keep up with the bulk inserts.
Perhaps most important is the hardware underneath, rather than the specific database.
You need to calculate the IOPS requirement of all these writes, which will probably be quite high.
So how many transactions do you expect per second? From 170 million records each day, that would be 2000 tps. Let's say that each record requires 10 IOs. That's 20000 IOPS required.
Next, your storage system needs to be able to handle that many write IOPS in a sustained way. Not so easy to do that without some serious storage system.
You need to calculate the IOPS requirement of all these writes, which will probably be quite high.
So how many transactions do you expect per second? From 170 million records each day, that would be 2000 tps. Let's say that each record requires 10 IOs. That's 20000 IOPS required.
Next, your storage system needs to be able to handle that many write IOPS in a sustained way. Not so easy to do that without some serious storage system.
Mimer has support for realtime database access. it should be able to clean out data more easy as well.
https://www.mimer.com/
Also consider only creating logfiles / day with just sequential writes... grep can be a search tool as well.
https://www.mimer.com/
Also consider only creating logfiles / day with just sequential writes... grep can be a search tool as well.
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.