Had a nice query to see which entities were writing the largest state changes.
…well now we need a new query to understand which entities are writing the most attributes into the new optimized table structure.
I believe this does it reasonably efficiently. (Note this is tested only on MariaDB)
select
entity_id
, b.cnt
, round(b.cnt * 100 / (select count(*) from states), 2) as "cnt_pct"
, b.bytes
, round(b.bytes * 100 / (select sum(length(shared_attrs)) from state_attributes), 2) as "bytes_pct"
from (
select entity_id, count(*) as "cnt", sum(length(shared_attrs)) as "bytes"
from ( select distinct s.entity_id, sa.attributes_id, sa.shared_attrs
from states s join state_attributes sa on sa.attributes_id = s.attributes_id
) a
group by entity_id
) b
order by bytes desc
Awesome - it’ll be cool to see the next iteration.
Integrations like UPnP/IGD that talk to wireless routers produce essentially random numbers. There’s not a whole lot of optimizations to be had there. So, queries like above will still be useful to identify the problem children to exclude from the Recorder entirely.
I was checking my database, now that I have collected a weeks’ worth of data again, and noticed that the events table is needlessly flooded with the state_changed entries.
I would like to disable tracking of state_changed in the events table, but it seems that when I disable this in the recorder integration, the actually change is also not tracked (which kind of makes sense).
Is it possible to only disable state_changed tracking in the events table? It is included in the states table anyway and causes the database to always have roughly twice the size and twice the writes it needs to have (two tables with more or less the same information).
If you don’t record the event, the logbook will no longer work. There will also be no way to historically tell which user caused an event, or which automation triggered an event since all the context ids will be missing.
The system isn’t designed to work in such a degraded state where part of the state_changed event is missing so its not possible to do what you are asking.
It is designed to give the user control. That is why you can set eveything to fatal (which I have).
It is also irrelevant who caused an event.
If, for some reason, you feel it is necessary to force the events table, then at least make it optional to track the needless state_changed. This has no usefull information. In basically all cases this is caused by the data source and not a user.
It was announced that database was optimized, yet ~50% of the entries (and hence writes) are useless writes in the event table (every state change gets one write in states and one in events, so this e.g. in my case is basically 50 %).
@bdraco: I was told I sound combative. That is not my intention and I apologize if that is the case.
Just trying to make my argument on the matter.
So, for me, the more control a user has the better and the more in line it is with the very good approach to improve the possibilities for the user to control his/her/their system.
As we are all trying to optimize our databases (for size, for write cycles or any other reason), I feel that it would be helpful if
Option 1: It were possible to deactivate writing to certain tables
Option 2: Certain features could be deactivated
Meaning, it would be awesome if we could e.g. disable state_changed in events table because it holds no raw states data. It is only a logbook of events. Many people do not need this and it is responsible for 50% of the writes and filesize (if statistics is disabled) or ~40% of writes and filesize (if statistiscs are enabled).
So in my opinion, the impact on write cycles and database size would be very, very significant.
Currently there are no plans get rid of the state_changed events in the events table as removing them would break the logbook and associated automation debugging. Its likely possible to remove them in the future with additional major refactoring (its a lot of work), but we are focusing on optimizations we can do without major breaking changes first.
Do you think it would be possible to select a different exclude filter in ‘recorder’ that would allow for filtering these out? Maybe that is the simpler approach?
At the moment the only way to prevent entries to events table is by excluding ‘event_type: state_changed’ but this will also exclude the states from states table (so the actually wanted data).
Maybe it would not be a breaking change anymore if excludes could be combined with target table?
That would break the purging data since the purge process could no longer find it in the database since it looks up the state from the linked event_id which would mean your database would grow in size forever.
So purging is not based on time stored in each table, but only the time stored in events table? Because states table also has time information, if I remember correctly.
It does have it, and the time values are the same for last_updated and time_fired for data that was recorded in the last few months (some users will still have ones that are not in sync on older data). They didn’t use to be the same but that was done to prepare to be able to optimize it in the future, we just aren’t ready to do that yet.
Self-sufficient tables were of course then a great improvement.
When was this introduced?
And it would “only” affect data that was stored ages ago as new data purges could be based on the times per table?
So no infinite growth. Only data before this change would be permanently stored.
Might it be an option to perform an automatic transfer of the time information from events to state for those old setups?
Since the links are available, an automatic update of the states table can be performed and afterwards the link can be broken. Then even old data would be complete and each table would be self-sufficient.
From memory it would be a join/merge of the two tables in a new one, then delete and rename (less direct approach to prevent data loss).
At this point we are no longer discussing the original topic and have wandered into feature request territory.
I suggest waiting for future optimizations to come down the pipe as what you are discussing here will likely mean adding more features that we would have to account for which would delay work on future optimizations.
Okay, looking forward to future improvements then
Fingers crossed you will find an easy solution. Should significantly reduce write cycles, judging by my db
I’m looking forward to all the changes! It’s great to see the Recorder database getting some developer love!
Speaking of FRs, there have been a number of them requesting variations on the theme of making it possible to retain some events and state changes longer than others, rather than have just one retention period for all (or exclude the entity altogether.) Here’s the one I submitted, but it’s a subject which comes up a lot, with a number of (good) ideas on how to implement it.
Don’t take it personally mate, most of the mods here seem to say that any time you suggest any feedback at all no matter how much you kiss up to them. I just praised the 2022.5 changes on the discord and mentioned one thing I considered unnecessary then got called “massively negative”.
I think you’ve got some very good points personally and I have noticed the sqlite database starting to grow out of control lately. @bdraco thanks for working on it! Much appreciated.
Wow, I had to look this up on discord because I was curious who you talked to and what you said. Do you think this is the best way to start a convo as your first post on discord?
TBH, it seems like you just said that to pick a fight. What do you expect?
There’s nothing to talk about, I’m just baffled that you think this is positive feedback. Nothing you said in discord was positive, you just started with a sarcastic assumption. Making your previous post a lie just to make the mods look bad. I just don’t get it. Why lie?
