Help Center/ TaurusDB/ Kernel/ Transaction Optimization/ Diagnosis on Large Transactions
Updated on 2026-01-15 GMT+08:00
View PDF
Share

Diagnosis on Large Transactions

Scenarios

Large transactions affect the health and stability of DB instances. For example, lengthy rollbacks of large transactions often prolong upgrade and specification change processes. TaurusDB provides diagnosis for large transactions. When there is a large transaction, an alarm is generated to notify you to commit the transaction in a timely manner.

Prerequisites

  • The kernel version must be 2.0.39.230300 or later. For details about how to check the kernel version, see How Can I Check the Version of a TaurusDB Instance?
  • 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.

How to Use

You can use system variables or run SQL statements to check whether there are large transactions.

Diagnosing Large Transactions Using System Variables

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).

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)

Diagnosing Large Transactions by Running SQL Statements

  • 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)
  • 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)
Parent Topic: Transaction Optimization