Hi everyone! I wanted to share a tip that has significantly improved my Home Assistant setup, especially for those of you who might be dealing with slow logbook performance or large database sizes.
I stumbled upon a bash script that automates the process of cleaning up the database. The script runs every 10 minutes via a cron job and deletes specific event types that aren’t crucial for the logbook. This has reduced my database size by over 95% and seems to have sped up the logbook functionality.
Here’s the script I’m using:
bash
#!/bin/sh
mysql -u hass -phass -e ‘flush hosts’;
mysql -uhass -phass HOMEBASE --execute=“USE HOMEBASE”;
mysql -uhass -phass HOMEBASE --execute=“DELETE FROM events WHERE event_type = ‘service_removed’”;
… [additional DELETE commands] …
mysql -uhass -phass HOMEBASE --execute=“OPTIMIZE TABLE EVENTS;”;
I switched from MySQL to MariaDB recently, but this script works seamlessly with both. My SQL user is ‘hass’ with the password ‘hass’, but you should replace these with your own credentials.
One thing I noticed is that the deleted entries don’t seem to affect the logbook’s functionality, which is great because it means we’re not losing any important data. If you’re considering this script, I recommend starting with a smaller cron interval to monitor the impact before setting it to run regularly.
I’d love to hear if others have implemented similar solutions or have additional tips for optimizing Home Assistant’s performance. Let’s keep the conversation going and help each other improve our smart home setups! 