Large Transaction Replication

Download

Focus Mode

Font Size

Last updated: 2026-09-24 14:14:30

AI-Translated & Reviewed

Feature Introduction
In row mode, a large transaction that updates multiple rows with a single statement generates one event per row. This produces a large volume of binlogs and slows down the apply process on the secondary database during replication, resulting in replication latency.
The Tencent Cloud kernel team analyzed and optimized large transaction replication scenarios to develop this feature. The large transaction replication optimization feature automatically identifies large transactions and converts row-format binlogs to statement-format binlogs, reducing binlog volume and improving replication efficiency.
Supported Versions
Kernel version MySQL 5.6 20210630 or later
Kernel version MySQL 5.7 20200630 or later
Kernel version MySQL 8.0 20200830 or later
Applicable Scenarios
This feature primarily improves the replay speed of large transactions on tables without primary keys in row mode. You can enable it when you determine that the latency is caused by slow replay of tables without primary keys.
This feature is mainly intended for scenarios where large transactions exist in row mode and replication is slow.
Performance Data
Replication time is reduced by 85% in update scenarios and by approximately 30% in insert scenarios.
Usage Instructions
The large transaction replication optimization feature determines whether a transaction is likely to be a large transaction based on the statistics of historical SQL execution. When a transaction is identified as a large transaction and is likely to be optimized, the feature automatically elevates its isolation level to RR (Repeatable Read) and writes the binlog in Statement format to reduce the execution time of large transactions on the secondary database. The details are as follows:
cdb_optimize_large_trans_binlog is the switch for this feature.
cdb_sql_statistics is the switch for collecting statistics on SQL execution.
cdb_optimize_large_trans_binlog_last_affected_rows_threshold and cdb_optimize_large_trans_binlog_aver_affected_rows_threshold together constitute the threshold conditions for large transactions.
cdb_sql_statistics_info_threshold is the number of historical statistics records stored in memory.
To better monitor transaction execution, the CDB_SQL_STATISTICS table is also added under the information_schema database for querying the statistics of current transactions.
New Parameters
Term
Status
Type
Default Value
Description
cdb_optimize_large_trans_binlog
true
bool
false
Switch for optimizing large transactions in binlog
cdb_optimize_large_trans_binlog_last_affected_rows_threshold
true
ulonglong
10000
Conditions for large transaction optimization: threshold for the number of rows affected last time
cdb_optimize_large_trans_binlog_aver_affected_rows_threshold
true
ulonglong
10000
Conditions for large transaction optimization: threshold for the average number of affected rows
cdb_sql_statistics
true
bool
false
Whether to enable the switch for collecting statistics on SQL running status
cdb_sql_statistics_info_threshold
true
ulonglong
10000
The maximum number of SQL statements whose statistics are stored in the map of CDB_SQL_STATISTICS
Note:
Currently, users cannot modify the values of the above parameters directly. If they need to be modified, submit a ticket for modification.
New information_schema.CDB_SQL_STATISTICS Table
Term
Type
Description
DIGEST_MD5
MYSQL_TYPE_STRING
The MD5 calculated from the digest of this SQL statement
DIGEST_TEXT
MYSQL_TYPE_STRING
The text format of the SQL digest
SQL_COMMAND
MYSQL_TYPE_STRING
The type of SQL command
FIRST_UPDATE_TIMESTAMP
MYSQL_TYPE_DATETIME
The time when this statistical record was first generated
LAST_UPDATE_TIMESTAMP
MYSQL_TYPE_DATETIME
Last update time of this statistic
LAST_ACCESS_TIMESTAMP
MYSQL_TYPE_DATETIME
The time when this statistical record was last accessed
EXECUTE_COUNT
MYSQL_TYPE_LONGLONG
The number of times this type of SQL statement is executed
TOTAL_AFFECTED_ROWS
MYSQL_TYPE_LONGLONG
The total number of affected rows
AVER_AFFECTED_ROWS
MYSQL_TYPE_LONGLONG
The average number of affected rows
LAST_AFFECTED_ROWS
MYSQL_TYPE_LONGLONG
The number of rows affected last time
STMT_BINLOG_FORMAT_IF_POSSIBLE
MYSQL_TYPE_STRING
Whether this type of SQL can be written to the binlog in statement format, TRUE or FALSE

Help and Support

Was this page helpful?

You can also Contact sales or Submit a Ticket for help.

Help us improve! Rate your documentation experience in 5 mins.

Feedback

tencent cloud

TencentDB for MySQL

Large Transaction Replication

Feature Introduction

Supported Versions

Applicable Scenarios

Performance Data

Usage Instructions

New Parameters

New information_schema.CDB_SQL_STATISTICS Table

Help and Support

Term	Status	Type	Default Value	Description
cdb_optimize_large_trans_binlog	true	bool	false	Switch for optimizing large transactions in binlog
cdb_optimize_large_trans_binlog_last_affected_rows_threshold	true	ulonglong	10000	Conditions for large transaction optimization: threshold for the number of rows affected last time
cdb_optimize_large_trans_binlog_aver_affected_rows_threshold	true	ulonglong	10000	Conditions for large transaction optimization: threshold for the average number of affected rows
cdb_sql_statistics	true	bool	false	Whether to enable the switch for collecting statistics on SQL running status
cdb_sql_statistics_info_threshold	true	ulonglong	10000	The maximum number of SQL statements whose statistics are stored in the map of CDB_SQL_STATISTICS