I want to raise attention of the combination of this bug with a Galera cluster: most of the time this bug takes out ALL nodes of a Galera cluster

We had this on our geo-distributed 5 node Galera cluster which we recently updated to 10.5.12 this morning for the 3rd time in 4 weeks.

What it causes on Galera is, that all nodes accumulate more and more queries until the cluster becomes completely unresponsive for updating queries. Trying to kill the queries wont work, they are stuck in state "killed" and the situation does not improve.

Only way we found so far out of that situation is to completely shut down the cluster / all nodes and restart them one by one with an SST.

I'm attaching the log of one of our nodes, in case someone want's to check it (I had to X out the database names, as they are customer names).

Is there a date for 10.5.13, or do we need to downgrade to 10.4, to fix the problem?

Ralf

mysql-0-20211028.log

I forgot t mention that we already used innodb_flush_method=O_DIRECT and after the 2nd incident and finding the bug report also set innodb_log_file_size=innodb_buffer_cache_size=8GB.

The above did NOT prevent having the described issue with a Galera cluster!

Ralf

Hi,

Encountered this issue even with MariaDB 10.5.13 Enterprise Edition.

2022-01-11 8:40:52 0 [Note] InnoDB: 10.5.13 started; log sequence number 361848522653; transaction id 405336
2022-01-11 8:40:52 0 [Note] InnoDB: Creating #1 encryption thread id 140471212390144 total threads 4.
2022-01-11 8:40:52 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2022-01-11 8:40:52 0 [Note] InnoDB: Creating #2 encryption thread id 140471103624960 total threads 4.
2022-01-11 8:40:52 0 [Note] InnoDB: Creating #3 encryption thread id 140471095232256 total threads 4.
2022-01-11 8:40:52 0 [Note] InnoDB: Creating #4 encryption thread id 140471086839552 total threads 4.
2022-01-11 8:40:52 0 [Note] Plugin 'FEEDBACK' is disabled.
2022-01-11 8:40:52 0 [Note] Using encryption key id 2 for temporary files
2022-01-11 8:40:52 0 [Note] InnoDB: Buffer pool(s) load completed at 220111 8:40:52
2022-01-11 8:40:52 0 [Warning] Failed to create a socket for IPv6 '::': errno: 97.
2022-01-11 8:40:52 0 [Note] Server socket created on IP: '0.0.0.0'.
2022-01-11 8:40:52 0 [Warning] 'proxies_priv' entry '@% root@xxxxxxx' ignored in --skip-name-resolve mode.
2022-01-11 8:40:52 0 [Note] Reading of all Master_info entries succeeded
2022-01-11 8:40:52 0 [Note] Added new Master_info '' to hash table
2022-01-11 8:40:52 0 [Note] mysqld: ready for connections.
Version: '10.5.13-9-MariaDB-enterprise-log' socket: '/var/run/mysqld/mysqld.sock' port: 53805 MariaDB Enterprise Server
2022-01-11 8:57:51 21 [Note] Start binlog_dump to slave_server(52396), pos(mysqld-bin.000001, 369), using_gtid(0), gtid('')
2022-01-11 9:10:35 22 [Note] InnoDB: Cannot close file ./ownbfmprd/stf_stat_availability_office_avl_hist.ibd because of 206 pending operations and pending fsync
2022-01-11 9:13:40 22 [Note] InnoDB: Cannot close file ./ownbfmprd/stf_stat_availability_office_bp_hist.ibd because of pending fsync
2022-01-11 9:14:42 22 [Note] InnoDB: Cannot close file ./ownbfmprd/fallback_stf_stat_availability_office_xs#P#p736782.ibd because of 1 pending operations
2022-01-11 9:23:08 22 [Note] InnoDB: Cannot close file ./ownbfmprd/stf_stat_availability_office_crystd_hist.ibd because of pending fsync
2022-01-11 9:25:07 22 [Note] InnoDB: Cannot close file ./ownbfmprd/stf_stat_availability_office_ediavl_hist.ibd because of 222 pending operations and pending fsync
2022-01-11 9:27:11 22 [Note] InnoDB: Cannot close file ./ownbfmprd/stf_stat_availability_office_edistd_hist.ibd because of pending fsync
2022-01-11 9:27:24 22 [Note] InnoDB: Cannot close file ./ownbfmprd/stf_stat_availability_ab_targetoffice_mp#P#p738529.ibd because of 1 pending operations
2022-01-11 9:27:33 22 [Note] InnoDB: Cannot close file ./ownbfmprd/stf_stat_availability_office_bench_met.ibd because of 1 pending operations
2022-01-11 9:28:39 22 [Note] InnoDB: Cannot close file ./ownbfmprd/stf_stat_availability_office_excluded_hist.ibd because of pending fsync
2022-01-11 9:29:35 22 [Note] InnoDB: Cannot close file ./ownbfmprd/stf_stat_availability_office_excluded_lfs_hist.ibd because of 146 pending operations and pending fsync
2022-01-11 9:31:17 22 [Note] InnoDB: Cannot close file ./ownbfmprd/stf_stat_availability_office_fop_hist.ibd because of pending fsync
2022-01-11 9:32:38 22 [Note] InnoDB: Cannot close file ./ownbfmprd/stf_stat_availability_office_fp_hist.ibd because of pending fsync
2022-01-11 10:57:54 22 [Note] InnoDB: Cannot close file ./ownbfmprd/stf_stat_availability_office_mp_hist.ibd because of pending fsync

gopinathkarangula, what you encountered is not the same as the originally reported issue, where the server error log was flooded with such messages. The rate-limiting seems to work for you. Please file a new ticket for this problem if you think that the configuration is reasonable.

ralfbecker, by innodb_buffer_cache_size I assume that you mean innodb_buffer_pool_size. Does 10.5.13 or 10.6.5 work for you?

@Marko Mäkelä: yes, innodb_buffer_pool_size, but it seem to have no effect on the issue we had.
Since the update to 10.5.13 it has never appeared again - so far.

Ralf