Ok, that time it only took about 2 minutes or so, which is great. Before this, I literally went out in the garage with my son, cut 4 4x4 blocks, routed the edges, drilled a 1" wide quarter inch deep hole in each board to display his baseballs, came back in and it was still loading. Two minutes is wonderful. Is there any type of cleanup script that I can run to clean out the database and rotate the logs on a regular basis? And how often would that need to be?
You could create a script that deletes these 3 files. You loose and historic sensor data when deleting these databases though. So use only if you don’t mind doing that.
To purge the database:
recorder:
purge_days: 2Also, you may want to switch to mysql. It took like 10 minutes for me to set it up and restarts and page loads are a good bit faster. Before it would take graphs about 30 seconds to load and now it is almost instant.
2 Likes
Do you have any instructions for doing that?
Install MySQL DB
$ sudo apt-get update && sudo apt-get upgrade
$ sudo apt-get install mysql-server && sudo apt-get install mysql-client
$ sudo apt-get install libmysqlclient-dev
$ sudo apt-get install python-dev python3-dev
$ mysql -uroot -p
$ CREATE DATABASE dbname;
$ CREATE USER ‘dbuser’@‘localhost’ IDENTIFIED BY ‘password’;
$ GRANT ALL PRIVILEGES ON dbname.* TO ‘dbuser’@‘localhost’;
$ FLUSH PRIVILEGES;
Test if user works:
$ mysql -udbuser dbname -p
Switch to HASS env
$ ssh pi@your_raspberry_pi_ip
$ sudo su -s /bin/bash hass
$ source /srv/hass/hass_venv/bin/activate
$ pip3 install --upgrade mysqlclient
Add to configuration.yaml
recorder:
db_url: mysql://dbuser:password@localhost/dbname
2 Likes
Thanks,
Is the netdisco.service part of HA, or just used by HA. When I try to restart HA from inside the web page, the log keeps showing that netdisco.service is continually scanning every few minutes. does it need to shut down as part of the restart?
@kenshin Could you add this to the documentation: https://home-assistant.io/components/recorder/#mysql ?
Thanks for the post. on mysql. My restarts were good and I had skipped 34 but updated today, wanted to play with speak. My restarts went from 20 seconds to 5 - 10 minutes.
I am running on the HASSbian image. Installed the database and restarts improved
How would I use secrets to obscure the database credentials?
I tried to use
db_url: mysql://!secret dbser:!secret dbpassword @!secret dblhost/!secret dbname
with the entries in the secrets.yaml
dbuser: ha_user
dbpassword: ChangeM3
dbname: ha
but I get
16-12-19 13:28:36 homeassistant.components.recorder: Error during connection setup: (_mysql_exceptions.OperationalError) (2005, “Unknown MySQL server host ‘!secret dbhost’ (110)”)
this works
db_url: mysql://ha_user:ChangeM3@localhost/ha
has anyone used secrets with the db connect string
Put all of the url in one secret: https://github.com/Danielhiversen/home-assistant_config/blob/master/configuration.yaml#L29
cool, thanks!
It’s still not working right. If I call the homeassistant restart service, HA never restarts (or at least it takes a REALLY long time). But if I go out and do a systemctl restart home-assistant@hass, it takes less than a minute to restart.
I can confirm that switching to MySQL using the instructions given by kenshin works. My restarts were really slow (used 0.34.4) and switching to MySQL made it a whole lot faster. After updating to 0.35.2 still as fast as before updating.
Also put purge_days on 2 (so I can keep it clean).
This was after switching to MySql. As I said, it’s nice and fast if restarted from the command prompt. But if restarting from the HA web page, it’s very slow. What information can I provide to help in resolving this?
I too have this problem, i believe it started with an upgrade to 34.5 for me, I’m currently on 35.2 and as of yesterday i was still having the issue. I haven’t tried MySQL yet and unfortunately I’m currently at work but I just wanted to add myself to the list.
Same issue, but perhaps not as patient to wait 10 mins so I don’t know if it will eventually boot, … I’ve been restarting via SSH. Occurs with both default recorder option and postgresql.
Not sure if related but in tracing this failure to boot problem I found if I include - platform: cast in my media players Hass won’t boot at all. Taking it out seems to fix it but half my chromecast players are not showing up any more.
looking at the syslog
This is what I get when I do a sudo systemctl restart home-assistant@hass
Dec 20 15:20:30 hass systemd[1]: Stopping Home Assistant...
Dec 20 15:20:30 hass hass[10219]: #033[32m16-12-20 15:20:30 INFO (MainThread) [homeassistant.core] Bus:Handling <Event homeassistant_stop[L]>#033[0m
Dec 20 15:20:30 hass hass[10219]: #033[32m16-12-20 15:20:30 INFO (Thread-8) [pubnubsubhandler] PubNub unsubscribing#033[0m
Dec 20 15:20:30 hass hass[10219]: #033[32m16-12-20 15:20:30 INFO (Thread-4) [homeassistant.components.zwave] Stopping ZWave network.#033[0m
Dec 20 15:20:30 hass hass[10219]: #033[32m16-12-20 15:20:30 INFO (Thread-4) [openzwave] Stop Openzwave network.#033[0m
Dec 20 15:20:30 hass hass[10219]: #033[32m16-12-20 15:20:30 INFO (Thread-4) [openzwave] ZWave configuration written to user directory.#033[0m
Dec 20 15:20:30 hass hass[10219]: #033[32m16-12-20 15:20:30 INFO (EndpointThread-Leave-0) [pubnubsubhandler] PubNub disconnected (lost internet?)#033[0m
Dec 20 15:21:08 hass hass[10219]: #033[32m16-12-20 15:21:08 INFO (Thread-13) [netdisco.service] Scanning#033[0m
Dec 20 15:21:40 hass named[602]: error (network unreachable) resolving 'ntp-g7g.amazon.com/A/IN': 2001:502:f3ff::1#53
Dec 20 15:21:40 hass rsyslogd-2007: action 'action 17' suspended, next retry is Tue Dec 20 15:23:10 2016 [try http://www.rsyslog.com/e/2007 ]
Dec 20 15:22:00 hass systemd[1]: [email protected] stop-sigterm timed out. Killing.
Dec 20 15:22:00 hass systemd[1]: [email protected]: main process exited, code=killed, status=9/KILL
Dec 20 15:22:00 hass systemd[1]: Unit [email protected] entered failed state.
Dec 20 15:22:00 hass systemd[1]: Starting Home Assistant...
Dec 20 15:22:00 hass systemd[1]: Started Home Assistant.
Notice at 15:22:00 the shutdown seems to timeout and just get killed. This doesn’t happen when doing it through the webpage.
This is what I get when I use the restart service through the web page
Dec 20 15:28:17 hass hass[10547]: #033[32m16-12-20 15:28:17 INFO (Thread-9) [pubnubsubhandler] PubNub unsubscribing#033[0m
Dec 20 15:28:17 hass hass[10547]: #033[32m16-12-20 15:28:17 INFO (Thread-6) [homeassistant.components.zwave] Stopping ZWave network.#033[0m
Dec 20 15:28:17 hass hass[10547]: #033[32m16-12-20 15:28:17 INFO (Thread-6) [openzwave] Stop Openzwave network.#033[0m
Dec 20 15:28:17 hass hass[10547]: #033[32m16-12-20 15:28:17 INFO (Thread-6) [openzwave] ZWave configuration written to user directory.#033[0m
Dec 20 15:28:17 hass hass[10547]: #033[32m16-12-20 15:28:17 INFO (EndpointThread-Leave-0) [pubnubsubhandler] PubNub disconnected (lost internet?)#033[0m
Dec 20 15:30:32 hass hass[10547]: #033[32m16-12-20 15:30:32 INFO (Thread-13) [netdisco.service] Scanning#033[0m
Dec 20 15:30:32 hass rsyslogd-2007: action 'action 17' suspended, next retry is Tue Dec 20 15:32:02 2016 [try http://www.rsyslog.com/e/2007 ]
Dec 20 15:32:17 hass named[602]: error (network unreachable) resolving './NS/IN': 2001:503:ba3e::2:30#53
Dec 20 15:32:17 hass named[602]: error (network unreachable) resolving 'google.com/DS/IN': 2001:503:ba3e::2:30#53
Dec 20 15:32:17 hass rsyslogd-2007: action 'action 17' suspended, next retry is Tue Dec 20 15:33:47 2016 [try http://www.rsyslog.com/e/2007 ]
Dec 20 15:32:40 hass named[602]: error (network unreachable) resolving './NS/IN': 2001:7fe::53#53
Dec 20 15:32:40 hass named[602]: error (network unreachable) resolving './NS/IN': 2001:503:c27::2:30#53
Dec 20 15:32:40 hass named[602]: error (network unreachable) resolving 'ndmdhs.com/DS/IN': 2001:7fe::53#53
Dec 20 15:32:40 hass named[602]: error (network unreachable) resolving 'ndmdhs.com/DS/IN': 2001:503:c27::2:30#53Seems to be a bug, that you should report here: https://github.com/home-assistant/home-assistant/issues/new
I also have to wait for a time-out by shutdown…
Using command sudo systemctl restart home-assistant@hass
Dec 21 16:34:04 raspberrypi systemd[1]: Stopping Home Assistant...
Dec 21 16:34:04 raspberrypi hass[31143]: 16-12-21 16:34:04 INFO (MainThread)[homeassistant.core] Bus:Handling <Event homeassistant_stop[L]>
Dec 21 16:35:34 raspberrypi systemd[1]: [email protected] stop-sigterm timed out. Killing.