History stops working after a while

Configuration
1

Recently, (maybe after 0.113) my history stops working after a few <24 hours after a hass restart. It can still retrieve old history but it seems to not be injecting new history into the database (all the graphs are flat etc).

I have homeassistant connected to a postgres database where I have configured it to not clean up history. Nothing is showing up in the logs, either postgres or home assistant and I am completely stumped!

2

A restart of home assistant brings back the history saving for a while, but then it dies again. Curiously the last two nights have seen it die at about 4am.

3

It looks like there was a change to the configuration of auto_purge, and the purge job was starting and running at 4:12am. (https://www.home-assistant.io/integrations/recorder/) I assume the purge job wasn’t ever actually completing (it was set to keep a lot of history and the database is probably large).

4

I have exactely the same issue here. (after upgrading to 0.112 or 0.113)

Flat graph after 4am. Come back to life after a restart then stop again.

Do you find a work around?

5

Me too… I’m using MariaDB running on Synology NAs and for 1 month everything was flawles… but then after day 31 (what a coincidence with my purge history parameter) I started to have serious issues with displaying history and very much delayed writing of events to history. Upon checking of NAS performance I found that around every 4AM it starts to be extremely loaded with task on MariaDB (thousands of IOps). Obviously HA restart helps, as it interrupts purge process… but also as side effect causes that old records are not deleted from DB. It ended up with resetting the DB and starting fresh with limited number of sensors and history shortened to 7 instead of 31 days. So bye bye to my all history. Yet 4 days to see how purge will work in this new circumstances.
BTW, hard to say as it was time coindicence; was the issue cased by 0.113.x or was it introduced earlier (new DB schema for optimized performance), but I just reached limit of history to start purge.

6

Try this in the developer tools / services menu (including the service data):

Screenshot_2020-08-09 Developer Tools - Home Assistant

The nightly purges at 4am will not delete the data marked as deleted by the recent database optimisation. Performing a repack will.

7

thanks! Before your answer, I deleted my MariaDB database and the automatic db rebuild seems to solve the issue. History graph was still alive this morning :slight_smile: If it happend again, I will try the repack option.

8

I am on the latest versions
core-2021.3.4
supervisor-2021.03.6
Home Assistant OS 5.12
using the default db
and this flat lining is happening. A repack fixes the problem for a short while and then the problem starts again.
My system has been working flawlessly for over 2 years and this problem just started in the last week or so. Don’t know if one of the updates has caused it or something else. There are no errors in the log file.

Edit. This happens with either the default DB or MariaDB.
Edit2: Yes, I tried deleting the DB so that it was built from scratch in case it was corrupted but still have the problem. Did this several times.

9

Same issue here but with Postgres backend. Tried deleting db, downgrading the Postgres version to 11. Still no luck.

Could this be a transaction on the database hanging? I know I’ve seen that once or twice while debugging this issue.

10

There appears to be some sensor that was causing the problem for me. I’m not sure which one as I fixed the problem by excluding the sensor domain in recorder and enabling, individually only those sensors entities that I really needed in the history. The added benefit is that now by database file is only 50MB.
Hope that helps.

1 Like
11

In my case, it seems to be related to an input_text value.

See also: Home assistant hangs after few hours - #30 by blaet

12

Hi, how do you delete the MariaDB database?

13

I suddenly started to have the same issue a week ago.
At 4am, HA CPU usage goes to 45%, the RAM usage jumps from 2.5 to 4GB and the history stops being recorded.
I am using VM on Proxmox with HAOS installed, 4 CPUs and 8GB of RAM.
After rebooting the HA machine, everything is back to normal.
Seems to me this problem started with 2024.7.1 again…

Core 2024.7.1
Supervisor 2024.06.2
Operating System 12.4
Frontend 20240705.0
14

Update - it seems to be known issue, just when I was about to report it, I found this:

15

And we already have a thread for that, too: