MariaDB Server 10.6.7 stuck on INSERT query after computer resumes from sleep

Question

I have a recent updated MariaDB installation that suddenly hangs on a every-minute-cronjob after my computer resumes from sleep. Configuration worked fine before latest apt upgrade.

Process list shows an insert query from a ~100 insert batch operation.

I tried cancelling all running scripts before sending the computer to sleep, but at the next wakeup it will still fail. I have to kill -9 the database to even get it to restart.

Id? User? Host? db? Command? Time? State? Info? Progress? 52 xxxxxx localhost xxxxxx Execute 4 Update INSERT INTO xxxxxxx

and it will never finish. This also happens if i wait ~30 seconds after wakeup and start the script manually, so i kinda guess MariaDB handles sleep/resume state not too well.

Is this a known bug? Any way around having my PC run 24/7 to get around this?

Server variables too much for this post, only changed

innodb_buffer_pool_size = 64G

innodb_log_file_size = 16G

As suggested, i checked SHOW ENGINE INNODB STATUS while the lock is happening, cannot post this as a comment due to length

-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 0 srv_active, 0 srv_shutdown, 4664 srv_idle
srv_master_thread log flush and writes: 4663
----------
SEMAPHORES
----------
------------
TRANSACTIONS
------------
Trx id counter 314781977
Purge done for trx's n:o < 314781976 undo n:o < 0 state: running
History list length 142
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 314781976, ACTIVE 106 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1128, 1 row lock(s), undo log entries 1
MariaDB thread id 57, OS thread handle 140131630528064, query id 273212 localhost xxx Update
INSERT INTO xyzxyz (`xyz`, `xyz`, `time`, `xyz`, `xyz`) VALUES (?,?,?,?,?)
--------
FILE I/O
--------
Pending flushes (fsync) log: 0; buffer pool: 0
19307 OS file reads, 24298 OS file writes, 24302 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 932, free list len 2162, seg size 3095, 1 merges
merged operations:
 insert 11, 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 44207022268
Log flushed up to   44207022268
Pages flushed up to 44197540796
Last checkpoint at  44197540784
0 pending log flushes, 0 pending chkp writes
24306 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 68753031168
Dictionary memory allocated 424873192
Buffer pool size   4153344
Free buffers       4133333
Database pages     20011
Old database pages 7367
Modified db pages  3024
Percent of dirty pages(LRU & free pages): 0.073
Max dirty pages percent: 90.000
Pending reads 1
Pending writes: LRU 0, flush list 0
Pages made young 32, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 19243, created 741, written 0
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 20011, unzip_LRU len: 0
I/O sum[0]: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 24161, updated 113, deleted 0, read 623966
0.00 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
============================

I checked whether it's a deadlock due to a bad update-query, but it's and INSERT-query with State update which just means mariadb is about to update the table (but never does). I use mariadb 10.6.7 on debian testing - i may need to use a stable release to sort this out. Sleeping happens from seconds (still happening) to hours.

Answer 1

I tried:

Removed PRIMARY and UNIQUE-key with the same indizes. Same problem

Change innodb_flush_log_at_trx_commit to zero to not have it update log after every insert - still stuck on first insert.