Database size has been steady at around 100MB for a few months. Currently database is over 1000MB and going up every day
I’m not doing anything with HA during the night when the size ramps up.
I don’t know exactly when the size started increasing - I upgraded to latest software (as below) last weekend - there’s no database history before that date.
Running Home Assistant 2023.6.3
Supervisor 2023.06.4
Operating System 10.3
Frontend 20230608.0 - latest
on RPi4 4GB
Relevant section of Configuration.yaml is:
recorder:
purge_keep_days: 7
commit_interval: 30
exclude:
domains:
- automation
- updater
- weather
- sun
- light
entity_globs:
- sensor.hall_sensor*
- sensor.landing_sensor*
entities:
- binary_sensor.hall_sensor_motion
- binary_sensor.landing_sensor_motion
I only added the ‘light’ domain to the exclude list today as noticed I’ve been getting a lot of Hue lights going unavailable and then returning a minute or two later - wondering if all these events are filling up the database - we will see.
Have a lot of indivdually disabled entities - to keep db size down - was working well until recently.
Other recent changes - last few weeks - added Passive BLE Monitor integration - its logbook shows only a few events each day (as expected - it’s monitoring a garage door).
Will set up a notification in future for db size increasing above ‘normal’ size so can jump on it sooner!
Any ideas how to diagnose the issue would be most welcome - thanks
Database size is increasing again this evening so looks like Hue issue is nothing to do with it.
There is this activity in the supervisor log - seems to start around the time the db size starts increasing
23-07-03 20:15:23 INFO (MainThread) [supervisor.resolution.check] Starting system checks with state CoreState.RUNNING
23-07-03 20:15:23 INFO (MainThread) [supervisor.resolution.checks.base] Run check for IssueType.TRUST/ContextType.SUPERVISOR
23-07-03 20:15:23 INFO (MainThread) [supervisor.resolution.checks.base] Run check for IssueType.PWNED/ContextType.ADDON
23-07-03 20:15:23 INFO (MainThread) [supervisor.resolution.checks.base] Run check for IssueType.DOCKER_CONFIG/ContextType.SYSTEM
23-07-03 20:15:23 INFO (MainThread) [supervisor.resolution.checks.base] Run check for IssueType.FREE_SPACE/ContextType.SYSTEM
23-07-03 20:15:23 INFO (MainThread) [supervisor.resolution.checks.base] Run check for IssueType.DNS_SERVER_IPV6_ERROR/ContextType.DNS_SERVER
23-07-03 20:15:23 INFO (MainThread) [supervisor.resolution.checks.base] Run check for IssueType.DNS_SERVER_FAILED/ContextType.DNS_SERVER
23-07-03 20:15:23 INFO (MainThread) [supervisor.resolution.checks.base] Run check for IssueType.SECURITY/ContextType.CORE
23-07-03 20:15:23 INFO (MainThread) [supervisor.resolution.checks.base] Run check for IssueType.IPV4_CONNECTION_PROBLEM/ContextType.SYSTEM
23-07-03 20:15:23 INFO (MainThread) [supervisor.resolution.checks.base] Run check for IssueType.MULTIPLE_DATA_DISKS/ContextType.SYSTEM
23-07-03 20:15:23 INFO (MainThread) [supervisor.resolution.checks.base] Run check for IssueType.NO_CURRENT_BACKUP/ContextType.SYSTEM
23-07-03 20:15:23 INFO (MainThread) [supervisor.resolution.check] System checks complete
23-07-03 20:15:23 INFO (MainThread) [supervisor.resolution.evaluate] Starting system evaluation with state CoreState.RUNNING
23-07-03 20:15:24 INFO (MainThread) [supervisor.resolution.evaluate] System evaluation complete
23-07-03 20:15:24 INFO (MainThread) [supervisor.resolution.fixup] Starting system autofix at state CoreState.RUNNING
23-07-03 20:15:24 INFO (MainThread) [supervisor.resolution.fixup] System autofix complete
23-07-03 20:17:50 INFO (MainThread) [supervisor.updater] Fetching update data from https://version.home-assistant.io/stable.json
23-07-03 20:18:28 INFO (MainThread) [supervisor.homeassistant.api] Updated Home Assistant API token
23-07-03 20:34:02 INFO (MainThread) [supervisor.api.proxy] Home Assistant WebSocket API request initialize
23-07-03 20:34:02 INFO (MainThread) [supervisor.api.proxy] WebSocket access from a0d7b954_vscode
23-07-03 20:34:02 INFO (MainThread) [supervisor.api.proxy] Home Assistant WebSocket API request running
23-07-03 20:39:02 INFO (MainThread) [supervisor.api.proxy] Home Assistant WebSocket API error: Received message 257:None is not WSMsgType.TEXT
23-07-03 20:39:02 INFO (MainThread) [supervisor.api.proxy] Home Assistant WebSocket API connection is closed
23-07-03 20:48:28 INFO (MainThread) [supervisor.homeassistant.api] Updated Home Assistant API token
23-07-03 21:15:24 INFO (MainThread) [supervisor.resolution.check] Starting system checks with state CoreState.RUNNING
23-07-03 21:15:24 INFO (MainThread) [supervisor.resolution.checks.base] Run check for IssueType.TRUST/ContextType.SUPERVISOR
23-07-03 21:15:24 INFO (MainThread) [supervisor.resolution.checks.base] Run check for IssueType.PWNED/ContextType.ADDON
23-07-03 21:15:24 INFO (MainThread) [supervisor.resolution.checks.base] Run check for IssueType.DOCKER_CONFIG/ContextType.SYSTEM
23-07-03 21:15:24 INFO (MainThread) [supervisor.resolution.checks.base] Run check for IssueType.FREE_SPACE/ContextType.SYSTEM
23-07-03 21:15:24 INFO (MainThread) [supervisor.resolution.checks.base] Run check for IssueType.DNS_SERVER_IPV6_ERROR/ContextType.DNS_SERVER
23-07-03 21:15:24 INFO (MainThread) [supervisor.resolution.checks.base] Run check for IssueType.DNS_SERVER_FAILED/ContextType.DNS_SERVER
23-07-03 21:15:24 INFO (MainThread) [supervisor.resolution.checks.base] Run check for IssueType.SECURITY/ContextType.CORE
23-07-03 21:15:24 INFO (MainThread) [supervisor.resolution.checks.base] Run check for IssueType.IPV4_CONNECTION_PROBLEM/ContextType.SYSTEM
23-07-03 21:15:24 INFO (MainThread) [supervisor.resolution.checks.base] Run check for IssueType.MULTIPLE_DATA_DISKS/ContextType.SYSTEM
23-07-03 21:15:24 INFO (MainThread) [supervisor.resolution.checks.base] Run check for IssueType.NO_CURRENT_BACKUP/ContextType.SYSTEM
23-07-03 21:15:24 INFO (MainThread) [supervisor.resolution.check] System checks complete
23-07-03 21:15:24 INFO (MainThread) [supervisor.resolution.evaluate] Starting system evaluation with state CoreState.RUNNING
23-07-03 21:15:24 INFO (MainThread) [supervisor.resolution.evaluate] System evaluation complete
23-07-03 21:15:24 INFO (MainThread) [supervisor.resolution.fixup] Starting system autofix at state CoreState.RUNNING
23-07-03 21:15:24 INFO (MainThread) [supervisor.resolution.fixup] System autofix complete
23-07-03 21:18:28 INFO (MainThread) [supervisor.homeassistant.api] Updated Home Assistant API token
23-07-03 21:24:57 INFO (MainThread) [supervisor.host.info] Updating local host information
23-07-03 21:24:58 INFO (MainThread) [supervisor.host.services] Updating service information
23-07-03 21:24:58 INFO (MainThread) [supervisor.host.network] Updating local network information
23-07-03 21:24:59 INFO (MainThread) [supervisor.host.sound] Updating PulseAudio information
23-07-03 21:24:59 INFO (MainThread) [supervisor.host.manager] Host information reload completed
23-07-03 21:48:29 INFO (MainThread) [supervisor.homeassistant.api] Updated Home Assistant API token
23-07-03 22:09:23 INFO (MainThread) [supervisor.backups.manager] Found 10 backup files
23-07-03 22:09:23 INFO (MainThread) [supervisor.backups.manager] Found 10 backup files
23-07-03 22:15:24 INFO (MainThread) [supervisor.resolution.check] Starting system checks with state CoreState.RUNNING
23-07-03 22:15:24 INFO (MainThread) [supervisor.resolution.checks.base] Run check for IssueType.TRUST/ContextType.SUPERVISOR
23-07-03 22:15:24 INFO (MainThread) [supervisor.resolution.checks.base] Run check for IssueType.PWNED/ContextType.ADDON
23-07-03 22:15:24 INFO (MainThread) [supervisor.resolution.checks.base] Run check for IssueType.DOCKER_CONFIG/ContextType.SYSTEM
23-07-03 22:15:24 INFO (MainThread) [supervisor.resolution.checks.base] Run check for IssueType.FREE_SPACE/ContextType.SYSTEM
23-07-03 22:15:24 INFO (MainThread) [supervisor.resolution.checks.base] Run check for IssueType.DNS_SERVER_IPV6_ERROR/ContextType.DNS_SERVER
23-07-03 22:15:24 INFO (MainThread) [supervisor.resolution.checks.base] Run check for IssueType.DNS_SERVER_FAILED/ContextType.DNS_SERVER
23-07-03 22:15:24 INFO (MainThread) [supervisor.resolution.checks.base] Run check for IssueType.SECURITY/ContextType.CORE
23-07-03 22:15:24 INFO (MainThread) [supervisor.resolution.checks.base] Run check for IssueType.IPV4_CONNECTION_PROBLEM/ContextType.SYSTEM
23-07-03 22:15:24 INFO (MainThread) [supervisor.resolution.checks.base] Run check for IssueType.MULTIPLE_DATA_DISKS/ContextType.SYSTEM
23-07-03 22:15:24 INFO (MainThread) [supervisor.resolution.checks.base] Run check for IssueType.NO_CURRENT_BACKUP/ContextType.SYSTEM
23-07-03 22:15:24 INFO (MainThread) [supervisor.resolution.check] System checks complete
23-07-03 22:15:24 INFO (MainThread) [supervisor.resolution.evaluate] Starting system evaluation with state CoreState.RUNNING
23-07-03 22:15:24 INFO (MainThread) [supervisor.resolution.evaluate] System evaluation complete
23-07-03 22:15:24 INFO (MainThread) [supervisor.resolution.fixup] Starting system autofix at state CoreState.RUNNING
23-07-03 22:15:24 INFO (MainThread) [supervisor.resolution.fixup] System autofix complete
23-07-03 22:17:43 INFO (MainThread) [supervisor.backups.manager] Found 10 backup files
23-07-03 22:17:50 INFO (MainThread) [supervisor.updater] Fetching update data from https://version.home-assistant.io/stable.json
23-07-03 22:18:29 INFO (MainThread) [supervisor.homeassistant.api] Updated Home Assistant API token
23-07-03 22:19:31 INFO (MainThread) [supervisor.store.git] Update add-on https://github.com/thomasmauerer/hassio-addons repository
23-07-03 22:19:31 INFO (MainThread) [supervisor.store.git] Update add-on https://github.com/esphome/home-assistant-addon repository
23-07-03 22:19:31 INFO (MainThread) [supervisor.store.git] Update add-on https://github.com/hassio-addons/repository repository
23-07-03 22:19:31 INFO (MainThread) [supervisor.store.git] Update add-on https://github.com/home-assistant/addons repository
23-07-03 22:19:37 INFO (MainThread) [supervisor.store] Loading add-ons from store: 72 all - 0 new - 0 remove
23-07-03 22:19:37 INFO (MainThread) [supervisor.store] Loading add-ons from store: 72 all - 0 new - 0 remove
I can go back to a backup from 12th June - what do you think, worth a try?
I restored from a backup from 29 June.
Still have a 1000+MB database file but supervisor log is quiet and db size is not currently increasing.
last few lines of supervisor log after restore:
23-07-03 22:46:00 INFO (SyncWorker_5) [supervisor.docker.interface] Starting homeassistant
23-07-03 22:46:01 INFO (MainThread) [supervisor.homeassistant.core] Wait until Home Assistant is ready
23-07-03 22:46:13 INFO (MainThread) [supervisor.resolution.evaluate] Starting system evaluation with state CoreState.FREEZE
23-07-03 22:46:13 INFO (MainThread) [supervisor.resolution.evaluate] System evaluation complete
23-07-03 22:46:15 INFO (MainThread) [supervisor.store.git] Update add-on https://github.com/thomasmauerer/hassio-addons repository
23-07-03 22:46:15 INFO (MainThread) [supervisor.store.git] Update add-on https://github.com/esphome/home-assistant-addon repository
23-07-03 22:46:15 INFO (MainThread) [supervisor.store.git] Update add-on https://github.com/hassio-addons/repository repository
23-07-03 22:46:15 INFO (MainThread) [supervisor.store.git] Update add-on https://github.com/home-assistant/addons repository
23-07-03 22:46:16 INFO (MainThread) [supervisor.updater] Fetching update data from https://version.home-assistant.io/stable.json
23-07-03 22:46:21 INFO (MainThread) [supervisor.store] Loading add-ons from store: 72 all - 0 new - 0 remove
23-07-03 22:46:21 INFO (MainThread) [supervisor.store] Loading add-ons from store: 72 all - 0 new - 0 remove
23-07-03 22:47:28 INFO (MainThread) [supervisor.homeassistant.core] Detect a running Home Assistant instance
23-07-03 22:47:28 INFO (MainThread) [supervisor.backups.manager] Full-Restore 5b586d76 done
23-07-03 22:48:01 INFO (MainThread) [supervisor.backups.manager] Found 10 backup files
23-07-03 22:48:01 INFO (MainThread) [supervisor.backups.manager] Found 10 backup files
23-07-03 22:48:30 INFO (MainThread) [supervisor.homeassistant.api] Updated Home Assistant API token
???
Guess what looks like it is the Passive BLE integration that is responsible.
Had to read around how to see what’s filling up the database - installed the sqlite web add on and copied some magic commands from here: How to keep your recorder database size under control
No real idea what I’m doing but the two entries at the top of the query results were from the BLE integration and with cnt (whatever that is) more than 100x higher than nearest other entity.
Disabled one of them and excluded the other from history so hopefully in a few days all will be well.
If anyone has any idea why the integration loads the database so much then please share.
I think HA is great but there needs to be an easier way of seeing what happening in the database - like I can see memory use on my phone - without sql query incantations.
Have a good one
1 Like
