Help & Documentation>TencentDB for MySQL>Operation Guide>Parameter Configuration>Performance Comparison of Parameter Templates

Performance Comparison of Parameter Templates

Last updated: 2023-09-04 10:18:38

On this page:

Test Tool

Sysbench 1.0.20 is the tool used to test the database benchmark performance.

Installing LZ4

Run the following code to install Sysbench 1.0.20:
git clone https://github.com/akopytov/sysbench.git
git checkout 1.0.20
yum install gcc gcc-c++ autoconf automake make libtool bzr mysql-devel git mysql
cd sysbench
./autogen.sh
./configure
make -j
make install
Note
The installation directions above apply to performance stress testing on a CentOS CVM instance. For directions on installing the tool on other operating systems, see the official Sysbench documentation.

Test environment

Local Disk Types
Note
Test instance specifications
Three common specifications, namely, 4-core CPU and 8 GB memory, 8-core CPU and 32 GB memory, and 16-core CPU and 128 GB memory
Client Configuration
64-core 128GB MEM
Client private network bandwidth
23Gbps
Test data volume
Database instance memory * 1.2
Test database instance versions
5.6 20210630、5.7 20210630、8.0 20210330
Note on client specification: high-spec client machines are used so as to ensure that the database instance performance can be measured through stress testing on a single client. For low-spec clients, it is recommended to use multiple clients for concurrent stress testing and aggregate the results.
Note on network latency: When performing the test, make sure that clients and database instances are in the same AZ to prevent the testing result from being affected by network factors.

Testing Method

Test data preparations

sysbench --db-driver=mysql --mysql-host=xxxx --mysql-port=xxxx --mysql-user=xxxx --mysql-password=xxxx --mysql-db=sbtest --table_size=xxxx --tables=xxxx --events=0 --time=600 --threads=xxxx --percentile=95 --report-interval=1 oltp_read_write prepare

Command for stress testing

sysbench --db-driver=mysql --mysql-host=xxxx --mysql-port=xxxx --mysql-user=xxxx --mysql-password=xxxx --mysql-db=sbtest --table_size=xxxx --tables=xxxx --events=0 --time=600 --threads=xxxx --percentile=95 --report-interval=1 oltp_read_write run
Descriptions of stress testing parameters:
oltp_read_write indicates to implement the OLTP test by calling the /usr/share/sysbench/oltp_read_write.lua script.
--tables=xxxx indicates the number of tables in this test.
--table_size=xxxx indicates the number of table rows in this test.
--threads=xxxx indicates the number of concurrent connections of the client in this test.
--report-interval=1 indicates that the test result is output once every second.
--percentile=95 indicates the sampling rate, which is 95% by default.
--time=600 indicates the execution time of this test, which is 600 seconds.

Scenario model

The test cases in this document all use the Lua script of sysbench. For the common configurations, performance testing is conducted for different parameter templates. The test results are as follows:

Test result

v5.6 20210630

CPU (Core)
Memory (GB)
threads
Test Duration
Template
SysBench QPS
SysBench TPS
avg_lat
4
8
32
10 min
Default template (disused)
34428.69
1721.43
18.59ms
High-Performance parameter template
35917.50
1795.87
17.82ms
High-Stability parameter template
34834.04
1741.70
18.37ms
8
32
64
10 min
Default template (disused)
61210.19
3060.51
20.91ms
High-Performance parameter template
67719.55
3385.98
18.90ms
High-Stability parameter template
64910.09
3245.50
19.72ms
16
128
128
10 min
Default template (disused)
106965.44
5348.27
23.93ms
High-Performance parameter template
127955.48
6397.77
20.00ms
High-Stability parameter template
119509.02
5975.45
21.41ms

v5.7 20210630

CPU (Core)
Memory (GB)
threads
Test Duration
Template
SysBench QPS
SysBench TPS
avg_lat
4
8
32
10 min
Default template (disused)
34428.69
1721.43
18.59ms
High-Performance parameter template
35917.50
1795.87
17.82ms
High-Stability parameter template
34834.04
1741.70
18.37ms
8
32
64
10 min
Default template (disused)
61210.19
3060.51
20.91ms
High-Performance parameter template
67719.55
3385.98
18.90ms
High-Stability parameter template
64910.09
3245.50
19.72ms
16
128
128
10 min
Default template (disused)
106965.44
5348.27
23.93ms
High-Performance parameter template
127955.48
6397.77
20.00ms
High-Stability parameter template
119509.02
5975.45
21.41ms

v8.0 20210330

CPU (Core)
Memory (GB)
threads
Test Duration
Template
SysBench QPS
SysBench TPS
avg_lat
4
8
32
10 min
Default template (disused)
32594.79
1629.74
19.63ms
High-Performance parameter template
33383.77
1669.19
19.17ms
High-Stability parameter template
32071.90
1603.60
19.95ms
8
32
64
10 min
Default template (disused)
65718.22
3285.91
19.47ms
High-Performance parameter template
70195.37
3509.77
18.23ms
High-Stability parameter template
60704.69
3035.23
21.08ms
16
128
128
10 min
Default template (disused)
132023.66
6601.18
19.38ms
High-Performance parameter template
151021.67
7551.08
16.95ms
High-Stability parameter template
132391.01
6619.55
19.33ms

中国站 - English