Alternative database storage options?

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 |
±------------------+`

[email protected]:/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

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.

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]
1 Like

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

2 Likes

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 -

[email protected]:/var/opt/emoncms/phpfina $ less 95.dat
“95.dat” may be a binary file. See it anyway?

David

1 Like

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

2 Likes

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

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

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.
:wink: