Configure WRITESET parameters to reduce MySQL replication lag | ApsaraDB RDS - ApsaraDB RDS

How WRITESET works

With the default COMMIT_ORDER mode, parallel replay on the secondary instance is limited by commit parallelism on the primary instance.

WRITESET detects conflicts at the row level. Transactions that modify different rows replay in parallel on the secondary instance, regardless of primary instance commit order.

Supported versions

The RDS instance must run one of the following MySQL versions:

MySQL version	Minimum minor engine version
MySQL 5.7	20211231
MySQL 8.0	20210930

Enable WRITESET

Set these three parameters through Modify instance parameters.

transaction_write_set_extraction = XXHASH64
binlog_transaction_dependency_tracking = WRITESET
binlog_transaction_dependency_history_size = 500000

Note

binlog_transaction_dependency_history_size controls how many rows are tracked in memory for conflict detection. Higher values improve parallelism.

Parameter dependencies

Warning

binlog_transaction_dependency_tracking and transaction_write_set_extraction are interdependent. Only the following combinations are valid.

binlog_transaction_dependency_tracking	transaction_write_set_extraction
`COMMIT_ORDER`	`OFF` or `XXHASH64`
`WRITESET` or `WRITESET_SESSION`	`XXHASH64`

Parameter reference

`binlog_transaction_dependency_tracking`

Controls how binary log transactions are replayed in parallel on the secondary instance.

Multi-threaded parallel replay requires replica_parallel_workers or slave_parallel_workers to be greater than 0.

Attribute	Value
Default	`WRITESET`
Valid values	`COMMIT_ORDER`, `WRITESET`, `WRITESET_SESSION`
Supported versions	MySQL 5.7 (minor engine version 20211231 and later), MySQL 8.0 (minor engine version 20210930 and later)

Value	Description
`COMMIT_ORDER`	Uses commit order to determine dependencies. Two transactions replay in parallel on the secondary instance only if they commit in parallel on the primary instance. Parallelism is limited by primary instance concurrency.
`WRITESET`	Detects conflicts at the row level. Transactions that modify different rows in tables with a primary or unique key replay in parallel, regardless of primary instance concurrency.
`WRITESET_SESSION`	Transactions within the same session replay serially. Transactions from different sessions follow the same rules as `WRITESET`.

`transaction_write_set_extraction`

Specifies the hash algorithm for calculating row-level hash values used in conflict detection.

Attribute	Value
Default	`XXHASH64`
Valid values	`OFF`, `XXHASH64`
Supported versions	MySQL 5.7 (minor engine version 20211231 and later), MySQL 8.0 (minor engine version 20210930 and later)

Value	Description
`OFF`	Disables row hash calculation. `WRITESET` mode cannot be used with this setting.
`XXHASH64`	Uses a 64-bit hash algorithm to calculate row hash values.

`binlog_transaction_dependency_history_size`

Specifies the maximum number of data rows stored in memory for conflict detection.

Attribute	Value
Recommended value	`500000`
Effect	A larger value tracks more data rows and enables higher parallelism.

When WRITESET becomes invalid

In certain scenarios, WRITESET falls back to COMMIT_ORDER. This does not degrade parallel replay performance.

WRITESET becomes invalid when:

DDL operations are performed
Large transactions are committed
Foreign key tables are modified
Tables without primary keys exist

Enable WRITESET to reduce replication lag on secondary and read-only instances.

References

Modify instance parameters
Use the parameter diagnostics feature for optimization recommendations.
MySQL official documentation: