Help Center> Distributed Database Middleware> Best Practices> Selecting Proper Specifications
View PDF

Selecting Proper Specifications

It is a basic requirement for databases to support concurrent service requests during peak hours. When using Query per Second (QPS) to measure service requirements, you can select proper specifications for DDM instances on the basis of testing data and your own service planning. This allows you to trim costs while meeting service demands, on the premise that DDM instances have no performance bottlenecks.

  • Actually, QPS depends on many factors, including the network, storage, table design, SQL execution plan, and size of a single data record. QPS metric values are obtained using the sysbench tool.
  • This document is based on HUAWEI CLOUD DDM practices and will guide you through the functions provided by DDM.

OLTP Performance Test

Test Environment

  • AZ: AZ1 of CN-North Beijing1
  • Subnet: subnet-ff59 (192.168.0.0/24)
  • Intranet security group: Sys-default
  • VPC: vpc-blue
  • RDS DB instance class: 16 vCPUs | 32 GB, common instance (c3)
  • Press (four sysbench tools): 16 vCPUs | 32 GB
Test Parameters
  • An example SQL statement used for table creation is as follows: 
    CREATE TABLE sbtest1( 
 id INTEGER UNSIGNED NOT NULL auto_increment PRIMARY KEY,
 k INTEGER UNSIGNED DEFAULT '0' NOT NULL,
 c CHAR(120) DEFAULT '' NOT NULL,
 pad CHAR(60) DEFAULT '' NOT NULL
 );
  • Press test parameters are described as follows: 
    --test='/usr/local/share/sysbench/oltp.lua' Test model is oltp.lua
--oltp-table-size=160000000  Prepare 160 million data records.
--oltp_auto_inc=off Disable the auto increment primary key.
--oltp_skip_trx=off Skip transactions.
--oltp_secondary Set the ID to a non-primary key to prevent primary key conflicts.
--oltp_range_size=5 Continuously assign five values so that five shards must be gone through.
--rand-init=on Each test table is a sysbench tool filled with random data.
--num-threads=256/512 Number of concurrent threads

Test Results

Table 1 describes the test result.

Table 1 OLTP performance test

DDM Specifications

Concurrency

Total QPS of Nodes in Cluster Mode

8 vCPUs | 16 GB

256

49,474

16 vCPUs | 32 GB

256

87,302

32 vCPUs | 64 GB

256

152,671

64 vCPUs | 128 GB

512

317,431

Simple Query Performance Test

Test Environment

  • AZ: AZ1 of CN-North Beijing1
  • RDS DB instance class: 16 vCPUs | 32 GB, common instance (c3)
  • Number of RDS DB instances: 8/16
  • RDS AZ: AZ1 of CN-North Beijing1
  • Press test ECS specifications: 16 vCPUs | 32 GB
  • Press test ECS region: AZ1 of CN-North Beijing1
Test Parameters
  • An example SQL statement for table creation is as follows: 
    CREATE TABLE sbtest1( 
 id INTEGER UNSIGNED NOT NULL auto_increment PRIMARY KEY,
 k INTEGER UNSIGNED DEFAULT '0' NOT NULL,
 c CHAR(120) DEFAULT '' NOT NULL,
 pad CHAR(60) DEFAULT '' NOT NULL
 );
  • Press test parameters are described as follows: 
    --test='/usr/local/share/sysbench/select.lua' The test model is select.lua, that is, equality query on sharding keys.
--oltp_tables_count=1 There is one table.
--oltp-table-size=160000000  Prepare 160 million data records.
--oltp_auto_inc=off Disable the auto increment primary key.
--oltp_secondary Set the ID to a non-primary key to prevent primary key conflicts.
--rand-init=on Each test table is a sysbench filled with random data.
--report-interval=1
--max-time=600 Perform the press test for 10 minutes.
--num-threads=256/512 Number of concurrent threads

Test Result

Table 2 describes the test result.

Table 2 Simple query performance test

DDM Specifications

Concurrency

QPS

8 vCPUs | 16 GB

256

108,003

16 vCPUs | 32 GB

256

205,228

32 vCPUs | 64 GB

256

425,585

64 vCPUs | 128 GB

512

796,614