What's your database setup? Mine's been migrating for about 5 hours, and still going

Tags: #<Tag:0x00007fc40dfe78a0> #<Tag:0x00007fc40dfe77d8>

Hi guys!

So today I decided to upgrade my HA instance from the ancient 2021.2.3 release to the latest 2021.5.

I checked around as usual to see if there were any people complaining about things being broken/not working, nothing much really apart from some posts about HA taking a while to reboot. Guessed it must be a database upgrade.

My current setup uses MySQL running in a virtual machine. The previous database upgrades were smooth sailing - usually just a couple minutes of downtime and everything goes back to normal. This upgrade is a beast though. My VM (and actually, the hypervisor) has been at it’s knees since 5AM this morning (lol). This is the state of the logs…

2021-05-07 05:26:16 WARNING (Recorder) [homeassistant.components.recorder.migration] Database is about to upgrade. Schema version: 11
2021-05-07 05:26:16 WARNING (Recorder) [homeassistant.components.recorder.migration] Modifying columns event_data in table events. Note: this can take several minutes on large databases and slow computers. Please be patient!
2021-05-07 07:27:00 WARNING (Recorder) [homeassistant.components.recorder.migration] Modifying columns attributes in table states. Note: this can take several minutes on large databases and slow computers. Please be patient!
2021-05-07 07:42:39 WARNING (Recorder) [homeassistant.components.recorder.migration] Modifying columns time_fired, created in table events. Note: this can take several minutes on large databases and slow computers. Please be patient!
2021-05-07 09:56:09 WARNING (Recorder) [homeassistant.components.recorder.migration] Modifying columns last_changed, last_updated, created in table states. Note: this can take several minutes on large databases and slow computers. Please be patient!
2021-05-07 10:13:37 WARNING (Recorder) [homeassistant.components.recorder.migration] Modifying columns event_type in table events. Note: this can take several minutes on large databases and slow computers. Please be patient!
2021-05-07 10:26:07 ERROR (MainThread) [homeassistant.components.recorder] The recorder queue reached the maximum size of 30000; Events are no longer being recorded

I’m at a loss as to how I can best manage the current database situation. Right now all I can do is wait it out until the migration finishes :sweat_smile: … On the up side, HA is running so it’s ‘business as usual’ for all my automations. I had to switch one of them off because of the high system utilisation lol.

What database engines are you guys using and what are your experiences so far?

I think the upgrade time depends on a number of things like the horsepower of your host, how many entities you have, and how long you are retaining the data for. A good indicator is the size of your home-assistant_v2.db database file.

I am running HA in Docker on a Synology 918+, I have 630 entities, my database size is 274MB which provides 7 days history, and I have un-needed entities excluded from the Recorder Integration (longer term history is handled by InfluxDB). Mine takes less than 5 mins for HA to fully restart.

There was a database upgrade with 2021.4. A small one with 2021.5, but regardless what version you’re coming from, if the major upgrade with 2021.4 wasn’t done, it will do that upgrade first.

I have HA Container on a Synology DS1515+ with a MariaDB container for the database. I don’t know how many entities/devices I store (I have hundreds, if not a thousand sensors and states), but 28 days of history at about 8.5Gb took roughly 7.5 hours to complete.

If you have a phpMyAdmin instance setup (or if you know the MySQL command line) you can query the running processes on a DB and see what commands are running and the percentage complete. However, this is not the total upgrade time, it is just the status of the current table command and there are many table commands with the DB upgrade.