1
$\begingroup$

I'm using a cloud machine with 512 RAM to store tick data in mysql, but I'm having trouble querying candle data. My current solution is to select all data within a time interval and do the tick -> candle conversion in the browser, but that calculation is too heavy for long time ranges.

I looked at similar questions, did some research on time series databases, even considered database as service solutions. (I'm still contacting one of them to see if they would provide the missing data for candles)

The question is this:

Given about 5 tick data per second (these belong to different assets), And low budget hardware (512 RAM, 1 Core, 15 GB empty disk), When I need to aggregate the data into candles of arbitrary periods, Which existing database solution would be more appropriate?

My current solution is to store an additional indexed columns with time rounded to 5 minutes, 10 minutes, 30 minutes, 1 hour, 5 hours, 1 day and I wrote mysql queries which can produce asset/open/high/low/close/start_time/end_time rows according to these columns in an attempt to speed up the queries. However, whatever index I use and however I optimize the queries, they end up taking seconds (and not milliseconds) which is unacceptable.

Are there any databases built to solve my problem?

Edit: guys at tempodb said that they just added first and last aggregate functions to their api :-) Min, max, first and last is all I needed to create bar data

Improve this question
$\endgroup$
8
  • 1
    $\begingroup$ Instead of aggregating later on, you could update multiple tables (4 for your time intervals (min, max, open, close) and replace the current value for the time period if its smaller/larger than the current one. $\endgroup$
    – user1157
    Commented Jan 23, 2014 at 9:01
  • $\begingroup$ How many rows do you have? This seems slow even on that hardware, unless your core is oversubscribed, then anything is possible. $\endgroup$
    – Bob Jansen ♦
    Commented Jan 23, 2014 at 9:06
  • $\begingroup$ @Anna I noticed that caching the candle data increases query speed greatly, but at great cost of disk space $\endgroup$
    – nurettin
    Commented Jan 23, 2014 at 9:26
  • $\begingroup$ @BobJansen At the moment it's hundreds of thousands of rows, but of course it will grow. Then I will have to think of buying more machines and database sharding. If I can drop query time to milliseconds scale, it will support hundreds of queries at the same time. $\endgroup$
    – nurettin
    Commented Jan 23, 2014 at 9:27
  • 1
    $\begingroup$ I would recommend PostgreSQL 9.3 and the use of Materialised Views(MV). You could create MVs for each time-interval i.e., one-min view, 5-min view, 1-hour view and so on. The advantage with MVs is that it will only update rows when certain conditions are matched on the server side. See: wiki.postgresql.org/wiki/Materialized_Views $\endgroup$
    – user6441
    Commented Jan 23, 2014 at 10:46

2 Answers 2

Reset to default
1
$\begingroup$

I would recommend de-normalising the data or using a document database such as NoSQL or MongoDB. These provide a speed advantage when performing select statements as there would be no need to lookup or join data from other tables. An advantage of using a document database is that they store data in an industry standard format such as JSON or XML so it should be easy to pull all the data client side and perform your transformations there instead of having to rely on your server to perform the calculations.

Improve this answer
$\endgroup$
0
$\begingroup$

Personally, I use HDF5/PyTables (very quick given that it's free) for the data storage component then use pandas to resample the data.

You'll likely be better off just using flat files than a relational database IMHO.

Improve this answer
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.