Database upgrade on MariaDB takes days to finish #123179

mackowskim · 2024-08-05T09:42:33Z

The problem

After the upgrade to 2024.8.0 beta 1 HA core is upgrading database schema on my MariaDB instance from v43 to v44.
It runs 11 hours already and just 28% is done for BIGINT column change.

Seems this is the change ongoing:
#121025

Is there any way to speed up the process?
My database sits on QNAP NAS RAID 10 disks which are not slow, but it would take another 2 days to finish the process.

Also, my "states" table is 215 M of rows.

What version of Home Assistant Core has the issue?

core-2024.8.0b1

What was the last working version of Home Assistant Core?

core-2024.7.4

What type of installation are you running?

Home Assistant OS

Integration causing the issue

database / recorder

Link to integration documentation on our website

No response

Diagnostics information

No response

Example YAML snippet

No response

Anything in the logs that might be useful for us?

2024-08-05 10:00:45.897 INFO (Recorder) [homeassistant.components.recorder.migration] Upgrading recorder db schema to version 44
2024-08-05 10:00:46.015 WARNING (Recorder) [homeassistant.components.recorder.migration] Modifying columns data_id, event_type_id in table events. Note: this can take several minutes on large databases and slow machines. Please be patient!
2024-08-05 10:00:46.526 WARNING (Recorder) [homeassistant.components.recorder.migration] Modifying columns event_id, old_state_id, attributes_id, metadata_id in table states. Note: this can take several minutes on large databases and slow machines. Please be patient!

Additional information

No response

home-assistant · 2024-08-05T10:17:42Z

Hey there @home-assistant/core, mind taking a look at this issue as it has been labeled with an integration (recorder) you are listed as a code owner for? Thanks!

Code owner commands

Code owners of recorder can trigger bot actions by commenting:

@home-assistant close Closes the issue.
@home-assistant rename Awesome new title Renames the issue.
@home-assistant reopen Reopen the issue.
@home-assistant unassign recorder Removes the current integration label and assignees on the issue, add the integration domain after the command.
@home-assistant add-label needs-more-information Add a label (needs-more-information, problem in dependency, problem in custom component) to the issue.
@home-assistant remove-label needs-more-information Remove a label (needs-more-information, problem in dependency, problem in custom component) on the issue.

_{^{(message by CodeOwnersMention)}}

recorder documentation
recorder source
_{^{(message by IssueLinks)}}

bdraco · 2024-08-05T14:44:29Z

Normally, I'd ask Erik to take a look, but it looks like its working as designed and there isn't anything to be done but wait given the size of the database and hardware combination.

InnoDB alters are slow, sometimes 2-3 orders of magnitude slower than SQLite.

lexiismadd · 2024-08-08T05:56:21Z

I'm also experiencing a very long time for the database upgrade to complete. I only upgraded to 2024.8.0 on the normal standard release schedule as of today and its been going for 4 hours now..
I'm using HA OS in a VM but using MariaDB in a separate VM on the same machine.
I need to restart HA for other reasons (a HACS integration update) but it says I can't restart the system until it completes.

emontnemery · 2024-08-08T06:33:20Z

@mackowskim, @lexiismadd out of curiosity, how many days of retention have you configured?
I agree with @bdraco that there's not too much we can do to improve here.

mackowskim · 2024-08-08T08:59:57Z

recorder:
db_url: !secret mariadb_url
purge_keep_days: 180
commit_interval: 1

Till now I was not able to finish the upgrade. After one day HA is failing because of the "out of memory error".

I have increased innodb_buffer_pool_size size to 3GB and innodb_buffer_pool_chunk_size to 128MB today and also added additional memory to HA VM itself (from 4GB to 6GB of RAM).
Seems a bit faster now, but still because of the HA table structure I have a huge states table.

MariaDB [homeassistant]> SHOW TABLE STATUS LIKE 'states'\G
*************************** 1. row ***************************
Name: states
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 218595256
Avg_row_length: 95
Data_length: 20962115584
Max_data_length: 0
Index_length: 28029059072
Data_free: 128974848
Auto_increment: 358234178
Create_time: 2024-08-04 22:34:15
Update_time: NULL
Check_time: NULL
Collation: utf8mb4_unicode_ci
Checksum: NULL
Create_options:
Comment:
Max_index_length: 0
Temporary: N
1 row in set (0,001 sec)

DAVIZINH0 · 2024-08-08T09:28:10Z

hello.
I have home assistant OS and mariadb as addon, and the message of migration of recorder is still apears. and messages in log like:
Modifying columns data_id, event_type_id in table events. Note: this can take several minutes on large databases and slow machines. Please be patient! Modifying columns event_id, old_state_id, attributes_id, metadata_id in table states. Note: this can take several minutes on large databases and slow machines. Please be patient!

Its no problem for me the wait. but i dont know the progress. and the speed.
How i obtain the percentage of progress??

my database is big (60gb)

mackowskim · 2024-08-08T11:43:53Z

Maybe an idea to purge recorder before the update?
How could I force the purge before database upgrade process starts?

mackowskim · 2024-08-08T14:07:10Z

Its no problem for me the wait. but i dont know the progress. and the speed. How i obtain the percentage of progress??

Login into mysql database and run:

show full processlist;

It will show you alter table process running along with estimated percent done in the last column.

DAVIZINH0 · 2024-08-08T15:54:27Z

Its no problem for me the wait. but i dont know the progress. and the speed. How i obtain the percentage of progress??

Login into mysql database and run:

show full processlist;

It will show you alter table process running along with estimated percent done in the last column.

thanks!
18hours and 41%
its ok for me. tomorrow it will finish :-P

EDIT:
my problem is other, because never finish each table.
in each table the error is like this:
Could not modify column MODIFY event_id BIGINT in table states
Could not modify column MODIFY old_state_id BIGINT in table states
Could not modify column MODIFY attributes_id BIGINT in table states

and the error is this:
MySQLdb.OperationalError: (1206, 'The total number of locks exceeds the lock table size')

my database is big. more than 60GB. and have more than 300GB of free space.
i think the problem is the default "lock table size in mariadb"

i will wait if other people have this problem, and keep reading the other issues because i thing im not alone :-P

ronytomen · 2024-08-08T21:23:55Z

To add some additional information.

Running on Yellow with 8GB RAM and NVM storage. Database migration failed, but not after HA Core OOM several times causing a cascade of other issues including HA App crashing on all my macs and IOS devices running out of memory...

I view my setup as a corner case, but if the recorder could drop data if the amount of items queued up causes the system to run out of memory would probably be helpful.

I will be migrating the database to a host with faster I/O to perform the upgrade as I do not want to loose my historical database.

lexiismadd · 2024-08-08T23:29:43Z

Mine eventually finished after a few h9urs

mackowskim · 2024-08-09T05:37:23Z

Its no problem for me the wait. but i dont know the progress. and the speed. How i obtain the percentage of progress??

Login into mysql database and run:
show full processlist;
It will show you alter table process running along with estimated percent done in the last column.

thanks! 18hours and 41% its ok for me. tomorrow it will finish :-P

EDIT: my problem is other, because never finish each table. in each table the error is like this: Could not modify column MODIFY event_id BIGINT in table states Could not modify column MODIFY old_state_id BIGINT in table states Could not modify column MODIFY attributes_id BIGINT in table states

and the error is this: MySQLdb.OperationalError: (1206, 'The total number of locks exceeds the lock table size')

my database is big. more than 60GB. and have more than 300GB of free space. i think the problem is the default "lock table size in mariadb"

i will wait if other people have this problem, and keep reading the other issues because i thing im not alone :-P

You might need to increase innodb_buffer_pool_size.
I set mine to 2GB and it was enough to finish first table update after aound 20h.

Check if you have enough available memory on server/VM to increase it without swapping:
free -m
In MariaDB check your current size in GB:
SELECT @@innodb_buffer_pool_size/1024/1024/1024;
Then resize accordingly to your free memory. There is an option to run temporary resize not requiring database restart and it will last until the restart.

https://mariadb.com/docs/server/storage-engines/innodb/operations/configure-buffer-pool/

DAVIZINH0 · 2024-08-09T09:08:56Z

Its no problem for me the wait. but i dont know the progress. and the speed. How i obtain the percentage of progress??

Login into mysql database and run:
show full processlist;
It will show you alter table process running along with estimated percent done in the last column.

thanks! 18hours and 41% its ok for me. tomorrow it will finish :-P
EDIT: my problem is other, because never finish each table. in each table the error is like this: Could not modify column MODIFY event_id BIGINT in table states Could not modify column MODIFY old_state_id BIGINT in table states Could not modify column MODIFY attributes_id BIGINT in table states
and the error is this: MySQLdb.OperationalError: (1206, 'The total number of locks exceeds the lock table size')
my database is big. more than 60GB. and have more than 300GB of free space. i think the problem is the default "lock table size in mariadb"
i will wait if other people have this problem, and keep reading the other issues because i thing im not alone :-P

You might need to increase innodb_buffer_pool_size. I set mine to 2GB and it was enough to finish first table update after aound 20h.

Check if you have enough available memory on server/VM to increase it without swapping: free -m In MariaDB check your current size in GB: SELECT @@innodb_buffer_pool_size/1024/1024/1024; Then resize accordingly to your free memory. There is an option to run temporary resize not requiring database restart and it will last until the restart.

https://mariadb.com/docs/server/storage-engines/innodb/operations/configure-buffer-pool/

thanks a lot for your information.
Its very usefull. but today the upgrade is finished when i wakeup i see the message of finish.
BUT some errors in the log apears and i think maybe the upgrade is not complet
this is the errors:
Could not modify column MODIFY event_id BIGINT in table states
Could not modify column MODIFY old_state_id BIGINT in table states
Could not modify column MODIFY attributes_id BIGINT in table states
Could not modify column MODIFY metadata_id BIGINT in table states
Could not modify column MODIFY state_id BIGINT NOT NULL AUTO_INCREMENT in table states

how can i check if the update is complet or if not all the MODIFY are completed?

mackowskim · 2024-08-09T10:52:43Z

Login into mysql:

use homeassistant;
desc states;

it should be bigint type in those columns:

MariaDB [homeassistant]> desc states;
+-----------------------+--------------+------+-----+---------+----------------+
| Field                 | Type         | Null | Key | Default | Extra          |
+-----------------------+--------------+------+-----+---------+----------------+
| state_id              | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| entity_id             | char(0)      | YES  |     | NULL    |                |
| state                 | varchar(255) | YES  |     | NULL    |                |
| attributes            | char(0)      | YES  |     | NULL    |                |
| event_id              | bigint(20)   | YES  |     | NULL    |                |
| last_changed          | char(0)      | YES  |     | NULL    |                |
| last_changed_ts       | double       | YES  |     | NULL    |                |
| last_updated          | char(0)      | YES  | MUL | NULL    |                |
| last_updated_ts       | double       | YES  | MUL | NULL    |                |
| old_state_id          | bigint(20)   | YES  | MUL | NULL    |                |
| attributes_id         | bigint(20)   | YES  | MUL | NULL    |                |
| context_id            | char(0)      | YES  |     | NULL    |                |
| context_user_id       | char(0)      | YES  |     | NULL    |                |
| context_parent_id     | char(0)      | YES  |     | NULL    |                |
| origin_idx            | smallint(6)  | YES  |     | NULL    |                |
| context_id_bin        | tinyblob     | YES  | MUL | NULL    |                |
| context_user_id_bin   | tinyblob     | YES  |     | NULL    |                |
| context_parent_id_bin | tinyblob     | YES  |     | NULL    |                |
| metadata_id           | bigint(20)   | YES  | MUL | NULL    |                |
| last_reported_ts      | double       | YES  |     | NULL    |                |
+-----------------------+--------------+------+-----+---------+----------------+
20 rows in set (0,008 sec)

mackowskim · 2024-08-09T10:56:22Z

BTW,
My upgrade finished already, but it would be useful to modify message for MariaDB users that the update can take hours, not minutes :)

And also that for large databases it's good to have larger buffer pool size set (at least 1GB)

DAVIZINH0 · 2024-08-09T11:05:52Z

Login into mysql:

use homeassistant;
desc states;

it should be bigint type in those columns:

MariaDB [homeassistant]> desc states;
+-----------------------+--------------+------+-----+---------+----------------+
| Field                 | Type         | Null | Key | Default | Extra          |
+-----------------------+--------------+------+-----+---------+----------------+
| state_id              | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| entity_id             | char(0)      | YES  |     | NULL    |                |
| state                 | varchar(255) | YES  |     | NULL    |                |
| attributes            | char(0)      | YES  |     | NULL    |                |
| event_id              | bigint(20)   | YES  |     | NULL    |                |
| last_changed          | char(0)      | YES  |     | NULL    |                |
| last_changed_ts       | double       | YES  |     | NULL    |                |
| last_updated          | char(0)      | YES  | MUL | NULL    |                |
| last_updated_ts       | double       | YES  | MUL | NULL    |                |
| old_state_id          | bigint(20)   | YES  | MUL | NULL    |                |
| attributes_id         | bigint(20)   | YES  | MUL | NULL    |                |
| context_id            | char(0)      | YES  |     | NULL    |                |
| context_user_id       | char(0)      | YES  |     | NULL    |                |
| context_parent_id     | char(0)      | YES  |     | NULL    |                |
| origin_idx            | smallint(6)  | YES  |     | NULL    |                |
| context_id_bin        | tinyblob     | YES  | MUL | NULL    |                |
| context_user_id_bin   | tinyblob     | YES  |     | NULL    |                |
| context_parent_id_bin | tinyblob     | YES  |     | NULL    |                |
| metadata_id           | bigint(20)   | YES  | MUL | NULL    |                |
| last_reported_ts      | double       | YES  |     | NULL    |                |
+-----------------------+--------------+------+-----+---------+----------------+
20 rows in set (0,008 sec)

wow. in my case the desc are totally diferent.
I think the migration mark as finished but not modify the type of the fields :-(

THANKS A LOT! I will open a new issue!

pdulich · 2024-08-12T16:38:50Z

So. I had the same (or a similar) issue.
The upgrade seemed to take forever before HA printed the error message that the migration couldn't finish.

After retrying again and again I logged into the DB and found out that the MariaDB got completely stuck.
Repeatingly executing show processlist returned the same progress value for the ALTER TABLE... process every time - also after hours.
After further investigation I found the message: enforcing dict cache limit.

To solve the issue I did the following:

SSHed into the machine where MariaDB is running (Synology NAS)
created the file /var/packages/MariaDB10/etc/my.cnf (the path depends on your DB installation method and platform; for you it could be that the file already exists)
if the file didn't exist already, create it and start the content with [mariadb]
changed the config line (or add the line) innodb_buffer_pool_size by increasing its value (to 4G)
restarted the DB so that the changes took effect
restarted HA to restart the DB migration
after around 1h the migration was finished

The final file (for me) looked like this:

[mariadb]
innodb_buffer_pool_size=4G

rmi1974 · 2024-08-12T20:08:02Z

@pdulich

So. I had the same (or a similar) issue. The upgrade seemed to take forever before HA printed the error message that the migration couldn't finish.
...

The final file (for me) looked like this:
[mariadb]
innodb_buffer_pool_size=4G

Thanks, your steps helped me as well.

My HA database is 55 GB in size, spanning historical data from two years running a large home lab with hundreds of sensors, many 10000s of datapoints per day.

It didn't work with default mariadb setting taking days with very little progress. It even triggered out of memory killer on my docker host multiple times (killing homeassistant/mariadb containers) until I figured out it was this issue reported here.

One of the worst HA upgrades when it comes to internal upgrade processes. For many end users a disaster.
People should have been warned about this in the release notes way more explicitly (big red letters!).

https://www.home-assistant.io/blog/2024/08/07/release-20248/

This can cause the database upgrade to take longer than usual, depending on the size of your database.

Please be patient and let the upgrade process finish. Do not interrupt the upgrade process, as this can lead to a corrupted database.

It can take days(!) or in various instances it never finishes running into out of memory problems.
There should have been mitigations already provided in the documentation section.
Also how to check the upgrade progress because HA doesn't provide any visibility on this.

Regards

EDIT: The db schema update process finished. Final DB size: 56 GB.

...
homeassistant  | 2024-08-12T19:47:05.085013000Z 2024-08-12 21:47:05.082 WARNING (Recorder) [homeassistant.components.recorder.migration] The database is about to upgrade from schema version 43 to 44
homeassistant  | 2024-08-12T19:47:05.149102000Z 2024-08-12 21:47:05.148 WARNING (Recorder) [homeassistant.components.recorder.migration] Modifying columns data_id, event_type_id in table events. Note: this can take several minutes on large databases and slow machines. Please be patient!
homeassistant  | 2024-08-12T19:47:05.209842000Z 2024-08-12 21:47:05.209 WARNING (Recorder) [homeassistant.components.recorder.migration] Modifying columns event_id, old_state_id, attributes_id, metadata_id in table states. Note: this can take several minutes on large databases and slow machines. Please be patient!
homeassistant  | 2024-08-12T19:47:05.252082000Z 2024-08-12 21:47:05.250 WARNING (Recorder) [homeassistant.components.recorder.migration] Modifying columns metadata_id in table statistics. Note: this can take several minutes on large databases and slow machines. Please be patient!
homeassistant  | 2024-08-12T19:47:05.286841000Z 2024-08-12 21:47:05.286 WARNING (Recorder) [homeassistant.components.recorder.migration] Modifying columns metadata_id in table statistics_short_term. Note: this can take several minutes on large databases and slow machines. Please be patient!
homeassistant  | 2024-08-12T19:47:05.321147000Z 2024-08-12 21:47:05.320 WARNING (Recorder) [homeassistant.components.recorder.migration] Modifying columns event_id in table events. Note: this can take several minutes on large databases and slow machines. Please be patient!
homeassistant  | 2024-08-12T19:47:05.356117000Z 2024-08-12 21:47:05.355 WARNING (Recorder) [homeassistant.components.recorder.migration] Modifying columns data_id in table event_data. Note: this can take several minutes on large databases and slow machines. Please be patient!
homeassistant  | 2024-08-12T19:47:05.404011000Z 2024-08-12 21:47:05.403 WARNING (Recorder) [homeassistant.components.recorder.migration] Modifying columns event_type_id in table event_types. Note: this can take several minutes on large databases and slow machines. Please be patient!
homeassistant  | 2024-08-12T19:47:05.436894000Z 2024-08-12 21:47:05.436 WARNING (Recorder) [homeassistant.components.recorder.migration] Modifying columns state_id in table states. Note: this can take several minutes on large databases and slow machines. Please be patient!
homeassistant  | 2024-08-12T19:47:05.470264000Z 2024-08-12 21:47:05.469 WARNING (Recorder) [homeassistant.components.recorder.migration] Modifying columns attributes_id in table state_attributes. Note: this can take several minutes on large databases and slow machines. Please be patient!
...
homeassistant  | 2024-08-13T06:29:28.221410000Z 2024-08-13 08:29:28.220 WARNING (Recorder) [homeassistant.components.recorder.migration] Modifying columns id in table statistics_short_term. Note: this can take several minutes on large databases and slow machines. Please be patient!
homeassistant  | 2024-08-13T06:36:07.134881000Z 2024-08-13 08:36:07.134 WARNING (Recorder) [homeassistant.components.recorder.migration] Modifying columns id in table statistics_meta. Note: this can take several minutes on large databases and slow machines. Please be patient!
homeassistant  | 2024-08-13T06:36:07.264380000Z 2024-08-13 08:36:07.263 WARNING (Recorder) [homeassistant.components.recorder.migration] Modifying columns run_id in table recorder_runs. Note: this can take several minutes on large databases and slow machines. Please be patient!
homeassistant  | 2024-08-13T06:36:07.312486000Z 2024-08-13 08:36:07.311 WARNING (Recorder) [homeassistant.components.recorder.migration] Modifying columns change_id in table schema_changes. Note: this can take several minutes on large databases and slow machines. Please be patient!
homeassistant  | 2024-08-13T06:36:07.358163000Z 2024-08-13 08:36:07.357 WARNING (Recorder) [homeassistant.components.recorder.migration] Modifying columns run_id in table statistics_runs. Note: this can take several minutes on large databases and slow machines. Please be patient!
homeassistant  | 2024-08-13T06:36:07.540539000Z 2024-08-13 08:36:07.540 WARNING (Recorder) [homeassistant.components.recorder.migration] Upgrade to version 44 done

Still, it took 10 hours, 50 minutes with the buffer sizes enlarged (non defaults).

I've already moved HA to a different server, separate from mariadb docker host machine due to OOM situations during db upgrade.
It seems during the db conversion process, HA does weird things on its own, requiring huge amounts of memory?

Prometheus node exporter dashboard from the docker host running HA:

CAdvisor2 dashboard:

Since the DB upgrade process already finished on the other machine, I've decided to restart HA docker container after waiting another hour because HA situation didn't improve. It was looking like eating all the available RAM and SWAP again. After container restart it's back on ok-ish levels again. I would consider this as another bug.

On the original machine where HA+mariadb were hosted (8GB) for years, this upgrade would never have worked. I was fortunate to have multiple machine available in my homelab so I could move docker containers/volumes around. But that's certainly not what everyone has.

Repeating myself here: I think the implications of the schema upgrade in 2024.8 should have been better communicated. Depending on the db size it requires a huge amount of time and RAM. For the non tech-savvy end users this is certainly a disaster.

lutzvahl · 2024-08-13T08:12:34Z

Same here. ~55GB DB, after 17h only 22% completed.
I've used the phpmyadmin Add-on to change the innodb_buffer_pool_size to speed up the process.

IMO we need those parameters to be exposed to the MariaDB Add-On UI as any config changes will be overwritten during the plugin update: https://community.home-assistant.io/t/addon-mariadb-expose-more-mariadb-config-parameters-in-addon-config/758474

lutzvahl · 2024-08-14T12:15:56Z

Another observation is: The states table is the largest, therefore most of the issues are encountered during the migration of it.
By removing all indexes except the primary key of the states table. This
A) Speed up the migration 10x
B) uses much less storage - in My case Indexes were ~40GB and data only ~12GB.

Export the structure of the table so you can restore the index afterwards.

tikismoke · 2024-08-18T13:42:01Z

Another observation is: The states table is the largest, therefore most of the issues are encountered during the migration of it. By removing all indexes except the primary key of the states table. This A) Speed up the migration 10x B) uses much less storage - in My case Indexes were ~40GB and data only ~12GB.

Export the structure of the table so you can restore the index afterwards.

Could you detalis this method please?

This is my database info :

And it take very long with on one side OOM crash from HAOS (related to another issue but might also come from migration never finished) and make migration restart and not take account what already done :(
I already update innodb_buffer_pool_size to 10Gb and still 24h it's not finished.

grooverdan · 2024-08-19T03:07:01Z

note innodb_buffer_pool_chunk_size affects the minimum increment/decrement amount by which the innodb_buffer_pool_size can be changed.

"The total number of locks exceeds the lock table size", might be a result of https://jira.mariadb.org/browse/MDEV-34167 (note fixed versions) but a large innodb buffer pool size might avoid it.

For large changes an increased innodb_log_file_size can help increase speed (Dynamicly changable on MariaDB 10.11+). Several GB matching buffer pool size is ok if sufficient storage space.

lutzvahl · 2024-08-21T10:43:51Z

Another observation is: The states table is the largest, therefore most of the issues are encountered during the migration of it. By removing all indexes except the primary key of the states table. This A) Speed up the migration 10x B) uses much less storage - in My case Indexes were ~40GB and data only ~12GB.
Export the structure of the table so you can restore the index afterwards.

Could you detalis this method please?

This is my database info : And it take very long with on one side OOM crash from HAOS (related to another issue but might also come from migration never finished) and make migration restart and not take account what already done :( I already update innodb_buffer_pool_size to 10Gb and still 24h it's not finished.

Sure, what I did was.
FIRST: Create a DB backup :)

Export the definition of the states table (phpmyadmin -> states tbl -> Export -> structure only)
Open the Structure of the states table in phpmyadmin
delete all indexes except the primary key PRIMARY KEY ('state_id')
Perform the HA update and wait until it's done
Add the index back (deleted in 3) - This will take a LOT of time and will use space. In my case indexes are ~30GB, data only 11GB. You can either add all indexes at once or one after each other. During this time HA is still slow
ALTER TABLE 'states' ADD KEY 'ix_states_old_state_id' ('old_state_id'), ADD KEY 'ix_states_attributes_id' ('attributes_id'), ADD KEY 'ix_states_last_updated_ts' ('last_updated_ts'), ADD KEY 'ix_states_context_id_bin' ('context_id_bin'(16)), ADD KEY 'ix_states_metadata_id_last_updated_ts' ('metadata_id','last_updated_ts');

Those steps could be repeated for all tables, but I did it only for states as it's by fare the largest one.

tikismoke · 2024-08-24T15:14:43Z

Sure, what I did was.
FIRST: Create a DB backup :)

Export the definition of the states table (phpmyadmin -> states tbl -> Export -> structure only)

Open the Structure of the states table in phpmyadmin

delete all indexes except the primary key PRIMARY KEY ('state_id')

Perform the HA update and wait until it's done

Add the index back (deleted in 3) - This will take a LOT of time and will use space. In my case indexes are ~30GB, data only 11GB. You can either add all indexes at once or one after each other. During this time HA is still slow
ALTER TABLE 'states' ADD KEY 'ix_states_old_state_id' ('old_state_id'), ADD KEY 'ix_states_attributes_id' ('attributes_id'), ADD KEY 'ix_states_last_updated_ts' ('last_updated_ts'), ADD KEY 'ix_states_context_id_bin' ('context_id_bin'(16)), ADD KEY 'ix_states_metadata_id_last_updated_ts' ('metadata_id','last_updated_ts');

Those steps could be repeated for all tables, but I did it only for states as it's by fare the largest one.

This help me to do the upgrade, also as it is now finished the OutOfMermory of haos also stop 🥰.
Thank's

bsafh · 2024-08-26T12:51:07Z

delete all indexes except the primary key PRIMARY KEY ('state_id')

Perform the HA update and wait until it's done

how do you do that;

I tried (ha supervised):
# ha core stop
then in mariadb cli
drop index <name> from states;
then on command line
ha core start

and the first thing ha does is: re-create the index .... ?
how can I prevent that?

RaphDaMan · 2024-09-06T18:49:18Z

Just for info and help:

Database size: 27.1GB
Update process running since: 13 hours
Progress on "copy to tmp table" (phpMyAdmin): 34.172%
Hardware: Intel NUK NUC7PJYH2 Intel Pentium Silver J5005

...all running fine until now and will give feedback when done.

Cheers,
Rapha

RaphDaMan · 2024-09-10T06:35:10Z

Had same issue on my side (HA on Ubuntu 22.04.04 LTS with MariaDB), several days and tries to upgrade database failed.

Solution: increase "innodb_buffer_pool_size"
My Database: 28GB

Upgrade did work with 512M, 128M failed with 40%.

eingemaischt · 2024-09-10T11:50:35Z

I had the same problem. I do have a 80GB database directory (mariadb) on a Raspberry Pi. The upgrade script should warn more explicitely and it should offer some other options.

One would be to purge old data automatically before the upgrade. Another would be to rename the table to _archive, create new ones in correct scheme and move the data to the new table over the next days.

If done in blocks of 1000 lines or so from new to old it would be perfectly interruptable by reboots, restarts and so on - and won't effect the regular process - only actions and processes relying on archive data would be affected (but they could use data from the archive-tables, if really needed...).

pdubs10 · 2024-09-13T17:55:36Z

Ended up completely nuking my states and events tables due to this. Auto purge evidently stopped working for me at some point and I didn't notice, leaving my DB size at 200GB. Migration was never going to complete and I didn't have enough room for temp tables. recorder.purge seemingly did nothing (probably due to the schema not migrating) and I didn't know enough about the schema to manually purge rows and keep the latest N days.

@eingemaischt is absolutely right on the proper approach to a primary key (clustered index) migration on datasets of this size. Rename, create, batch insert, and optimize (probably with some auto-increment tomfoolery in there as well).

mackowskim · 2024-09-13T19:19:31Z

If your database is too big to handle the upgrade you can always rollback to previous version of HA core and run the purge before upgrading again.

If you run haos you can do it by running:
ha core update --version x.y.z

dominikandreas · 2024-09-16T06:23:00Z

My latest update process (2024.9.1) has not gone through after waiting a whole week (home assistant yellow 8GB).

Home assistant hung up twice (likely OOM). Clearing ram by stopping some add-ons fixed the OOM but still not completing. Now trying again with the innodb_buffer_pool_size increased to 4G as suggested above.

This is a real end user nightmare. All the statistics im loosing bc of this in the meantime is alone unfortunate enough, but I'm worried about all the users who don't understand why this is taking days instead of the announced hours and don't have the technical background to patch some config file inside a docker container via the terminal.

My db size is ~70GB, 2 years of retention. I don't really need the full 2 years for most states, but unfortunately it's not possible to specify different retention times for different sets of entities.

therealeldaria · 2024-09-21T12:55:45Z

I also had this fail each time my Home Assistant OS instance restarted it would try to upgrade and then fail after several hours.
Thankfully I found this discussion and the suggestion to change innodb_buffer_pool_size to 4G worked. I had to also give the VM running Home Assistant more memory and disk. It had 6GB RAM, I had already increased this but it was not enough. So while the upgrade ran I gave the VM 16GB instead.

TraipZe · 2024-09-22T18:25:52Z

how to i allocate more ram to my mariaDB in home assistant OS ?

dominikandreas · 2024-09-22T18:58:20Z

@TraipZe MariaDB addon shares memory with the rest of the system (as with all docker containers). I used https://github.com/TazzerMAN/increase_swap_addon to create an additional 4GB of swap space (6GB in swap space total). Swap is like virtual ram that is stored on disk and used when physical ram is full.

I also upgraded from my old 256GB nvme to 1TB. With all of this the database upgrade finally went through. This whole process took a whole month now in total.

TraipZe · 2024-09-22T20:06:11Z

Oh im using x86 nuc with ha os. Not sure how I can allocate more ram. Med venlig hilsen / Best regardsThomas Hansen. Den 22. sep. 2024 kl. 20.58 skrev dominikandreas ***@***.***>: @TraipZe I used https://github.com/TazzerMAN/increase_swap_addon to create an additional 4GB of swap space (6GB in total). Swap is like virtual ram that is stored on disk and used when physical ram is full. I also upgraded from my old 256GB name to 1TB. With all of this the database upgrade finally went through. This whole process took a whole month now in total. —Reply to this email directly, view it on GitHub, or unsubscribe.You are receiving this because you were mentioned.Message ID: ***@***.***>

dominikandreas · 2024-09-22T20:11:01Z

Just install the addon, the swap space acts as additional virtual ram.

mackowskim changed the title ~~Database upgrade on MariaDB takes ages~~ Database upgrade on MariaDB takes days to finish Aug 5, 2024

mib1185 added this to the 2024.8.0 milestone Aug 5, 2024

mib1185 added integration: recorder database: mariadb labels Aug 5, 2024

grooverdan mentioned this issue Aug 19, 2024

Add config parameters from mariadb-server.cnf to Addon config in HA MariaDB/mariadb-docker#602

Open

mib1185 mentioned this issue Sep 9, 2024

2024.09.1 - Database error during schema migration - version 46 to 47 #125606

Closed

Database upgrade on MariaDB takes days to finish #123179

Database upgrade on MariaDB takes days to finish #123179

Comments

mackowskim commented Aug 5, 2024 • edited Loading

The problem

What version of Home Assistant Core has the issue?

What was the last working version of Home Assistant Core?

What type of installation are you running?

Integration causing the issue

Link to integration documentation on our website

Diagnostics information

Example YAML snippet

Anything in the logs that might be useful for us?

Additional information

home-assistant bot commented Aug 5, 2024

bdraco commented Aug 5, 2024 • edited Loading

lexiismadd commented Aug 8, 2024

emontnemery commented Aug 8, 2024

mackowskim commented Aug 8, 2024 • edited Loading

DAVIZINH0 commented Aug 8, 2024 • edited Loading

mackowskim commented Aug 8, 2024

mackowskim commented Aug 8, 2024

DAVIZINH0 commented Aug 8, 2024 • edited Loading

ronytomen commented Aug 8, 2024

lexiismadd commented Aug 8, 2024

mackowskim commented Aug 9, 2024 • edited Loading

DAVIZINH0 commented Aug 9, 2024

mackowskim commented Aug 9, 2024

mackowskim commented Aug 9, 2024

DAVIZINH0 commented Aug 9, 2024 • edited Loading

pdulich commented Aug 12, 2024

rmi1974 commented Aug 12, 2024 • edited Loading

lutzvahl commented Aug 13, 2024

lutzvahl commented Aug 14, 2024

tikismoke commented Aug 18, 2024

grooverdan commented Aug 19, 2024

lutzvahl commented Aug 21, 2024 • edited Loading

tikismoke commented Aug 24, 2024 • edited Loading

bsafh commented Aug 26, 2024

RaphDaMan commented Sep 6, 2024 • edited Loading

RaphDaMan commented Sep 10, 2024

eingemaischt commented Sep 10, 2024

pdubs10 commented Sep 13, 2024

mackowskim commented Sep 13, 2024 • edited Loading

dominikandreas commented Sep 16, 2024

therealeldaria commented Sep 21, 2024

TraipZe commented Sep 22, 2024

dominikandreas commented Sep 22, 2024 • edited Loading

TraipZe commented Sep 22, 2024 via email

dominikandreas commented Sep 22, 2024

mackowskim commented Aug 5, 2024 •

edited

Loading

bdraco commented Aug 5, 2024 •

edited

Loading

mackowskim commented Aug 8, 2024 •

edited

Loading

DAVIZINH0 commented Aug 8, 2024 •

edited

Loading

DAVIZINH0 commented Aug 8, 2024 •

edited

Loading

mackowskim commented Aug 9, 2024 •

edited

Loading

DAVIZINH0 commented Aug 9, 2024 •

edited

Loading

rmi1974 commented Aug 12, 2024 •

edited

Loading

lutzvahl commented Aug 21, 2024 •

edited

Loading

tikismoke commented Aug 24, 2024 •

edited

Loading

RaphDaMan commented Sep 6, 2024 •

edited

Loading

mackowskim commented Sep 13, 2024 •

edited

Loading

dominikandreas commented Sep 22, 2024 •

edited

Loading