OK, yes so it wil just purge, like it’s todays purge … and you wont notice much, because you still have 10 days
I will try to call the service with 2 days interval in several days, when DB size will be constant (with 10 days interval). Without repack, just to see if a file size decreased.
So, this service (repack) will not help to remove unneeded data added due to a bug?
If you observed this increase over the last 10 days and your purge_keep_days is set to 10 days then it makes sense that there’s no change in DB size. As the doc on that service says:
Call the service
recorder.purgeto start a purge task which deletes events and states older than x days, according tokeep_daysservice data
The new data is obviously less then 10 days old since you’ve observed the increase over 10 days. So it won’t get purged, its not older then the threshold.
Btw, do you have auto_purge and auto_repack set to true? That is the default so if haven’t set them to false then that’s what they are. If so you should rarely expect a significant change calling this service unless you change the keep_days in the service call. Your DB is already purged of data older then purge_keep_days and repacked every night at 4AM already.
1 Like
So that’s what it’s doing at this hour ? , i have sometimes wondered WTH it’s doing early in the morning … 1 time i was about to open a Topic 
Here is my recorder.yaml:
recorder:
auto_purge: true
purge_keep_days: 10
…
If no bug occurs:
- db file is deleted; HA started, new db file is created;
- db file size is increasing;
- after 10 days the db file size stops increasing due to that “04:00 AM” call.
Right?
But - here I described my case.
Db file size is not supposed to increase significantly after 10 days when there is no changes in HA. But it is rapidly increasing largely.
Ok, this could happen due to some integration, I do not know. And this is not a thread to discuss a possible reason of this bug, I think.
But - I believed that the “purge+repack” service could remove that “wrongly added” data.
So far I had to delete the whole db file. Normally the file size in not bigger than ~4GB; but last time due to that bug it was ~6GB, before it was ~15GB…
And as I said - tried to call this service with repack and got no visible changes…
Yes true, anything/many-thing could cause it to suddenly grow, an “ha-update” a “device/lovelace-card/integration-update” , some manual made script/automation/etc etc.change in same …
Whatever you can imagine that would cause an i.e “event” or i.e “State” Change !
“Events And States” changes is the out-most biggest reasons for the DB to increase, so a wrong automation/script etc that causes millions of “New entries” in event/state table, maybe even cause of a chain-reaction in “state-changes” “event” etc.
So to your question, Yes you can use the sqlite-addon ( beside the usual logfiles ) to figure out if it’s a specific integration/device/entities which causes a sudden increase ( and actually even delete entries in sqlite, but also with purge by "Domän/Area/Entities )
Check this out ( and above this ) and also The Purge in Docs.
EDIT: If you choose to delete via “command” you can also after this run “Repack” to reduce the DB-file, instantly, othervice it will/should go back to “real-normal-size”
This is HA in container; there are no automatically installed updates.
All my automations do not create new data, they may only change input_boolean helpers.
I suspect only one thing: when a huge CPU load occurs (python, as it is reported by “top”), the HA is unable to process data properly, so DB is flooded by some trash. For testing, I switched off almost all my integrations (Life360, Traccar, Companion app, Met.no weather, netdata, open hardware monitor, asuswrt, xiaomi, …), but still observe this huge CPU load sometimes. Reinstalling Debian does not help.
I will read it, thank you!
But my initial question was - may I use the “purge” service to remove this wrongly added data ; I did not mean using SQL which I really do not know)))
Yes you can , if you know which entities/ or domän / or area , unfortunately it’s not as easy to do, if you don’t know what was filling up the DB, and here sqlite-addon is a good “companion” to penetrate the event/state table ( removing newest entries etc.)
With purge you should also be able to Purge specific, but i’ve never tried with a “list” of entities , only individual entities and Domäns … i purged 1 time ALL by Domäns, i.e. Light/Switches/Device_Trackers etc etc. 1 by 1, with purge-interval “Lower than default”
But i guess im done with that part , i have restricted Recoder to “include” , so i just have to include what ever i want it to record
As I understood, if some entity (-ies) is specified, then the service will purge only this particular entity (means - remove all records which are older than “today - purge_interval”).
This is not what I need; I think that DB is supposed to contain only entities which are “allowed to be recorded” in the Recorder’s settings (I myself use only “exclude” options). So, since I need these “allowed” entities’ data - I am not supposed to delete them.
What I need is to get rid of a trash added due to a bug, wrote about it many times. I do not think that this trash is some “allowed” entity’s data - in this case this trash should be deleted automatically on 04:00 AM, which does not happen.
Your database will only change size on repacks, which by default occurs on the 2nd sunday of each month.
As I said - I tried to call the “purge” service with the “repack” option , no results.
repack takes a long time, it’s not quick at all
The recorder.purge service does not look at your includes and excludes. As documented, this is all it does:
Call the service
recorder.purgeto start a purge task which deletes events and states older than x days, according tokeep_daysservice data.
So it does not matter what is in your include and exclude config. Any rows older then 10 days will be purged, any rows less then or equal to 10 days will not be purged.
If you want to purge data from a specific entity you believe has been wrongfully recorded, you need to use the recorder.purge_entities service. And then you need to specify which entity IDs, domains and/or entity globs to purge. Note that this service does not default to your include and exclude config, it also does not look at that. You must specify some entities to purge and it will purge only those.
The only part of recorder that looks at your include and exclude config is the live-recording aspect. As in when an event comes in it looks at your config to see whether that event should be recorded or not. Once an event is in the DB, it is not purged unless it is older then keep_days or you specifically tell recorder to purge its data via the recorder.purge_entities service.
This is why I was saying before if you are just running it with the same value for keep_days its really not doing much. You are already purging all data older then 10 days and repacking the DB every night at 4AM. Running the purge service again that same day won’t really have much work to do.
1 Like
I see no changes even after several hours.
Are there any visible signs of “repack is in progress”?
Thanks again for a detailed answer.
Unfortunately, it does not help me.
Assume my “normal” file size is 4GB, suddenly it becomes 15GB. Means - some data are recorded.
What are these data related to?
Assume these data are for some entity which was NOT allowed to be recorded. I am 99% sure that this this assumption is wrong: all entities excluded from Recorder cannot produce this rapid and huge increase.
Then assume these data are some “buggy” data; I am not an expert in DB at all, but think that a crash in a db server may cause recording some trash.
So, why I am telling about my problems HERE? Because I believed that the “repack” function may clean DB - remove all trash and leave only needed data. My tests with calling the “purge+repack” service did not succeed, DB size was not restored back to the “normal” size.
I repeat: my posting here is mainly not about “why DB size is increased and what this trash is”, it is about “may I use purge+repack to remove this trash”.
Answering the 1st question is a very complex thing, this is beyond a scope of this thread. Surely I need to eliminate a reason of this “increasing”, but now I just want to learn how to “fix” a DB.
What evidence do you have for this? This seems impossible to me. HA is what tells the DB to add something. If it crashed, what would tell the DB to add a row? There’s essentially only 3 things recorded in the DB from HA:
- Events fired over the bus (exception:
state_changedevents since state changes are different) - Changes in state for entities (including attributes)
- Statistics for entities with numeric states
Almost certainly if your DB is increasingly rapidly you have some integration that is rapid firing state changes at HA. See here for some tips on finding those entities.
That’s not how this works. Repack isn’t an HA thing, its a native database thing. It does not remove any actual user data. That would be a major, major issue because admins do repacks on production databases regularly. It essentially does a clean up, re-organizing data on disk to optimize indices and layout and such. It also does admin things like cleaning up the transaction table (which may remove some data but not user data, internal data). Among other stuff, I don’t really know all it does, I’m not a db expert either.
But the point is it is something HA is triggering in the DB, it is not a process written by HA. Therefore it has absolutely no knowledge of what you have included and excluded in your recorder config, it doesn’t even know what is in the tables. Everything is just bytes to it, it would never delete your stuff.
1 Like
This is a very clear explanation what the repack is. It is like “HDD defragmentation does not delete files, it rearranges them”.
So, my guess about using repack to remove wrongly added data is wrong.
Thanks again.
As for finding a reason of my DB issue - yes, it could be a problem with some integration. As I said, once I disabled almost all integrations but still observed this issue. Will continue to find the reason…
One more thing.
Assume some integration starts recording some entity rapidly. If each record is “marked” with a current timestamp - I believe these rapidly-added data (1000000000 records per day instead of normal 100) should be purged in 10 days - which does not happen in my case, DB size is not decreasing (for instance, 2-3 weeks ago (before 2023.1) DB size suddenly grown from 4GB to 6.7GB and was same these 2-3 weeks).
Ok. I mean there might be a bug but you’d need more proof then that. You’d really need to query the db and find data in there that was recorded even though your filters said not to or was more then 11 days old (since purge runs once a day you’d expect things to live up to 24 hours over the limit until the job can run).
Fwiw my understanding is the fastest way to build up a large db is having a lot of attributes. Particularly changing data in attributes. Because then everytime an attribute changes a row is added with the value of all attributes, even the ones that didn’t change. So if you have frequently changing data in attributes or heavy use of attributes, I’d start looking there. It’s better to bump data that is changing out into its own entity as the state if possible.
2 Likes
I suggest you change your “strategy” to only use Only “Include” in your Recorder settings, by using Exclude ( as you do ) or Exclude+Include then the Recorder might/Will record something you really don’t want it to , because you can’t always “predict” what will happen in your system/configurations/settings.etc.
By only using Include, you tell Recoder to “Record only events / states / state-changes” in/from the list of entities/Domäns you specify in Recorder-Config(conf.yaml)
EDIT: Not entirely truth, HA does apparently records some “system events” regardless of Recorder-settings
By using exclude, Recorder Records everything , but not the entries/domäns you have specified in “exclude”
… And Everything is as it sounds ( you have no/less control ) , an update, whether a ha, or integration could create new entities, services etc, a script/automation/template will create events
If you know which data you want in your DB, You should use “only” Include in your Recorder-settings.
This will minimize the risk that the Recorder (for unknown reasons) fills your DB with garbage
Within last 24 hours started the “purge” service TWICE (with several hours interval):
No reaction, no db file size changes…
“Days to keep” set to “1 day” - specially to see a difference.
No corresponding records in Log.
BUT - there is no a longer history for entities in History!
An available history starts from “yesterday, 07:00” .
Seems that the History shortened to 1 day.
Then why the db file size is not changed?
Using a standard “filesize” integration; also measured by calling “ls -la”, same file size reported.
Thank you, I need to think about it.
My initial idea was “let all entities have a history by default”; but your proposal has own advantages, need to think…