I just checked my db4o persistence database, and it’s almost 500MB. Seems a bit unnecessary/inefficient. I’ll go through and reduce the items I’m persisting, but can anyone recommend a utility to delete say all database entries more than three months old?
This comes up periodically and there has been no satisfactory answer found. A lot of people simply delete the DB files and let OH start over periodically, dealing with any errors in the brief period where the DB is empty.
Personally, a database like db4o should only be used as a last resort. By this I mean it should only be used for those Items where you need very accurate (i.e. not averaged like rrd4j does to older data) for long periods of time or non-numeric data. Items for which you only need numeric historic data in the recent past (last week) rrd4j should be used. For Items where persistence is only needed for restoreOnStartup use MapDB.
I’ve outlined a good approach to achieve this with groups here:
In openHAB2 I’m currently adding this sort of function to HABmin and ESH. Currently I’ve added deleting and editing values in databases, and will likely add the option to delete a range of data, and also all data from an item (useful when the item has been deleted, but its data is still in the database). This is all tested in the REST API and waiting for review in ESH -:
I use MapDB for most and rrd4j for only those Items that you chart. MapDB only saves the most recent value so the DB stays really small. It also supports non-numeric values like Strings whereas rrd4j only supports numbers.
Thanks - I’d forgotten rrd4j only supports numbers. So I think I’m be lazy, leave things as they are, and rely on the enterprising Mr Jackson’s splendid interface to tidy things up when I eventually upgrade to openHAB 2.
Don’t forget you can have more than one persistence active at a given time. Per that link I posted above, I control which Items get persisted to which DBs using groups. I have a gChart group which I put only those few Items I chart. It works really well and there is no DB maintenance.
Yep, the issue is that eventually the size of the db gets too large and things slow down. I normally just nuke it, the problem is that I have a lot of rules that use db4o as sorta a scratch db for historic values. I have not found a way in openhab to check if something is in the db without throwing an error so after I nuke the db I have to manually populate the db some some old values.
Just would be nice to test if something was in the db and if not do x.
This is available in OH2. I’ve recently added methods to allow querying of the database, and also delete and storing of timed data so HABmin2 provides methods to edit the database and will be able to support wholesale deletion.
Yeah my db was getting big by the end of the day when I needed it in evening for historic light info and found a significant delay of checking persistent data when do was big - found changing to 6pm meant I had a nice small db when I needed it in the evening
Archiving looks like it migrates the last state before creating a new file so you don’t get null errors but I’d say timing is everything if you needed state before the change time. It’s also cron based so you could do it every X hours
Like Rik said tiering the persistance based on need and type is ultimately the proper solution
Also there is no db4o binding in OH2 yet and not sure if it’s planned?