Issue with Waiting for schema metadata lock

[tha...@gmail.com]

I am upgrading from 10.5 to 10.11 and I am having an issue importing databases. Previously I had an issue where I was running our of free blocks so I increased the innodb_buffer_pool_size to match my old servers recommended amount

 InnoDB: Could not free any blocks in the buffer pool! 519168 blocks are in use and 0 free. Consider increasing innodb_buffer_pool_size.

Then I ran into an issue where all of my nodes were collapsing from memory exhaustion, but databases would still import until nodes started crashing due to exhaustion, so I upped the memory (they all have a crap load more than needed, 100G)

Now I am running into an issue where the first database wont even finish, it just gets stuck waiting for schema metadata lock. The only reason it moved onto matomo was because I killed a 547, though the process is still showing

+------+-------------+-----------+----------------+---------+------+----------------------------------+------------------------------------------------------------------------------------------------------+----------+
| Id   | User        | Host      | db             | Command | Time | State                            | Info                                                                                                 | Progress |
+------+-------------+-----------+----------------+---------+------+----------------------------------+------------------------------------------------------------------------------------------------------+----------+
|    1 | system user |           | NULL           | Sleep   | 9117 | wsrep aborter idle               | NULL                                                                                                 |    0.000 |
|    2 | system user |           |                | Sleep   | 7102 | Waiting for schema metadata lock | alter database `citation_index` CHARACTER SET utf8                                                   |    0.000 |
|  547 | root        | localhost | citation_index | Killed  | 7102 | Freeing items                    | INSERT INTO `vectors` VALUES (1033886,'[0.039515189826488495,-0.03698547184467316,0.0510983131825923 |    0.000 |
| 1391 | root        | localhost | NULL           | Sleep   | 1235 |                                  | NULL                                                                                                 |    0.000 |
| 1471 | root        | localhost | matomo         | Query   | 3683 | Waiting to execute in isolation  | DROP TABLE IF EXISTS `matomo_access`                                                                 |    0.000 |
| 1664 | root        | localhost | mysql          | Query   | 3153 | Waiting to execute in isolation  | ALTER USER 'haproxy_check'@'%' IDENTIFIED WITH mysql_native_password AS ''                           |    0.000 |
| 2295 | root        | localhost | NULL           | Query   |    0 | starting                         | show processlist                                                                                     |    0.000 |
+------+-------------+-----------+----------------+---------+------+----------------------------------+------------------------------------------------------------------------------------------------------+----------+

MariaDB [(none)]> SHOW ENGINE INNODB STATUS \G
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
2024-05-31 13:16:18 0x7fef98830640 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 55 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 27 srv_active, 0 srv_shutdown, 9143 srv_idle
srv_master_thread log flush and writes: 9170
----------
SEMAPHORES
----------
------------
TRANSACTIONS
------------
Trx id counter 967581
Purge done for trx's n:o < 967579 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION (0x7fef9ac3e680), not started
mysql tables in use 1, locked 1
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION (0x7fef9ac3db80), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
--------
FILE I/O
--------
Pending flushes (fsync): 0
18246 OS file reads, 914823 OS file writes, 14422 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
---
LOG
---
Log sequence number 481909696683
Log flushed up to   481909696683
Pages flushed up to 481137735617
Last checkpoint at  481137735617
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 153578635264
Dictionary memory allocated 911765208
Buffer pool size   9280960
Free buffers       8297736
Database pages     983224
Old database pages 362967
Modified db pages  70517
Percent of dirty pages(LRU & free pages): 0.760
Max dirty pages percent: 90.000
Pending reads 0
Pending writes: LRU 0, flush list 0
Pages made young 168, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 18112, created 965442, written 894945
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: 983224, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 read views open inside InnoDB
state: sleeping
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set (0.001 sec)