Got error 5 during COMMIT error after large transaction from master

MySQL & MariaDB Percona XtraDB Cluster 5.x
1

Good day,

We have a Percona XtraDB cluster with 3 nodes replicating from a MySQL master VM instance that had a huge number of inserts from the application. There is another MySQL VM replicating from the master which is able to successfully apply the transaction and other MySQL VMs replicating from the replica are also able to apply the same. However, only the Percona XtraDB cluster was not able to successfully apply the transaction with the error below and replication is currently stopped.

I could not increase the value of wsrep_max_ws_size beyond 2GB which seems to be the suggested solution to fix the issue.

We’re getting ready to release the cluster to production and this is one of the issues that’s blocking us so any recommendations would be much appreciated.

 Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'xxxxxx' at master log mysql-bin.xxxx, end_log_pos xxxx. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.  

         CHANNEL_NAME:
            WORKER_ID: 1
            THREAD_ID: NULL
        SERVICE_STATE: OFF
LAST_SEEN_TRANSACTION: xxxx
    LAST_ERROR_NUMBER: 1180
   LAST_ERROR_MESSAGE: Worker 1 failed executing transaction 'xxxx' at master log mysql-bin.xxxx, end_log_pos xxxx; Got error 5 during COMMIT
 LAST_ERROR_TIMESTAMP: 2021-06-11 23:17:45
1 Like
2

@Ireyes

Large transactions are problematic for Percona XtraDB Cluster.
The most appropriate solution I can think of is to divide your insert transactions into chunk, where each chunk would insert only 10000 rows or so (or adjust size to fit into 2GB)