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
last aggregate functions to their api :-)
Min, max, first and last is all I needed to create bar data