core: Database integrity check takes too long on older hardware

Since 0.114.0 (and also in 0.114.1), the problem was there. On a Raspberry Pi 3B, recorder is taking almost 30 minutes to start. My DB is (only) 785Mb I had to change the value of SLOW_SETUP_MAX_WAIT from 300 to 1800 to accommodate. The db is on a network mounted disk so I have a failover system that can take over with the same db in case of failure.

Output of sqlite3 commands to time the “pragma quick_check” operation:

(homeassistant) homeassistant@hassbian:~/.homeassistant $ sqlite3 home-assistant_v2.db
SQLite version 3.27.2 2019-02-25 16:06:06
Enter ".help" for usage hints.
sqlite> .timer on
sqlite> pragma quick_check;
ok
Run Time: real 1711.354 user 3.275670 sys 16.711496
sqlite>

In 0.114.2, the issue is “solved” by freezing the timeout but then my HA is taking 28 minutes to start when it was less than 2 minutes prior to 0.114.0.

So, for me, this is not a solution, at most a workaround.

Why not adding a parameter to recorder?

recorder:
  quick_check: false

Yes? No? Maybe?

P.S.: Events table contains around 200,000 records P.P.S.: I’ve replaced “PRAGMA QUICK_CHECK” with “SELECT COUNT(*) FROM EVENTS” in setup.py. It is my own workaround waiting for a definitive fix of the issue.

About this issue

Original URL
State: closed
Created 4 years ago
Comments: 25 (18 by maintainers)

Most upvoted comments

If your instance is up and running it will return null. As soon as you shutdown cleanly we write the shutdown time to the end column. If the power cord gets pulled or we have a hard crash, the field isn’t populated and we know something went wrong so we should check the database.

That makes sense

Row in the table after a clean stop:

run_id	start				end				closed_incorrect	created
14	2020-08-17 18:28:19.029862	2020-08-17 18:51:30.766136	0			2020-08-17 18:47:11.152188

Here is the result of the following startup with the given code:

Aug 17 20:52:39 hassbian systemd[1]: Started Home Assistant.
Aug 17 20:52:45 hassbian hass[785]: 2020-08-17 20:52:45 WARNING (MainThread) [homeassistant.loader] You are using a custom integration for hacs which has not been tested by Home Assistant. This component might cause stability problems, be sure to disable it if you experience issues with Home Assistant.
Aug 17 20:53:04 hassbian hass[785]: 2020-08-17 20:53:04 WARNING (MainThread) [homeassistant.components.binary_sensor] Setup of binary_sensor platform ping is taking over 10 seconds.

Please note that time in the table and in the log are shifted by 2 hours (UTC+2 in Belgium)

And … no issue with recorder anymore (on clean start/restart). 👍

OToussaint on Aug 17, 2020