Migrated from 10.1.22 to 10.6.7 and queries get stuck unless we manually analyze table.

TLDR: Migrated from 10.1.22 to 10.6.7 and queries get stuck unless we manually analyze table.

We have a database with a fairly heavy workload. It tracks all stock movements for hundreds of shops every day. We calculate stock holding which involves getting the current days information and joining on pricing information. So you can imagine thats a fairly chunky join. Until recently this was running on a modest VM with spinning rusts disks based on Ubuntu 16.04. MariaDB 10.1.22. Fairly old in a cluster config (although the other nodes were dead at this point).

We have migrated to MariaDB 10.6.7 on Ubuntu 22.04 and rebuilt the cluster. We did this by stopping the old one, rsycning the data and then starting the new one which then upgraded the tables etc. No other combination of replication would quite stretch between so many versions. The database works well and now has very wide IO bandwidth.

Since the migration that query above and similar sometimes stop working - or don't return so we have to abort them. I let some run up to 6000 seconds when the normally return in no more than 100 seconds. Running Analyze then restores them to running in a few seconds. So I thought maybe it was a quirk of the migration and stale indexes etc. But a week later same again then 3 days later same again. I did my best to migrate the config as was from the old one but I'm wondering if some setting is interfering with housekeeping optimisation tasks or if the newer version isn't working correctly. Obviously googled around but not really certain what I'm looking for except to know that Analyze fixes the problem so its definitely something to do with optimization.

Server config included in the hope that something jumps out (this largely came from the old server which I didn't set up):

[server]

[mysqld]

basedir                 = /usr
bind-address            = 0.0.0.0
bulk_insert_buffer_size = 16M
character-set-server    = utf8mb4
collation-server        = utf8mb4_general_ci
connect_timeout         = 5
datadir                 = /var/lib/mysql
default_storage_engine  = InnoDB
expire_logs_days        = 3
group_concat_max_len    = 1000000

innodb_buffer_pool_instances    = 5
innodb_buffer_pool_size         =  5G
innodb_change_buffer_max_size   = 50
innodb_file_format      = Barracuda
innodb_file_per_table   = 1
innodb_flush_method     = O_DIRECT
innodb_io_capacity      = 400
innodb_large_prefix     = on
innodb_log_buffer_size  = 1G
innodb_log_file_size    = 640M
innodb_log_files_in_group       = 2
innodb_open_files       = 400
innodb_read_io_threads  = 16
innodb_thread_concurrency       = 8
innodb_write_io_threads = 16

join_buffer_size        = 2M
#key_buffer_size         = 16M
lc-messages-dir         = /usr/share/mysql
log_bin                 = /var/log/mysql/mariadb-bin
log_bin_index           = /var/log/mysql/mariadb-bin.index
log_error               = /var/log/mysql/error.log
# https://docs.rackspace.com/support/how-to/mariadb-error-log-configuration/
log_warnings            = 1  

max_allowed_packet      = 256M
max_binlog_size         = 512M
max_connections         = 400
max_heap_table_size     = 64M
myisam_recover_options  = BACKUP
performance_schema      = on
pid-file                = /var/run/mysqld/mysqld.pid
port                    = 3306
query_cache_limit       = 128K
query_cache_size        = 64M
skip-external-locking
skip-name-resolve       = 1
socket                  =  /var/run/mysqld/mysqld.sock
sort_buffer_size        =  4M
table_definition_cache  = 16384
table_open_cache        = 16384
thread_cache_size       = 128
thread_cache_size       = 8
thread_stack            = 192K
tmp_table_size          = 256M
tmpdir                  = /tmp
user                    = mysql
userstat                = 1
wait_timeout            = 600

[embedded]

[mariadb]
plugin_load_add         = ha_federatedx
[mariadb-10.1]