MariaDB 10.5.13 , galera provider 26.4.10 ,FreeBSD 12 and 13 . ZFS storage, 1500G RAM , 64 or 96 Cores

From which version the upgrade was done? Can you please share the configuration file(s)?

Hello ,

Versions history is as follows:

MySQL 5.7
Migration to MariaDB 10.5.9 via mysqldump && import
Upgrade to MariaDB 10.5.12_1 - version described as innodb corruption bug fixed
Upgrade to MariaDB 10.5.13

Configuration files are quit different now. I will try to restore the version from Nov 2021.

Cheers
Rumen

valerii I would need show processlist and show engine innodb status outputs and full error log on all nodes.

ramesh Can you test this using galapq (yes no FreeBSD) so that we know is this FreeBSD only problem.

jplindst julien.fritsch Could not reproduce this issue on galapq (ubuntu) server.

We would need:

• show processlist ouput when node is hang state
• show engine innodb status when node is hang state
• more detailed instructions how to reproduce is needed
• full error logs

We could not reproduce the issue. This could be FreeBSD only issue.

valerii Full backtrace from 10.6ES is not very useful to analyze problem on 10.5CS. Maybe better to escalate to Codership.

Hello there,

We do have the same issue happening on a 3-nodes MariaDB Galera Cluster running 10.4.24 on Debian 10.

The SQL workload is fully load balanced between the 3 nodes, the average metrics during workday are (on each node) :

• selects : ~4000/s
• updates : ~200/s
• inserts : ~80/s
• deletes : < 1/s

The issue mostly happens with 2 or 3 INSERT transactions on the same table (with different values). The table is about 43M rows with 31GB of datas and 20GB of index.

When it happens, a graceful stop of "bad" node does unblock the whole cluster but it always needs some SIGKILL to be able to restart the service. Usually the restarted node does sync with IST, sometimes it needs a SST (whereas the configured 1 GB cache should cover about 3 hours of downtime...).

We keep track of each incident with full processlist on JSON format + mysqld log (with "WSREP: BF lock wait long for" messages). How can we privately send you these informations that might be useful to troubleshoot the issue ?

Hi Mathieu CAUSERO,

Backtrace of all threads taken with gdb when node hangs would be a good start. I think it can be shared in public as it does not contain any really sensitive information (please, review and check if in doubts before attaching).

mathieucausero I have few questions. Do you have primary key on all tables affected? Do you have foreign keys between tables affected? Do you use auto increment as primary key column and if you do, do you set different offset for every node?

@Jan Lindström to answer your few questions :

• primary key : the table mainly involved by these freezes has a composed primary key (for an unkown reason) -> PRIMARY KEY (`IDappels`,`identifiantProprietaire`)
• no foreign key on these tables
• auto increment is set on the primary key (well on the previous example on IDappels)

From what I see on our customer's database, every impacted table (= every table that have had requests involved in cluster deadlock) is made like this.

Would that composed PK be an issue ?

To add a bit more context, the most impacted table is made of 68M rows with 36GB of datas. It's taken dozens of INSERT per second from 3 galera nodes (all are used as writers). Servers are all baremetal servers with 128GB of RAM (InnoDB buffer pool is bigger than the datas+index size), NVMe storage, 2 Gbit/s private network with MTU 9000.

There has been significant fixes on Galera conflict resolution and multi-master execution. I suggest trying more recent version of MariaDB and Galera library.