R u use repack: true?
No, ‘repack’ is not something I have every used. I am under the impression that postgresql has it’s own clean up routines that run without me having to do anything. AutoVacuum I think it is called, this is different than the manual Vacuum in postgresql.
Yes exactly. Actually, why I created the topic, because it seemed to me that the automatic postgres cleanup stopped working as before. Okay, I continue to monitor it.
Since around version 2021.5.x I am seeing the recorder purge fail to remove old records. I am currently on 2021.6.6 and problem persists.
# configuration.yaml
# database
recorder:
purge_keep_days: 65
db_url: !secret postgres_url
auto_purge: true
# auto_purge: false
this query appears to be constantly repeating
SELECT events.event_id AS events_event_id
FROM events
WHERE events.time_fired < '2021-04-19T15:51:20.303441+00:00'::timestamptz
LIMIT 1000
SELECT events.event_id AS events_event_id
FROM events
WHERE events.time_fired < '2021-04-19T15:52:53.819384+00:00'::timestamptz
LIMIT 1000
this is the home-assistant.log with
debug turned on for recorder purge, these messages are constantly repeating since 4:12 am
2021-06-23 08:55:56 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging hasn't fully completed yet
2021-06-23 08:55:56 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging states and events before target 2021-04-19 15:55:56+00:00
2021-06-23 08:56:05 DEBUG (Recorder) [homeassistant.components.recorder.purge] Selected 84 event ids to remove
2021-06-23 08:56:05 DEBUG (Recorder) [homeassistant.components.recorder.purge] Selected 84 state ids to remove
2021-06-23 08:56:05 DEBUG (Recorder) [homeassistant.components.recorder.purge] Updated 84 states to remove old_state_id
2021-06-23 08:56:05 DEBUG (Recorder) [homeassistant.components.recorder.purge] Deleted 84 states
2021-06-23 08:56:05 DEBUG (Recorder) [homeassistant.components.recorder.purge] Deleted 84 events
2021-06-23 08:56:05 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging hasn't fully completed yet
2021-06-23 08:56:05 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging states and events before target 2021-04-19 15:56:05+00:00
2021-06-23 08:56:13 DEBUG (Recorder) [homeassistant.components.recorder.purge] Selected 101 event ids to remove
2021-06-23 08:56:13 DEBUG (Recorder) [homeassistant.components.recorder.purge] Selected 101 state ids to remove
2021-06-23 08:56:13 DEBUG (Recorder) [homeassistant.components.recorder.purge] Updated 101 states to remove old_state_id
2021-06-23 08:56:13 DEBUG (Recorder) [homeassistant.components.recorder.purge] Deleted 101 states
2021-06-23 08:56:13 DEBUG (Recorder) [homeassistant.components.recorder.purge] Deleted 101 events
2021-06-23 08:56:13 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging hasn't fully completed yet
2021-06-23 08:56:13 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging states and events before target 2021-04-19 15:56:13+00:00
2021-06-23 08:56:22 DEBUG (Recorder) [homeassistant.components.recorder.purge] Selected 78 event ids to remove
2021-06-23 08:56:22 DEBUG (Recorder) [homeassistant.components.recorder.purge] Selected 78 state ids to remove
2021-06-23 08:56:22 DEBUG (Recorder) [homeassistant.components.recorder.purge] Updated 78 states to remove old_state_id
2021-06-23 08:56:22 DEBUG (Recorder) [homeassistant.components.recorder.purge] Deleted 78 states
2021-06-23 08:56:22 DEBUG (Recorder) [homeassistant.components.recorder.purge] Deleted 78 events
2021-06-23 08:56:22 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging hasn't fully completed yet
2021-06-23 08:56:22 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging states and events before target 2021-04-19 15:56:22+00:00
2021-06-23 08:56:30 DEBUG (Recorder) [homeassistant.components.recorder.purge] Selected 93 event ids to remove
2021-06-23 08:56:30 DEBUG (Recorder) [homeassistant.components.recorder.purge] Selected 93 state ids to remove
2021-06-23 08:56:30 DEBUG (Recorder) [homeassistant.components.recorder.purge] Updated 93 states to remove old_state_id
2021-06-23 08:56:30 DEBUG (Recorder) [homeassistant.components.recorder.purge] Deleted 93 states
2021-06-23 08:56:30 DEBUG (Recorder) [homeassistant.components.recorder.purge] Deleted 93 events
2021-06-23 08:56:30 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging hasn't fully completed yet
2021-06-23 08:56:30 DEBUG (Recorder) [homeassistant.components.recorder.purge] Purging states and events before target 2021-04-19 15:56:30+00:00
Well, nothing new at all. I have put automation on some weekdays for db purge. Seems worked time to time…
Hello @SoulAssassin . Wondered if you were still having the issue? Sounds like it. Well, now I have joined you it looks like. Unfortunately, I can not pin down when this started to occur for my recorder database. My database history length and number of records that I am adding per day does seem to have ‘pushed me over the edge’
Curious if you know about how many records per day your home assistant system adds? Thx.
So from what you said in your reply, you never got the nightly purge to work with your Postgresql setup?
As u see, at 4:00 it was someting like an auto purge in postgres. But the db has grown a little in size, but one more interesting point, the last access/record in the database was 2 hours ago. This is how the database lives its own life. I have 4 days of db data storage, and sometimes when the automation works on Monday/Friday, it happens that it kills 300-500 mb. In general, something like that.
Btw, last access was at today’s os upgrade 6.1, I mean at boot 
'bout how many records per day my home assistant system adds - I think 150-200 mb sould be, but, again, my db is not growing right now, and I don’t know, wtf is going on… after 5 hours, the records possible to growing again, and stop again after 4:00 for example.
I opened a bug report on the issue that I am seeing with recorder purge, I am not sure if what I am seeing has same root cause as you issue, but have a look at my report below. I have gotten the experts to have a look at the issue and they have generated some changes to the purge code. I am going to try this new code today and see what happens.
1 Like
Here is todays history.
Hey @SoulAssassin ,
I’ve been testing the proposed change to the recorder purge service. It appears to fix the issue that I ran across, the constant deleting of events and states records. I’m not sure how or if this will effect the issue you are seeing.
Additionally, there appears to be a memory leak during the purge operations. I think this issue has been in the recorder purge code longer than the recent issue I found, the new issue just caused the memory leak to grow at a much faster rate. Even with the current fix, I think my install starts to be marginally stable after maybe 14 to 20 days due to this memory leak. I remember the days long ago when my Home Assistant server showed uptime on the order of 90 days…
Here is hoping for progress on your issue!
1 Like