Modify WRITESET-related parameters - ApsaraDB RDS - Alibaba Cloud Documentation Center

You can enable WRITESET and use the binlog_transaction_dependency_tracking and transaction_write_set_extraction parameters to manage the mechanism that is used to detect transaction conflicts on your ApsaraDB RDS for MySQL instance. This improves parallel replay on your secondary RDS instance and reduces the latency between the primary and secondary RDS instances.

Introduction to WRITESET

In MySQL, COMMIT_ORDER is the default mechanism that is used to identify conflicts among transactions committed in primary-secondary replication. COMMIT_ORDER is prone to being affected by the parallelism settings of primary RDS instances. WRITESET is introduced to resolve this issue. When you commit a transaction, if you enable WRITESET, the system checks whether the data rows that are modified by the transaction conflict with the data rows modified by other transactions. If no conflicts are detected, the transactions can be replayed in parallel in your secondary RDS instance. This improves parallel replay on the secondary RDS instance.

Prerequisites for enabling WRITESET

The RDS instance runs one of the following database engine versions:

The RDS instance runs MySQL 5.7 and a minor engine version of 20211231 or later.
The RDS instance runs MySQL 8.0 and a minor engine version of 20210930 or later.

Note

In scenarios in which DDL operations are performed, large transactions are committed, foreign key tables are modified, and tables that do not have primary keys exist, WRITESET becomes invalid. In this case, the system uses COMMIT_ORDER to detect transaction conflicts. COMMIT_ORDER does not deteriorate parallel replay on the secondary RDS instance. If replication latency exists between your secondary and read-only RDS instances, we recommend that you enable WRITESET.

Parameter settings to enable WRITESET

To enable WRITESET, you must configure the following parameters. For more information, see 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 specifies the maximum number of data rows stored in the memory for conflict detection. A large value indicates a large number of data rows and a high parallelism.

Parameter setting rules

Warning

binlog_transaction_dependency_tracking and transaction_write_set_extraction affect each other. You must configure the parameters based on the mappings in the following table. Otherwise, the settings are blocked.

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

Parameter description

Important

binlog_transaction_dependency_tracking and transaction_write_set_extraction are supported for the following minor engine versions:

If your RDS instance runs MySQL 5.7, the valid range of minor engine versions is [20211231, 99999999).
If your RDS instance runs MySQL 8.0, the valid range of minor engine versions is [20210930, 99999999).

binlog_transaction_dependency_tracking:
- Description: binlog_transaction_dependency_tracking determines the method to identify the conflicts and dependencies between transactions in multi-thread parallel replay. This allows transactions in binary logs to be replayed on the secondary RDS instance in parallel. In multi-thread parallel replay, one of the following parameters is greater than 0: replica_parallel_workers and slave_parallel_workers.
- Default value: WRITESET
- Valid values and meanings:
  - COMMIT_ORDER: Dependencies between transactions are determined based on transactions committed in primary-secondary replication. If two transactions can be committed on the primary RDS instance in parallel, the transactions do not conflict with each other and can be replayed in parallel on the secondary RDS instance. This mode is prone to being affected by the parallelism settings on the primary RDS instance.
  - WRITESET: Dependencies between transactions are determined at the row level. If two transactions do not modify the same data row of a table that contains a primary key or unique key, the transactions can be replayed in parallel on the secondary RDS instance. This mode is not affected by the parallelism settings on the primary RDS instance.
  - WRITESET_SESSION: Transactions of the same SESSION value are serially replayed in the secondary RDS instance. Transactions of different SESSION values are replayed in the secondary RDS instance based on the rules of WRITESET.
transaction_write_set_extraction:
- Description: This parameter specifies the hash algorithm that is used by WRITESET to calculate the hash value of a data row. If WRITESET is enabled, the hash value of the data row calculated by using the specified algorithm is used to check whether the same data row is modified by different transactions.
- Default value: XXHASH64
- Valid values and meanings:
  - OFF: does not calculate the hash value of the data row. Make sure that WRITESET is disabled.
  - XXHASH64: calculates the hash value of the data row by using a 64-bit hash algorithm.

References

You can use the parameter diagnostics feature to obtain parameter optimization solutions. For more information, see Use the parameter diagnostics feature.
MySQL official documentation: