开发者

How to efficiently store this big amount of data? Database or what?

I have to do an application that will check the changes of 35 item开发者_开发知识库s each second. Each item have 3 values that will fit into 5 bytes each one, so 15 bytes for item. Values will not change each second but there isn't a pattern, maybe they change continuously or they stall for a while ...

So I did a small calculation and I got that storing all the fields each second on a relational database (SQL) I will have:

35 * 15 bytes * 60 seconds * 60 minutes * 24 hours * 365 = 16.5 Gb a year.

This is too much for an SQL database. What would you do to reduce the size of the data? I was thinking on storing the data only when there is a change but then you need to store when the change was done and if the data changes too often this can require more space than the other approach.

I don't know if there are other repositories other than SQL databases that fit better with my requirements.

What do you think?

EDIT: More information.

There is no relation between data other than the one I could create to save space. I just need to store this data and query it. The data can look like (putting it all in one table and saving the data each second):

  Timestamp    Item1A      Item1B     Item1C     Item2A    Item2B ....

   whatever     1.33        2.33       1.04       12.22     1.22 
   whatever     1.73        2.33       1.04       12.23     1.32
   whatever     1.23        2.33       1.34       12.22     1.22
   whatever     1.33        2.31       1.04       12.22     1.21

I can feel that must be better solutions rather than this aproach ...

EDIT 2:

I usually will query the data about the values of an Item over the time, usually I won't query data from more than one Item ...


This is too much for an SQL database

Since when is it too much?
That's really peanuts for almost any RDBMS out there (~17GB of data every year).

MySQL can do it, so can PostgreSQL, Firebird and plenty others but not the likes of Sqlite. I'd pick PostgreSQL myself.

Having SQL databases with hundreds of TB of data is not that uncommon these days, so 17GB is nothing to think about, really. Let alone 170GB in 10 years (with the machines of the time).

Even if it gets to 30GB a year to account for other data and indexes, that's still OK for an SQL database.

Edit
Considering your structure, that looks to me solid, the minimal things that you need are already there and there are no extras that you don't need.
You can't get any better than that, without using tricks that have more disadvantages than advantages.


I'm currently considering using compressed files instead of SQL databases. I will keep the post upgraded with the info I get.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜