[SOLVED] Matching persistence to the needs: a difficult choice

Setup, Configuration and Use Beginners
, ,
1

Hi folks,

I’m running the latest OH2.4 on a RPi(3) with MQTT set up on the same board. After a couple of days of messing around I finally have a couple of T/H sensors publishing data to the MQTT. I’ve even managed to get the T showing up on PaperUI (not the H for some reason … but I’ll sort that out later.).

Historically I have these ESP8266 sensors posting data to Apache and the Emon package to graph the data and store it. I’ll gradually move them all to OH2+MQTT+some form of persistence.

This brings me to the main point: choosing the right persistence for my needs. I’ve read a few posts on the forums and everyone seems to have their opinions on what is the most appropriate setup. What I seem to have figured out is that:

a) Influx+Garfana is not a good idea on RPi(3). Has anyone tried an RPI(4)? The processing power is probably not much different but by getting my hands on a 4GB RPI(4) shoul make a differece?

b) rrd4j seems to only manage numerical data. This means that if I want to store “string” data from MQTT I would probably not succeed?

So this brings me to the questions as to what persistence setup I should consider, knowing that:
a) I want to be able to chart data easily over a day, a week or a month (i.e. I want access to all historical data) … from what I’ve read, people with this sort of setup have not considered storage to be a problem.
b) I doubt I would ever exceed 40-50 units (around 10 at the moment) in the future logging data to the server. Most sensors (e.g. T/H) would probably be logging at 10-15 min intervals.
c) If my little server shuts down/crashes, I will the charts to display data to the pre-crash/shutdown status.
d) I want to be able to store strings in the DB too.
e) possibly consider importing my Emon data in the new solution.

So, questions are:

  1. what persistence setup matches the above best?
  2. what single board hardware (i.e. I want a low energy setup for the server) should I consider (e.g. Pi(3), Pi(4), etc.).

I look forward to your responses.

Many thanks

2

It might not be obvious that you can run multiple persistence services, with differing content for different purposes.

3

I guess you are right although too much complexity introduces too much maintainence. I try to achieve my goals with the maximum simplicity, where possible of course.

4

rrd4j does handle numericals only, so yes, for storing strings you need another service (additional or not).
For each database (not only rrd4j) that is storing data over time the growing size has to be considered. Do you really want to KEEP ALL historical data? Older data migth not be needed in the same granularity as actual data. rrd4j and InfluxDB have techniques in that direction.
So what are your needs for the historical data, that could be different for numerical and string data.

5

Just to clarify, rrd4j does not continually grow in size but new data replaces the oldest data in a round robin fashion.

6

Cheers Bruce_Osburne, sounds like rrd4j doesn’t suit my needs although I thought I read that what it does is archive older data so that you actaully still have access to historical data. This would work fine with me as long asI could decide where to truncate the data for archiving.

Opus, I hear what you are saying. My day job is to mess around with data so I’m a bit of a data geek. One of my devices is a weather station and the longer your time series, the more meaningful are you analyses, hence my need to log historical data.

I’m starting to think that maybe I need a RPi(4) that does the OH2+MQTT bit and one that does the InfluDB+Garfana bit. Relegating all the services to one machine sounds like it would stretch the limits of a RPi and also having services spread over two or more units would introduce more fault tollerance. I suspect I could have Openhabian on both and just turn off OH2 on the second unit and letting it run InfluDB and Garfana?

7

I have worked a little bit with rrd. It tends to keep fewer data points for older data, assuming you just need general trending information. Here is a link to the original author’s site.

Rrd4j

8

“Just to clarify”, rrd4j allways drops datapoints that are older then the customisable archives length.

@geotux
rrd4j can be setup to keep every saved value without any “compression” for a selectable timeframe. The downside for you migth be this timeframe can’t be unlimited

1 Like
9

the name rrd is for “Round Robin Database” this is, per definition “delete old data to save new data”. As rrd works in several levels, the database will downsample data to average for saving space. rrd will not raise file size, that’s why.

If you want to store data “forever”, just use SQL (which version is up to you, MySQL, PostgreSQL…)

10

And something larger than an SD Card on a Pi.

11

:laughing: sure…

I’m using MySQL (well, in fact MariaDB) but on a different machine. There are several NAS devices which support serving databases.

12

ok folks, so I guess that I can alway store the data I want to keep on MariaDB and leave the rest to rrd4j/InfluxDB. I messed a bit with MySQL in the past and it can be slow as data retrieval with huge datasets. Hence, maybe best to leave the charting funcionality to rrd4j/influDB and the storage funcionality to MariaDB. After all, different databases are developed with different functionalities in mind.

Udo_Hartmann, is setting up MariaDB a major challenge? Are there predefined database schemas or do you have to create your database structure based on the needs?

13

I would look here to start.

14

I was reading that last night. I have a test database in /var/libopenhab2/persistence/testSqlite.db

In the /etc/openhab2/service/jdbc.cfg I have:

url=jdbc:sqlite:/var/lib/openhab2/persistence/testSqlite.db

I have also set this in /etc/openhab2/services/runtime.cfg:

org.eclipse.smarthome.persistence:default=jdbc

That is where I got stuck. I don’t understand how, even though I set up the user and password in jdbc.cfg, I actually get the database to use these.

From what I gather, items you want to persist, need a line in /etc/openhab2/persistence/.persist so I have created one that looks like this:

Strategies {
        everyHour : "0 0 * * * ?"
        everyDay  : "0 0 0 * * ?"

           default = everyChange
}

Items {
        Temperature* : strategy = everyChange
}

In the log there isn’t much to go on other than a line:

2019-10-01 23:25:08.362 [INFO ] [persistence.jdbc.internal.JdbcMapper] - JDBC::openConnection: Driver is available::Yank setupDataSource

which I guess means that the drive is installed. I’m not getting errors so this must be a good sign. But them interrogating the database with sqlite3 shows and empty item table:

[23:29:17] openhabian@typhon:/var/lib/openhab2/persistence$ sqlite3 testSqlite.db 
SQLite version 3.27.2 2019-02-25 16:06:06
Enter ".help" for usage hints.
sqlite> .tables
items
sqlite> SELECT * FROM items;
sqlite>

I have an file in /etc/openhab2/items/sensors.item with the following:

Number Temperature "Dining Room Air Temperature [%.1f °C]" {channel="mqtt:broker:topic:diningroom:temperature"}

Any suggestions or clues as to why I’m not seeing anything being written to the database? I don’t quite understand how the temperature values from my ESP is making its way from the MQTT broker (which is definitively working as I see the temperature being updated in Paper UI) to the SQLite database. Is it OH that is reading from MQTT and writing to SQLite (although it doesn’t seem to be writing anything in my case …)?

15

This * means it will persist member Items of Group Item Temperature

Temperature is not a Group type, it has no members to persist.

Just take the * out, you’ll probably need to restart to pick up the change.

1 Like
16

As you want to use jdbc as persistence service, the file in /etc/openhab2/persistence/ has to be named jdbc.persist. This is due to the fact, that there is no reference to the used persistence service in the file itself. Instead you set up one file per persistence service, each with individual strategies and items to be persisted.

As already stated by @rossko57, the asterisk has a slightly different meaning as usual in *.persist files.
The meaning of the line

Temperature* : strategy = everyChange

is: openHAB, take the Group Item Temperature, read the members of this Group Item, persist these members, but NOT the group itself.

1 Like
17

Very good guys. Its now working! Thanks a lot. Does this mean that when I hook up all my other temperature sensors I should put the asterix back?

[22:08:39] openhabian@typhon:/var/lib/openhab2/persistence$ sqlite3 testSqlite.db 
SQLite version 3.27.2 2019-02-25 16:06:06
Enter ".help" for usage hints.
sqlite> .tables
item0001  items   
sqlite> SELECT * FROM item0001;
2019-10-02 22:07:52.094|19.5

I was expecting the table name to pick the name of the item. item0001 is not very meaningful and I can see it being a nightmare when you have all the items persisting with these vague names … Can I change the table name? Where would OH2 be keeping track of the table names?

18

No, you can’t change the table names. Instead you can get the item names via the table items.
in jdbc.cfg, you can set tableUseRealItemNames to true, but please be aware that openHAB will create new tables instead of renaming the old ones, i.e. you will lose old data.

19

So I’ve made the suggested change in jdbc.cfg by setting the tableUseRealItemNames to true. However, this has made no change to the table layout whatsoever. The temperature of the sensor is still being persisted in the item0001 table. The items table just shows:

sqlite> SELECT * FROM items;
1|Temperature

Would it be right to assume that if the item named Temperature were to have the item name changed to Diningroom_Temperature and I were to change the jdbc.persist to reflect this, I should see a second line added to the items table with something like 2|Diningroom_Temperatureand then a third table item0002 automatically generated were the values of Diningroom_Temperature were recorded? I would also expect to stop seeing values pop up in the item0001 table. Correct?

None of this is happening. As soon as I change the name of the item, and do a sudo service openhab2 restart, values of the sensor are not persisted to the database anymore. Then, I change the item name back to Temperature and it starts persisting again …

Maybe I just don’t understand how items are persisted … or is this some sort of bug? Please enlighten me :slight_smile:

20

I assume you have to start with a fresh DB or at least a fresh items table. Otherwise the system will find your old table name for the item in the items table and reuse it.