Hot Row Update

Principles

The following figure shows the architecture of GaussDB(for MySQL) hot row update. There are two parts: Counter_hash and Group_hash. Counter_hash is used to automatically determine which rows are hot rows. Group_hash consists of multiple hotspot groups and is used to update hot rows. Each hotspot group corresponds to a hot row. Each hotspot group consists of multiple batches to ensure that the statements that update hot rows can be committed alternately.

Constraints

• The kernel version of your GaussDB(for MySQL) instance must be 2.0.54.240600 or later.
• Usage constraints:
  • In a WHERE condition, only a primary key or unique index can be used for equality matching, and only one record can be updated.
  • Index columns cannot be modified.
  • The modifications apply only for the columns of the integer type.
  • Only two elements in a hot row record can be added or subtracted. The first element is the same as the left side of the equal sign (=) and meets the constraints such as unique indexes. Value assignment is not allowed. Assuming that c is the column to be modified and d is a common column, only operations similar to c=c+1 or c=c-1 are allowed. Operations such as c=d+1, c=1+c, c=c+1+1, and c=1+c+1 are not allowed.
  • This function applies only for implicit transactions. That is, AUTOCOMMIT must be set to ON and cannot be used in BEGIN and COMMIT transactions.
  • You need to use HOTSPOT to explicitly mark hot row update transactions, or set rds_hotspot_auto_detection_threshold to a value other than 0 to enable automatic hot row update identification. For details about how to use rds_hotspot_auto_detection_threshold, see the parameter description.
  • The isolation level of transactions in a database must be READ COMMITTED (RC).
  • This function cannot be used in stored functions, triggers, or events. Otherwise, the following error is reported on the client:

    HOTSPOT hints can not be used in stored function, trigger or event

• Behavior change: In a hotspot transaction group, except the transactions that failed to be executed or that were killed in the update phase, other transactions are committed in batches and recorded in redo logs and undo logs. These transactions can only be committed or rolled back in batches and cannot be rolled back separately. Dozens to hundreds of transactions can be committed in each batch.

Parameter Description

Status Description

New Keywords

The following table lists new keywords.

The preceding keywords are placed at the end of a SQL statement. HOTSPOT must be placed at the beginning. NOT_MORE_THAN and NOT_LESS_THAN can be placed at any position.

For example, if id is a primary key column and c is an INT column, the following syntax is supported:

UPDATE c=c+1 where id=10 HOTSPOT;
UPDATE c=c+1 where id=10 HOTSPOT NOT_MORE_THAN 100; // The value of the c column is not greater than 100.
UPDATE c=c-1 where id=10 HOTSPOT NOT_LESS_THAN 0; // The value of the c column is not less than 0.
UPDATE c=c+1 where id=10 HOTSPOT NOT_MORE_THAN 100 NOT_LESS_THAN 0; // The value of the c column is not greater than 100 and not less than 0.
UPDATE c=c+1 where id=10 HOTSPOT NOT_LESS_THAN 0 NOT_MORE_THAN 100; // The value of the c column is not greater than 100 and not less than 0.

When any value exceeds the value of NOT_MORE_THAN or NOT_LESS_THAN, the following error is reported to the client:

HOTSPOT field value exceeds limit

Example

1. Create a table and prepare data.

   CREATE TABLE test.hotspot1 (
     `id` int NOT NULL primary key,
     `c` int NOT NULL DEFAULT '0'
   ) ENGINE=InnoDB;
   INSERT INTO test.hotspot1 VALUES (1, 1);

2. Enable hot row update.

   SET GLOBAL rds_hotspot = ON;

3. Change the isolation level to AUTOCOMMIT.

   SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
   SET SESSION AUTOCOMMIT = ON;

4. Initiate an update with HOTSPOT keyword.

   UPDATE test.hotspot1 SET c=c+1 WHERE id=1 HOTSPOT;

5. Check the update status of hot rows.

   SHOW STATUS like "%hotspot%";

   Performance test

   • Test environments

     Instance specifications: 8 vCPUs | 32 GB, 32 vCPUs | 128 GB

     ECS specifications: 32 vCPUs | 64 GB

     Region: CN North-Beijing4

     Test tool: sysbench-1.0.18

     Data models:

     • One table with one data record
     • Eight tables, with each table containing one data record
   • Parameter settings:

     rds_hotspot=ON

     transaction_isolation=READ-COMMITTED

     max_prepared_stmt_count=1048576

     rds_global_sql_log_bin=OFF

   • Test method

     Definition of the data tables required for the test:

     CREATE TABLE sbtest (id int NOT NULL AUTO_INCREMENT,k int NOT NULL DEFAULT '0',PRIMARY KEY (id));

     Test statement:

     UPDATE sbtest%u SET k=k+1 WHERE id=1 hotspot;

   • Test scenarios and results

     Test scenario 1: updating a single hot row of an instance with 8 vCPUs and 32 GB

     Test result: The performance of all concurrent requests was improved to different degrees. The performance of 64 or less concurrent requests was not improved significantly, but the performance of 128 or more concurrent requests was improved significantly (up to 9.26 times).

     

     

     Test scenario 2: Updating a single hot row of an instance with 32 vCPUs and 128 GB

     Test result: The performance of 128 or more concurrent requests was improved significantly, by 639 times.

     

     

     Test scenario 3: Updating eight hot rows of an instance with 32 vCPUs and 128 GB

     Test result: The performance of 256 or less concurrent requests was not improved, but the performance of 512 or more concurrent requests was improved significantly, by 78 times.