After tinkering with HA for a few years, I’m finally starting to try to figure out how to make sure I’m only capturing data in MySQL that I care about.
I have a 180 day purge, but honestly, I’d like to permanently preserve some of that data but not choke the database.
From a performance standpoint, is there any reason to be concerned about the size of the MySQL database if disk space is not a concern?
Yes, Home Assistant will gradually slow down as the database size increases. Primarily because HA treats an SQL database as somewhat halfway between a flat file and a NoSQL Document database. Storing the full entity ID in the form of a string, the state, and a glob of JSON full of attributes for every state change. It is a massively inefficient way of storing data and requires a lot of work to perform queries upon due to the string comparisons and parsing the attributes. The database is roughly 20x the size it should be.
Lately a lot of changes have been made to try and speed things up which have help immensely. Fundamentally however, the database schema is flawed and needs to be rewritten (as well as the code to access it).
Permanently preserving data is another issue entirely however. I gather there is an Influx DB integration which can move some data to an Influx DB for long term. It does mean running another DB of course. And still relies on the recorder. The other option would be to write a stored procedure in MySQL and trigger upon INSERT to add the data to another table.
Hmm…well, I could probably write a script and fire it off with a cron job on my Ubuntu box to move data daily, managing it completely outside of Home Assistant, then use some sort of reporting tool to analyze older data, and keep the live data to a minimum in the actual HA database.
I’m not a database expert by any means…but I can screw things up really easily by playing around with it ;).