Home-assistant_v2.db is >3GB's in Size Home Assistant unresponsive after 1-2 days uptime

My Home Assistant will start slowing down and eventually be unresponsive after running for a few days straight. I noticed a bunch of rest sensor timeout errors after it’s been running for about a day… and, wont’ stop with those timeout errors until I either restart Home Assistant or it eventually becomes unresponsive (requiring a forced restart).

Configuration.yaml:

recorder:
  purge_keep_days: 7

home-assistant_v2.db is 3,140,360Kb in size!

The below log errors start accumulating after Home Assistant has been running for 6-8 hours. I’m not sure why I’m seeing all those REST sensor timeouts since the respective API endpoints are accessible from any device on my LAN within just a 1-3 seconds response time.

Could someone please tell me if my database file size has something to with the stability issue and all the errors below? I’ve tried deleting the database… but then it just grows back to that size again. I’m not sure how to troubleshoot this issue.

2020-07-09 01:58:29 WARNING (MainThread) [homeassistant.helpers.entity] Update of sensor.horizon_ram is taking over 10 seconds
2020-07-09 01:58:29 ERROR (SyncWorker_4) [homeassistant.components.rest.sensor] Error fetching data: http://192.168.1.2:61208/api/3/mem failed with HTTPConnectionPool(host='192.168.1.2', port=61208): Read timed out. (read timeout=10)
2020-07-09 01:59:45 WARNING (MainThread) [homeassistant.components.sensor] Updating rest sensor took longer than the scheduled update interval 0:00:10
2020-07-09 02:00:04 WARNING (MainThread) [homeassistant.helpers.entity] Update of sensor.horizon_pagefile is taking over 10 seconds
2020-07-09 02:00:04 ERROR (SyncWorker_3) [homeassistant.components.rest.sensor] Error fetching data: http://192.168.1.2:61208/api/3/memswap failed with HTTPConnectionPool(host='192.168.1.2', port=61208): Read timed out. (read timeout=10)
2020-07-09 02:01:12 WARNING (MainThread) [homeassistant.helpers.entity] Update of sensor.horizon_ram is taking over 10 seconds
2020-07-09 02:01:12 ERROR (SyncWorker_1) [homeassistant.components.rest.sensor] Error fetching data: http://192.168.1.2:61208/api/3/mem failed with HTTPConnectionPool(host='192.168.1.2', port=61208): Read timed out. (read timeout=10)
2020-07-09 02:01:23 WARNING (MainThread) [homeassistant.helpers.entity] Update of sensor.gpu is taking over 10 seconds
2020-07-09 02:01:23 ERROR (SyncWorker_4) [homeassistant.components.rest.sensor] Error fetching data: http://192.168.1.2:61208/api/3/gpu failed with HTTPConnectionPool(host='192.168.1.2', port=61208): Read timed out. (read timeout=10)
2020-07-09 02:01:23 WARNING (SyncWorker_4) [homeassistant.components.rest.sensor] Empty reply found when expecting JSON data
2020-07-09 02:01:23 ERROR (MainThread) [homeassistant.components.template.sensor] Could not render template horizon_gpu_memory: UndefinedError: 'mappingproxy object' has no attribute 'mem'
2020-07-09 02:01:23 WARNING (MainThread) [homeassistant.components.alexa.capabilities] Invalid temp value  for sensor.horizon_gpu_temperature
2020-07-09 02:18:31 WARNING (MainThread) [homeassistant.helpers.entity] Update of sensor.horizon_pagefile is taking over 10 seconds
2020-07-09 02:18:31 WARNING (MainThread) [homeassistant.helpers.entity] Update of sensor.horizon_ram is taking over 10 seconds
2020-07-09 02:18:31 ERROR (SyncWorker_1) [homeassistant.components.rest.sensor] Error fetching data: http://192.168.1.2:61208/api/3/mem failed with HTTPConnectionPool(host='192.168.1.2', port=61208): Read timed out. (read timeout=10)
2020-07-09 02:18:31 ERROR (SyncWorker_0) [homeassistant.components.rest.sensor] Error fetching data: http://192.168.1.2:61208/api/3/memswap failed with HTTPConnectionPool(host='192.168.1.2', port=61208): Read timed out. (read timeout=10)
2020-07-09 02:18:32 WARNING (MainThread) [homeassistant.components.sensor] Updating rest sensor took longer than the scheduled update interval 0:00:10
2020-07-09 05:28:20 WARNING (MainThread) [homeassistant.components.sensor] Updating rest sensor took longer than the scheduled update interval 0:00:10
2020-07-09 05:54:38 ERROR (SyncWorker_3) [homeassistant.components.openweathermap.weather] Exception when calling OWM web API to update data
2020-07-09 06:56:01 WARNING (MainThread) [homeassistant.components.sensor] Updating rest sensor took longer than the scheduled update interval 0:00:10
2020-07-09 08:18:21 WARNING (MainThread) [homeassistant.helpers.entity] Update of sensor.horizon_uptime is taking over 10 seconds
2020-07-09 08:18:21 ERROR (SyncWorker_2) [homeassistant.components.rest.sensor] Error fetching data: http://192.168.1.2:61208/api/3/uptime failed with HTTPConnectionPool(host='192.168.1.2', port=61208): Read timed out. (read timeout=10)
2020-07-09 09:17:59 WARNING (MainThread) [homeassistant.helpers.entity] Update of sensor.horizon_ram is taking over 10 seconds
2020-07-09 09:17:59 ERROR (SyncWorker_0) [homeassistant.components.rest.sensor] Error fetching data: http://192.168.1.2:61208/api/3/mem failed with HTTPConnectionPool(host='192.168.1.2', port=61208): Read timed out. (read timeout=10)
2020-07-09 09:18:00 WARNING (MainThread) [homeassistant.components.sensor] Updating rest sensor took longer than the scheduled update interval 0:00:10
2020-07-09 09:18:16 WARNING (MainThread) [homeassistant.helpers.entity] Update of sensor.horizon_uptime is taking over 10 seconds
2020-07-09 09:18:16 ERROR (SyncWorker_2) [homeassistant.components.rest.sensor] Error fetching data: http://192.168.1.2:61208/api/3/uptime failed with HTTPConnectionPool(host='192.168.1.2', port=61208): Read timed out. (read timeout=10)
2020-07-09 09:18:18 WARNING (MainThread) [homeassistant.helpers.entity] Update of sensor.horizon_pagefile is taking over 10 seconds
2020-07-09 09:18:18 ERROR (SyncWorker_4) [homeassistant.components.rest.sensor] Error fetching data: http://192.168.1.2:61208/api/3/memswap failed with HTTPConnectionPool(host='192.168.1.2', port=61208): Read timed out. (read timeout=10)
2020-07-09 09:18:32 WARNING (MainThread) [homeassistant.helpers.entity] Update of sensor.horizon_ram is taking over 10 seconds
2020-07-09 09:18:32 ERROR (SyncWorker_0) [homeassistant.components.rest.sensor] Error fetching data: http://192.168.1.2:61208/api/3/mem failed with HTTPConnectionPool(host='192.168.1.2', port=61208): Read timed out. (read timeout=10)
2020-07-09 09:18:47 ERROR (bellows.thread_0) [bellows.uart] CRC error in frame b'00217aa1ad90fb7e' (b'90fb' != b'0537')
2020-07-09 09:18:54 ERROR (SyncWorker_0) [homeassistant.components.rest.sensor] Error fetching data: http://192.168.1.2:61208/api/3/mem failed with HTTPConnectionPool(host='192.168.1.2', port=61208): Read timed out. (read timeout=10)
2020-07-09 09:18:54 WARNING (MainThread) [homeassistant.helpers.entity] Update of sensor.horizon_ram is taking over 10 seconds
2020-07-09 09:18:54 WARNING (MainThread) [homeassistant.components.sensor] Updating rest sensor took longer than the scheduled update interval 0:00:10
2020-07-09 09:19:15 WARNING (MainThread) [homeassistant.helpers.entity] Update of sensor.horizon_ram is taking over 10 seconds
2020-07-09 09:19:15 ERROR (SyncWorker_4) [homeassistant.components.rest.sensor] Error fetching data: http://192.168.1.2:61208/api/3/mem failed with HTTPConnectionPool(host='192.168.1.2', port=61208): Read timed out. (read timeout=10)
2020-07-09 09:19:36 WARNING (MainThread) [homeassistant.helpers.entity] Update of sensor.horizon_ram is taking over 10 seconds
2020-07-09 09:19:36 ERROR (SyncWorker_0) [homeassistant.components.rest.sensor] Error fetching data: http://192.168.1.2:61208/api/3/mem failed with HTTPConnectionPool(host='192.168.1.2', port=61208): Read timed out. (read timeout=10)
2020-07-09 09:19:58 WARNING (MainThread) [homeassistant.helpers.entity] Update of sensor.horizon_ram is taking over 10 seconds
2020-07-09 09:19:58 ERROR (SyncWorker_3) [homeassistant.components.rest.sensor] Error fetching data: http://192.168.1.2:61208/api/3/mem failed with HTTPConnectionPool(host='192.168.1.2', port=61208): Read timed out. (read timeout=10)
2020-07-09 09:20:18 WARNING (MainThread) [homeassistant.helpers.entity] Update of sensor.horizon_uptime is taking over 10 seconds
2020-07-09 09:20:18 ERROR (SyncWorker_2) [homeassistant.components.rest.sensor] Error fetching data: http://192.168.1.2:61208/api/3/uptime failed with HTTPConnectionPool(host='192.168.1.2', port=61208): Read timed out. (read timeout=10)
2020-07-09 09:20:31 WARNING (MainThread) [homeassistant.helpers.entity] Update of sensor.horizon_ram is taking over 10 seconds
2020-07-09 09:20:31 ERROR (SyncWorker_1) [homeassistant.components.rest.sensor] Error fetching data: http://192.168.1.2:61208/api/3/mem failed with HTTPConnectionPool(host='192.168.1.2', port=61208): Read timed out. (read timeout=10)
2020-07-09 09:20:44 WARNING (MainThread) [homeassistant.helpers.entity] Update of sensor.horizon_pagefile is taking over 10 seconds
2020-07-09 09:20:44 ERROR (SyncWorker_1) [homeassistant.components.rest.sensor] Error fetching data: http://192.168.1.2:61208/api/3/memswap failed with HTTPConnectionPool(host='192.168.1.2', port=61208): Read timed out. (read timeout=10)
2020-07-09 09:21:04 WARNING (MainThread) [homeassistant.helpers.entity] Update of sensor.horizon_ram is taking over 10 seconds
2020-07-09 09:21:04 ERROR (SyncWorker_1) [homeassistant.components.rest.sensor] Error fetching data: http://192.168.1.2:61208/api/3/mem failed with HTTPConnectionPool(host='192.168.1.2', port=61208): Read timed out. (read timeout=10)
2020-07-09 09:21:19 WARNING (MainThread) [homeassistant.helpers.entity] Update of sensor.horizon_uptime is taking over 10 seconds
2020-07-09 09:21:19 ERROR (SyncWorker_2) [homeassistant.components.rest.sensor] Error fetching data: http://192.168.1.2:61208/api/3/uptime failed with HTTPConnectionPool(host='192.168.1.2', port=61208): Read timed out. (read timeout=10)
2020-07-09 09:21:37 WARNING (MainThread) [homeassistant.helpers.entity] Update of sensor.horizon_ram is taking over 10 seconds
2020-07-09 09:21:37 ERROR (SyncWorker_3) [homeassistant.components.rest.sensor] Error fetching data: http://192.168.1.2:61208/api/3/mem failed with HTTPConnectionPool(host='192.168.1.2', port=61208): Read timed out. (read timeout=10)
2020-07-09 09:21:59 WARNING (MainThread) [homeassistant.helpers.entity] Update of sensor.horizon_ram is taking over 10 seconds
2020-07-09 09:21:59 ERROR (SyncWorker_1) [homeassistant.components.rest.sensor] Error fetching data: http://192.168.1.2:61208/api/3/mem failed with HTTPConnectionPool(host='192.168.1.2', port=61208): Read timed out. (read timeout=10)
2020-07-09 09:22:20 WARNING (MainThread) [homeassistant.helpers.entity] Update of sensor.horizon_uptime is taking over 10 seconds
2020-07-09 09:22:20 ERROR (SyncWorker_0) [homeassistant.components.rest.sensor] Error fetching data: http://192.168.1.2:61208/api/3/uptime failed with HTTPConnectionPool(host='192.168.1.2', port=61208): Read timed out. (read timeout=10)
2020-07-09 09:22:32 WARNING (MainThread) [homeassistant.components.sensor] Updating rest sensor took longer than the scheduled update interval 0:00:10
2020-07-09 09:22:32 ERROR (SyncWorker_0) [homeassistant.components.rest.sensor] Error fetching data: http://192.168.1.2:61208/api/3/mem failed with HTTPConnectionPool(host='192.168.1.2', port=61208): Read timed out. (read timeout=10)
2020-07-09 09:22:32 WARNING (MainThread) [homeassistant.helpers.entity] Update of sensor.horizon_ram is taking over 10 seconds
2020-07-09 09:22:49 WARNING (MainThread) [homeassistant.helpers.entity] Update of sensor.horizon_pagefile is taking over 10 seconds
2020-07-09 09:22:49 ERROR (SyncWorker_2) [homeassistant.components.rest.sensor] Error fetching data: http://192.168.1.2:61208/api/3/memswap failed with HTTPConnectionPool(host='192.168.1.2', port=61208): Read timed out. (read timeout=10)
2020-07-09 09:23:03 WARNING (MainThread) [homeassistant.helpers.entity] Update of sensor.horizon_ram is taking over 10 seconds
2020-07-09 09:23:03 WARNING (MainThread) [homeassistant.components.sensor] Updating rest sensor took longer than the scheduled update interval 0:00:10
2020-07-09 09:23:03 ERROR (SyncWorker_4) [homeassistant.components.rest.sensor] Error fetching data: http://192.168.1.2:61208/api/3/mem failed with HTTPConnectionPool(host='192.168.1.2', port=61208): Read timed out. (read timeout=10)
2020-07-09 09:23:21 WARNING (MainThread) [homeassistant.helpers.entity] Update of sensor.horizon_uptime is taking over 10 seconds
2020-07-09 09:23:35 WARNING (MainThread) [homeassistant.helpers.entity] Update of sensor.horizon_ram is taking over 10 seconds
2020-07-09 09:23:35 ERROR (SyncWorker_3) [homeassistant.components.rest.sensor] Error fetching data: http://192.168.1.2:61208/api/3/mem failed with HTTPConnectionPool(host='192.168.1.2', port=61208): Read timed out. (read timeout=10)
2020-07-09 09:23:51 WARNING (MainThread) [homeassistant.helpers.entity] Update of sensor.horizon_pagefile is taking over 10 seconds
2020-07-09 09:23:51 ERROR (SyncWorker_2) [homeassistant.components.rest.sensor] Error fetching data: http://192.168.1.2:61208/api/3/memswap failed with HTTPConnectionPool(host='192.168.1.2', port=61208): Read timed out. (read timeout=10)
2020-07-09 09:24:07 WARNING (MainThread) [homeassistant.helpers.entity] Update of sensor.horizon_ram is taking over 10 seconds
2020-07-09 09:24:07 ERROR (SyncWorker_4) [homeassistant.components.rest.sensor] Error fetching data: http://192.168.1.2:61208/api/3/mem failed with HTTPConnectionPool(host='192.168.1.2', port=61208): Read timed out. (read timeout=10)
2020-07-09 09:24:22 WARNING (MainThread) [homeassistant.helpers.entity] Update of sensor.horizon_uptime is taking over 10 seconds
2020-07-09 09:24:22 ERROR (SyncWorker_1) [homeassistant.components.rest.sensor] Error fetching data: http://192.168.1.2:61208/api/3/uptime failed with HTTPConnectionPool(host='192.168.1.2', port=61208): Read timed out. (read timeout=10)
2020-07-09 09:24:40 WARNING (MainThread) [homeassistant.helpers.entity] Update of sensor.horizon_ram is taking over 10 seconds
2020-07-09 09:24:40 ERROR (SyncWorker_3) [homeassistant.components.rest.sensor] Error fetching data: http://192.168.1.2:61208/api/3/mem failed with HTTPConnectionPool(host='192.168.1.2', port=61208): Read timed out. (read timeout=10)
2020-07-09 09:24:54 WARNING (MainThread) [homeassistant.helpers.entity] Update of sensor.horizon_pagefile is taking over 10 seconds
2020-07-09 09:24:54 ERROR (SyncWorker_4) [homeassistant.components.rest.sensor] Error fetching data: http://192.168.1.2:61208/api/3/memswap failed with HTTPConnectionPool(host='192.168.1.2', port=61208): Read timed out. (read timeout=10)
2020-07-09 09:25:13 WARNING (MainThread) [homeassistant.helpers.entity] Update of sensor.horizon_ram is taking over 10 seconds
2020-07-09 09:25:13 ERROR (SyncWorker_4) [homeassistant.components.rest.sensor] Error fetching data: http://192.168.1.2:61208/api/3/mem failed with HTTPConnectionPool(host='192.168.1.2', port=61208): Read timed out. (read timeout=10)

There are some tips here:

Personally (and it’s a bit of a pain to setup and keep up with, though worth it in the end), you can set your recorder: settings in configuration.yaml to include / exclude entities and it’ll only keep track of what you want. I have hundreds of entities and mine’s never over ~400mb with the same 7 day ‘keep_days’.

recorder:
  purge_keep_days: 7
  include:
    domains:
      - sensor
      - switch
      - shell_command
      - binary_sensor
      - input_text
      - light
      - script
      - input_boolean
      - input_text
  exclude:
    entities:
      - sensor.time
      - sensor.6900_master_last_changed
      - sensor.6900_kitchen_last_changed
      - sensor.6900_upstairs_last_changed
      - sensor.4055_front_last_changed
      - sensor.4055_middle_last_changed
      - sensor.4055_rear_last_changed
      - sensor.garage_door_east_last_changed
     .......

I have dozens more listed below this as entities I want to EXCLUDE.

Typically I use the logbook and see what’s in there that I don’t care about and add the specific entities to this list. Took a bit initially and now it’s just random whenever I check the logbook and see something I’d rather not…

Thanks for your responses. If i were to go through the trouble of reducing the size of my database side, would that resolve the issues I’m having? I was just guessing that the cause of my issues is because of a large database size. I dont mind having a large database as long as it doesn’t cause instability. Is there any way to troubleshoot this the errors I’m seeing in the logs I posted and why Home Assistant eventually becomes unresponsive?

Which version are you running?

I’m running: Home Assistant 0.112.3.

Can you check the states table to see if it is a specific entity that is filling up the database?

This was a great tip. Thanks, i now know exactly what sensors are taking up most of the space. Several of them are used for monitoring my server’s performance; some of them polling every 8 seconds. I use this info to produce cards with graphs in Lovelace.

Below, is a list of my top 25 sensors with respective counts in my database:

sensor.diskio_1|53530
sensor.diskio_0|53511
sensor.network|53476
sensor.horizon_e_drive_write_bytes|53450
sensor.horizon_c_drive_write_bytes|53431
sensor.horizon_total_speed|51709
sensor.horizon_download_speed|50426
sensor.horizon_upload_speed|48602
sensor.horizon_cpu|48227
zwave.aeon_labs_zw100_multisensor_6|39916
sensor.horizon_e_drive_read_bytes|37239
sensor.horizon_c_drive_read_bytes|37231
sensor.aeon_labs_zw100_multisensor_6_luminance|32749
sensor.log_sunlight|26958
sensor.rt_ac5300_packets_sent|20627
sensor.rt_ac5300_b_sent|20626
sensor.rt_ac5300_packets_received|20626
sensor.rt_ac5300_b_received|20536
sensor.rt_ac5300_packets_s_sent|20505
sensor.rt_ac5300_packets_s_received|20500
sensor.rt_ac5300_kib_s_sent|20459
sensor.rt_ac5300_kib_s_received|20444
sensor.asuswrt_upload_speed|18344
sensor.asuswrt_download_speed|18192
sensor.horizon_uptime|16142

Consider decreasing the polling frequency or wait for 0.113 which should significantly improve the performance of frequent state changes.

@bdraco More database optimisation?

I started with 1.5GB for 7 days of data with an already heavy use of excludes. Whittled that down to 850MB with a more methodical application of excludes (while still retaining pages and pages of 24hour and 7 day history graphs).

Your recent changes to the DB structure have seen that reduce to 350MB and it hasn’t finished 7 days of purging since I updated to 0.112 yet. I reckon it will bottom out around 300MB.

My database is going to disappear if you keep this up. :rofl:

I don’t understand why it took longer than 7 days (my keep data interval) to purge after the application of excludes though. Do you?

Seriously top work though. Thank you.

0.113 is focused on core performance and scalability. If you have a lot of entities and/or state changes, it should be much kinder to your cpu. The database size shouldn’t drop anymore as there isn’t much left to optimize there without some tradeoffs.

Unless you call the purge service with repack, the data is just marked as deleted instead of actually being removed from the database https://www.home-assistant.io/integrations/recorder/#service-purge

1 Like

Ah, so the nightly purge does not repack. I might try calling the service manually with repack and see what happens.

EDIT: 240MB! That’s an 84% reduction on the 1.5GB I started with.

On a related note do you know anything about influxDB?

I it possible to drop all measurements with no value?

I started out recording everything then set it to only keep a day’s data and started using includes to only record what I wanted. After a couple of days I set the keep time back to a year expecting all the extraneous measurements to be gone but they’re still there, they just have no values.

I came across this post about a month ago on reddit. I haven’t done it, others have with success. I guess I was just waiting for it to be built into the MariaDB add-on itself as it is mentioned by someone in one of the replies.

Thanks guys. I think I’m just going to wait for version 0.113.

I’m now running 113, but I’m still seeing the same behavior OP reported with loads of log entries about “Update of XXXX is taking over 10 seconds” along with the UI being totally unresponsive. When it’s in this state, some of my automations will continue to run, but definitely not all of them. This is all I can get from the UI:

I’m seeing the UI go unresponsive usually about once every 8 hours. When it happens, it’s always accompanied by the “Update is taking over…” log entries. I’m currently running MariaDB for recorder and have excluded quite a few entities in hopes that it might have some effect, but nothing has helped.

My database size is currently at ~250 MB, which seems reasonable to me for 7 days of data. I’m not sure that, at least for me, the unresponsiveness is related to the log database.

My bigger question is what can be done to make this state impossible? If there’s an issue with logging (or anything else), the UI should still respond and tell us that there’s a problem. The behavior I’m seeing is that the UI goes unresponsive and there’s no error directly pointing to any reason why that’s the case.

Regardless of the underlying cause, that shouldn’t be possible.

Hi Ryan, I’m not convinced the issue I reported in the OP is related to the database. I’m not certain that the time-out errors in my log is related either. I opened a Github issue on this. If you think your issue is related to mine, feel free to also post here:

Github Issue:
A lot of “is taking over 10 seconds” and “took longer than the scheduled update
interval” errors

1 Like

Thanks for letting me know! I’ll go add my $.02 there too. I really hope that this is something that gets figured out. I’ve seen a bunch of different posts describing basically this same problem - unresponsive UI, etc.

No matter the root cause, the condition should be something that can be detected and, at very least, trigger an alert of some kind so that it can be responded to. But really, there shouldn’t be an error condition that can cause the UI to just stop responding.