Hot row performance optimization

Background

Hot rows present the following challenges:

• When a transaction updates a data row, it acquires a lock on the target row and does not release the lock until the transaction is committed or rolled back. During this time, only one transaction can update the row, and other transactions must wait. This means that update requests for a single hot row are executed serially. Traditional database and table sharding strategies provide limited performance improvements in this scenario.

• In these scenarios, a large number of update requests for hot rows can reach the backend database system in a short period. This leads to severe row lock contention and long wait times, which degrades system performance. A long wait for an update request can significantly impact business operations.

Simply scaling up hardware cannot meet these low-latency requirements. To address this, PolarDB provides innovative optimizations at the database kernel level. The system automatically identifies hot row update requests and groups updates to the same data row that occur within a specific time interval. Different groups are then processed in parallel using a pipeline. These optimizations greatly improve system performance.

Technical solution

• From sequential to pipeline processing

  Parallel processing is the most direct way to improve database performance, but it is difficult to fully parallelize update operations on the same hot row. PolarDB uses an innovative pipeline processing approach to maximize the parallelism of hot row update operations.

  

  SQL statements used for hot row update operations are marked with autocommit or COMMIT_ON_SUCCESS. The optimized MySQL kernel layer automatically identifies update operations with these marks. Within a certain time interval, it performs a Hash on the collected update operations based on their primary key or unique key. For update operations that are mapped to the same bucket by the Hash, the kernel groups and commits them in batches according to their arrival order.

  To process these update operations in a pipeline, two execution units are used to group them. When the first group is collected and ready to commit, the second group immediately starts collecting update operations. When the second group is collected and ready to commit, the first group has already been committed and begins collecting a new batch of update operations. The two groups alternate and execute in parallel.

  This pipeline processing model fully utilizes hardware resources, increases CPU utilization, and improves the system's parallel processing capability, maximizing its throughput.

• Eliminate waiting when requesting row locks

  To ensure logical data consistency, an update first acquires a lock on the target data row. If the lock request cannot be granted immediately, the request enters a waiting state. This not only increases processing latency but also triggers deadlock detection, which adds extra overhead.

  As mentioned earlier, update operations on the same data row are grouped in chronological order. The first update operation in a group is the Leader. It reads the target data row and locks it. Subsequent update operations are Followers. When a Follower requests a lock on the target data row and finds that the Leader already holds the row lock, it can acquire the lock immediately without waiting.

  This optimization reduces the number of row lock acquisitions and the associated time overhead. As a result, overall system performance improves significantly.

• Reduce B-tree index traversals

  MySQL manages data by using B-tree indexes. Each query must traverse the index to locate the target data row. The larger the data table and the deeper the index, the longer the traversal takes.

  In the grouping mechanism described earlier, only the Leader of each group traverses the index to locate the data row, and then caches the updated row in memory (Row Cache). After a Follower in the same group successfully acquires the lock, it reads the target data row directly from memory without traversing the index again.

  This reduces the total number of index traversals and their associated overhead.

Prerequisites

• Your PolarDB cluster must run one of the following versions:

  • PolarDB for MySQL 5.6, with a minor engine version of 20200601 or later.

  • PolarDB for MySQL 5.7, with a minor engine version of 5.7.1.0.17 or later.

  • PolarDB for MySQL 8.0, with a minor engine version of 8.0.1.1.10 or later.

• binlog is enabled.

• The cluster parameter rds_ic_reduce_hint_enable must be disabled.

  • For PolarDB for MySQL 5.6 and PolarDB for MySQL 8.0, this parameter is disabled by default.

  • For PolarDB for MySQL 5.7, this parameter is enabled by default. Before you enable hot row optimization, you must change the parameter value to OFF.

  Note

  To ensure compatibility with MySQL configuration files, the PolarDB console prefixes all cluster parameters with loose_. To modify the rds_ic_reduce_hint_enable parameter in the PolarDB console, you must select the parameter with the loose_ prefix, which is loose_rds_ic_reduce_hint_enable.

Limitations

Hot row optimization does not apply in the following scenarios:

• The table that contains the hot row is a partitioned table.

• A trigger is defined on the table that contains the hot row.

• The hot row uses the Statement Queue mechanism.

• If global binlog is enabled but session-level binlog is disabled, hot row optimization is not applied to UPDATE statements.

• After you enable hot row optimization, columns that rely solely on the ON UPDATE CURRENT_TIMESTAMP attribute for automatic updates will no longer be automatically updated. You must explicitly assign values to these columns in your UPDATE statements by using SET column_name = CURRENT_TIMESTAMP(n).

Usage

1. Enable hot row optimization.

   In the PolarDB console, you can modify the following parameter to enable or disable hot row optimization.

   Parameter	Description
   hotspot	Specifies whether to enable the hot row optimization feature. Valid values: ON: enabled. OFF (default): disabled.

   Note

   To ensure compatibility with MySQL configuration files, the PolarDB console prefixes all cluster parameters with loose_. To modify the hotspot parameter in the PolarDB console, select the parameter with the loose_ prefix, which is loose_hotspot.

2. Use hint syntax to apply hot row optimization.

   Hint	Required	Description
   COMMIT_ON_SUCCESS	Required	Commits the transaction if the update is successful.
   ROLLBACK_ON_FAIL	Optional	Rolls back the transaction if the update fails.
   TARGET_AFFECT_ROW(1)	Optional	Specifies that the request is expected to update only one row. If this condition is not met, the update fails.

   Note

   Because this hint automatically commits the transaction, you must include it in the last SQL statement of the transaction.

   Example: Update the value of the c column in the sbtest table.

   UPDATE /*+ COMMIT_ON_SUCCESS ROLLBACK_ON_FAIL TARGET_AFFECT_ROW(1) */ sbtest SET c = c + 1 WHERE id = 1;

Related operations

SHOW variables LIKE "hotspot%";

+------------------------------+-------+
|Variable_name                 | Value |
+------------------------------+-------+
|hotspot                       | OFF   |
|hotspot_for_autocommit        | OFF   |
|hotspot_lock_type             | OFF   |
|hotspot_update_max_wait_time  | 100   |
+------------------------------+-------+

SHOW GLOBAL status LIKE 'Group_update%';

Performance test