Benchmark single-row hotspot update performance - ApsaraDB RDS - Alibaba Cloud -

This topic describes the method and results of a performance test for single-row hotspot updates in ApsaraDB RDS for MySQL.

You can use Inventory Hint to improve the performance of single-row hotspot updates on ApsaraDB RDS for MySQL. For more information, see Inventory Hint.

Test environment

This test uses two ApsaraDB RDS for MySQL instances: a High-availability Edition instance with the rds.mysql.st.v52 instance type and an Enterprise Edition instance with the mysql.st.12xlarge.25 instance type.

Instance version: MySQL 5.7
Instance type: 90 cores, 720 GB (dedicated host)
RDS editions: High-availability Edition and Enterprise Edition
Storage type: local disk
Template: high-performance parameter template

Test data

The test uses a single table that contains 100 rows. The table schema is as follows:

CREATE TABLE `sbtest1`
(
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT
,`k` INT(10) UNSIGNED NOT NULL DEFAULT '0'
,`c` CHAR(120) NOT NULL DEFAULT ''
,`pad` CHAR(60) NOT NULL DEFAULT ''
,PRIMARY KEY (`id`)
,KEY `k_1` (`k`)
)
ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT
CHARSET=utf8 MAX_ROWS=1000000

Test script

The script performs concurrent updates on the row where id=100:

UPDATE sbtest1 SET k=k+1 WHERE id=100

The test uses the following Lua script:

pathtest = string.match(test,"(.*/)")
if pathtest then
 dofile(pathtest .."common.lua")
else
 require("common")
end
function thread_init(thread_id)
 set_vars()
end
function event(thread_id)
 local table_name
 table_name ="sbtest".. sb_rand_uniform(1, oltp_tables_count)
 rs = db_query("begin")
 rs = db_query("update /*+commit_on_success rollback_on_fail target_affect_row(1) */ sbtest1 SET k=k+1 WHERE id=100")
 rs =db_query("commit")
end

Test results

Instance type	Maximum single-row TPS
RDS High-availability Edition	12,000
RDS Enterprise Edition	31,000

Figure 1. Test results for Enterprise Edition

OLTP test statistics:
    queries performed:
        read:                            0
        write:                           1865967
        other:                           3731934
        total:                           5597901
    transactions:                        1865967 (30822.67 per sec.)
    read/write requests:                 1865967 (30822.67 per sec.)
    other operations:                    3731934 (61645.34 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)