I recently switched the database to MariaDB and immediately saw that the database was growing like crazy. 2 million records a day almost. So I went on a mission to exclude sensors from history (recorder) and wrote a bunch of template sensors to round values to reduce the number of records written to history and then I excluded the source sensors. Database growth did not slow at all. I then learned that everything is still written to states, it’s just not in “history” and now there is nothing to manage that growth because the recorder ignores excluded sensors. I actually made the problem WORSE.
I removed all of the exclusions and deleted everything from states and states_meta to start fresh, but I’m back where I started in still looking for a way to manage database growth. I’ve disabled sensors I don’t need but that won’t make a dent in how many records are written to the database.
Solar Assistant is the worst offender. Of the 30 sensors with the most records in states, 13 of them are from Solar Assistant. There is no option (that I know of) to throttle the frequency of updates. Some sensors write a new history record every 5 seconds.
My only option seems to be to limit the number of days of history I keep to to 7 days. This is sad. I had hoped to keep a month of history.
I’m running HA on a Raspberry Pi 4 with an SSD. Is there any rule of thumb about how many records I can keep without suffering noticeable performance issues?
The default is 10, and I expect it’s at that level for a reason.
You’re aware of long-term statistics? They’re never purged and take up less space because they’re hourly. Also they’re only retained for sensors with a state_class of measurement, total or total_increasing.
That’s what I would expect but they still seem to be creating records in the state table. I put the entities in an exclude list under recorder and they stopped showing up in history. The old history was stiill there though. Is there some different way to do it where the old history is deleted?
Yes I would just expect exclude to stop recording new history.
If you want to delete old records, just either wait and they will expire naturally at the end of the retention period, or you can use the recorder.purge or recorder.purge_entities service calls.
That’s what I expected to happen but I’ve been told that once entities are not tracked they become orphaned and are not purged from the database, and that whether they’re in “history” or not, they still write state changes which are then never deleted so the problem actually becomes worse.
What I know is that I went through a lot of trouble to accomplish nothing and just made matters worse. I will now just keep as much history as I can and call it a day. This way I’ll feel confident that old records are being purged and my database isn’t accumulating a lot of cruft that will be in there forever. You’re right that I should not claim what I said as fact. I don’t have any way to know for certain. But I can say that excluding about a hundred sensors made absolutely zero difference in how fast my database grew.
Excluding sensors from recorder seems like a niche scenario that’s typically better handled in other ways. It only applies if you need the sensor but don’t need ANY history. If I don’t need the sensor, like grid frequency, I disable the sensor. If I need a day to a few days of history I purge the sensor to x days with an automation.
I don’t know what happened with my database but things seems to be straightened out now. I’m excluding some entities from the recorder and that seems to be working. I did a recorder.purge_entities service call and verified that all of the records were gone and they were. I’m purging some other entities via that same service call with keep_days = 1 and that’s working. I’ve disabled some other sensors that I don’t need at all, like grid frequency. With an all-of-the-above strategy I’m getting database growth tamed.
Again, I don’t know what got mucked up with my database, but excluding entities from the recorder is working now.