Shulou(Shulou.com)06/01 Report--

This article introduces how to use Sysbench to benchmark Didi Cloud MySQL, the content is very detailed, interested friends can refer to, hope to be helpful to you.

Sysbench is an open source multi-threaded, cross-platform benchmark tool that can perform performance testing of CPU/ memory / threads / IO/ databases. Sysbench is used here to benchmark Didi Cloud's MySQL.

The MySQL database used is a Didi Cloud MySQL database with 4G memory and 50G storage capacity. Since Didi Cloud MySQL cannot be accessed through the public network by default, this database is connected through a Didi Cloud Virtual Machine (DC2). The use of Didi Cloud MySQL can refer to the following link, which will not be discussed here.

Https://help.didiyun.com/hc/kb/article/1143410/

A key step is to create a user for the CVM to access the MySQL normally. To facilitate testing, it is recommended to create a high-permission account.

Commonly used benchmark indicators include

TPS/QPS: measures throughput.

Response time: including average response time, minimum response time, maximum response time, percentage of time, etc., in which the percentage of time is of great significance, such as the maximum response time of the first 95% of requests.

Concurrency: the number of query requests processed simultaneously.

1. Install the MySQL5.7 client on the CVM: [root@dc2-user] # wget http://repo.mysql.com/mysql57-community-release-el7-8.noarch.rpm[root@dc2-user]# rpm-ivh mysql57-community-release-el7-8.noarch.rpm [root@dc2-user] # yum install mysql-community-client2. Install sysbench on the CVM

There are some differences between different versions of sysbench syntax, and the version used in this article is sysbench2.0.

Download and decompress:

[root@dc2-user] # wget https://github.com/akopytov/sysbench/archive/1.0.zip-O "sysbench-1.0.zip" [root@dc2-user] # unzip sysbench-1.0.zip [root@dc2-user] # cd sysbench-1.0

Installation dependencies:

[root@dc2-user] # yum install automake libtool mysql-devel-y

Install and confirm that the current directory is the sysbench directory.

[root@dc2-user] #. / autogen.sh [root@dc2-user] #. / configure [root@dc2-user] # make & & make install

The default sysbench command is in this path, / usr/local/bin/sysbench. If / usr/local/bin is not added as an environment variable, you can add a soft chain to the sysbench command for convenience.

[root@dc2-user] # ln-s / usr/local/bin/sysbench / usr/bin/sysbench

Verify command availability:

[root@dc2-user] # sysbench-- versionsysbench 1.0.203. Sysbench command usage

Use sysbench-- help to view the command usage of sysbench, and here are only some of the command options and parameters used in this article.

[root@dc2-user] # sysbench-- helpUsage: sysbench [options]. [testname] [command]

The [options] parameter used in this example is as follows:-- the hostname of the mysql-host:MySQL server. If the default localhost; uses localhost to report an error on the local machine, it indicates that the MySQL server cannot be connected, and it should be changed to the IP address of the local machine. -- mysql-port:MySQL server port. Default is 3306-- mysql-user: user name-- mysql-password:MySQL server password-- oltp-test-mode: execution mode, including simple, nontrx and complex. Default is complex. Only simple queries are tested in simple mode; nontrx not only tests queries, but also inserts updates, etc., but does not use transactions; in complex mode, testing is the most comprehensive, testing additions, deletions, changes and queries, and transactions are used. In this case, complex is used. -- oltp-tables-count: the number of tables tested. For this example, select 10 tables. -- oltp-table-size: the size of the table being tested. In this example, 100000 pieces of data are selected. -- threads: the number of concurrent connections of the client. In this example, 10 is selected. -- time: the time it takes for the test to execute (in seconds). In this example, 120 seconds is selected. -- report-interval: the interval between generating the report (in seconds). In this example, 10 seconds is selected.

[testname] specify which lua script to use for testing. It can be written by yourself or shipped with sysbench. The self-contained script is located under tests/include/oltp_legacy in the decompressed sysbench directory. In this example, oltp.lua script is used to simulate general OLTP operations such as concurrency, table creation, data insertion, data deletion and so on. [command] options include prepare, run, and cleanup, that is, data preparation, test execution, and post-test data cleaning.

4. Test example

First create a sbtest database on the Didi Cloud page.

(1) prepare data

[root@dc2-user] # sysbench / home/dc2-user/sysbench-1.0/tests/include/oltp_legacy/oltp.lua-- mysql-host=10.254.170.17-- mysql-port=3306-- mysql-user=root-- mysql-password=123456-- oltp-test-mode=complex-- oltp-tables-count=10-- oltp-table-size=100000-- threads=10-- time=120-- report-interval=10 preparesysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2) Creating table 'sbtest1'...Inserting 100000 records Into 'sbtest1'Creating secondary indexes on' sbtest1'...Creating table 'sbtest2'...Inserting 100000 records into' sbtest2'Creating secondary indexes on 'sbtest2'...Creating table' sbtest3'...Inserting 100000 records into 'sbtest3'Creating secondary indexes on' sbtest3'...Creating table 'sbtest4'...Inserting 100000 records into' sbtest4'Creating secondary indexes on 'sbtest4'...Creating table' sbtest5'...Inserting 100000 records into 'sbtest5'Creating secondary indexes on' sbtest5'...Creating table 'sbtest6'. .. Inserting 100000 records into 'sbtest6'Creating secondary indexes on' sbtest6'...Creating table 'sbtest7'...Inserting 100000 records into' sbtest7'Creating secondary indexes on 'sbtest7'...Creating table' sbtest8'...Inserting 100000 records into 'sbtest8'Creating secondary indexes on' sbtest8'...Creating table 'sbtest9'...Inserting 100000 records into' sbtest9'Creating secondary indexes on 'sbtest9'...Creating table' sbtest10'...Inserting 100000 records into 'sbtest10'Creating secondary indexes on' sbtest10'...

(2) the execution test can save the execution results in a file for subsequent analysis.

[root@dc2-user] # sysbench. / sysbench-1.0/tests/include/oltp_legacy/oltp.lua-- mysql-host=10.254.170.17-- mysql-port=3306-- mysql-user=root-- mysql-password=123456-- oltp-test-mode=complex-- oltp-tables-count=10-- oltp-table-size=100000-- threads=10-- time=120-- report-interval=10 run > > / home/dc2-user/mysysbench.log

(3) Clean up data

[root@dc2-user] # sysbench. / sysbench-1.0/tests/include/oltp_legacy/oltp.lua-- mysql-host=10.254.170.17-- mysql-port=3306-- mysql-user=root-- mysql-password=123456 cleanupsysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2) Dropping table 'sbtest1'...

# # interpret test results # # Open the saved test results.

[root@dc2-user] # cat mysysbench.log sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2) Running the test with following options:Number of threads: 10Report intermediate results every 10 second (s) Initializing random number generator from current timeInitializing worker threads...Threads started! [10s] thds: 10 tps: 358.81 qps: 7186.31 (r/w/o: 5032.37 qps) lat (ms) 95%): 39.65 err/s: 0.00 reconn/s: 0.00 [20s] thds: 10 tps: 342.69 qps: 6850.20 (r/w/o: 4794.16 qps 1370.66) lat (ms,95%): 44.98 err/s: 0.00 reconn/s: 0.00 [30s] thds: 10 tps: 364.58 qps: 7296.91 (r/w/o: 5108.46 Universe 1459.40) lat (ms) 95%): 38.25 err/s: 0.00 reconn/s: 0.00 [40s] thds: 10 tps: 371.62 qps: 7428.24 (r/w/o: 5199.11 ms,95% 1485.79 reconn/s) lat (ms,95%): 38.94 err/s: 0.00 reconn/s: 0.00 [50s] thds: 10 tps: 355.44 qps: 7112.96 (r/w/o: 4980.30 Universe 1421.7710.89) lat (ms) 95%): 43.39 err/s: 0.00 reconn/s: 0.00 [60s] thds: 10 tps: 377.68 qps: 7551.83 (r/w/o: 5285.47) 1511.01) lat (ms,95%): 36.89 err/s: 0.00 [70s] thds: 10 tps: 378.59 qps: 7572.99 (r/w/o: 5301.26,1514.56) lat (ms) 95%): 38.25 err/s: 0.00 reconn/s: 0.00 [80s] thds: 10 tps: 365.19 qps: 7303.79 (r/w/o: 5113.05 1460.46 reconn/s 730.28) lat (ms,95%): 41.10 err/s: 0.00 reconn/s: 0.00 [90s] thds: 10 tps: 354.24 qps: 7084.59 (r/w/o: 4959.65max 1416.36) lat (ms: 708.58) 95%): 40.37 err/s: 0.00 reconn/s: 0.00 [100s] thds: 10 tps: 372.75 qps: 7453.38 (r/w/o: 5216.16 1491.82 reconn/s 745.41) lat (ms,95%): 38.94 err/s: 0.00 reconn/s: 0.00 [110s] thds: 10 tps: 349.95 qps: 7002.81 (r/w/o: 4902.60) lat (ms: 1400.20 qps) Err/s: 0.00 reconn/s: 0.00 [120s] thds: 10 tps: 371.82 qps: 7432.30 (r/w/o: 5202.68, 1486.08) lat (ms Err/s: 611030 reconn/s: 0.00SQL statistics: queries performed: read: 611030 write: 174580 other: 87290 total: 872900 transactions: 43645 (363.59 per sec.) Queries: 872900 (7271.87 per sec.) Ignored errors: 0 (0.00 per sec.) Reconnects: 0 (0.00 per sec.) General statistics: total time: 120.0362s total number of events: 43645Latency (ms): min: 18.29 avg: 27. 49 max: 677.90 95th percentile: 40.37 sum: 1199972.75Threads fairness: events (avg/stddev): 4364.5000 execution time 72.30 execution time (avg/stddev): 119.9973

We generally pay attention to these two indicators here.

Queries: 872900 (7271.87 per sec) 95th percentile: 40.37

We can see that the maximum delay of 95% requests with 7271.87 QPS is 40.37 milliseconds. For the currently configured mysql, the test result is ideal.

So much for sharing about how to use Sysbench to benchmark Didi Cloud MySQL. I hope the above content can be of some help and learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.