@majordennisbloodnok

Over the last few days the Heat Pump has kicked back into action as the outdoor temperature has dropped below 15c, which has given me the opportunity to take a look at the Home Assistant data.

So far so good. I have lots of data feeding through from the HP which is super helpful. I can now see in detail what's been happening in the background and the HP seems to be running as expected. I can now see when it cycles, which thankfully appears to be very little, only once an hour if that in this milder weather and track the energy consumption in much more detail.

I never managed to get Open Energy Monitor installed after many attempts, and for the moment have abandoned this. It would be great to see the SCOP etc, however I am not overly fussed as I know the HP must be running pretty efficiently, as the energy usage is around 300w per hour over a sustained period with little cycling.

As previously discussed, HA seems to log the detailed data over a 10 day period and then archives (if that's the right way to describe it) the data as an hourly (?) average.

This is ok, however I am thinking of increasing the 10 day window so that I can drill into the data over a longer period, particularly over this first full autumn/winter period.

I have HA running on an old ish very low power laptop which houses a 512GB SSD. With this in mind, what are the pitfalls of increasing the 10 day limit, to maybe 100, or perhaps more?

I am not monitoring much, just the HP and the Shelly energy meter. Will increasing this limit a good amount significantly increase the data storage amount and could it slow down the system, or make it run a lot harder (if that makes sense!)?

Thanks.

That's a great question, @bash.

The straightforward answer is that if you extend the retention time by a factor of 10, you'll end up storing 10 times the amount of data. You would need to know how much data you're currently storing to understand what a tenfold increase would mean in practice, so your first port of call would be to go to Settings\System\Storage and take a look at how much space your various system components are using. Since I have a 60 day retention period and a 256Gb disc which is just under 40% full, I suspect you'd be fine with a 512Gb disc. As for the system running slower, it won't affect Home Assistant itself at all since Home Assistant offloads the data to its database; it's the database that may be slower to return data to Home Assistant if it has to sort through lots of data, but that's OK because that's its job.

The more complex answer, however, is twofold.

Firstly, I think you are now aware you can significantly tweak what you do and don't include or exclude from recording data. This means that if you exclude anything you know you won't be interested in looking back on (for me, for instance, I don't care what the Met Office's forecasted humidity was; just what it is or will be), that saves space that you can use for things you are interested in keeping. Even if you don't amend the retention period, doing something like this helps keep your system running as efficiently as it can.

Secondly, not all databases are equal. The Home Assistant default is SQLite but others are available to - I happen to use MariaDB instead. In your situation I doubt it would make much sense to get your head around that extra complexity, but I just want to highlight that different database systems excel at different things. I don't envisage your historical querying to be that intensive in database terms, but if you started wanting to regularly run lots of reports simultaneously then database choice might make a difference.

Coming back to the simple again, though, I doubt you'll run into any significant issues if you retain the last six months or a year's worth of data. The only proviso I would make, though, is to ensure you've sorted out regular backups of your installation and are storing those backups somewhere not on the HA box itself. If you haven't done this, I'd strongly suggest you tackle this before making any further config changes.

@majordennisbloodnok 

Thanks.

Currently the system is using 25.8gb, total storage with everything else is 26.4gb.

I have just removed a few of the sensors that aren't populating, or that I have found aren't of any value. I may remove some more if I find they don't prove to be of value going forward. I assume these are easy to re-enable if I disable them for now?

Backups are on, however I manually copy these onto another local computer. Can this be automated (relatively easily) to OneDrive as I have 2tb storage available?

The system storage is already at 25.8gb. This seems high, perhaps because the ESP32 board in the HP is set to refresh the sensor data every 10 seconds?

It's your App data that gives you an idea of the database size. 25Gb for the system is what is being taken up by the operating system and so is not going to appreciably increase from day to day. Given the Home Assistant installation is on top of that and you're only taking 26.4Gb in total, it looks like your database is tiny at the moment.

As for backups, I have an app installed. https://github.com/lavinir/hassio-onedrive-backup

That allows me to schedule backups and store them on OneDrive. Configuration once installed is via a relatively intuitive web interface running on your HA box, so you might want to take a squizz at it and see what you think.

@majordennisbloodnok 

Great!

I am assuming I just need to add this into the configuration.yaml file?

recorder:
purge_keep_days: 100

I'll take a look at the OneDrive backup app, as that would be ideal.

Posted by: @bash

↑

@majordennisbloodnok 

Great!

I am assuming I just need to add this into the configuration.yaml file?

recorder:
purge_keep_days: 100

 

I'll take a look at the OneDrive backup app, as that would be ideal.

That's right, and you'd probably want to have auto_purge as well.

@majordennisbloodnok 

Thanks, what would the auto_purge look like?

Posted by: @bash

↑

@majordennisbloodnok 

Thanks, what would the auto_purge look like?

Something like this:

recorder:
  purge_keep_days: 100
  auto_purge: true # Default value, but good to be explicit
  commit_interval: 5 # How often data is written to disk (in seconds)
  exclude:
    domains:
      - updater
      - automation
      - script
      - update
    entities:
      - sensor.last_boot
      - sensor.met_office_someweatherstation_humidity_3_hourly
    entity_globs:
      - device_tracker.*

I've left in some excludes as well so you can see the format, but obviously that's going to be unique to you. The commit_interval is also up for debate since that's a tradeoff between data retention and disc longevity. If you have it set to 0 then data will be written to the database as soon as it comes in which is great for ensuring all data is captured but is more write-intensive. If you keep the default offset of 5 seconds, if you were to lose power then any data received in that last 5 seconds would not have been written to the database and would therefore be lost. With Home Assistant the data received is generally not that critical, so the more disc-friendly offset is a good compromise.

Posted by: @majordennisbloodnok

↑

Secondly, not all databases are equal. The Home Assistant default is SQLite but others are available to - I happen to use MariaDB instead.

The issue with HA is tons of columns in the table. They do contain useful data, for example what automation flipped a switch, etc. It is a nice model for sensors that don't update too often. However, for stuff that updates often, it simply uses too much space.

If possible, sensors should be configured to publish quickly when the value changes, but slowly when it doesn't.

Since all sensors publish on MQTT (via zigbee2mqtt or home assistant statestream) I store all MQTT traffic in clickhouse. It's a time series database. The table is very simple: topic, timestamp, value as float. I used a bunch of filters to only store relevant data. With compression, it uses about 1.5 byte per row, ie the table with currently 4 billion rows takes about 6GB. 

Posted by: @bobflux

↑

the table with currently 4 billion rows takes about 6GB

Cripes! 

I gave up with the HA database (SQLite, no interest in migrating to something even more impenetrable), it was just too badly organised. Extracting data was a right royal PITA. It also has a habit of self-destructing if you touch it the wrong way. In fact I was able to ditch HA altogether, and use python to collect over modbus and then process my data and put it in csv files. My minute data file (the data collected every minute) currently has 1.8 million rows and takes up 169,094,302 bytes which is just under 100 bytes per row but the rows are quite long (20 columns), and the file is not compressed. Being a csv file I can open it in Notepad++ or any of the large csv file editors, or use sql (using q text as data) which is very capable and extremely fast, to extract and process data.

Not an approach for everyone of course, but it is always an option for those who want to try such an approach.    

Posted by: @cathoderay

↑

…

I gave up with the HA database (SQLite, no interest in migrating to something even more impenetrable), it was just too badly organised. Extracting data was a right royal PITA.

….

It’s well enough organised for what it’s designed to do, but sadly deeper analysis is not at the top of its priorities. The very reason I use MariaDB (or MySQL) is that it is a far better enterprise-grade architecture for querying from remote clients. However, data is my world and there is a lot to be said for sticking with familiarity; a fully featured beast like MySQL is not for everyone no matter how robust it is.

Several billion records is a helluva lot of data for a domestic setting and I can’t imagine my needing that level of granularity. However, it does illustrate how different individuals’ needs can be as well as just how flexible something like Home Assistant can be to accommodate. The price, of course, is complexity and such a setup is definitely not plug and play for the average punter.

Posted by: @majordennisbloodnok

↑

Several billion records is a helluva lot of data for a domestic setting

It's laziness lol

The PV system is very chatty, meter power is used to control loads like the EV charger, so it updates something like 10 times/s, plus all the other stuff. This is not stored in home assistant, it's stored in clickhouse like the rest of MQTT. I considered purging the clickhouse database periodically, but it grows by 1GB/year so at this rate the 250GB SSD is never going to fill up anyway! So right now all I got is a filter that defers publishing something until it has changed enough to be worthwhile.

After about a year it got slow (ie, zooming out to display the entire year took a second to load) that's when I realized I had forgotten to enable the aggregated materialized views and not noticed because clickhouse aggregates the raw data at about 200M rows/s lol