Purging db isnt working. Can I empty these tables?

Configuration
1

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:

image
image961Ă—371 82.1 KB

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?

2

There is also purge entities service, it is not happening immediately but slowly, you can select some entities, domains or wild cards to purge

1 Like
3

Ah, excellent, that actually seems to be working! Does it purge all data for that entity, or only data older than “purge keep days”?

4

All is deleted, irrespective of purge days, similar to yaml based exclusion (actually same thing happens)

5

Btw, FYI, these row numbers are close enough estimations, if you click on them, you would get the actual numbers but it would take time

6

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,

image
image1137Ă—214 14.1 KB

but a few minutes later, I see more records, not less

7

or… hang on, it may be purging state_attributes first. Will check later. Edit: nope, that one is going up too

8

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.

9

Well if it takes longer to delete them then it does to add more new records in the same time, its not gonna help :slight_smile: 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?

10

You can disable the recorder first :slight_smile: and continue purging afterwards

11

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 :cry: :

12

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.

13

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:

service: recorder.purge
data:
  keep_days: 0
  repack: true
  apply_filter: true

What is your experience now a few months later?

14

Still working fine here. IIRC, I dropped the states and state_attributes table. Purging now works, and I didnt lose any long term stats.

1 Like