Summary of sysbench performance comparison test results between Klustron and TiDB

The Klustron team aims at friendly exchanges and learning. After each version is released, we will make a series of performance comparisons between Klustron and several other common distributed database systems in the industry for reference by industry insiders. We will release the comparison results of each product in succession in the near future. You are welcome to read the comparison and verify it yourself. If you have any questions, please ask questions in our official website forum.

Specifically, we will use Klustron to compare the performance of sysbench, TPC-C, TPC-H, TPC-DS with TiDB, CockroachDB, OceanBase, and compare the performance of TPC-H and TPC-DS with greenplum. At the same time, we will use Klustron-storage (that is, the storage node of Klustron) to compare the performance of sysbench and TPC-C with Percona-MySQL, PostgreSQL, and OpenGauss. Welcome everyone to continue to pay attention, forward and comment.

Sysbench performance comparison test between Klustron and TiDB

machine information

The machines used in this demonstration test are all from Alibaba Cloud's ECS. The specific machine information is:

Machine IP	ECS instance type	vCPUs	Memory	data disk	Role
172.22.161.13	ecs.i2.2xlarge	8	64GB	Local disk storage: 1788 GiB	Compute node + storage node + metadata node
172.22.161.14	ecs.i2.2xlarge	8	64GB	Local disk storage: 1788 GiB	Compute node + storage node + metadata node
172.22.161.15	ecs.i2.2xlarge	8	64GB	Local disk storage: 1788 GiB	Compute node + storage node + metadata node
172.22.161.16	ecs.c7.2xlarge	8	16GB	none	Sysbench client

Note:

• For Klustron, the computing node refers to Klustron-server, the storage node refers to Klustron-storage, and the metadata node refers to Kunlun-Meta;
• For TiDB, the computing node refers to TiDB, the storage node refers to TiKV, and the metadata node refers to PD;
• The following will distribute the last section using the ip addresses of these 4 machines, that is, 13, 14, 15, 16 as the ID of the machine to refer to this server.

Test data information

The test data is fed uniformly using sysbench 1.0.17 to generate 16 tables, and each table is filled with 10 million rows of data. The total amount of data is 0.2K 10000000 16 bytes, close to 32GB. where the table is defined as:

Product and Cluster Information

Klustron

Klustron also uses the latest official version 1.0.2. Among the three machines in the cluster, one metadata node, one computing node and two storage nodes are deployed on each machine.

Six storage nodes form two data shards. Since the read-write separation function is currently not enabled, only the primary node directly participates in read-write operations for each shard.

The cache size of the two master nodes is set to 16GB, so the total memory used by the cache is: 16 x 2 = 32 GB.

TiDB

TiDB uses the latest official version 6.1.1. Among the three machines in the cluster, each machine deploys a PD, a TiKV, and a TiDB node.

TiDB uses 40% of the machine memory as cache by default, so each machine uses 25.6GB as cache. The total cache used is 25.6 x 3 = 76.8 GB.

Test situation and results

In this test, five operations were compared, namely point check (oltp_point_select), update index field (oltp_update_index), update non-index field (oltp_update_non_index), mixed read and write (oltp_read_write), and compound query (oltp_read_only).

For each operation, three test streams are run at the same time, and three computing nodes on three machines are stress tested at the same time.

Each test flow uses 100 threads and runs for 60 seconds to save demo time.

For oltp_point_select, the corresponding command is:

Klustron: sysbench oltp_point_select --tables=16 --table-size=10000000 --db-driver=pgsql --pgsql-host=172.22.161.13/14/15 --pgsql-port=47001 --pgsql-user=abc --pgsql-password=abc --pgsql-db=test --threads=100 --report-interval=5 --time=60 run
TiDB: sysbench oltp_point_select --tables=16 --table-size=10000000 --db-driver=mysql --mysql-host=172.22.161.13/14/15 --mysql-port=40002 --mysql-user=root --mysql-password=root  --mysql-db=test --threads=100 --report-interval=5 --time=60 run

For oltp_update_index, the corresponding command is:

Klustron: sysbench oltp_update_index --tables=16 --table-size=10000000 --db-driver=pgsql --pgsql-host=172.22.161.13/14/15 --pgsql-port=47001 --pgsql-user=abc --pgsql-password=abc --pgsql-db=test --threads=100 --report-interval=5 --time=60 run
TiDB: sysbench oltp_update_index --tables=16 --table-size=10000000 --db-driver=mysql --mysql-host=172.22.161.13/14/15 --mysql-port=40002 --mysql-user=root --mysql-password=root --mysql-db=test --threads=100 --report-interval=5 --time=60 run

For oltp_update_non_index, the corresponding command is:

Klustron: sysbench oltp_update_non_index --tables=16 --table-size=10000000 --db-driver=pgsql --pgsql-host=172.22.161.13/14/15 --pgsql-port=47001 --pgsql-user=abc --pgsql-password=abc --pgsql-db=test --threads=100 --report-interval=5 --time=60 run
TiDB: sysbench oltp_update_non_index --tables=16 --table-size=10000000 --db-driver=mysql --mysql-host=172.22.161.13/14/15 --mysql-port=40002 --mysql-user=root --mysql-password=root --mysql-db=test --threads=100 --report-interval=5 --time=60 run

For oltp_read_write, the corresponding command is:

Klustron: sysbench oltp_read_write --tables=16 --table-size=10000000 --db-driver=pgsql --pgsql-host=172.22.161.13/14/15 --pgsql-port=47001 --pgsql-user=abc --pgsql-password=abc --pgsql-db=test --threads=100 --report-interval=5 --time=60 run
TiDB: sysbench oltp_read_write --tables=16 --table-size=10000000 --db-driver=mysql --mysql-host=172.22.161.13/14/15 --mysql-port=40002 --mysql-user=root --mysql-password=root --mysql-db=test --threads=100 --report-interval=5 --time=60 run

For oltp_read_only, the corresponding command is:

Klustron:  sysbench oltp_read_only --tables=16 --table-size=10000000 --db-driver=pgsql --pgsql-host=172.22.161.13/14/15 --pgsql-port=47001 --pgsql-user=abc --pgsql-password=abc --pgsql-db=test --threads=100 --report-interval=5 --time=60 run
TiDB: sysbench oltp_read_only --tables=16 --table-size=10000000 --db-driver=mysql --mysql-host=172.22.161.13/14/15 --mysql-port=40002 --mysql-user=root --mysql-password=root --mysql-db=test --threads=100 --report-interval=5 --time=60 run

The aggregated results of the three execution flows for each operation are:

Klustron:

Note: ms means milliseconds, and delay refers to the execution time of the entire transaction

TiDB: Note: ms means milliseconds, and delay refers to the execution time of the entire transaction

resource usage information

Currently, tools such as iostat and mpstat are used to collect information such as cpu and io, and the final summary results are as follows:

END