Big Database, info on how to reduce size needed

[Rigor_M (J-F)]

Hi all,

i’ve setup HA with a MySQL backend and for now, the size is 21GB. I have the config setup with this for now :

recorder:
  purge_keep_days: 60

mainly, the states and state_attributes tables are out of control.

Any tricks I can use to reduce the database size?

Thanks.

[tom_l]

How to reduce your database size and extend the life of your SD card

Preamble: @tom_l is the original author of this community guide. He proposed an analytical approach to decide for each entity_id whether to include or exclude it from the underlying database. @mbuscher suggested to go after the entities most often updated in the database, and exclude only those. Both approaches are now included in this community guide. Target audience: anyone who uses an installation on flash memory like a Raspberry Pi with an SD card or an SSD drive, or anyone who wishes to re…

But the biggest change you could make would be if you only kept 7 days of data. That would bring it under 2.5GB.

If you want to keep data for longer than that use InfluxDB.

[Rigor_M (J-F)]

is there a way to use Mysql and InfluxDB at the same time ?

[tom_l]

Yes, they are independent.

[CaptTom (Tom)]

You can delete everything in the statistics_… tables if you don’t use statistics. Apparently those are never purged. Not a huge gain, but every little bit helps.

The biggest bang for the buck is to ruthlessly exclude every entity you don’t need history for, as described in the thread linked above.

But most people find some things they need to keep longer than 7 days (or whatever they set it to.) It’s been an ongoing frustration for many here. Feel free to add your vote to this FR, if you think this is a good idea. Meanwhile, I’ve resorted to saving run-time statistics about my heating system to a text file, and just keep those forever.

[Rigor_M (J-F)]

yeah… I see… mainly, i’d like to keep my energy & device_tracker stats… those are useful to me… the switch states… less…

I need to find a way to put the important stats into influxdb (long terms data) and the rest into mysql where i can shrink the table size with a less meaningful timeline.