更新时间:2024-09-06 GMT+08:00

大事务检测能力

众所周知,大事务的存在对实例的健康平稳运行有一些影响,典型场景如大事务的回滚时间很长,会导致升级、规格变更时间变长。GaussDB(for MySQL)提供了大事务检测的能力,当出现大事务,可以通过告警通知客户及时提交。

前提条件

  • 内核2.0.39.230300及以上版本支持该功能。
  • 需要根据以下条件设置对应的参数值为“ON”
    • 内核版本小于2.0.45.230900时,需要确认“log-bin”参数值为“ON”
    • 内核版本大于或等于2.0.45.230900时,需要确认“rds_global_sql_log_bin”参数值为“ON”

使用方法

  1. 增加系统变量“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):

    mysql> 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)
  2. 查看当前连接的事务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)
  3. 查看当前所有连接的事务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)