Im using MariaDB. I have configured the recorder to keep 7 days of data, but something has gone wrong, and purging is not working and the db is growing out of control:
10 million records in states and state_attributes. Wow.
I dont mind losing short term stats and states, but I dont want to lose my long term (energy) statistics, so Id rather not reinstall the mariadb addon. would it be ok to just empty the states and state_attributes tables?
Aha. Indeed, I was basing myself on that to think it was working (saw the numbers going down), but that might have been premature. I dont think its working. I ran this 10 minutes ago:
SELECT entity_id, COUNT(*) as count FROM states GROUP BY entity_id ORDER BY count DESC LIMIT 60;
entity_id count
sensor.telenet_modem_power 159951
sensor.telenet_modem_energy 154387
sensor.telenet_modem_device_temperature 154287
switch.telenet_modem 154278
switch.telenet_modem_power_outage_memory 154276
binary_sensor.telenet_modem_update_available 154275
So I tried purging my noisiest device, the telenet modem,
i usually run this and wait couple of hours, it takes time and patience:-)
i didn’t check the actual implementation but sure that it cannot delete all these records at once, it has to go through row by row or segment by segment because rows are connected to each other.
Well if it takes longer to delete them then it does to add more new records in the same time, its not gonna help Ill check back in an hour but I dont think its working, overall records still going up, including those for that particular device.
Is there another way? Could I back up the long term statistics table, start a new db and import those?
Doesnt help. Neither purging the whole db or a single entity, not a record gets removed when the recorder is stopped. When I try to purge the entire db, mariadb uses 100% cpu on one core (it always does that when trying to purge overnight, until I restart the addon). Trying to purge a single entity causes no cpu load. Either way, no records seem to get deleted, Ill probably bite the bullet and nuke the db. I just wish I could keep those long term stats :
Thought I might as well try emptying the tables, and yep, that worked. Everything still seems to be working, and I still have my long term stats.
Cant tell yet if the purging now works again, but at least until there is a good way to backup/restore long term stats, I can occasionally nuke all the other stuff this way if need be.
I’m facing the same issue and was also considering to simply empty the states table. I did so now and it looks like while it didn’t work for all the old data in the states table this command does work for the new data coming in: