1

I don't have that much experience in databases but I need to accomplish this as best as I can. It is rather specific.

Database will contain only data structured like this: dataID-timestamp-value

Value could be of any datatype. There will be up to 20 million new entries per hour. Read queries would always look for one or few dataIDs in a specific time period, i.e. - yesterday between 12:00 - 13:00 or something like that. And these queries need to be fast on relatively weak hardware. Old data can be overwritten if space is scarce.

I am open to any suggestions, sql, nosql and so on.

Thank you.

  • There isn't magic bullet for fast writes with overwrite and fast random reads based on timestamps and using cheap hardware. Using DynamoDB in AWS would be quite OK. – Andrew Smith Sep 16 '14 at 13:00
  • Thanks, but no, the DB has to be on a specific industrial PC with specific system specifications which include an atom processor, 2gb of RAM and an SSD of various sizes – Algirdyz Sep 16 '14 at 13:07
  • OK, I am working on similar problem myself right now. Large sequential writes, and few random reads as well some aggregation but not much. I have test app in Groovy to generate data, and few backends - MariaDB, MongoDB, PostgreSQL, DB2, MSSQL, H2, Cassandra as well AWS services. I will do some more tests so I will be able to tell something. I am looking into filling the database, then "rotating the data", and I am measuring response times. So far it's quite challenging for all of these. – Andrew Smith Sep 16 '14 at 17:39
0

I tested few databases for the similar scenario (ingesting logs), and MongoDB seems to be quite useful.

I used following collection:

db.createCollection("apache", {capped: true, size: 10000000000});

It seems that once the collection is capped it is not growing any more.

Now there is a tricky part - retrieving the data. If you need to query periods of time, it requires indexes. If you add index, it will be still capped (e.g. no need to compact indexes on capped collection - I just tested it) and you would be able to retrieve the data very quickly.

However as the collection is being filled-up, the insertion performance will degrade, but then it will become somewhat stable as the indexes don't grow. I think it would be good to limit number of online rows because of that, or split it into multiple collections. I would just test the latest version to see how it behaves (with selects over multiple collections while inserting only to one at the time).

In MongoDB there is default index on _id field (including the capped collection, even the documentation says otherwise, maybe it's out of day), which contains timestamp inside and is is usable, e.g. you can use this field/index to select periods of time however if you want to use also value, I think it would be best to just add another index for the value. You could also try compound index on timestamp and the value, it would take more space in overall, but could be faster.

I tested few other backends, and the problem is that you need to use indexes and therefore lock the tables during cleaning them up. DB2 can do this online, but you still need to do it (reclaim free space). So the DB2 would be the second option as MySQL and PostgreSQL are locking tables during space reclamation. Cassandra seems to be overkill for the task as it's wide-column store. SQL databases are also over-kill as they are transactional DBs.

Also I had some performance problems with PHP propably to some bug in the build, in the PHP driver or maybe just slow connect (it's PHP/CGI). Now I am using Groovy/Tomcat with permanent connection and it is far much faster and I am using also batch inserts which are twice as fast as it seems.

Also, Atoms are very slow CPUs. There might be problem with overhead on I/O. It would be very important to enable Write-Back cache on HDDs and use as much RAM as possible as well tune I/O scheduler (test it with noop or deadline), and make sure that these Atoms are running at max frequency. It would be also very important to run 64bit kernel and setup virtual memory size to unlimited with ulimit as Mongo needs it for file operations above 2GB (some systems have limits on Max Virtual Memory Size e.g. SLES). Also Centos7 seems to be performing very well (it's very fast), so that would help as well.

  • Thanks. My current plan for insertion is to limit the size to a certain amount, say 50gb or something like that, and then after its full it will start overwriting the oldest entries. How would that work with mongoDB collecitons? – Algirdyz Sep 17 '14 at 9:52
  • You just create collection like this: db.createCollection("yourcollection", {capped: true, size: 50000000000}); and it will automatically overwrite the oldest data and index entries. – Andrew Smith Sep 17 '14 at 10:24
0

How unique are the DataID's? Are there just a handful of DataID's for the 20 million entries per hour or are DataID's more numerous?

But if your most frequent query is based on a narrow time frame first and then specific DataID's then this create statement should give you decent performance. You will need to change the data types to what makes sense for your environment.

Note: statements are for MS-SQL

CREATE TABLE TestData 
(
DataID VARCHAR(20) NOT NULL
, [TimeStamp] DATETIME2 NOT NULL
, Value VARCHAR(1000) NULL
)
GO

CREATE CLUSTERED INDEX IDX_C_TestData ON TestData ([TimeStamp], DataID)
GO

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.