# 大事务检测能力
#### 操作场景
大事务的存在会对实例的健康平稳运行产生一定的影响，典型场景如大事务的回滚时间很长，会导致升级、规格变更时间变长。TaurusDB提供了大事务检测的能力，当出现大事务，可以通过告警通知客户及时提交。
#### 前提条件
- 内核2.0.39.230300及以上版本支持该功能。内核版本的查询方法请参见[如何查看云数据库 TaurusDB实例的版本号](https://support.huaweicloud.com/taurusdb_faq/taurusdb_faq_0141.html)。
- 需要根据以下条件设置对应的参数值为"ON"。
  - 内核版本小于2.0.45.230900时，需要确认"log-bin"参数值为"ON"。
  
  - 内核版本大于或等于2.0.45.230900时，需要确认"rds_global_sql_log_bin"参数值为"ON"。
   
 
#### 使用方法
可以通过系统变量或执行SQL语句查询是否有大事务存在。
#### 通过系统变量检测大事务
增加系统变量"rds_warn_max_binlog_cache_size"检测大事务。
表1参数说明 
| **参数名称**                       | **级别** | **描述**                                                                                                                                                                                           |
|:---|:---|:---|
| rds_warn_max_binlog_cache_size | global | 当大事务产生的Binlog超过该值时，则报WARNING提示客户。 默认值为：18446744073709547520 取值范围为：\[4096，18446744073709547520\] |
   
事务产生的Binlog大小超过rds_warn_max_binlog_cache_size时，为了提醒客户及时提交事务，系统会报WARNING到客户端。为了避免给客户端发送多个WARNING，限制事务中每个statement发送一次这个WARNING给客户端。
当配置rds_warn_max_binlog_cache_size为40KB(40960)：
```
CREATE TABLE t1 (
    -> a longtext
    -> ) DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.12 sec)
mysql> show variables like 'rds_warn_max_binlog_cache_size';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| rds_warn_max_binlog_cache_size | 40960 |
+--------------------------------+-------+
1 row in set (0.01 sec)
```
```
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO t1 VALUES (REPEAT('a',20000));
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO t1 VALUES (REPEAT('a',20000));
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO t1 VALUES (REPEAT('a',20000));
Query OK, 1 row affected, 1 warning (0.00 sec)
```
```
mysql> show warnings;
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                             |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 4008 | Recommend you to INSERT/UPDATE/DELETE rows in batches by multiple transactions. The current transaction required more than 'rds_warn_max_binlog_cache_size' (40960) bytes of storage. Which shall cause replication latency. Please commit it. |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
```
```
mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.01 sec)
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.01 sec)
```
```
mysql> INSERT INTO t1 VALUES (REPEAT('a',50000));
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> show warnings;
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message           |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 4008 | Recommend you to INSERT/UPDATE/DELETE rows in batches by multiple transactions. The current transaction required more than 'rds_warn_max_binlog_cache_size' (40960) bytes of storage. Which shall cause replication latency. |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
```
#### 通过执行SQL查询大事务
- 查看当前连接的事务Binlog cache大小。
  ```
  mysql> CREATE TABLE t1 (
  ->   a longtext
  -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  Query OK, 0 rows affected (0.10 sec)
  mysql> SHOW STATUS LIKE 'Rds_binlog_trx_cache_size';
  +---------------------------+-------+
  | Variable_name             | Value |
  +---------------------------+-------+
  | Rds_binlog_trx_cache_size | 0     |
  +---------------------------+-------+
  1 row in set (0.04 sec)
  mysql> begin;
  Query OK, 0 rows affected (0.00 sec)
  mysql> INSERT INTO t1 VALUES (REPEAT('a',20000));
  Query OK, 1 row affected (0.01 sec)
  mysql> SHOW STATUS LIKE 'Rds_binlog_trx_cache_size';
  +---------------------------+-------+
  | Variable_name             | Value |
  +---------------------------+-------+
  | Rds_binlog_trx_cache_size | 20150 |
  +---------------------------+-------+
  1 row in set (0.05 sec)
  mysql> commit;
  Query OK, 0 rows affected (0.00 sec)
  mysql> SHOW STATUS LIKE 'Rds_binlog_trx_cache_size';
  +---------------------------+-------+
  | Variable_name             | Value |
  +---------------------------+-------+
  | Rds_binlog_trx_cache_size | 0     |
  +---------------------------+-------+
  1 row in set (0.09 sec)
  ```
  
- 查看当前所有连接的事务Binlog cache大小。
  ```
  mysql> SHOW GLOBAL STATUS LIKE 'rds_binlog_trx_cache_size';
  +---------------------------+-------+
  | Variable_name             | Value |
  +---------------------------+-------+
  | Rds_binlog_trx_cache_size | 40300 |
  +---------------------------+-------+
  1 row in set (0.05 sec)
  ```