Data Integration Test #

Test Plan A: Data Export #

Data migration. Migrate 36 gigabytes of data (140 million lines) from a single database and table into the target source.

Test Objective #

Once the data reaches a large amount, databases’ performance will decrease notably, affecting the read/write operations. We can use DBPlusEngine-Proxy’s scaling feature to migrate the original data to multiple target databases without affecting the business operations.

Test Tool #

Sysbench

Test Environment #

Application	IP Address	Port
DBPlusEngine-Proxy	192.168.xx.25	3307
MySQL	192.168.xx.20	13306
MySQL	192.168.xx.21	13306
MySQL	192.168.xx.22	13306
MySQL	192.168.xx.23	13306
MySQL	192.168.xx.24	13306

Server Configuration

Item	Configuration
CPU	48 C
Memory	96 G
Hard Disk	SSD 820 G
JDK	17.0.2

Test Procedure #

Create a test table.

mysql -utest -h192.168.xx.20 -P13306 -p

DROP DATABASE IF EXISTS migration_ds_0;

CREATE DATABASE IF NOT EXISTS migration_ds_0;

mysql -utest -h192.168.xx.21 -P13306 -p

DROP DATABASE IF EXISTS migration_ds_1;

CREATE DATABASE IF NOT EXISTS migration_ds_1;

mysql -utest -h192.168.xx.22 -P13306 -p

DROP DATABASE IF EXISTS migration_ds_2;

CREATE DATABASE IF NOT EXISTS migration_ds_2;

mysql -utest -h192.168.xx.23 -P13306 -p

DROP DATABASE IF EXISTS migration_ds_3;

CREATE DATABASE IF NOT EXISTS migration_ds_3;

mysql -utest -h192.168.xx.24 -P13306 -p

DROP DATABASE IF EXISTS migration_ds_4;

CREATE DATABASE IF NOT EXISTS migration_ds_4;

mysql -uroot -h192.168.xx.25 -P3307 -proot

DROP DATABASE IF EXISTS sphereex_demo;

CREATE DATABASE IF NOT EXISTS sphereex_demo;

Install test tools.

tar -xzvf sysbench-1.0.20.tar.gz -C /usr/local
cd /usr/local/sysbench-1.0.20
./autogen.sh
./configure 
make && make install

Obtain data.

Use the Sysbench tool to insert 140 million pieces of data into the source database.

sysbench oltp_read_only --mysql-host='192.168.xx.20' --mysql-port=13306 --mysql-user=test --mysql-password='test' --mysql-db=migration_ds_0 --tables=1 --table-size=140000000 --report-interval=10 --time=3600 --threads=256 --max-requests=0 --percentile=99 --mysql-ignore-errors="all" --rand-type=uniform --range_selects=off --auto_inc=off cleanup

sysbench oltp_read_only --mysql-host='192.168.xx.20' --mysql-port=13306 --mysql-user=test --mysql-password='test' --mysql-db=migration_ds_0 --tables=1 --table-size=140000000 --report-interval=10 --time=3600 --threads=256 --max-requests=0 --percentile=99 --mysql-ignore-errors="all" --rand-type=uniform --range_selects=off --auto_inc=off prepare

Migration Procedure.

Create a new logical database and configure resources and rules through Proxy.

mysql -uroot -h 192.168.xx.25 -P3307 -proot

CREATE DATABASE sphereex_demo;

USE sphereex_demo;

REGISTER STORAGE UNIT ds_0 (
    URL="jdbc:mysql://192.168.xx.20:13306/migration_ds_0?serverTimezone=UTC&useSSL=false",
    USER="test",
    PASSWORD="test",
    PROPERTIES("maximumPoolSize"=500,"idleTimeout"=60000)
);

Add target sources.

REGISTER STORAGE UNIT ds_1 (
    URL="jdbc:mysql://192.168.xx.21:13306/migration_ds_1?serverTimezone=UTC&useSSL=false",
    USER="test",
    PASSWORD="test",
    PROPERTIES("maximumPoolSize"=50,"idleTimeout"="60000")
), ds_2 (
    URL="jdbc:mysql://192.168.xx.22:13306/migration_ds_2?serverTimezone=UTC&useSSL=false",
    USER="test",
    PASSWORD="test",
    PROPERTIES("maximumPoolSize"=50,"idleTimeout"="60000")
), ds_3 (
    URL="jdbc:mysql://192.168.xx.23:13306/migration_ds_3?serverTimezone=UTC&useSSL=false",
    USER="test",
    PASSWORD="test",
    PROPERTIES("maximumPoolSize"=50,"idleTimeout"="60000")
), ds_4 (
    URL="jdbc:mysql://192.168.xx.24:13306/migration_ds_4?serverTimezone=UTC&useSSL=false",
    USER="test",
    PASSWORD="test",
    PROPERTIES("maximumPoolSize"=50,"idleTimeout"="60000")
);

Create rules.

CREATE SHARDING TABLE RULE sbtest1(
STORAGE_UNITS(ds_1,ds_2,ds_3,ds_4),
SHARDING_COLUMN=id,
TYPE(NAME="hash_mod",PROPERTIES("sharding-count"=4)),
KEY_GENERATE_STRATEGY(COLUMN=id,TYPE(NAME="snowflake"))
);

Add the migration configuration.

ALTER MIGRATION RULE (
READ(
  WORKER_THREAD=40,
  BATCH_SIZE=1000,
  SHARDING_SIZE=7000000
),
WRITE(
  WORKER_THREAD=40,
  BATCH_SIZE=1000
)
);

Configure source resources.

REGISTER MIGRATION SOURCE STORAGE UNIT ds_0 (
    URL="jdbc:mysql://192.168.xx.20:13306/migration_ds_0?serverTimezone=UTC&useSSL=false",
    USER="test",
    PASSWORD="test",
    PROPERTIES("maximumPoolSize"=50,"idleTimeout"="60000")
);

Start executing the task.

MIGRATE TABLE ds_0.sbtest1 INTO sbtest1;

Test Result #

The migration process takes 10 mins 49 s.

Test result: the computing node reaches the bottleneck first under the above configuration.

Monitoring Information #

Source DB

DBPlusEngine-Proxy

One of target DB

Glossary #

Id: id of the migration task.

tables: name of the table to be migrated.

sharding_total_count: number of the source tables.

active: indicates whether the migration task is running. If it’s false, the migration task is disabled and will not run again.

create_time: the start time of the migration task.

stop_time: the end time of the migration task.

Test Plan B: Data Import #

Use JMH and DBPlusEngine-Driver to insert data into databases and tables. The insertion duration is 5 mins and the amount of data inserted is counted.

Use JMH to insert data into MySQL directly. The insertion duration is 5 mins and the amount of data inserted is counted.

Test Objective #

By comparing the two ways of importing data, we can:

Work out the performance indicator of DBPlusEngine-Driver.
Compare this with the performance loss of MySQL native import.

Test Tool #

JMH

Test Environment #

Application	IP Address	Port	Version
DBPlusEngine-Driver	192.168.xx.24	3307	1.2
MySQL	192.168.xx.20	13306	8.0.29

Server Configuration

Item	Configuration
CPU	48 C
Memory	96 G
Hard Disk	SSD 820 G
JDK	17.0.2

Key parameters

–Dtables=1: quantity.
-f 1: executions.
-r 300: execution time.
-t 200: number of threads used.
-w 0: number of warm-ups.

Test Procedure #

Create a test database.

mysql -utest -h192.168.xx.20 -P13306 -p

DROP SCHEMA IF EXISTS insert_db; 

CREATE TABLE `sbtest1` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `k` int NOT NULL DEFAULT '0',
  `c` char(255) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=1;

Install test tools.

Note: users have to configure JAVA and MAVEN environments in advance. openjdk 17.0.2 and Maven 3.6.3 are used in the following example.

Establish DBPlusEngine-Driver.

Users can write their own test cases based on the JMH framework or compile the test case set using the following codes.

git clone https://github.com/SphereEx-QE/database-jmh.git
cd database-jmh/jmh-shardingsphere5
mvn clean package && cd target

testssj.yaml configuration:

dataSources:
  write_ds:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.jdbc.Driver
    jdbcUrl: jdbc:mysql://192.168.xx.20:13306/insert_db?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
    username: root
    password: root
    maximumPoolSize: 300
    minimumIdle: 1

rules: []
props:

mysql.properties configuration:

jdbc-url=jdbc:mysql://192.168.xx.21:13306/pure_mysql?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
username=root
password=root

1. Perform the test.

Transfer the path parameter of test.yaml and other JMH parameters using the following commands (Appendix).

# If you'd like to learn about JMH parameters, you can run -help after the following commands.
java -classpath 'dependency/*:jmh-shardingsphere5-1.0-SNAPSHOT.jar'  -Dshardingsphere.configurationFile=/root/testssj.yaml  -Dtables=1  org.openjdk.jmh.Main "com.sphereex.jmh.shardingsphere5.ShardingSphereInsertOnlyBenchmark" -f 1 -i1 -r200 -t  100 -wi 0
java -classpath 'dependency/*:jmh-jdbc-1.0-SNAPSHOT.jar'  -Dconf=/root/mysql.properties  -Dtables=1  org.openjdk.jmh.Main "com.sphereex.jmh.jdbc.UnpooledReadOnlyBenchmarkJDBC" -f 1 -i1 -r200 -t  100 -wi 0

Test Result #

Test Object	TPS	Rows Count
DBPlusEngine-Driver	46,055	13,821,323
MySQL	46,402	13,909,701

Result: by comparing the imports of DBPlusEngine-Driver and MySQL, it turns out that there’s hardly any performance loss for DBPlusEngine-Driver.

The result is for reference only. Users can carry out a test based on actual business scenarios.

Monitoring Information #

DBPlusEngine-Driver

MySQL

Glossary #

TPS: transaction per second