ERROR 1180 (HY000): Got error 5 "Input/output error" during COMMIT

最新推荐文章于 2023-04-07 23:05:15 发布

lwei_998

最新推荐文章于 2023-04-07 23:05:15 发布

阅读量3.2k

点赞数

分类专栏： MariaDB Galera Cluster

本文链接：https://blog.csdn.net/lwei_998/article/details/80434028

版权

MariaDB Galera Cluster 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

MariaDB [(none)]> select * from information_schema.GLOBAL_status where variable_name in ('wsrep_provider_name','wsrep_provider_version') union all select * from information_schema.GLOBAL_VARIABLES where variable_name in ('version','innodb_version') ;
+------------------------+-----------------------+
| VARIABLE_NAME          | VARIABLE_VALUE        |
+------------------------+-----------------------+
| WSREP_PROVIDER_NAME    | Galera                |
| WSREP_PROVIDER_VERSION | 25.3.23(r3789)        |
| VERSION                | 10.0.35-MariaDB-wsrep |
| INNODB_VERSION         | 5.6.39-83.1           |
+------------------------+-----------------------+
4 rows in set (0.00 sec)

一个业务插入数据时，报如下错误

MariaDB [test]> insert into mgc(c1,c2)  select c1,c2 from mgc;               
**ERROR 1180 (HY000): Got error 5 "Input/output error" during COMMIT**

错误日志中出现如下错误：

[Warning] WSREP: transaction size limit (65536) exceeded: 98304
[ERROR] WSREP: Failed to write buffer of length 0 to '/data/GRA_2465_-1.log'
[ERROR] WSREP: rbr write fail, data_len: 0, 2

由于在Galera Cluster集群中大的事务会影响整个集群的性能，因此我们限制了单个事务的大小。
调整repl.max_ws_size为128M后，sql能正常执行。

MariaDB [test]> set global wsrep_provider_options="repl.max_ws_size=134217728";
Query OK, 0 rows affected (0.02 sec)

MariaDB [test]> insert into mgc(c1,c2)  select c1,c2 from mgc;
Query OK, 5242880 rows affected (3 min 15.78 sec)
Records: 5242880  Duplicates: 0  Warnings: 0

日志中出现创建gcache.page。说明gcache不够用了。
[Note] WSREP: Created page /data/gcache.page.000000 of size 134217728 bytes

关于wsrep_max_ws_size的含义参考：
https://mariadb.com/kb/en/library/galera-cluster-system-variables/#wsrep_max_ws_size

Description: Maximum permitted size in bytes per writeset. Writesets exceeding this will be rejected. Note that versions from and before MariaDB 10.1.17 and MariaDB Galera 10.0.27 permitted the maximum to be set beyond 2GB, which was rejected by Galera.
Commandline: --wsrep-max-ws-size=#
Scope: Global
Dynamic: Yes
Data Type: Numeric
Default Value:
2147483647 (2GB, >= MariaDB Galera 10.0.27, MariaDB 10.1.17)
1073741824 (1GB, <= MariaDB Galera 10.0.26, MariaDB 10.1.16)
Range: 1024 to 2147483647