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

You need to use AWS and Azure Mysql services at work, and you need to test and compare the performance of the two cloud services. So start Baidu + google, looking for performance testing tools. Finally decided to use sysbench.

Sysbench introduction

Sysbench is an open source multithreading performance testing tool that can perform performance tests on CPU/ memory / threads / IO/ databases and so on.

The database currently supports MySQL/Oracle/PostgreSQL. This article is just a brief demonstration of the use of several tests, and we are going to use sysbench to conduct a series of tests on MySQL. Some specific parameter settings need to be adjusted according to different test requirements.

Installation

1. Download the installation package at https://github.com/akopytov/sysbench

# wget https://github.com/akopytov/sysbench/archive/1.0.zip-O "sysbench-1.0.zip" # unzip sysbench-1.0.zip#cd sysbench-1.0

two。 Install dependent libraries

# yum install automake libtool-y

3. Start installation

#. / autogen.sh#./configure#ERROR: cannot find MySQL libraries. If you want to compile with MySQL support cannot find the mysql library. You need to specify-- with-mysql-includes and-- with-mysql-libs#./configure-- with-mysql-includes=/alidata/server/mysql5.7/include/-- with-mysql-libs=/alidata/server/mysql5.7/lib/ with parameters.

4. Since mysql is installed by yum, I don't know where the path is. How do I find the installation path for mysql?

Install mysql-devel before you can use mysql_config

# yum install-y mysql-devel#mysql_config-helpUsage: / usr/bin/mysql_config-64 [OPTIONS] Options:--cflags [- I/usr/include/mysql-g-fstack-protector-M64-fPIC-g-fabi-version=2-fno-omit-frame-pointer-fno-strict-aliasing]-- cxxflags [- I/usr/include/mysql-g-fexceptions-fstack-protector-M64-fPIC-g-fabi-version=2-fno- Omit-frame-pointer-fno-strict-aliasing]-- include [- I/usr/include/mysql]-- libs [- L/usr/lib64/mysql-lmysqlclient-lpthread-lm-lrt-ldl]-- libs_r [- L/usr/lib64/mysql-lmysqlclient-lpthread-lm-lrt-ldl]-- plugindir [/ usr/lib64/mysql/plugin]-- socket [/ var/lib/mysql/mysql.sock ]-- port [0]-- version [5.6.39]-- libmysqld-libs [- L/usr/lib64/mysql-lmysqld-lpthread-lm-lrt-lcrypt-ldl-laio-lnuma]-variable=VAR VAR is one of:pkgincludedir [/ usr/include/mysql] pkglibdir [/ usr/lib64/mysql] plugindir [/ usr/lib64/mysql/plugin]

5. Execute config again, successful

#. / configure-with-mysql-includes=/usr/include/mysql-with-mysql-libs=/usr/lib64/mysql#make

6. Execute the following command:

# sysbench-- help#sysbench: error while loading shared libraries: libmysqlclient.so.20: cannot open shared object file: No such file or directory# cause: sysbench cannot find the library file of mysql. It may be that the environment variable LD_LIBRARY_PATH is not set. You can solve this problem after setting it: # export LD_LIBRARY_PATH=/alidata/server/mysql5.7/lib/lib#sysbench-- versionsysbench 1.0.12 (using bundled LuaJIT 2.1.0-beta2)

Prepare test tables and data

1. Create a test database:

Mysql > create database dbtest

two。 Test command

# / home/mysql/sysbench-1.0/src/sysbench-- test=/home/mysql/sysbench-1.0/tests/include/oltp_legacy/oltp.lua\-- mysql-host=mysql-host-ip-- mysql-port=3306-- mysql-user=envision-- mysql-password=password\-- mysql-db=dbtest-- oltp-tables-count=10-- oltp-table-size=500000\-- report-interval=10-- rand-init=on-- max-requests=0\-- oltp-read-only=off-- max-time=120-- num-threads=30\ [prepare | run | cleanup]

# # choose one of three

Prepare preparation

Run operation

Cleanup cleans up data.

Note that in the last line, a test requires prepare to prepare the table and data, run performs a real stress test, and cleanup is used to clear the data and table.

3. Execute the sysbench command parameter explanation:

#-test=/root/sysbench-1.0/tests/include/oltp_legacy/oltp.lua means to invoke oltp.lua script to test oltp mode #-- oltp_tables_count=10 indicates that 10 test tables will be generated #-- oltp-table-size=500000 indicates that each test table is populated with 500000 #-- rand-init=on indicates that each test table is populated with random data #-num-threads=8 indicates that 8 concurrent connections are initiated -oltp-read-only=off says do not take a read-only test That is, the test will be tested in read-write mixed mode #-report-interval=10 indicates that the test progress report is output every 10 seconds #-rand-type=uniform indicates that the random type is fixed mode, and several other optional random modes: uniform (fixed), gaussian (Gaussian), special (specific), pareto (Pareto) #-max-time=120 indicates a maximum execution time of 120 seconds #-max-requests=0 indicates that the total number of requests is 0 Because the total execution time has been defined above, the total number of requests can be set to 0 You can also set only the total number of requests but not the maximum execution time #-percentile=99 means to set the sampling ratio. The default is 95%, that is, discard 1% of long requests and take the maximum value in the remaining 99%.

4. Test preparation: 30 concurrent connections, 10 tables filled with 50W pieces of data, the maximum request time is 120s

# / home/mysql/sysbench-1.0/src/sysbench-- test=/home/mysql/sysbench-1.0/tests/include/oltp_legacy/oltp.lua\-- mysql-host=mysql-host-ip-- mysql-port=3306-- mysql-user=envision-- mysql-password=password\-- mysql-db=dbtest-- oltp-tables-count=10-- oltp-table-size=500000\-report-interval=10-- rand-init=on-- max-requests=0\-- oltp-read-only=off-- max-time=120-- num-threads=30\ PrepareCreating 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'.Inserting 100000 records into 'sbtest9'Creating secondary indexes on' sbtest9'...Creating table 'sbtest10'...Inserting 100000 records into' sbtest10'Creating secondary indexes on 'sbtest10'...

5. Execute sysbench test and output test report

# / home/mysql/sysbench-1.0/src/sysbench-- test=/home/mysql/sysbench-1.0/tests/include/oltp_legacy/oltp.lua\-- mysql-host=mysql-host-ip-- mysql-port=3306-- mysql-user=envision-- mysql-password=password\-- mysql-db=dbtest-- oltp-tables-count=10-- oltp-table-size=500000\-- report-interval=10-- rand-init=on-- max-requests=0\-- oltp-read-only=off-- max-time=120-- num-threads=128\ run > / tmp/liang/mysql-report.txt

6. View test report

30 threads

Sysbench 1.0.12 (using bundled LuaJIT 2.1.0-beta2) Running the test with following options:Number of threads: 30Report intermediate results every 10 second (s) Initializing random number generator from current timeInitializing worker threads...Threads started! [10s] thds: 30 tps: 188.63 qps: 3795.16 (r/w/o: 2659.89 + 755.31) lat (ms) 95%): 223.34 err/s: 0.00 reconn/s: 0.00 [20s] thds: 30 tps: 177.80 qps: 3563.97 (r/w/o: 2496.95max 711.31comp355.71) lat (ms,95%): 248.83 err/s: 0.00 reconn/s: 0.00 [30s] thds: 30 tps: 177.20 qps: 3542.62 (r/w/o: 2479.82Univ 708.30Universe 354.50) lat (ms) 95%): 235.74 err/s: 0.00 reconn/s: 0.00 [40s] thds: 30 tps: 189.60 qps: 3797.38 (r/w/o: 2658.9999max 759.20reconn/s 379.20) lat (ms,95%): 227.40 err/s: 0.00 reconn/s: 0.00 [50s] thds: 30 tps: 190.40 qps: 3798.09 (r/w/o: 2655.29Unip 762.20Universe 380.60) lat (ms) 95%): 211.60 err/s: 0.00 reconn/s: 0.00 [60s] thds: 30 tps: 179.70 qps: 3598.40 (r/w/o: 2520.50 thds 718.40 reconn/s 359.50) lat (ms,95%): 235.74 err/s: 0.00 reconn/s: 0.00 [70s] thds: 30 tps: 187.30 qps: 3740.91 (r/w/o: 2617.61 thds 748.50 tps 374.80) lat (ms) 95%): 227.40 err/s: 0.00 reconn/s: 0.00 [80s] thds: 30 tps: 187.40 qps: 3755.29 (r/w/o: 2630.19Maple 750.40reconn/s 374.70) lat (ms,95%): 211.60 err/s: 0.00 reconn/s: 0.00 [90s] thds: 30 tps: 188.10 qps: 3762.11 (r/w/o: 2632.800.753.10 qps) lat (ms 95%): 211.60 err/s: 0.00 reconn/s: 0.00 [100s] thds: 30 tps: 183.70 qps: 3676.35 (r/w/o: 2575.16 reconn/s 733.69 reconn/s 367.49) lat (ms,95%): 227.40 err/s: 0.00 reconn/s: 0.00 [110s] thds: 30 tps: 190.80 qps: 3816.33 (r/w/o: 2671.42 thds 763.41) lat (ms) 211.60 err/s: 0.00 reconn/s: 0.00 [120s] thds: 30 tps: 195.49 qps: 3907.36 (r/w/o: 2733.40 qps) lat (ms) ): 204.11 err/s: 0.00 reconn/s: 0.00SQL statistics: queries performed: read: 313488-- Total number of reads write: 89568-- Total number of writes other: 44784-- Operations other than CURD For example, COMMIT) total: 447840-- Total total transactions: 22392 (186.27 per sec.)-- Total transactions (transactions per second) queries: 447840 (3725.43 per sec.)-- Total (total transactions per second) ignored errors: 0 (0.00 per sec.)-- Total number of ignore errors (number of ignore errors per second) reconnects: 0 (0.00 per sec.)-- Total number of reconnections (number of reconnections per second) General statistics: total time: 120.2098s-- Total time consuming total number of events: 22392-total number of transactions Latency (ms): min: 105.91-minimum time consuming avg: 160.86-average time consuming max: 850.77-longest time consuming 95th Percentile: 223.34-over 95% average elapsed time sum: 3601892.56Threads fairness: events (avg/stddev): 746.4000 execution time 4.95-Total number of handled events / standard deviation execution time (avg/stddev): 120.0631 Compact 0.05 copyright-Total execution time / standard deviation

After testing, the mysql performance of AWS and Azure is about the same. Sysbench's tools are also relatively simple and intuitive to use. Suitable for simple performance testing of Mysql. If you have time later, take a look at sysbench's performance tests such as CPU,IO and how you feel about it.

Reference documentation:

Http://blog.csdn.net/oahz4699092zhao/article/details/53332105

Http://www.jb51.net/article/93924.htm

Https://nsimple.top/archives/mysql-sysbench-tool.html

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.