A few weeks ago, we received an interesting Galera Cluster support case from one of our customers that the application is not working well and they face a lot of troubles in their Galera Cluster setup.
After some investigations, we found a lot of insert queries in state "query end" and lasting for long time without being completed. Also some other queries which were sleeping for long time having the info of "XA COMMIT":
SQL> SHOW PROCESSLIST;
27 user host:33214 foodmart Query 14440 sleeping XA COMMIT 0x31302e31312e31332e34372e746d30303336383030303031,0x31302e31312e31332e34372e746d333638,0xSQL> SHOW ENGINE INNODB STATUS;
TRANSACTIONS
============
---TRANSACTION 2DE71D, ACTIVE 14459 sec
9 lock struct(s), heap size 1248, 1 row lock(s), undo log entries 115
MySQL thread id 27, OS thread handle 0x7fc21a42c700, query id 96187 host host-ip foodmart …