search

home-assistant / core

:house_with_garden: Open source home automation that puts local control and privacy first.
https://www.home-assistant.io
Apache License 2.0
71.67k stars 29.95k forks source link

Endless purge process with MariaDB 10.6.10 #87352

Closed helepeut closed 1 year ago

helepeut commented 1 year ago

The problem

Hello,

I moved to MariaDB using the addon 1 month ago. As I moved to a SSD, I decided to keep history data for 30 days. Since 2 weeks, the daily purge process is VERY long, with a significant increase in CPU load (at a point that I need to restart HA to interrupt the process and come back to a normal CPU load, even after 24 hours of processing). The database has 5M lines in states and state_attributes tables, for a total database size of ~13Gb. I remember that I have 2 zigbee devices that were publishing a lot of attributes when I moved to MariaDB, and I changed their configuration to reduce their number a few days later. When HA 2023.2 was released, I noticed that MariaDB was upgraded to 10.6.10 with optimizations recording the purge, so of course I moved to this version, the database was successfully upgrade, and I restarted the purge process in order and reducing the keep_days parameter to be right after my change in zigbee devices to be sure that "old" attributes alues was removed from the DB. (I was thinking that it could be the issue). But no, the process is running for 40 hours now, and still not finished. When I watch the full processlist, I see that most of the time I'm on the "SELECT min(states.attributes_id) AS" query. When looking at the logs (put in DEBUG mode for the recorder), I see that sqlalchemy events like "DEBUG (Recorder) [homeassistant.components.recorder.purge] Selected 19775 shared attributes to remove" with corresponding actions are logged sometimes after 40 minutes, sometimes after 90 minutes.

Is there something I can do to have a quicker processing ?

What version of Home Assistant Core has the issue?

2023.2.0

What was the last working version of Home Assistant Core?

No response

What type of installation are you running?

Home Assistant OS

Integration causing the issue

MariaDB

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?

No response

Additional information

No response

bdraco commented 1 year ago

This sounds similar to #85366

In that issue the user is stuck on 10.5.x though

Is it possible to provide a mysqldump of the database so I can try loading it up on a 10.6.10 instance and try to replicate the issue?

bdraco commented 1 year ago

Also can you post a full process list and the output of

SHOW ENGINE INNODB STATUS

home-assistant[bot] commented 1 year ago

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` Change the title of 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.

(message by CodeOwnersMention)

recorder documentation recorder source (message by IssueLinks)

helepeut commented 1 year ago

Thanks @bdraco ! Could I be stuck on 10.5.x even if it says ?

select version();
10.6.10-MariaDB

For the mysqldump, do you want the dump of the full database or just the schema ? (if so, how should I share it ?)

Here are the results: 

SHOW FULL PROCESSLIST;

801 homeassistant   172.30.32.1:43330   homeassistant   Sleep   772     NULL    0.000   
802 homeassistant   172.30.32.1:35358   homeassistant   Sleep   14      NULL    0.000   
803 homeassistant   172.30.32.1:52500   homeassistant   Sleep   772     NULL    0.000   
804 homeassistant   172.30.32.1:52514   homeassistant   Sleep   772     NULL    0.000   
805 homeassistant   172.30.32.1:52524   homeassistant   Sleep   772     NULL    0.000   
834 homeassistant   172.30.32.1:54256   homeassistant   Query   128 Optimizing  SELECT min(states.attributes_id) AS min_1 
FROM states 
WHERE states.attributes_id = 8400988 UNION ALL SELECT min(states.attributes_id) AS min_2 
FROM states 
WHERE states.attributes_id = 8400989 UNION ALL SELECT min(states.attributes_id) AS min_3 
FROM states 
WHERE states.attributes_id = 8400990 UNION ALL SELECT min(states.attributes_id) AS min_4 
FROM states 
WHERE states.attributes_id = 8400991 UNION ALL SELECT min(states.attributes_id) AS min_5 
FROM states 
WHERE states.attributes_id = 8400992 UNION ALL SELECT min(states.attributes_id) AS min_6 
FROM states 
WHERE states.attributes_id = 8400993 UNION ALL SELECT min(states.attributes_id) AS min_7 
FROM states 
WHERE states.attributes_id = 8400994 UNION ALL SELECT min(states.attributes_id) AS min_8 
FROM states 
WHERE states.attributes_id = 8400995 UNION ALL SELECT min(states.attributes_id) AS min_9 
FROM states 
WHERE states.attributes_id = 8400996 UNION ALL SELECT min(states.attributes_id) AS min_10 
FROM states 
WHERE states.attributes_id = 8400997 UNION ALL SELECT min(states.attributes_id) AS min_11 
FROM states 
WHERE states.attributes_id = 8400998 UNION ALL SELECT min(states.attributes_id) AS min_12 
FROM states 
WHERE states.attributes_id = 8400999 UNION ALL SELECT min(states.attributes_id) AS min_13 
FROM states 
WHERE states.attributes_id = 8401000 UNION ALL SELECT min(states.attributes_id) AS min_14 
FROM states 
WHERE states.attributes_id = 8401001 UNION ALL SELECT min(states.attributes_id) AS min_15 
FROM states 
WHERE states.attributes_id = 8401002 UNION ALL SELECT min(states.attributes_id) AS min_16 
FROM states 
WHERE states.attributes_id = 8401003 UNION ALL SELECT min(states.attributes_id) AS min_17 
FROM states 
WHERE states.attributes_id = 8401004 UNION ALL SELECT min(states.attributes_id) AS min_18 
FROM states 
WHERE states.attributes_id = 8401005 UNION ALL SELECT min(states.attributes_id) AS min_19 
FROM states 
WHERE states.attributes_id = 8401006 UNION ALL SELECT min(states.attributes_id) AS min_20 
FROM states 
WHERE states.attributes_id = 8401007 UNION ALL SELECT min(states.attributes_id) AS min_21 
FROM states 
WHERE states.attributes_id = 8401008 UNION ALL SELECT min(states.attributes_id) AS min_22 
FROM states 
WHERE states.attributes_id = 8401009 UNION ALL SELECT min(states.attributes_id) AS min_23 
FROM states 
WHERE states.attributes_id = 8401010 UNION ALL SELECT min(states.attributes_id) AS min_24 
FROM states 
WHERE states.attributes_id = 8401011 UNION ALL SELECT min(states.attributes_id) AS min_25 
FROM states 
WHERE states.attributes_id = 8401012 UNION ALL SELECT min(states.attributes_id) AS min_26 
FROM states 
WHERE states.attributes_id = 8401013 UNION ALL SELECT min(states.attributes_id) AS min_27 
FROM states 
WHERE states.attributes_id = 8401014 UNION ALL SELECT min(states.attributes_id) AS min_28 
FROM states 
WHERE states.attributes_id = 8401015 UNION ALL SELECT min(states.attributes_id) AS min_29 
FROM states 
WHERE states.attributes_id = 8401016 UNION ALL SELECT min(states.attributes_id) AS min_30 
FROM states 
WHERE states.attributes_id = 8401017 UNION ALL SELECT min(states.attributes_id) AS min_31 
FROM states 
WHERE states.attributes_id = 8401018 UNION ALL SELECT min(states.attributes_id) AS min_32 
FROM states 
WHERE states.attributes_id = 8401019 UNION ALL SELECT min(states.attributes_id) AS min_33 
FROM states 
WHERE states.attributes_id = 8401020 UNION ALL SELECT min(states.attributes_id) AS min_34 
FROM states 
WHERE states.attributes_id = 8401021 UNION ALL SELECT min(states.attributes_id) AS min_35 
FROM states 
WHERE states.attributes_id = 8401022 UNION ALL SELECT min(states.attributes_id) AS min_36 
FROM states 
WHERE states.attributes_id = 8401023 UNION ALL SELECT min(states.attributes_id) AS min_37 
FROM states 
WHERE states.attributes_id = 8401024 UNION ALL SELECT min(states.attributes_id) AS min_38 
FROM states 
WHERE states.attributes_id = 8401025 UNION ALL SELECT min(states.attributes_id) AS min_39 
FROM states 
WHERE states.attributes_id = 8401026 UNION ALL SELECT min(states.attributes_id) AS min_40 
FROM states 
WHERE states.attributes_id = 8401028 UNION ALL SELECT min(states.attributes_id) AS min_41 
FROM states 
WHERE states.attributes_id = 8401029 UNION ALL SELECT min(states.attributes_id) AS min_42 
FROM states 
WHERE states.attributes_id = 8401030 UNION ALL SELECT min(states.attributes_id) AS min_43 
FROM states 
WHERE states.attributes_id = 8401031 UNION ALL SELECT min(states.attributes_id) AS min_44 
FROM states 
WHERE states.attributes_id = 8401032 UNION ALL SELECT min(states.attributes_id) AS min_45 
FROM states 
WHERE states.attributes_id = 8401033 UNION ALL SELECT min(states.attributes_id) AS min_46 
FROM states 
WHERE states.attributes_id = 8401034 UNION ALL SELECT min(states.attributes_id) AS min_47 
FROM states 
WHERE states.attributes_id = 8401035 UNION ALL SELECT min(states.attributes_id) AS min_48 
FROM states 
WHERE states.attributes_id = 8401036 UNION ALL SELECT min(states.attributes_id) AS min_49 
FROM states 
WHERE states.attributes_id = 8401037 UNION ALL SELECT min(states.attributes_id) AS min_50 
FROM states 
WHERE states.attributes_id = 8401038 UNION ALL SELECT min(states.attributes_id) AS min_51 
FROM states 
WHERE states.attributes_id = 8401039 UNION ALL SELECT min(states.attributes_id) AS min_52 
FROM states 
WHERE states.attributes_id = 8401040 UNION ALL SELECT min(states.attributes_id) AS min_53 
FROM states 
WHERE states.attributes_id = 8401041 UNION ALL SELECT min(states.attributes_id) AS min_54 
FROM states 
WHERE states.attributes_id = 8401042 UNION ALL SELECT min(states.attributes_id) AS min_55 
FROM states 
WHERE states.attributes_id = 8401043 UNION ALL SELECT min(states.attributes_id) AS min_56 
FROM states 
WHERE states.attributes_id = 8401044 UNION ALL SELECT min(states.attributes_id) AS min_57 
FROM states 
WHERE states.attributes_id = 8401045 UNION ALL SELECT min(states.attributes_id) AS min_58 
FROM states 
WHERE states.attributes_id = 8401046 UNION ALL SELECT min(states.attributes_id) AS min_59 
FROM states 
WHERE states.attributes_id = 8401047 UNION ALL SELECT min(states.attributes_id) AS min_60 
FROM states 
WHERE states.attributes_id = 8401048 UNION ALL SELECT min(states.attributes_id) AS min_61 
FROM states 
WHERE states.attributes_id = 8401049 UNION ALL SELECT min(states.attributes_id) AS min_62 
FROM states 
WHERE states.attributes_id = 8401050 UNION ALL SELECT min(states.attributes_id) AS min_63 
FROM states 
WHERE states.attributes_id = 8401051 UNION ALL SELECT min(states.attributes_id) AS min_64 
FROM states 
WHERE states.attributes_id = 8401052 UNION ALL SELECT min(states.attributes_id) AS min_65 
FROM states 
WHERE states.attributes_id = 8401053 UNION ALL SELECT min(states.attributes_id) AS min_66 
FROM states 
WHERE states.attributes_id = 8401054 UNION ALL SELECT min(states.attributes_id) AS min_67 
FROM states 
WHERE states.attributes_id = 8401055 UNION ALL SELECT min(states.attributes_id) AS min_68 
FROM states 
WHERE states.attributes_id = 8401056 UNION ALL SELECT min(states.attributes_id) AS min_69 
FROM states 
WHERE states.attributes_id = 8401057 UNION ALL SELECT min(states.attributes_id) AS min_70 
FROM states 
WHERE states.attributes_id = 8401058 UNION ALL SELECT min(states.attributes_id) AS min_71 
FROM states 
WHERE states.attributes_id = 8401059 UNION ALL SELECT min(states.attributes_id) AS min_72 
FROM states 
WHERE states.attributes_id = 8401060 UNION ALL SELECT min(states.attributes_id) AS min_73 
FROM states 
WHERE states.attributes_id = 8401061 UNION ALL SELECT min(states.attributes_id) AS min_74 
FROM states 
WHERE states.attributes_id = 8401062 UNION ALL SELECT min(states.attributes_id) AS min_75 
FROM states 
WHERE states.attributes_id = 8401063 UNION ALL SELECT min(states.attributes_id) AS min_76 
FROM states 
WHERE states.attributes_id = 8401064 UNION ALL SELECT min(states.attributes_id) AS min_77 
FROM states 
WHERE states.attributes_id = 8401065 UNION ALL SELECT min(states.attributes_id) AS min_78 
FROM states 
WHERE states.attributes_id = 8401066 UNION ALL SELECT min(states.attributes_id) AS min_79 
FROM states 
WHERE states.attributes_id = 8401067 UNION ALL SELECT min(states.attributes_id) AS min_80 
FROM states 
WHERE states.attributes_id = 8401068 UNION ALL SELECT min(states.attributes_id) AS min_81 
FROM states 
WHERE states.attributes_id = 8401069 UNION ALL SELECT min(states.attributes_id) AS min_82 
FROM states 
WHERE states.attributes_id = 8401070 UNION ALL SELECT min(states.attributes_id) AS min_83 
FROM states 
WHERE states.attributes_id = 8401071 UNION ALL SELECT min(states.attributes_id) AS min_84 
FROM states 
WHERE states.attributes_id = 8401072 UNION ALL SELECT min(states.attributes_id) AS min_85 
FROM states 
WHERE states.attributes_id = 8401073 UNION ALL SELECT min(states.attributes_id) AS min_86 
FROM states 
WHERE states.attributes_id = 8401074 UNION ALL SELECT min(states.attributes_id) AS min_87 
FROM states 
WHERE states.attributes_id = 8401075 UNION ALL SELECT min(states.attributes_id) AS min_88 
FROM states 
WHERE states.attributes_id = 8401076 UNION ALL SELECT min(states.attributes_id) AS min_89 
FROM states 
WHERE states.attributes_id = 8401077 UNION ALL SELECT min(states.attributes_id) AS min_90 
FROM states 
WHERE states.attributes_id = 8401078 UNION ALL SELECT min(states.attributes_id) AS min_91 
FROM states 
WHERE states.attributes_id = 8401079 UNION ALL SELECT min(states.attributes_id) AS min_92 
FROM states 
WHERE states.attributes_id = 8401081 UNION ALL SELECT min(states.attributes_id) AS min_93 
FROM states 
WHERE states.attributes_id = 8401082 UNION ALL SELECT min(states.attributes_id) AS min_94 
FROM states 
WHERE states.attributes_id = 8401083 UNION ALL SELECT min(states.attributes_id) AS min_95 
FROM states 
WHERE states.attributes_id = 8401084 UNION ALL SELECT min(states.attributes_id) AS min_96 
FROM states 
WHERE states.attributes_id = 8401085 UNION ALL SELECT min(states.attributes_id) AS min_97 
FROM states 
WHERE states.attributes_id = 8401086 UNION ALL SELECT min(states.attributes_id) AS min_98 
FROM states 
WHERE states.attributes_id = 8401087 UNION ALL SELECT min(states.attributes_id) AS min_99 
FROM states 
WHERE states.attributes_id = 8401088 UNION ALL SELECT min(states.attributes_id) AS min_100 
FROM states 
WHERE states.attributes_id = 8401089    0.000   
1335    service 172.30.33.6:37340   homeassistant   Query   0   starting    SHOW FULL PROCESSLIST   0.000   
1336    service 172.30.33.6:37356   phpmyadmin  Sleep   0       NULL    0.000

and the other one : 

SHOW ENGINE INNODB STATUS;

InnoDB      
=====================================
2023-02-04 11:42:43 0x7f8f995050 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 6 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 32 srv_active, 0 srv_shutdown, 269617 srv_idle
srv_master_thread log flush and writes: 269649
----------
SEMAPHORES
----------
------------
TRANSACTIONS
------------
Trx id counter 2282431
Purge done for trx's n:o < 2280000 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION (0x7f9c16f600), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 2280000, ACTIVE 2764 sec starting index read
mysql tables in use 100, locked 0
363 lock struct(s), heap size 57464, 60146 row lock(s), undo log entries 39920
MariaDB thread id 834, OS thread handle 548076286032, query id 551436 172.30.32.1 homeassistant Optimizing
SELECT min(states.attributes_id) AS min_1 
FROM states 
WHERE states.attributes_id = 8401395 UNION ALL SELECT min(states.attributes_id) AS min_2 
FROM states 
WHERE states.attributes_id = 8401396 UNION ALL SELECT min(states.attributes_id) AS min_3 
FROM states 
WHERE states.attributes_id = 8401397 UNION ALL SELECT min(states.attributes_id) AS min_4 
FROM states 
WHERE states.attributes_id = 8401398 UNION ALL SELECT min(states.attributes_id) AS min_5 
FROM states 
WHERE states.attributes_id = 8401399 UNION ALL SELECT min(states.attributes_id) AS min_6 
FROM states 
WHERE states.attributes_id = 
Trx read view will not see trx with id >= 2280000, sees < 2280000
---TRANSACTION (0x7f9c16de00), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION (0x7f9c16d200), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION (0x7f9c16c600), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION (0x7f9c16ba00), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION (0x7f9c16ae00), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
--------
FILE I/O
--------
Pending flushes (fsync) log: 0; buffer pool: 0
1682567 OS file reads, 2063473 OS file writes, 184417 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 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 105412011770
Log flushed up to   105412011770
Pages flushed up to 105397348062
Last checkpoint at  105397330275
0 pending log flushes, 0 pending chkp writes
58717 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 150994944
Dictionary memory allocated 924456
Buffer pool size   8112
Free buffers       0
Database pages     8109
Old database pages 2973
Modified db pages  4219
Percent of dirty pages(LRU & free pages): 52.022
Max dirty pages percent: 90.000
Pending reads 0
Pending writes: LRU 0, flush list 0
Pages made young 800846, not young 119933204
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1680491, created 183892, written 1967416
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: 8109, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
1 read views open inside InnoDB
Process ID=0, Main thread ID=0, state: sleeping
Number of rows inserted 352891, updated 12512778, deleted 2503239, read 112458765
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 1.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
============================

Thanks for your help !

bdraco commented 1 year ago

Thanks for that. It does look like it's a problem with the MariaDB optimizer.

I'll definitely need a complete dump to proceed with a solution

For the mysqldump, do you want the dump of the full database or just the schema ? (if so, how should I share it ?)

Dropbox nick@koston.org or Google drive bdraco@gmail.com

Cheers

helepeut commented 1 year ago

Thanks @bdraco ! I'm sorry to ask, but I can't find the right way to create the mysqldump, as I'm on HAOS. I'm using phpmyadmin to execute basic SQL queries + I have Terminal & SSH add-on, but I don't know how to create the correct dump. It's been a long time since I work on DBs, and I'm more comfortable on SQL Server ;-). Can you bring me on the right procedure ? Thanks !

bdraco commented 1 year ago

You can do it with the ssh addon

apk add mariadb-client
mysqldump --user=homeassistant -h core-mariadb -p homeassistant -q  | gzip - > /config/db.sql.gz

Enter the password and when it finishes the dump with be in /config/db.sql.gz

You'll need to download it from there.

helepeut commented 1 year ago

Hi @bdraco ,

Thanks a lot, very clear. This is exactly what I wasn't able to find regarding mysqldump. I sent you the db on GDrive.

I hope this will help the science and your pull request !

bdraco commented 1 year ago 
MariaDB [issue87352]> select count(*) from state_attributes;
+----------+
| count(*) |
+----------+
|  3273077 |
+----------+
1 row in set (0.359 sec)

WOW!

bdraco commented 1 year ago

Its all coming from something called Linky_Production_FPM3

bdraco commented 1 year ago

Please send a note to the dev that develops that integration

https://developers.home-assistant.io/docs/core/entity#generic-properties

DANGER Entities that generate a significant amount of state changes can quickly increase the size of the database when the extra_state_attributes also change frequently. Minimize the number of extra_state_attributes for these entities by removing non-critical attributes or creating additional sensor entities.

helepeut commented 1 year ago

Thanks @bdraco ! Yes, this is exactly the zigbee device for which I decided - too late - to remove all unused entities, changing the recorder filter, and just keep 2 of them. As far as I remember, I changed the Z2M config around January 6th. This is why I wanted to purge everything from this date, understanding that the DB was flooded.

helepeut commented 1 year ago

And Yes, i'll send a message to the creator of the device and also the Z2M owner. Thanks again !

helepeut commented 1 year ago

Just as a follow-up, I installed the 2022.2.3 version with your optimization, and my DB has now downcreased by 6 times, and the process is really quick. Thanks a lot !