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]