Database vs Flat files for rarely accessed data

Question

We are receiving realtime data from over a 1000 sensors, each of which sends a data point every 10 seconds in average, which is amounting to about 1 million rows of data every day. Our system gives users the ability to select any sensor and a date range and download the data points as an Excel file.

We have seen that our users are mostly interested in data that is less than 30 days old. Data that is more than 30 days old is most probably already been downloaded. Only about 1% of our data retrieval requests come for data that is more than 30 days old. However, we cannot say that this data is totally useless, because our users sometimes want to download data that is even more than a year old. Deleting old data is not a possibility.

Currently we are using MySQL database to store the data, and all data is being stored into a single table. The table now has over 60 million rows. We use SSD and we have the right indices due to which the data retrieval still happens considerably faster.

An example database query we use to select every minute data is:

SELECT
    data_value AS value, param_id AS param_id,
    data_timestamp AS ts,
FROM tbl_data_log
WHERE param_id in (?)
    AND data_timestamp >= ? AND data_timestamp <= ?
GROUP BY DATE(data_timestamp), HOUR(data_timestamp),
    MINUTE(data_timestamp),
    param_id
ORDER BY data_timestamp ASC

Currently, this query takes less than 5 seconds for retrieving data that is more than 30 days old for a specific sensor.

As more data is stored into this table, it is going to get bigger, maybe up to 2 billion rows in the next 1 year (we are also adding more sensors everyday). I do not know how the query performance would be at that stage. To me, storing all this data in a MySQL database doesn't seem to be right, because it is accessed very rarely, and having data that is more than 4 months old indexed, seems unnecessary.

One approach I thought of is to have only last 30 days of data in MySQL, and move old data to flat files with a folder structure like /old_data/%YEAR%/%MONTH%/%DATE%/%PARAM_ID%.dat. This way our data size is not going to become unmanageable but at the same time data is still indexed in the form of flat files on disk.

Is the current approach good to scale? Does moving old data to flat files help or not? Is storing all data in a single table correct? Do we need to change our database engine itself? Please give your thoughts on this architecture. Thank you very much in advance!

Answer 1

Database engines are in principle designed to cope with huge amounts of data much faster than with raw data files, when you have to access data in a non-sequential manner.

You say that you have all the right indexes on your table to get an optimized access, so data_timestamp is certainly indexed. However, I see in your query example that you use:

GROUP BY DATE(data_timestamp), HOUR(data_timestamp),
    MINUTE(data_timestamp),
    param_id

This forces your database engine to convert the timestamp of every row matching the where of the query do date, which I suspect is very time consuming.

As I suppose that date and time are comonly used in your application, I'd suggest to consider a little bit of denormalization here to facilitate the database's job by precomputing the date (DATE type) and the time rounded to the minute (either TIME type or eventually a SMALLINT between 0000 and 2359). That's 5 bytes overhead per row. Create an index on them for accelerating the GROUP BY clause.

If this is not sufficient, make sure the server is correctly dimensioned for its big data challenge, and look if your DBMS is sufficiently well placed in benchmarks with other DBMS.

As a work around you also could consider using two tables: an active table for the last 30 days, and a second table with all the historical data older than 30 days. Some batch jobs would then move the expiring data from one table to the other.

Answer 2

When you insert data into big indexed tables at a high data rate, then easily index maintenance can become the limiting factor. Every single row's values have to be inserted into their indexes.

So, limiting the table size is a good idea. But not by having one table and deleting old entries from the table because that means another index update, this time removing entries from the index, which takes about the same amount of time as the insertion.

Remove data from the DB by dropping/truncating complete tables instead of deleting rows.

I don't know if mySQL has something similar to Oracle's Partitioned Tables (physically of multiple tables, accessed logically like a single one / similar to a view showing a UNION of the partitions). If mySQL doesn't have it, there's no black magic necessary to do it with a bunch of normal database tables, a view and a bit of logic.

With such a scheme, I think you can keep the old data in the database without compromising query performance for your typical current-month queries.

Answer 3

Here's an idea. Start a new database (file) every month. The only problem I see with that is that you may get a query that spans two different months. You could just tell your users to execute two different queries if they need data from different months, or make some proxy code that can split a query and stitch up the data before returning it (you could do this in VBA so your users can execute it from their workbook).

The timestamp should be in the name of your database so the database to be addressed can be constructed from the query parameters. You may need a catalog that maps a database name to a server so you can scale over different servers. You could create the future databases upfront, enter some dummy data and test with those.

You may want to compress (older) databases and unzip them just before mounting them, and delete the unzipped databases each night or before a different old database is addressed. That would give your users a considerable performance hit when addressing old data but it would still be possible and you could save a lot on space.

Answer 4

Here's another idea. Since your data structure is so simple you could write your own program, define a struct/record for what is now your database record and use a file stream to access the data. You could have one file per day, or month.

For any query you jump into your stream, see what timestamp you hit, if you are too far jump in halfway in the part you know your data to be in et cetera. You should find the start record in just a few tries. Then do the same for the end record and assemble your result set from all the records in between.

You would save on the overhead of the RDBMS. This could be the most efficient solution (you could also combine this with unzipping on the fly) but it would be less flexible. If your "data point" data would change a bit, you would probably have more work than you would have with the MySql solution.

Answer 5

You may look at:

• REDIS sorted set ZRANGEBYSCORE
  • https://stackoverflow.com/questions/45342453/how-to-get-data-based-on-date-range-in-redis with key as sensor id
  • https://www.infoq.com/articles/redis-time-series for lua script in this context
  • find how to cluster it on time data
• scylladb with eventually kairosdb
  • http://www.scylladb.com/2017/05/24/9-steps-building-highly-available-time-series-solution-scylla-kairosdb/
  • https://www.slideshare.net/patrickmcfadin/apache-cassandra-apache-spark-for-time-series-data
• A scalable time series database as InfluxDB (clustering not open source here)

I think in your case I would use just a properly clustered scylladb.