Big Database, info on how to reduce size needed

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.

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.

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

Yes, they are independent.

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.

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.

1 Like