Alternative database storage options?

zag · 3 December 2022 16:26

As I understand it, the application configuration (graph definitions, users, etc . . .) are stored in a MariaDB database which can be accessed using mysql, while the actual sensor readings are stored in flat binary files (such as 36.dat, 37.dat, 38.dat below) which are utilised to reduce writes to storage for SD card longevity reasons.

Is there an option to have the sensor data written to another database? I plan on using this data along with data from other systems and it would make sense (to me anyway) to have all the data stored in one location and one database format.

I haven’t experienced any issues with the existing method, but it would be interesting to know if the option existed.

Thanks,

David

MariaDB [emoncms]> show tables;
±------------------+
| Tables_in_emoncms |
±------------------+
| app_config |
| dashboard |
| demandshaper |
| device |
| feeds |
| graph |
| input |
| multigraph |
| postprocess |
| rememberme |
| schedule |
| setup |
| sync |
| users |
±------------------+`

pi@emonpi:/var/opt/emoncms/phpfina $ ls -la
total 7457
drwxr-xr-x 2 www-data root 3072 Nov 30 21:26 .
drwxr-xr-x 7 www-data root 1024 Jul 21 2021 …
-rw-r–r-- 1 www-data www-data 26816 Dec 3 16:15 36.dat
-rw-r–r-- 1 www-data www-data 16 Nov 19 17:09 36.meta
-rw-r–r-- 1 www-data www-data 26812 Dec 3 16:15 37.dat
-rw-r–r-- 1 www-data www-data 16 Nov 19 17:09 37.meta
-rw-r–r-- 1 www-data www-data 26812 Dec 3 16:15 38.dat
-rw-r–r-- 1 www-data www-data 16 Nov 19 17:09 38.meta

borpin · 3 December 2022 17:18

No, they are text files with an associated metadata file for each Feed.

If you want to save the data source elsewhere, yes (a few use InfluxDB). But there is no feature that sends the data from EmonCMS to another data store (other than publishing to MQTT), and no way of using the graphical interface with a different data store.

Timbones · 3 December 2022 17:20

You can store feed data in the same database instead of using binary files.
Just need unhide the MYSQL engine in settings.ini.

; Supported engines. List engines by id to disable feed creation.
; Existing feeds with a hidden engine still work
; Recommended emoncms feed engines are PHPFINA and PHPTIMESERIES
; MYSQL:0, MYSQLMEMORY:8, PHPTIMESERIES:2, PHPFINA:5, CASSANDRA:10
engines_hidden = [10]

TrystanLea · 3 December 2022 18:18

Just a note to say I wouldn’t advise using the mysql database, support is limited outside of phpfina for things like the sync and postprocess module. Emoncms is very much geared around phpfina

zag · 3 December 2022 18:43

Thanks Tim & Trystan - I may try logging to two feeds with two different engines. The ability to store & query using MySQL tools seems like a useful addition for my use case. I don’t knowingly use sync or postprocess modules, but if I can log to both datastores then I can track over time and see if there’s any impact in terms of the data stored. My aim is to move everything off the existing Pi and on to a beefier machine with more storage and more memory, so processing power and disk writes won’t be as much of an issue.

I see there is a Python subclass that I can use to access the contents of a phpfina store - PyFina · PyPI so I will probably have a bash at using that also.

Thanks Brian - I was going off the message presented at the console, followed by the string of unprintable characters -

pi@emonpi:/var/opt/emoncms/phpfina $ less 95.dat
“95.dat” may be a binary file. See it anyway?

David

TrystanLea · 3 December 2022 18:55

Sounds like a good plan. Just didn’t want to provide the impression that the mysql engine gets as much development attention. I did update it recently to mirror the behaviour of the phpfina engine from the UI perspective. It would be worth having a read through the timeseries bit here , especially the page on phpfina , the way the data is stored is very simple GitHub - emoncms/emoncms: Web-app for processing, logging and visualising energy, temperature and other environmental data

Bill.Thomson · 3 December 2022 20:58

As Trystan alluded to in the post above this one, you’ll definitely see an “impact.”
Here’s the gist of it:

github.com

emoncms/emoncms/blob/master/docs/timeseries/History.md

## Emoncms Time Series engine history

Writing, storing and viewing time series data is a core part of an energy monitoring application like emoncms. Over the years the way time series data has been handled in emoncms has gone through several significant revisions both out of necessity as emoncms.org use has grown and through increased understanding of how disks and filesystems work and how to reduce disk IO through in-memory storage.
In order to best describe the current solution it helps to begin with a brief history of time series database design within emoncms.

Time series data is a series of timestamped measurements. For example a temperature time series may look like this:

![TimeSeries](files/timeseries.png)

The first solution used in emoncms was a simple mysql table with two fields one for the time-stamp and the other for the value and one table per time series feed. Data was requested using a mysql query that picked out a table row 'every nth row'.

After around 5 months query times became increasingly slow nearing 12s for a historical data request which involved selecting 1000 data points out of 2.5 million.  A temporary solution was found that initially gave an improvement of 10x. The solution involved adding an index on the time column and making repeated requests from php pulling out a set of data points at given time intervals. 

[12 Apr 2012: Speeding up emoncms feed data requests](http://openenergymonitor.blogspot.co.uk/2012/04/speeding-up-emoncms-feed-data-requests.html)

While it worked sufficiently well on a server with a light load as emoncms.org grew this approach became again unusable. Whenever the mysql query queue became more than a few tens of lines the delay added as queue items waited to be processed slowed down the overall historical data request significantly. A series of improvements to emoncms that reduced the number of mysql queries performed in input processing gave a little bit more time of 'ok' performance but it never took long for emoncms to grow and usability to deteriorate.

[30th May 2013: Emoncms.org load stats](http://openenergymonitor.blogspot.com/2013/05/emoncmsorg-load-stats.html)

In May 2013 Mike Stirling who wrote the [timestore time series database](http://www.mike-stirling.com/redmine/projects/timestore) got in contact, suggesting I took a look at his work. Timestore is a dedicated time series database written in c, its data request performance and disk use blew the results I had go so far with mysql away: Over halving disk use and reducing data request times by another 10x on a non loaded server and around 398x to 196ms on a busy server.

This file has been truncated. show original

zag · 4 December 2022 12:54

Maybe I should have been clearer - I meant impact in terms of data integrity or availability. I understand that the phpfina engine provides performance improvements over MySQL in terms of speed to query as laid out in the posts you and Trystan linked to.

Actually, looking back over the text I see I was clearer -

track over time and see if there’s any impact in terms of the data stored

David

Bill.Thomson · 4 December 2022 18:17

Respectfully disagree with you there.

e.g. if there’s any impact in terms of the data stored
is not equal to
impact in terms of data integrity or availability.

The first one is rather general. The second, specific.