Rethinking the data input and storage core of emoncms: benchmarks

Storage engine test

All tested on a raspberrypi, running off the standard SanDisk SDHC 4Gb SD Card. 

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

MYISAM | INNODB READ

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

1000000 datapoint table: (115 days @ 10s)

• 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

PHP loop method timing may be quite a bit longer if the server is under heavy load as it involves making many separate mysql queries, each query needs to wait for other queries in the mysql process list to complete.

• Initial benchmarking results on netbook: http://emoncms.org/site/docs/developdatastorage
• Recent blog post on current emoncms implementation: The current feed storage implementation

Timestore

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

https://github.com/emoncms/experimental/blob/master/storage/timestore/timestore_read.php

• Read 1000 datapoints over 5 hours: 45ms
• Read 10 datapoints over 5 hours 20ms

Includes layer averaging and multiple layers so there is quite a bit more going on (that would still need to be added to other implementations like direct file and mysql above), so benchmarks are not directly comparable.

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

Redis
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

• Removing redundant datapoints - part 1
• Removing redundant datapoints – algorithm 1

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 

Servers

• Apache
• nginx http://www.ismsolar.com/sistema-de-monitorizacion-de-alto-rendimiento

Emoncms.org stats

• Emoncms.org load 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/17/data-data-data/
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.