If you don’t compact your database (whatever is appropriate for the database platform that you are using) would you even notice a change? I’m more familiar with with SQLite and MySQLand they won’t release unused space unless you vacuum (SQLite) or optimize (MySQL).
If you use the recorder.purge service with repack the database will get cleaned up with SQLite, PostgreSQL, MySQL and MariaDB. Please note that 0.112 is the first version to support MySQL and MariaDB with repack.
The default auto_purge will not repack
1 Like
So far with the tweaks from this thread and the 0.112 beta, my database size for my 14 day purge interval is down to 2.8GB from 6.6GB and looks like it’s getting lower.
1 Like
Is this the correct format for my exclude.yaml?
- binary_sensor.alarm_siren_son_sv_status
- sensor.esp_connected_bssid_alarm_siren_son_sv
- sensor.esp_connected_ssid_alarm_siren_son_sv
six spaces, that’s what i copied out of my config.yaml
You could use SQL to get the database size. It wouldn’t be exactly the same as disk allocation, but it should be close.
SELECT
table_name AS `Table Name`,
table_rows AS `Row Count`,
ROUND(SUM(data_length)/(1024*1024*1024), 3) AS `Table Size [GB]`,
ROUND(SUM(index_length)/(1024*1024*1024), 3) AS `Index Size [GB]`,
ROUND(SUM(data_length+index_length)/(1024*1024*1024), 3) `Total Size [GB]`
FROM information_schema.TABLES
WHERE table_schema = 'homeassistant'
GROUP BY table_name
ORDER BY table_name
Above is for an installation on MariaDB. Adjust the database name (table_schema) as needed, or remove the WHERE clause entirely if all you have on that service is your Home Assistant schema.
You’d get something like the following (here produced using phpMyAdmin):
4 Likes
tl;dr Is the likelihood of SD card failures post HA 0.112 lower?
This week my SD card failed. I’d had a notification of a power issue at some point, which I foolishly ignored. Soon after my SD failed and I had to restart. I had some ‘faux’ backups so it wasn’t too bad. First thing I did was to create a real backup using the snapshots, the excellent Hass.io Google Drive Backup, IFTTT and Dropbox.
Next I am looking at logbook, history and recorder. A long time ago I’d thrown date, time and weather into my excludes but now I am wondering what do I really want in there past what’s useful for troubleshooting and whether this is even an issue with the new DB changes. I run HA from a RPi 3 model B, official psu and now SanDisk Extreme 64 GB microSDXC A2 App Performance as the previous card was the 32Gb with A1 and I noted that the A2 is the recommended one.
I know many people recommend upgrading from the Pi, but for me personally the Pi has many advantages;
- cheap (much cheaper than a NUC which would be the step up)
- lower power requirements (I am guessing noticeably so over a NUC but correct me if I am wrong)
- silent (again, a NUC will have fans I am guessing)
Now I have a ‘true’ backup I am thinking that if the SD goes again, I’m just a £15 SD card and a few minutes away from being back up and running, which seems a much cheaper option than everything that would be involved with moving off the Pi. Has anyone switched from the Pi to the NUC and felt that there wasn’t much gained from it? I appreciate that it’s a little early with 0.112 to really judge the DB stuff.
With this methodical application of excludes and the improvements in 0.112 my 7 day database has fallen from 1.5GB to 240MB, which is an 84% reduction in size. I still have all the graphs and history I was interested in (pages and pages of graphs).
1 Like
use JSON_EXTRACT instead of JSON_VALUE if on SQLite
1 Like
I have read this thread from the first post by @tom_l, and this is what I have done so far…
- Created a directory www
- Created files logbook.yaml, history.yaml and recorder.yaml
- Added the following to my configuration.yaml
logbook: !include logbook.yaml
history: !include history.yaml
recorder: !include recorder.yaml
- In the scripts .yaml file added the following
print_entities_to_file:
alias: 'Print Entities To File'
sequence:
- service: notify.entity_log
data_template:
message: >
{% for state in states %}
- {{- state.entity_id -}}
{% endfor %}
- Restarted HASS and from developer tools service, called the service script.turn_on for the entity script.print_entities_to_file. this generated the entity_log.txt file in the www directory. I downloaded this file and opened it in my notepad++.
- In a worksheet I have copied these entities and made the table as described and run the sort also.
Now that I have done the exercise of checking exclude and include for the recorder, history and logbook. do I Iist these exclude/include in the respective yaml files ie logbook.yaml, history.yaml and recorder.yaml
I request guidance from @tom_l to proceed with the Mariadb part.
What MariaDB part?
In your earlier post in which the worksheet data is sorted, just after this you have defined recorder.yaml code.
The begining is
db_url: !secret mariadb_url
Can you explain please. FYI I have not added anything to my secrets.yaml file.
I have started Mariadb in which a login (username and password has been defined)
What is to be added to the secrets.yaml file?
See: https://www.home-assistant.io/integrations/recorder/#db_url
And the documentation in the addon (supervisor / mariadb ) for generating this URL.
Setting up MariaDB is beyond the scope of this guide. If you have further questions search the forum or start a topic in the configuration category.
Along with this I have added these to my configuration.yaml
history: !include history.yaml
recorder: !include recorder.yaml
All three are trapped in the check config step.The error is
Invalid config for [logbook]: [logbook] is an invalid option for [logbook]. Check: logbook->logbook->logbook. (See /config/configuration.yaml, line 16).
Invalid config for [recorder]: [recorder] is an invalid option for [recorder]. Check: recorder->recorder->recorder. (See /config/configuration.yaml, line 18).
Invalid config for [history]: [history] is an invalid option for [history]. Check: history->history->history. (See /config/configuration.yaml, line 17)
How do I get to have recorder, logboog and history congiguration in seperate files?
If you have
logbook:
In logbook.yaml, that is the culprit. Same for history and recorder.
1 Like
Thanks @francisp!
With your help and this post I am able to choose what I want for the record a, history and logbook.
That was done using InfluxDB and the Grafana addons. See: https://youtu.be/m9qIqq104as
Also make sure to use includes or excludes to reduce the size of your InfluxDB database.
Hi,
You can get the DB-size with a sensor.
Use almost the same url as for the recorder.
sensor:
- platform: sql
db_url: !secret dbsizeurl_nas
queries:
- name: DB size mB
query: 'SELECT table_schema "database", Round(Sum(data_length + index_length) / (1024 * 1024), 1) "value" FROM information_schema.tables WHERE table_schema="homeassistant" GROUP BY table_schema;'
column: "value"
unit_of_measurement: mB
**note tableschema should be the same as in the database section of Mariadb addon
From secrets.yaml
For a local DB
dbsizeurl: mysql://<dbuser>:<password>@core-mariadb/homeassistant
i.e.
dbsizeurl: mysql://homeassistant:CorrectHorseBatteryStaple@core-mariadb/homeassistant
For a db running on another instance
dbsizeurl_nas: mysql://<dbuser>:<password>@<ipadress>:<port>/homeassistant
i.e.
dbsizeurl_nas: mysql://dbuser:[email protected]:3307/homeassistant
Is there something similar, reliable for an InfluxDB?
Is this what you mean?
I have no idea no where I got this from 
- platform: influxdb
host: a0d7b954-influxdb
port: 8086
username: homeassistant
password: !secret influxdb_password
queries:
- name: InfluxDB Database Size
unit_of_measurement: MB
value_template: '{{ (value | float / 1024 /1024) | round(1) }}'
group_function: sum
measurement: '"monitor"."shard"'
database: _internal
where: 'time > now() - 10s'
field: diskBytes
2 Likes