7

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!

|improve this question
  • 3
    I wouldn't store it all in the same database let alone the same table. Could you setup two database catalogues with identical structures for the querying of current data and another for historical data? You could have a job that moves current data into historical data after 30 days or so. This should improve query performance for your most queried data. – CarneyCode Nov 19 '17 at 7:07
  • 2
    Database size shouldn't impact your query performance if the tables are indexed properly. – GrandmasterB Nov 19 '17 at 9:31
  • 1
    Have you tried using a timeseries-database for your timeseries-data? e.g. blog.timescale.com/timescaledb-vs-6a696248104e – Patrick Nov 19 '17 at 11:11
  • 6
    Before guessing around what will happen when you get 2 billion rows, I would recommend to make a test, generate that much data artificially and profile this. I would not be astonished when the test shows no noteable performance degradation for those queries. However, you should also have backup/recovery times in mind, those will definitely increase linearly with the database size. – Doc Brown Nov 19 '17 at 15:17
  • 3
    MySQL provides partitioning for just this type of use-case. While there are definitely cases where it makes sense to split the data into different repositories, I suspect this isn't one of them. – kdgregory Nov 20 '17 at 18:55
8

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.

|improve this answer
  • @DocBrown that's my point: it's very ambitious to try to outperform a db optimizer that dynamically builds its strategy on the size and characteristics of tables and indexes involved (and eventually spreads data over several disks for concurrency). If only 1% of the queries use older data, but you have to read the full flat files to select the relevant non-sequential data (e.g param_id in the where clause), these special queries will take much of the computing power. Of course, if the goal is to export older data just in case of, that's another story and you would be completely right. – Christophe Nov 20 '17 at 8:01
  • @DocBrown I see your point. It's true that realtime processing with an event stream would be based on a couple of flat file and would access the data sequentially. Thanks for pointing it out: I've edited the introduction accordingly. – Christophe Nov 20 '17 at 19:45
4

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.

|improve this answer
  • 2
    MySQL does have partitioning, and it's a primary use case for that feature. – 9000 Nov 20 '17 at 19:47
  • Partitioning is the solution! Older partitions could be migrated to cheaper slower disks. – James Anderson Nov 21 '17 at 16:45
2

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.

|improve this answer
  • 1
    So you are suggesting that the data be indexed by timestamp and partitioned by month. What value does the partitioning across multiple databases provide here? I would expect the data is already indexed by timestamp. Queries over large time spans will still be slow. Or are you suggesting a MapReduce style approach where different months can be searched in parallel? – amon Nov 19 '17 at 9:44
  • Queries get slower as the db grows and there will be a maximum size to a single database, either because of limits imposed by the db itself or by volume size restrictions. – Martin Maat Nov 19 '17 at 10:16
1

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.

|improve this answer
  • 1
    So you are suggesting that OP reinvents the wheel and creates their own in-application database engine? The algorithm you've sketched is known as binary search. Likely, the present database engine is already using this (or equivalently a binary tree index). I'm not saying writing such a program is impossible or excessively difficult, but it does seem rather unnecessary. Does really remove so much of the “overhead of RDBMS”? Wouldn't in-process databases like SQLite produce a similar benefit (if any exists) without so much effort? – amon Nov 19 '17 at 9:53
  • The question translates to "how do I make this scalable". I just provided a couple of options with different trade offs. If you only need the axel without the spokes or the tire, rolling your own "wheel" can be a viable option. – Martin Maat Nov 19 '17 at 10:05
  • If the entire db could fit into main memory, then your suggestion may have merit. However, accessibg data in a btree is much faster when dealing with data stored on disk. – Robert Baron Nov 19 '17 at 13:57
  • @Robert I am talking about efficiency regarding resource use, thus addressing the scalability question. This is not about gaining a ms or two.. – Martin Maat Nov 19 '17 at 21:46
  • In memory, things are a great deal less complicated. This is where what you propose work work. When you are dealing with disks, you need to deal with disk cache. Assuming the pameters of the database and os are tuned to block size of the disk, btree will be faster than what you have outlined as there will be fewer reads from disk. I am assuming that you have confise efficiency with speed of access. Efficiency can and is defined in different ways . – Robert Baron Nov 20 '17 at 15:10
1

You may look at:

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

|improve this answer

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.