In primary-secondary replication, the default COMMIT_ORDER mechanism determines parallel replay based on whether transactions can be committed in parallel on the primary RDS instance. This limits replay parallelism to the primary instance's concurrency. WRITESET removes this limitation by detecting transaction conflicts at the row level, which reduces replication lag between primary and secondary RDS instances.
How WRITESET works
When COMMIT_ORDER is used, two transactions can replay in parallel only if they can be committed in parallel on the primary RDS instance.
WRITESET checks whether the data rows modified by each transaction conflict with those modified by other transactions. If no conflicts exist, the transactions replay in parallel on the secondary RDS instance, regardless of how they were committed on the primary RDS instance.
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 the following three parameters. For instructions, see Modify instance parameters.
transaction_write_set_extraction = XXHASH64
binlog_transaction_dependency_tracking = WRITESET
binlog_transaction_dependency_history_size = 500000binlog_transaction_dependency_history_size sets the maximum number of data rows stored in memory for conflict detection. A larger value tracks more data rows and improves parallelism.
Parameter dependencies
binlog_transaction_dependency_tracking and transaction_write_set_extraction affect each other. Use the valid combinations listed below. Invalid combinations are blocked.
binlog_transaction_dependency_tracking | transaction_write_set_extraction |
|
|
|
|
Parameter reference
binlog_transaction_dependency_tracking
Determines how transactions recorded in binary logs are replayed in parallel on the secondary RDS instance.
Multi-threaded parallel replay requires replica_parallel_workers or slave_parallel_workers to be greater than 0.
Attribute | Value |
Default |
|
Valid values |
|
Supported versions | MySQL 5.7 (minor engine version 20211231 and later), MySQL 8.0 (minor engine version 20210930 and later) |
Value | Description |
| Determines dependencies based on commit order. Two transactions can be replayed in parallel on the secondary RDS instance if they can be committed in parallel on the primary RDS instance. This mode is affected by the parallelism settings on the primary RDS instance. |
| Determines dependencies at the row level. Two transactions replay in parallel if they do not modify the same data row of a table that has a primary key or unique key. This mode is not affected by the parallelism settings on the primary RDS instance. |
| Transactions within the same session replay serially. Transactions from different sessions follow the same rules as |
transaction_write_set_extraction
Specifies the hash algorithm used to calculate the hash value of a data row. The hash value determines whether different transactions modify the same data row.
Attribute | Value |
Default |
|
Valid values |
|
Supported versions | MySQL 5.7 (minor engine version 20211231 and later), MySQL 8.0 (minor engine version 20210930 and later) |
Value | Description |
| Does not calculate the hash value of data rows. |
| Calculates the hash value of data rows by using a 64-bit hash algorithm. |
binlog_transaction_dependency_history_size
Specifies the maximum number of data rows stored in memory for conflict detection.
Attribute | Value |
Recommended value |
|
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 for transaction conflict detection. This fallback does not degrade parallel replay performance on the secondary RDS instance.
WRITESET becomes invalid when:
DDL operations are performed
Large transactions are committed
Foreign key tables are modified
Tables without primary keys exist
If replication lag exists between your secondary and read-only RDS instances, enable WRITESET to reduce it.
References
Use the parameter diagnostics feature to obtain parameter optimization recommendations.
MySQL official documentation: