So here are some initial benchmarks of feed data storage in different storage engines: mysql (myisam vs innodb), timestore and direct file access. I also thought Id have a go at writing the current implementation of input processing in both python and nodejs in addition to php to learn a bit more about these languages as they are being used and favoured by others in the community such as Jerome (python), Houseahedron (python) and Jean Claude Wippler of Jeelabs (nodejs). Id like to see if there is any measurable difference in performance between these different languages for the kind of application that we are developing and if there are any other benefits: easier to do certain things etc.
Housemon by Jean Claude Wippler is a good example of how a timeseries data storage and visualisation application can be implemented in a different way by using a mixture of direct file storage and a redis in-memory database with the server side part of the application written in nodejs.
Intrigued by the idea of using direct file storage as Jean Claude Wippler does in Housemon and following the approach used by Mike Stirling in timestore of using a fixed time interval to simplify and speed up searching I had a go at writing a basic implementation using php file access and the results are good.
MYSQL
https://github.com/emoncms/experimental/blob/master/storage/MYSQL/mysql.php
- InnoDB INSERT 1000 points 21s,25s,20s (Normalised to 100,000 ~ 2200s)
- InnoDB INSERT 10000 points 167s,183s (Normalised to 100,000 ~ 1750s)
- MYISAM INSERT 10000 points 15-17s (Normalised to 100,000 ~ 160s)
- MYISAM INSERT 100000 points 165s
Benchmark of current emoncms mysql read function that selects given number of datapoints over a time window.
MYISAM results on the left | INNODB results on the right
https://github.com/emoncms/experimental/blob/master/storage/MYSQL/mysql_read.php
10000 datapoint table:
- 1000dp over 5 hours (average method) 232ms | 391ms
- 1000dp over 24 hours (average method) 424ms | 675ms
- all 0.2 hours (all method) 40ms | 38ms
- all 0.5 hours (all method) 58ms | 55ms
- all over 1 hours (all method) 90ms | 82ms
- all over 1.3 hours (all method) 108ms | 100ms
- 1000dp over 3 hours (average method) 237ms | 272ms
- 1000dp over 5 hours (average method) 280ms | 327ms
- 1000dp over 24 hours (average method) 726 ms | 949ms
- 1000dp over 48 hours (average method) 1303 ms | 1767ms
- 1000dp over 52 hours (php loop method) 2875 ms | 2650ms
- 1000dp over 100 hours (php loop method) 3124 ms | 2882ms
- 1000dp over 200 hours (php loop method) 2934 ms | 2689ms
- 1000dp over 400 hours (php loop method) 2973 ms | 2749ms
- 1000dp over 2000 hours (php loop method) 2956 ms | 2762ms
- 1000dp over 2600 hours (php loop method) 2969 ms | 2767ms
- Initial benchmarking results on netbook: http://emoncms.org/site/docs/developdatastorage
- Recent blog post on current emoncms implementation: The current feed storage implementation
Timestore is a promising solution, developed specifically for timeseries data, written by Mike Stirling.
Blog post on timestore: Timestore timeseries database
https://github.com/emoncms/experimental/blob/master/storage/timestore/timestore.php
- 10000 inserts 52s
- 100,000 inserts 524s
- Read 1000 datapoints over 5 hours: 45ms
- Read 10 datapoints over 5 hours 20ms
Direct file
For some reason I did not think this method would work as well as the benchmarks show but its great that it does because from an implementation point of view its really simple and very flexible as its easy to modify the code to do what you want, see the examples linked:
- Direct file write 100,000: 6-7s
- Direct file write 100,000 open and close each time: 27,24,26s
- Direct file read 1000 datapoints over 5 hours of 10 second data in 85-88ms
- Reads 1000 datapoints over 200 hours of 10 second data in 93ms
- Reads 1000 datapoints over 2000 hours of 10 second data in 130ms
- Reads 1000 datapoints over 2600 hours of 10 second data in 124ms
For a short term storage, storage to disk may not be necessary, instead we can store data in memory using an in-memory database like redis. Benchmarks to add.
Blog post: Redis idea
Other ideas for storage format
Languages
What about the programming language? No benchmarks yet but interesting to look at the difference in how the code looks. I found each language pretty straightforward to use and online resources to get me passed the bits I didn't know where readily available. The language links below show the core parts of the input processing stage of emoncms written in php, nodejs and python. I've also linked to emonweb a port of emoncms (or more a build in its own right be now) by Frank Oxener in ruby on rails.
- PHP
- nodejs
- python
- ruby https://github.com/dovadi/emonWeb (Frank Oxener)
- go
Emoncms.org stats
HouseMon
HouseMon by Jean Claude Wippler stores data in 3 forms:
- Raw log of the serial data received to file (compressed daily)
- Redis in-memory storage for last 48 hours which makes for quick access of most recent data.
- Archival storage via direct file access for data older than 48 hours, the archive is hourly aggregated data (hourly - unless a use case demands finer resolution at which point the archive can be rebuilt from the raw logs).
http://jeelabs.org/2013/02/18/who-needs-a-database/
Its quite clear from some of the above tests that the housemon implementation is going to be fast in terms of data access speeds (with redis storing everything in memory for the last 48 hours) and efficient in terms of data storage (binary files – hourly data), the big difference is that full resolution data is not available after 48 hours but Jean Claude Wippler argues that it would be better to wait for a use case rather than implement higher resolution for higher resolution sake and that logs can be used to rebuild archives at higher resolution if needed anyway.
If you have a standard emoncms raspberrypi install, changing the mysql storage engine to myisam should bring immediate performance improvements, especially if you have a lot of feeds being recorded, I will try and put together a script to make this easier and also update the ready to go image.
The next development step I think is to integrate redis into emoncms by rebuilding the input processing implementation to use redis rather than go to disk to get the last feed and input values. Then it would be good to test both timestore and the integrated direct file storage in action on several parallel raspberrypi's, keep benchmarking the differences and then see where that gets us.
My solution to the performance and scalability issue was to preprocess the data by calculating average values and storing them in separate tables. See https://github.com/dovadi/emonWeb/wiki/DataStore for a detailed explanation. When fetching data, it simply queries the 'next' average table if the dataset becomes larger than let's say 800 data points. And the preprocessing can be done in the background.
ReplyDeleteLater I abstracted this approach in its own library (https://github.com/dovadi/data_store). A solution inspired by RRDTool (http://oss.oetiker.ch/rrdtool/) but implemented with a database (mysql, postgresql or sqlite) instead of a file system. You can set the compression schema, frequency, maximum datapoints etc.
An additional advantage of this approach is that it allows you to manage the total size of your historical data. You can delete older records from the 'original' table while keeping the historical data with a lower resolution
Hello Frank
ReplyDeleteGood to hear from you. Yes I think you have the right solution there with the averaged layers of data at different resolutions, When Mike Stirling got in contact about timestore, I immediately thought that was the same approach to what you where using and that if two intelligent people can come up with the same approach independently then that certainly verifies the validity of the approach, especially in light of the poor performance that I have been getting.
I was initially reluctant to complicate the table structure or add to the number of tables by calculating many average layers but now I can see the benefits are much clearer, so I take back my initial scepticism.
What do you think of timestore? It's very similar to your approach but written in c and uses the file system directly. It would be interesting to compare performance figures. I guess the main and significant difference is that the timestore approach does not store the timestamp, relying on a known start time and fixed interval rate to assign timestamps to the data and search quickly as well as being efficient with disk space as it doesnt save the timestamp field for each datapoint or need an index.
Trystan
A file system approach gives different challenges in combination with a web application when you need to scale in which I'm less comfortable. Somehow I think it is easier to do this with relational databases ( with sharding). Also you have the advantage of replication, but this is my personal opinion.
ReplyDeleteI haven't looked into Timestore, but for me storing without a timestamp would quiet a challenge, you can't always rely on a fixed time interval, but maybe I'm overlooking something.
Just had an other idea for implementing the file system approach but with a timestamp and without an index, aside from the average layers approach which has the other benefit of averages.
ReplyDeleteBecause the timeseries data is an ordered list of ascending order time and the query type that we want to do is quite specific, we could potentially implement the current emoncms approach quite efficiently
get_feed_data(feedid,start,end,dp)
we could do a binary search to find the start value and then a simple sequential selection of datapoints every x seconds assuming roughly a fixed time interval which most of the time is correct.
As we advance through the datapoints we check if the datapoint > end and exit if it is and add to the returned data if not.
typical_data_interval = 10s
query_interval = (end-start)/dp
skipsize = round(query_interval/typical_data_interval)
filepos = binarysearch(start_timestamp)
data = [];
for (p=0; p<dp; p++)
{
seek(filepos)
datapoint = read()
if (datapoint.time < end)
{
data.push(datapoint);
} else {
return data
}
filepos += skipsize
}
return data
I'm little worried about the 'most of the time' part of the solution ... :-)
ReplyDeleteAnother thing is that the skipsize approach is less accurate, in fact it is possible to fetch a 'wrong' value(s). That was another reason for me to go for preprocessing and calculate 'real' average values.
Frank ideia to calculating average values and storing them in separate tables seems the most easy to implement and reliable way.
ReplyDeleteI suggest Trystan an easy way to deploy this implementation in production environments:
Instead of doing realtime calculations, calculate averages for the desired time scales one time only and save to new tables on the first invocation.
Basicaly the idea is to use scale tables as cache, if they exists they are used. If not they are created and populated at next data query.