Best way to prevent HA from slowing to a grind?

Hi all,

I’ve been having some issues with HA, which seem to get progressively worse with newer versions and also with more devices added.

My HA (On Pi3) is showing a few issues:

  • is very slow to pull any UI metadata, in particular graphs
  • the Pi green activity light is permanently lit
  • the home-assistant_v2.db file grows expodentially (currently at 3GB)

I have tried:

  • deleting the home-assistant_v2.db file
  • in configuration.yaml setting recorder: to purge_days: 14

I seem to only get temporary relief and the within a few days the file grows out of control again.
My DB file is about 20 days old at this point (3+ GB) and is unmanageable, and doesn’t seem to clean up or maintain a usable size.

Any suggestions?

One of the last posts in the thread has a walk-thru.

2 Likes

I was part of that thread and I can tell you that if your major issue is with the db, moving to MySQL or one of the other dbs mentioned will have a huge impact on performance. For me, it was night and day and I am really happy with MySQL.

But also consider that .39 introduced a number of new issues, not the least of which is a memory leak that is still being tracked down. If you are running .39 you will want to do some research on that too.

Thanks guys, very interesting reading.

@ih8gates I’m not much interested in running a cloud DB, as I’m trying to keep my HA config as local to my Pi3 as possible. Running a DB on my larger home server is an option maybe, but I’d still rather not even do that (Separation of concerns). Did you install MySQL on a Pi? Any advice on performance?

@rpitera How did you migrate to MySQL? I’m also interested in how you implemented MySQL?
The slowdown issues I’ve been getting have been prior to .39, I only updated >.37 yesterday.

I wasn’t aware of this memory leak issue, but it might explain some of the issues I’ve had over the past few months, where my running Pi would grind slower and slower, sometimes becoming completely unresponsive. I’ve been wondering if my SD card is telling me that its dying, but then I see that the UI is snappy and my Z-Wave devices are quite snappy (After the ~15 minutes it takes to wake up after rebooting).

That thread I linked to shows how to install MySQL on the same Pi as HA. No cloud, no separate machine.

I used the same steps given in the link that @ih8gates provided. It wasn’t a migrate as much as a replacement; you start from scratch when it comes to history. It works great on my Pi and I haven’t seen any impact on performance or memory from a system standpoint. From an HA stand point, it is a huge improvement.

Thanks guys, this is awesome.

I’m going to take this advice, make a snapshot of my Pi and give it a go. :smiley:

1 Like

That’s the ticket! Can’t go wrong doing that.

1 Like

Is it possible to setup mysql on a Synology NAS instead of the same Pi? I am thinking I can change this to include the IP address of Synology:

db_url: mysql://hass:********@NAS_IP/homeassistant?charset=utf8

I am trying to avoid the frequent read/write to the SD card.

I can’t see why not. A URL is a URL.

If you are using the internal RPI3 Bluetooth as a tracker (BLE as well), that can cause huge slow downs, I’ve disabled mine and am using a external dongle and the PI is much more responsive now. If you are not using BT then it won’t be an issue :slight_smile:

1 Like

I found moving my primary HA to my mac and using another instance of HA on a pi to run GPIO commands / media player and only essential automations, improved performance significantly. I have about 50+ devices and sensors and run my DSC home alarm through the mac HA through envisalink.

Thanks everyone, I made a snapshot then got MySQL installed and configured using the instructions from @ih8gates thread and modifications from @ih8gates in the same thread.

Super pleased with the performance, until I seemed to have run into the memory leak issue encountered here that @rpitera mentioned, here and all over the forums.

In the end I downgraded to 0.38.4 and everything seems to be rockin’ on just fine now.
For anyone wondering how to downgrade, just log into your user you use to upgrade:

sudo systemctl stop home-assistant.service
sudo su -s /bin/bash hass
source /srv/hass/hass_venv/bin/activate

and use:

pip3 install homeassistant==0.38.4

I’m going to leave this setup just as it is for ~2 weeks now, as that will be a good comparison for the issues I reported at the beginning of this thread. After that I’ll see about upgrading again I think. It will give me time to check if the performance degrades again also, an empty DB doesn’t tell much.

Before I delteted it, by home-assistant_v2.db file was 5GB+ :unamused:

@keithh666 I’m not using the Bluetooth on my Pi3, so I dont think that may be affecting it.

Thanks all.
Will report back my findings. :thumbsup:

1 Like

That thread also tells you how to tune sqlite and troubleshoot so you can figure out what part of HA is filling the database. My DB hovers around 100mb, though the number of things you have setup within HA is very related to the size of your DB.

Another good thread for moving to a more robust database is Installing Postgres database on Pi
I installed this and found the performance so much better. MYSQL is great but Postgres is another alternative worthy of a look.

A lot of folks here are also using Influx. I hear that Influx and Grafana are a great combination; I’ve been thinking of looking into it.

@bbrendon I’ve been looking at reducing the amount of logged data, but I haven’t gotten to tuning it yet. For the moment I’m personally going to be tuning my existing setup to see how well I can get this going with a 2-week history of everything, as that will give me an apples-to-apples comparison. :smile: after that I will start cutting away the fat :wink:

@mchinery i’ll definitely consider this in future, I’ve heard generally good things about postgres also.

Also, for anyone wondering (@keithh666 might be interested to know), there seems to be some progress in this github thread about Bluetooth causing slowdowns. The recommendation is to update your RPi firmware.
I don’t use bluetooth, but I did this and I’ve seen a boost in responsiveness.
I updated by doing this:

sudo apt-get update
sudo apt-get upgrade -y
sudo apt-get install rpi-update
sudo rpi-update

My updating looked like this:

[email protected]:~ $ sudo rpi-update
 *** Raspberry Pi firmware updater by Hexxeh, enhanced by AndrewS and Dom
 *** Performing self-update
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100 12016  100 12016    0     0   132k      0 --:--:-- --:--:-- --:--:--  134k
 *** Relaunching after update
 *** Raspberry Pi firmware updater by Hexxeh, enhanced by AndrewS and Dom
 *** We're running for the first time
 *** Backing up files (this will take a few minutes)
 *** Backing up firmware
 *** Backing up modules 4.4.50-v7+
#############################################################
WARNING: This update bumps to rpi-4.9.y linux tree
Be aware there could be compatibility issues with some drivers
Discussion here:
https://www.raspberrypi.org/forums/viewtopic.php?f=29&t=167934
##############################################################
Would you like to proceed? (y/N)
 *** Downloading specific firmware revision (this will take a few minutes)
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100   168    0   168    0     0    312      0 --:--:-- --:--:-- --:--:--   312
100 53.5M  100 53.5M    0     0   652k      0  0:01:24  0:01:24 --:--:--  750k
 *** Updating firmware
 *** Updating kernel modules
 *** depmod 4.9.13-v7+
 *** depmod 4.9.13+
 *** Updating VideoCore libraries
 *** Using HardFP libraries
 *** Updating SDK
 *** Running ldconfig
 *** Storing current firmware revision
 *** Deleting downloaded files
 *** Syncing changes to disk
 *** If no errors appeared, your firmware was successfully updated to d8803bc3b369b087450e636beb0cb30857e3215e
 *** A reboot is needed to activate the new firmware

do a reboot after:

sudo reboot now

Seemed worth it to me. :thumbsup: :smile:

Wanted to report back after my 2+ weeks of testing.

Everything works great still after:

  • Updating Pi firmware
  • Installing MySQL

I haven’t optimised my logging, just a straight apples-to-apples test.

Special thanks to @ih8gates @bbrendon @rpitera

Just to note, I experienced great consistent performance running on 0.38.4 for the last few weeks, I just upgraded to 0.41.0 and seems pretty nice. The odd errors in my logs are gone, replaced with odd Python Traceback errors, but otherwise seems to be operating fine. :thumbsup:

2 Likes