Large statistics database (1,9G, 40% of total DB)

[CaptTom (Tom)]

@tom_l

Other than you can only see back as far as your purge_keep_days setting.

…Which is exactly what I wanted.

@tom_l

There’s multi-select for that now.

I saw that in the release notes. Thank you for the reminder. Some people might want to update to 2024.10.x before trying this.

@tom_l

No one is forcing you. As advised 18 days ago:

If you were to actually read my thread, you’ll see that’s exactly what I did.

Please understand, this thread is about different ways to deal with a large amount of LTS data in the database. Mine isn’t the only way. But neither is yours. I only offered my experience for those who might be helped by it.

I never asked for LTS, and never asked that every single entity be retained. This was indeed “forced” on users, and this work-around is hardly intuitive. The great thing about HA is that we can all use it in different ways. That shouldn’t be discouraged.

[tom_l]

@CaptTom

If you were to actually read my thread, you’ll see that’s exactly what I did.

I did read it. You presented it as something you discovered rather than something you were told about over two weeks ago.

Project decisions like this are for the majority. They will keep happening. Fortunately in this case there was something you could do but do not be surprised if they keep happening. The developers have said many times they are not interested in supporting making everything configurable.

[petro]

FYI that work around will likely produce repairs for every entity in 2024.10+ 1 to 4 hours after each restart.

[Ildar_Gabdullin]

@petro

produce repairs for every entity in 2024.10+ 1 to 4 hours after each restart.

I observe these repairs only for a few entities (belonging to Traccar Server integration which is broken) out of ~30…40 entities with customized “state-class: none”.

[CaptTom (Tom)]

Thank you for the heads-up. I’ll be watching for that when I update in a few days.

If anyone has a better way to disable LTS recording, either by entity or altogether, feel free to chime in!

[Frank_R (Frank)]

Hi, please check this feature request for more options to trim LTS.
(and the issue of different options). Especially disabled entities are an issue as those will remain in LTS forever. (Pls vote )

Add FIX option for Long Term Statistics for disabled entities

For the one’s that want to clean-up manually: Use this query to find disabled entities that are still in LTS. (As freshly disabled entities are still in ‘states_meta’ for the purge-period, do this manual clean-up later) Step1- Search for disabled id’s SELECT * FROM statistics_meta WHERE statistics_meta.statistic_id NOT IN (SELECT entity_id FROM states_meta); Step 2 - Remove (BE CAREFUL, MAKE BACKUP) DELETE FROM statisticsWHEREmetadata_id IN (<< fill in some of the ID's here, comma seperate…

[CaptTom (Tom)]

@petro

FYI that work around will likely produce repairs for every entity in 2024.10+ 1 to 4 hours after each restart.

Just to follow up on this, it’s been about 8 hours now since I restarted, and so far nothing in Repairs. I’ll keep looking, but at this point the workaround seems to be exactly what I wanted.

[Ildar_Gabdullin]

See my post above, a only HAD “repairs” with sensors with state_class=none of some particular integration (out of many other sensors with customized state_class=none). Since some update there are no repairs for statistics at all.

[Frank_R (Frank)]

The 2024.11 update also came with the “fix”/feature to easy remove statistics for disabled entities. (or every entity without states This finally provides an option to clear up old/removed/changed and disabled entities. I had MANY, hundreds and hundreds… but after some cleaning if paid off

From 13M+ records to ~6M, size reduces to 900MB.
Still twice as much as the states table and more than 50% of the total 1,7GiB DB size.
Significant, but okay, this is manageable.

[CaptTom (Tom)]

@Frank_R

From 13M+ records to ~6M, size reduces to 900MB.
Still twice as much as the states table and more than 50% of the total 1,7GiB DB size.

Thank you for sharing that. It is consistent with what I’ve seen. And inconsistent with the comments dismissing the size of the statistics tables as irrelevant.

And, it’s great to see the ability to at least clean up the disabled or orphaned records.

[Frank_R (Frank)]

Yeah, you’re right. It is definitely not irrelevant and not sustainable on the long run. Especially not for larger installations. An opt-in would maybe a better approach.