It depends on how large your database is.
Itās been 6h now, and mariadb is still using 100% cpu and python3 on the RPI is at 120%ā¦
Would purging entries now, while itās still doing the database upgrade, be problematic?
Thereās most likely a lock on the db so I would assume a purge would do nothing until the process is complete.
Check your MySQL/MariaDB processlist to see whatās going on. For the default settings it shouldnāt take more than a few minutes to do the latest migration unless you have a massive amount of entities
1 Like
I have a history statistics sensor that stopped resetting at midnight after the upgrade. I just resolved the issue with another reboot, but am not sure why it happened. It may have been related to Make all history stats tests async by balloob Ā· Pull Request #87973 but if it is, I canāt tell why.
sensor:
- platform: history_stats
name: Boiler Runtime Today
entity_id: switch.boiler
state: "on"
type: time
start: "{{ now().replace(hour=0, minute=0, second=0) }}"
end: "{{ now() }}"
Also, the update sensors changed from āoffā to āunavailableā. This was not resolved with the reboot, but this could have been intentional (āupdate unavailableā makes sense). However, I didnāt see that announced in the blog, nor can I find it in any of the release notes.
Apart from understanding why this happened, Iām also curious whether itās possible to fix the data for the boiler runtime sensor.
Itās definitely not related to that PR. Those are the unit tests for the history stats integration. The integration itself did not change, just the tests we perform on the integration (to make sure it works).
Did you have any errors in your logs?
I see a lot of references to a database upgrade. I had no issue, but am puzzled that there seems to be no reference to a database update in the release notes. Did I miss it? Does it relate to mysql/mariadb only?
Not here. History working fine.
Well, itās been over 13h now, and it is still in the same state.
I had read about issues for people using mariadb, and upgrade stalling.
SHOW FULL PROCESSLIST
only shows a single hass related entry in running state:
| 3380 | hass_user | 192.168.10.5:38516 | hass_db | Query | 0 | Searching rows for update | UPDATE statistics set start_ts=IF(start is NULL,0,UNIX_TIMESTAMP(start) ), created_ts=UNIX_TIMESTAMP(created), last_reset_ts=UNIX_TIMESTAMP(last_reset) where start_ts is NULL LIMIT 250000 | 0.000 |
I guess I can assume that itās stuffed now.
Is the process id changing from 3380 or is it stuck?
Which version of MariaDB?
I see a lot of references to a database upgrade. I had no issue, but am puzzled that there seems to be no reference to a database update in the release notes. Did I miss it? Does it relate to mysql/mariadb only?
mariadb is listed in the Breaking Changes section (link) of the 2023.2 upgrade from last month. The most recent version of mariadb 10 in the Synology Package Center is 10.3.32.-1040, which isnāt new enough to meet the requirements listed in the breaking changes. Perhaps people got bit by thatā¦
1 Like
version is 10.6.12-MariaDB-1:10.6.12+maria~ubu1804
I rebooted HA machine now.
Itās back at showing me that a database upgrade is in the process.
mysqldb is back to 100%.
task varies between:
| 153 | hass_user | pine64.local:53350 | hass_db | Query | 0 | Updating | UPDATE statistics set start_ts=IF(start is NULL,0,UNIX_TIMESTAMP(start) ), created_ts=UNIX_TIMESTAMP(created), last_reset_ts=UNIX_TIMESTAMP(last_reset) where start_ts is NULL LIMIT 250000 | 0.000 |
and
| 152 | hass_user | pine64.local:52718 | hass_db | Query | 0 | Searching rows for update | UPDATE statistics set start_ts=IF(start is NULL,0,UNIX_TIMESTAMP(start) ), created_ts=UNIX_TIMESTAMP(created), last_reset_ts=UNIX_TIMESTAMP(last_reset) where start_ts is NULL LIMIT 250000 | 0.000 |
| 152 | hass_user | pine64.local:52718 | hass_db | Query | 0 | Updating | UPDATE statistics set start_ts=IF(start is NULL,0,UNIX_TIMESTAMP(start) ), created_ts=UNIX_TIMESTAMP(created), last_reset_ts=UNIX_TIMESTAMP(last_reset) where start_ts is NULL LIMIT 250000 | 0.000 |
each run of SHOW FULL PROCESSLIST make it goes between those id 152 and 153.
I made a full database backup before expecting something to go wrong. Iām unsure now if I just press forward deleting whatever it is that made it stuck, or restore backup.
Its doing them in chunks of 250k at a time. Since its cycling back and forth I assume they are being processed. How many rows do you have in statistics?
For comparison on a large install with ~3000 entities and 1 year of stats data
> select count(id) from statistics;
2191137
Also check the mariadb server logs and make sure you arenāt running out of temp space.
> select count(id) from statistics;
+-----------+
| count(id) |
+-----------+
| 1467600 |
+-----------+
error.log is empty for today and yesterday.
That should go rather quickly. It may be that it canāt handle 250k in one transaction but itās more likely something else is going on with your MariaDB instance. We did lower it to 100k per batch in 2023.3.2 to reduce memory requirements (I donāt think you are on an RPI3) but itās not out yet.
You might also check SHOW INNODB STATUS to see if something has gone wrong with the storage engine.
I canāt say I can read the output of it.
My switch from sqllite to mariadb is rather recent (under 6 months ago), sqllite became just too slow and gigantic ; so there hasnāt been much opportunity to mess things up.
| InnoDB | |
=====================================
2023-03-08 10:59:44 0x7f59c07f1700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 11 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 0 srv_active, 0 srv_shutdown, 8109 srv_idle
srv_master_thread log flush and writes: 8106
----------
SEMAPHORES
----------
------------
TRANSACTIONS
------------
Trx id counter 15468416
Purge done for trx's n:o < 15468416 undo n:o < 0 state: running
History list length 4
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION (0x7f59d8339780), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION (0x7f59d8338c80), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION (0x7f59d8338180), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION (0x7f59d8337680), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION (0x7f59d8336b80), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
--------
FILE I/O
--------
Pending flushes (fsync) log: 0; buffer pool: 0
8079008 OS file reads, 84469 OS file writes, 4951 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.27 writes/s, 0.27 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 840, seg size 842, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 220742117981
Log flushed up to 220742117981
Pages flushed up to 220721064629
Last checkpoint at 220721064629
0 pending log flushes, 0 pending chkp writes
2836 log i/o's done, 0.27 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 167772160
Dictionary memory allocated 878376
Buffer pool size 8112
Free buffers 0
Database pages 8112
Old database pages 2974
Modified db pages 3219
Percent of dirty pages(LRU & free pages): 39.677
Max dirty pages percent: 90.000
Pending reads 0
Pending writes: LRU 0, flush list 0
Pages made young 11320, not young 1157297756
0.09 youngs/s, 0.00 non-youngs/s
Pages read 8078984, created 66698, written 81413
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 8112, unzip_LRU len: 0
I/O sum[1]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 read views open inside InnoDB
Process ID=0, Main thread ID=0, state: sleeping
Number of rows inserted 4544020, updated 1, deleted 7, read 2110041619
7.54 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
Number of system rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
no idea one bit if itās good or bad.
Iāll try again once 2023.3.2 is out.
Iāve lost the history of the last 16h (including the energy screen), but they were no longer working in between anyway.
You might create a fresh db on 2022.2.x and compare your schema to it to make sure everything is correct if you did the migration manually.
It doesnāt look like anything is deadlocking but Iām not sure why its not moving along. mysqlcheck may reveal if there is a problem with the tables on disk.
itās showing all tables as okay.