Internal or external SQl?

Hello guys.
I’m totally noob, have read around that the system supports many different logging methods… I was thinking of using sql? But should I use the internal server or should I install Microsoft sql server? I’m planning running the system on a Windows pc :slightly_smiling:

It isn’t clear to me what benefit one gets from putting the logs into a database. Do you mean Persistence where your Item’s states are stored in the DB?

In any case, the answer to your question depends on what you intend on doing with the databased information. If you plan on using third party tools to analyze them you would probably be better off with an external DB that your tools are more likely to understand. If OH is the only one that is going to read the data one of the imbedded DBs is probably lighter weight and definitely less work to set up.

If you want to keep track of history but don’t want to DB to grow forever (and are only persisting numbers) rrd4j (the default) is a good choice. If you want to persist Strings and/or don’t care about the growth of the DB, db4o is a good choice. If you don’t care about history and only care about restoring your Item’s previous state on an OH restart, the Map DB is a good choice (it only keeps track of the last state).

Okay, actually I was just thinking of something like logging temperatures for the last week and ability to trend them later.
Also log some data from the power consumption from heat pump and solar inverter etc…
Also log some data to restore item states :slight_smile:

Maybe I then should stick to the internal integrated app? Rrd4j…

OK, the nomenclature had me a little confused. typically with openHAB “logging” refers to the application’s activity log where you would look to see what openHAB is doing and debug problems. Item Persistence is what you are talking about.

The rrd4j has a lot of advantages including a fixed sized database but it has some limitations which may or may not impact your decision. First of all it has a quirk which requires you to use an every minute strategy or else it doesn’t work very well (e.g. charting is blank, history methods in rules do not return values, etc.). Also, as data ages, rrd4j will average values and store the average in place of two or more adjacent values. For example, it stores a value every minute for yesterday but it stores only the average of all the data for an hour for data from last week. So if you want a precise and exact history of your item’s state rrd4j is not a good choice. Finally, rrd4j can only store numerical values so Strings are not persisted.

Also, keep in mind that you can use multiple persistence engines and choose which Items get saved where. So you can put your temp and power states into something like db4o and put everything else into map db with the restoreOnStartup strategy. Then your historical data will be persisted exactly as logged in db4o and everything will be restored when OH restarts but you are not losing database space storing historical data for Items you only care about its most recent value.

Okay, then the rrd4j won’t fit my needs, since i get a S0 puls from my heatpump every 1/200 kwh, so i need to have a database that can log theese all the time, and i need to see what the “max” was for the actual day, week, month & year. I’m not interested in a mean value for a day, that doesnøt give much sense.

But maybe it can be setup so some of the data will be archieved as max values for the last week? And maybe some other data could be archieved as meanvalues - or is it max or average and no ability to “change” between theese?

Yes ofcourse, alternatively i can use different engines, and some decide which items should be stored where, here you talk about the db4o - is this a better choice instead of SQL?

I’m having a Synology NAS running, with a lot of storage, and was thinking of setting the DB in that storage, instead of the device running my OH, there isn’t that much storage Space :slightly_smiling:

I think there is a small misunderstanding. Rrd4j can save the values as fast as they come in. But you must save a value at least every minute for the other things like charting etc. to work.

The data gets decimated as it ages so it depends on how far back you want to look as to whether rrd4j’s compression will impact the max or not. For example, if you are looking back five years then you are probably going to get something closer to the average for a given day. If you are looking at last week or last month your values are probably not exactly precise but close pretty darn close. If you look at the rrd4j wiki page it will tell you the policy it uses to “compress” the db as the data ages. For the most part, until data gets older than a month or so it really doesn’t do much.

But again, if you want to go way back in time or you want exact values even as the data ages, rrd4j is not for you.

You could set up some Items that store the max and some rules that update those Items, particularly for the older values. Since these will not be changing much even as the data is aged and “compressed” it isn’t really going to change much.

It depends on what your requirements are. Db4o is nice because you don’t need to install and configure an external DB server and it just works. But you really don’t have any access to the data stored there with tools outside of OH. It is easy to experiment to find what works best for you.