Best way to prevent HA from slowing to a grind?

[plonka2000 (Kareem)]

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?

[ih8gates (Scott Reston)]

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

Large homeassistant database files

My db grew to nearly 1.7GB since installing Home Assistant just a few weeks ago. My graphs started getting incredibly slow, more than 60 seconds to load. I’ve followed these instructions from @MojaveTom more or less. Had to make some small changes to fit my install, and also I was not interested in migrating my data so I could skip that part. Here is how I did it: sudo apt-get update && sudo apt-get upgrade sudo apt-get install mysql-server && sudo apt-get install mysql-client sudo apt-get ins…

[rpitera (Robert Pitera)]

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.

[plonka2000 (Kareem)]

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).

[ih8gates (Scott Reston)]

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

[rpitera (Robert Pitera)]

@plonka2000

How did you migrate to MySQL?

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.

[plonka2000 (Kareem)]

Thanks guys, this is awesome.

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

[rpitera (Robert Pitera)]

@plonka2000

make a snapshot of my Pi

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

[arsaboo]

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.

[rpitera (Robert Pitera)]

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

[keithh666 (Keith Hull)]

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

[RobDYI]

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.

Logbook loads 4 times faster on a mac than on rasp pi2

I was unhappy with the load time for the log book on my HA installation on a rasp pi2 so I tried HA on a mac today. I used the same mysql database on the mac for both HA installations. I have a lot of events and set the purged to 1 day. It took 15 secs for the mac to load logbook and 1 min for the pi2. I now want to run HA http on the mac, is there any advantage to the master / slave setup?

[plonka2000 (Kareem)]

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+

@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.

[bbrendon]

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.

[mchinery (Martin Chinery)]

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.

[rpitera (Robert Pitera)]

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.

[plonka2000 (Kareem)]

@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. after that I will start cutting away the fat

@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:

pi@pi:~ $ 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.

[plonka2000 (Kareem)]

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.