So for the last few months I had been struggling with performance issue on my HaOS server. I had tried everything that I could think of as far as rearranging components and troubleshooting network, DNS, routing and everything OUTSIDE of HaOS and home assistant that could of possibly been related.
I have been using MariaDB for my recorder component and I am running on an Intel NUC with 8GB of memory on m2 SSD so all the help articles saying to recreate my sdcard for my pi and what not didn’t apply. Although I had a feeling that my DB has literally been through hell over all the years and could have possibly had some issues that were causing the delay. OK well what the hell I finally bit the bullet and dumped MariaDB and moved over to PostgreSQL/TimescaleDB like I have been planning for years. Well low and behold that was it, performance was super snappy, load times were gone. no more staring at little spinning circles I was actually getting graphs popping up right away. Oh man I almost hurt my arm patting myself on the back.
Well long story short, the excitement lasted for about three or four days and again was getting progressively slower and taking longer and longer to load as the database grew, now mind you I already had a pretty extensive recorder.yaml with a ton of exclusions so this was really odd. Again down the path of troubleshooting recorder, all my add-ons, my telegrafDB and grafana instance. Nothing worked.
So to get to the meat of this damned problem, I dug into the settings for postgres add-on and remembered setting it to auto-tune during install and that set a hard limit of 512MB for memory. Which for postgres is a bit on the low side. Bumped it to a gig and oh boy things were improving, kicked it up to 2GB, we are back in business boys!! Snappy and responsive like a fresh install. I bumped my recorder back up to 30 days and she has been going strong since. Database is hovering a bit over 5GB which is very manageable compared to my MariaDB instance that would never drop below 16GB.
All that being said it was an awesome adventure and I improved a lot of other stuff on my system trying to get to the root of this issue. But damn I did come up with the mother of all recorder.yaml files.
Having poured all through this forum, reddit and the rest of the interwebz I can’t say I had read about this particular issue with all the stuff out there on slow loading charts and graphs so I thought I would share in hopes of helping someone else out.