How to optimize hotspot updates? - PolarDB - Alibaba Cloud Documentation Center

This topic describes how to optimize hotspot updates.

Background information

When updating data in a database, the system typically follows a sequence of locking the data, updating it, and then unlocking it to ensure data consistency. However, this process can lead to lock contention and latency issues when multiple concurrent requests attempt to update the same row. Such contention can significantly reduce the number of transactions per second (TPS) and result in slower responses. For example, in flash sale scenarios, frequent updates to product inventory can suffer from performance degradation, leading to delays and inefficiencies.

To address the issues, PolarDB-X recommends using a batch processing scheme within the database kernel, where updates to the same record are grouped and committed as a single unit. This changes the update sequence to locking the data, performing a group update, and then unlocking the data, which effectively reduces lock contention. Combined with pipeline processing and other optimizations, this design significantly improves TPS in high-concurrency scenarios. For more information, see Test results.

Supported versions

This feature is available only in PolarDB-X Enterprise Edition MySQL 5.7.

Usage notes

The hotspot update feature leverages inventory hints to optimize update performance for hot rows within a single database shard. It is not intended for use in distributed database environments where data is spread across multiple shards or databases.

Preparations

Important

If you use the SET GLOBAL statement to specify a parameter, the configuration takes effect only for new sessions created after the configuration.

Use a privileged account to enable hotspot-related features.
```
SET GLOBAL HOTSPOT=ON;
SET GLOBAL HOTSPOT_LOCK_TYPE=ON
```
Globally disable shared ReadView if it is enabled.
To check the status of shared ReadView, run the following statement:
```
-- To check if shared ReadView is enabled, run the following statement:
SHOW VARIABLES LIKE '%SHARE_READ_VIEW%';
```
If shared ReadView is enabled, globally disable it by using the following statement:
```
SET GLOBAL SHARE_READ_VIEW = FALSE;
```
Important
Globally disabling shared ReadView may slow down some queries.
Modify the transaction type.
```
-- Global level settings
SET GLOBAL TRANSACTION_POLICY = 'XA'; 
SET GLOBAL ENABLE_XA_TSO = FALSE;
-- Session level settings
SET SESSION TRANSACTION_POLICY = 'XA'; 
SET SESSION ENABLE_XA_TSO = FALSE;
```
Check the transaction type.
```
SHOW VARIABLES LIKE 'TRANSACTION_POLICY';
```
Note
If the transaction type is not XA, switch it to XA before using the hotspot update feature. Alternatively, you can set the session level each time before you use the hotspot update feature. However, this change may cause unexpected results. Proceed with caution.

Syntax

To use an inventory hint, add the INVENTORY HINT clause to the UPDATE statement and place it as the last statement in the transaction.

BEGIN;
UPDATE /*+ commit_on_success rollback_on_fail target_affect_row(number)*/ table_reference 
    SET assignment_list
    [WHERE where_condition];
COMMIT | ROLLBACK; -- Choose one based on the update result.

Important

The WHERE condition must be an equality condition on the primary key or unique key, and does not support tables with global indexes (local indexes are allowed).

Parameters

Parameter	Required	Description
commit_on_success	Yes	If the statement is successfully executed, it is committed with the previous uncommitted statements.
rollback_on_fail	No	If the statement fails the execution, it is rolled back with the previous uncommitted statements.
target_affect_row(number)	No	Checks the number of updated rows. If the number is not as expected, the update fails.

Examples

Create tables.

CREATE TABLE table_test  (
    id INT AUTO_INCREMENT PRIMARY KEY,
    c INT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE table_test_2  (
    id INT AUTO_INCREMENT PRIMARY KEY,
    c INT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Add the commit_on_success hint to the UPDATE statement to automatically commit the transaction if the update is successful.
```
BEGIN;
UPDATE /*+ commit_on_success*/ table_test SET c = c - 1 WHERE id = 1;
COMMIT;
```
Note
Choose COMMIT or ROLLBACK based on the update status.
Add the rollback_on_fail hint to the UPDATE statement to automatically roll back the transaction if the update fails.
```
BEGIN;
UPDATE /*+ commit_on_success rollback_on_fail*/ table_test SET c = c - 1 WHERE id = 1;
COMMIT;
```
Add the target_affect_row(number) hint to the UPDATE statement to specify the expected number of rows to be updated. If the actual number of rows updated does not match the expected value, the update fails.
```
BEGIN;
UPDATE /*+ commit_on_success rollback_on_fail target_affect_row(1)*/ table_test SET c = c - 1 WHERE id = 1;
COMMIT;
```

You can perform other DML operations before executing the UPDATE statement with the inventory hint.

BEGIN;
INSERT INTO table_test_2 VALUES (1,1);
UPDATE /*+ commit_on_success rollback_on_fail target_affect_row(1)*/ table_test SET c = c - 1 WHERE id = 1;
COMMIT;

Check the group commit status

To check the group commit status, execute the following SQL statement. An increasing value of Group_update_leader_count indicates that the group commit optimization is triggered.

SHOW GLOBAL STATUS LIKE "%Group_update%";
+---------------------------------------+--------+
| Variable_name                         | Value  |
+---------------------------------------+--------+
| Group_update_fail_count               | 54     |
| Group_update_follower_count           | 962869 |
| Group_update_free_count               | 2      |
| Group_update_group_same_count         | 0      |
| Group_update_gu_leak_count            | 0      |
| Group_update_ignore_count             | 0      |
| Group_update_insert_dup               | 0      |
| Group_update_leader_count             | 168292 |
| Group_update_lock_fail_count          | 0      |
| Group_update_mgr_recycle_queue_length | 0      |
| Group_update_recycle_queue_length     | 0      |
| Group_update_reuse_count              | 23329  |
| Group_update_total_count              | 2      |
+---------------------------------------+--------+

To check the status of the UPDATE statement, execute the show physical full processlist statement. If the UPDATE statement is executed with the HOTSPOT keyword, the group commit optimization is enabled.

SHOW physical FULL processlist WHERE command != 'Sleep';
+-------+------+-----+---------+-------------+---------+------+-------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Group | Atom | Id  | User    | db          | Command | Time | State                   | Info                                                                                                                                                                                         |
+-------+------+-----+---------+-------------+---------+------+-------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|     0 |    0 |  56 | diamond | test_000001 | Query   |    0 | hotspot wait for commit | /*DRDS /127.0.0.1/12e774cab8800000-128/0// */UPDATE  /*+COMMIT_ON_SUCCESS ROLLBACK_ON_FAIL TARGET_AFFECT_ROW(1) */ `test_hotline_lZTr` AS `test_hotline` SET `b` = (`b` + 1) WHERE (`a` = 1) |
|     0 |    0 | 822 | diamond | test_000001 | Query   |    0 | query end               | /*DRDS /127.0.0.1/12e774c4e9400000-563/0// */UPDATE  /*+COMMIT_ON_SUCCESS ROLLBACK_ON_FAIL TARGET_AFFECT_ROW(1) */ `test_hotline_lZTr` AS `test_hotline` SET `b` = (`b` + 1) WHERE (`a` = 1) |
|     0 |    0 | 831 | diamond | test_000001 | Query   |    0 | hotspot wait for commit | /*DRDS /127.0.0.1/12e774c551000000-509/0// */UPDATE  /*+COMMIT_ON_SUCCESS ROLLBACK_ON_FAIL TARGET_AFFECT_ROW(1) */ `test_hotline_lZTr` AS `test_hotline` SET `b` = (`b` + 1) WHERE (`a` = 1) |
|     0 |    0 | 838 | diamond | test_000000 | Query   |    0 | starting                | show full processlist                                                                                                                                                                        |
+-------+------+-----+---------+-------------+---------+------+-------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Performance testing for hotspot updates

Test preparations

Create a test table.

CREATE TABLE sbtest(id INT UNSIGNED NOT NULL PRIMARY KEY, c BIGINT UNSIGNED NOT NULL);

Use the following statement for testing.

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

Install and configure the testing tool. For more information, see Sysbench tests.
Use a PolarDB-X Enterprise Edition instance with two compute nodes and two data nodes, each having 4 CPU cores and 8 GB of memory.

Test results

Scenario	1 thread	4 threads	8 threads	16 threads	32 threads	64 threads	128 threads	256 threads	512 threads
Hotspot update	298	986	1872	3472	6315	10138	13714	15803	23262
Common update	318	423	409	409	412	428	448	497	615

Note

The parameters are set to 1 for the sampled data used in the test.
The values in the preceding table are measured in TPS.
The TPS when hotspot updates are performed depends on the instance specifications, the request concurrency, and the update statement. The test results are only for reference and may vary based on the use case and environment.