How Big Can Your Galera with MariaDB Transactions Be

先說結論 Ref: wsrep_max_ws_size 官方文件寫得死死了 ; 看到 permitted the maximum to be set beyond 2GB, which was rejected by Galera.
就又再更心死了一次

2147483647 (2GB, >= MariaDB Galera 10.0.27, MariaDB 10.1.17)
1073741824 (1GB, <= MariaDB Galera 10.0.26, MariaDB 10.1.16)

為什麼這次要研究這個問題 ; 因為我們需要討論的是一大包 Transactions 的合理性，

改不改得動是商務邏輯的事，但從架構面能不能解決 ERROR 1180 看起來還是有點辦法的

MariaDB [test]> COMMIT;
ERROR 1180 (HY000): Got error 5 "Input/output error" during COMMIT

基於 Percona 對 MySQL 做的測試在這可以參考，我們這次的測試標的基本資訊如下

[root@localhost ~]# cat /etc/redhat-release
CentOS Linux release 7.9.2009 (Core)
[root@localhost ~]# mysql -e "\s" | grep -i "Server version"
Server version:         10.4.15-MariaDB-log MariaDB Server
[root@localhost ~]# mysql -e "show status like '%wsrep%'" | grep 'wsrep_local_state_comment\|wsrep_cluster_status\|wsrep_ready'
wsrep_local_state_comment       Synced
wsrep_cluster_status    Primary
wsrep_ready     ON
[root@localhost ~]# mysql -e "select @@wsrep_max_ws_size" # 因為需要容易觸發的條件，所以就把設定最小值帶上
+---------------------+
| @@wsrep_max_ws_size |
+---------------------+
|                1024 |
+---------------------+
[root@localhost ~]# mysql -e "select @@binlog_row_image"
+--------------------+
| @@binlog_row_image |
+--------------------+
| FULL               |
+--------------------+

測試經過語法 & schema 如下

CREATE TABLE t (
    a bigint primary key DEFAULT UUID_SHORT(),
    b text,
    d text default "123",
    e text default "123",
    f text default "123",
    g text default "123"
);

SET autocommit=0;
START TRANSACTION;
-- binlog_row_image = FULL 的情況下 b 僅能指定 300 個字元，再上去就 ERROR 1180
UPDATE t SET b='xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx';
COMMIT;

-- 錯誤訊息就不另贅述了
MariaDB [test]> COMMIT;
ERROR 1180 (HY000): Got error 5 "Input/output error" during COMMIT

此時 COMMIT 成功的 binlog 長這樣，不相關的欄位也被一起帶進來了，所以合理推測 Transactions 內容也會把其他 column 帶入，如果是 blob / text 欄位的話

那踩到 wsrep_max_ws_size 上限也就合理了

#220220 20:19:55 server id 1  end_log_pos 4434 CRC32 0x68307979         Update_rows: table id 25 flags: STMT_END_F

BINLOG '
6zESYhMBAAAANgAAANAOAAAAABkAAAAAAAEABHRlc3QAAXQABgj8/Pz8/AUCAgICAj7xstL3
6zESYhgBAAAAggIAAFIRAAAAABkAAAAAAAEABv//wAEAANAREmIB9gB4eHh4eHh4eHh4eHh4eHh4
eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4
eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4
eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4
eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4
eHgDADEyMwMAMTIzAwAxMjMDADEyM8ABAADQERJiASwBeHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4
eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4
eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4
eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4
eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4
eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4AwAxMjMDADEy
MwMAMTIzAwAxMjN5eTBo
'/*!*/;
### UPDATE `test`.`t`
### WHERE
###   @1=99662009468977153
###   @2='123'
###   @3='123'
###   @4='123'
###   @5='123'
###   @6='123'
### SET
###   @1=99662009468977153
###   @2='xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
###   @3='123'
###   @4='123'
###   @5='123'
###   @6='123'
# Number of rows: 1
# at 4434
#220220 20:19:55 server id 1  end_log_pos 4465 CRC32 0x6dd7ab45         Xid = 11
COMMIT/*!*/;
# at 4465
#220220 20:22:56 server id 1  end_log_pos 4488 CRC32 0x8b4370c7         Stop
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

所以應該用 binlog_row_image=minimal 解

[root@localhost ~]# mysql -e "select @@wsrep_max_ws_size"
+---------------------+
| @@wsrep_max_ws_size |
+---------------------+
|                1024 |
+---------------------+
[root@localhost ~]# mysql -e "select @@binlog_row_image"
+--------------------+
| @@binlog_row_image |
+--------------------+
| MINIMAL            |
+--------------------+

[root@localhost ~]# mysql test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 22
Server version: 10.4.15-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [test]> SET autocommit=0;
Query OK, 0 rows affected (0.001 sec)

MariaDB [test]> START TRANSACTION;
Query OK, 0 rows affected (0.000 sec)

-- binlog_row_image = MINIMAL 的情況下 b 僅能指定 448 個字元，再上去就 ERROR 1180

MariaDB [test]> UPDATE t SET b='xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx';
Query OK, 0 rows affected (0.001 sec)
Rows matched: 1  Changed: 0  Warnings: 0

MariaDB [test]> COMMIT;
Query OK, 0 rows affected (0.000 sec)

此時 binlog 狀態長這樣

#220220 20:49:28 server id 1  end_log_pos 2106 CRC32 0x118f42b9         Update_rows: table id 25 flags: STMT_END_F

BINLOG '
2DgSYhMBAAAANgAAAEwGAAAAABkAAAAAAAEABHRlc3QAAXQABgj8/Pz8/AUCAgICAj5x9VP7
2DgSYhgBAAAA7gEAADoIAAAAABkAAAAAAAEABgEC/gAAAMM3EmIB/sABeHh4eHh4eHh4eHh4eHh4
eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4
eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4
eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4
eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4
eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4
eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4
eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4
eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eLlCjxE=
'/*!*/;
### UPDATE `test`.`t`
### WHERE
###   @1=99662172459630592
### SET
###   @2='xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
# Number of rows: 1
# at 2106
#220220 20:49:28 server id 1  end_log_pos 2137 CRC32 0x3ffe0ba5         Xid = 27
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

Transactions 內容是多了點沒錯，但原則上還是應急用考量

畢竟 binlog_row_image 不在 FULL 條件下的 Point-in-time recovery (PITR) 也不好實作

畢竟一個 2GB 的 Transactions 種種考量下要 roll back 也不容易。

所以還是建議拆細 Transactions 粒度才是最佳解。