Diagnosis on Large Transactions

Prerequisites

• The kernel version is 2.0.39.230300 or later.
• The related parameter is configured based on the following conditions:
  • If the kernel version is earlier than 2.0.45.230900, set the value of log-bin is ON.
  • If the kernel version is 2.0.45.230900 or later, set the value of rds_global_sql_log_bin to ON.

Usage

1. Configure the parameter rds_warn_max_binlog_cache_size as required.

   Table 1 Parameter description

   Parameter	Level	Description
   rds_warn_max_binlog_cache_size	global	Controls the maximum binlog cache size for a transaction. If the size in a transaction exceeds the parameter value, a WARNING message is reported. Default value: 18446744073709547520 Value range: 4096 to 18446744073709547520

   To prevent multiple WARNING messages from being sent to the client, a WARNING message can be sent to the client once for each statement in a transaction.

   In this example, rds_warn_max_binlog_cache_size is set to 40960 (40 KB).

   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. Check the binlog cache size of the transactions in the current connection.

   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. Check the binlog cache size of transactions in all connections.

   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)