Install and use the MySQL Performance Testing Tool sysbench

Install and use the MySQL Performance Testing Tool sysbench

We need to use AWS and Azure MySQL services at work. We need to test and compare the performance of the two cloud services. As a result, Baidu + google began to search for performance testing tools. Finally, we decided to use sysbench.

Sysbench Introduction

Sysbench is an open-source multi-threaded performance testing tool that can perform performance tests on CPU, memory, thread, IO, and database.

The database currently supports MySQL, Oracle, and PostgreSQL. This article only briefly demonstrates the usage of several tests. We will use sysbench to perform a series of tests on MySQL in the future. Specific parameter settings must be adjusted according to different test requirements.

Install

1. Download the installation package, address 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

2. Install the dependent Library

# 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 database, you must specify the parameters -- with-mysql-related des and -- with-mysql-libs.
#./Configure -- with-mysql-supported des =/alidata/server/mysql5.7/include/-- with-mysql-libs =/alidata/server/mysql5.7/lib/

4. because mysql is installed in yum, I do not know where the path is. How can I find the installation path of mysql?

To use mysql_config, install mysql-devel.

# Yum install-y mysql-devel
# Mysql_config-help
Usage:/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. sock]
-- Port [0]
-- Version [5.6.39]
-- Libmysqld-libs [-L/usr/lib64/mysql-lmysqld-lpthread-lm-lrt-lcrypt-ldl-laio-lnuma]
-- Variable = VAR is one:
Pkgincludedir [/usr/include/mysql]
Pkglibdir [/usr/lib64/mysql]
Plugindir [/usr/lib64/mysql/plugin]

5. Run config again. The operation is successful.

#./Configure -- with-mysql-related des =/usr/include/mysql -- with-mysql-libs =/usr/lib64/mysql
# Make

6. Run 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 mysql database file. It may be because the environment variable LD_LIBRARY_PATH is not set. After setting, the problem can be solved:
# Export LD_LIBRARY_PATH =/alidata/server/mysql5.7/lib
# Sysbench -- version
Sysbench 1.0.12 (using bundled LuaJIT 2.1.0-beta2)

Prepare test tables and data

1. Create a test database:

1 mysql> create database dbtest;

2. 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]

# Select one from three

Prepare preparation;

Run;

Cleanup clears data.

Note that in the last row, you must use prepare to prepare tables and data before a test starts. run runs the real stress test and cleanup is used to clear data and tables.

3. Run the sysbench command to explain the parameters:

#-Test =/root/sysbench-1.0/tests/include/oltp_legacy/oltp. lua indicates to call the oltp. lua script for oltp mode testing
# -- Oltp_tables_count = 10 indicates that 10 test tables will be generated.
# -- Oltp-table-size = 500000 indicates that the amount of data filled in each test table is 500000
# -- Rand-init = on indicates that each test table is filled with random data.
#-Num-threads = 8 indicates that eight concurrent connections are initiated.
# -- Oltp-read-only = off indicates that the read-only test is not performed, that is, the read/write hybrid mode test is used. # -- report-interval = 10 indicates that the Test Progress report is output every 10 seconds.
# -- Rand-type = uniform indicates that the random type is fixed. The other several optional random modes are uniform (fixed), gaussian (gaussian), special (specific), and pareto ()
# -- Max-time = 120 indicates the maximum execution duration of 120 seconds # -- max-requests = 0 indicates that the total number of requests is 0, because the total execution duration has been defined above, therefore, you can set the total number of requests to 0, or set the total number of requests without setting the maximum execution duration.
# -- Percentile = 99 indicates that the sampling ratio is set. The default value is 95%, that is, to discard a 1% long request and obtain the maximum value in the remaining 99%.

4. Test preparation: 30 concurrent connections, 10 tables with a maximum request time of 120 million data records per table (s)

#/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
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 '...
......
Inserting 100000 records into 'sbtest9'
Creating secondary indexes on 'sbtest9 '...
Creating table 'sbtest10 '...
Inserting 100000 records into 'sbtest10'
Creating secondary indexes on 'sbtest10 '...

5. Run the sysbench test and output the 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 the test report

30 threads

Sysbench 1.0.12 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 30
Report intermediate results every 10 second (s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[10 s] thds: 30 tps: 188.63 qps: 3795.16 (r/w/o: 2659.89/755.31/379.96) lat (MS, 95%): 223.34 err/s: 0.00 reconn/s: 0.00
[20 s] thds: 30 tps: 177.80 qps: 3563.97 (r/w/o: 2496.95/711.31/355.71) lat (MS, 95%): 248.83 err/s: 0.00 reconn/s: 0.00
[30 s] thds: 30 tps: 177.20 qps: 3542.62 (r/w/o: 2479.82/708.30/354.50) lat (MS, 95%): 235.74 err/s: 0.00 reconn/s: 0.00
[40 s] thds: 30 tps: 189.60 qps: 3797.38 (r/w/o: 2658.99/759.20/379.20) lat (MS, 95%): 227.40 err/s: 0.00 reconn/s: 0.00
[50 s] thds: 30 tps: 190.40 qps: 3798.09 (r/w/o: 2655.29/762.20/380.60) lat (MS, 95%): 211.60 err/s: 0.00 reconn/s: 0.00
[60 s] thds: 30 tps: 179.70 qps: 3598.40 (r/w/o: 2520.50/718.40/359.50) lat (MS, 95%): 235.74 err/s: 0.00 reconn/s: 0.00
[70 s] thds: 30 tps: 187.30 qps: 3740.91 (r/w/o: 2617.61/748.50/374.80) lat (MS, 95%): 227.40 err/s: 0.00 reconn/s: 0.00
[80 s] thds: 30 tps: 187.40 qps: 3755.29 (r/w/o: 2630.19/750.40/374.70) lat (MS, 95%): 211.60 err/s: 0.00 reconn/s: 0.00
[90 s] thds: 30 tps: 188.10 qps: 3762.11 (r/w/o: 2632.80/753.10/376.20) lat (MS, 95%): 211.60 err/s: 0.00 reconn/s: 0.00
[100 s] thds: 30 tps: 183.70 qps: 3676.35 (r/w/o: 2575.16/733.69/367.49) lat (MS, 95%): 227.40 err/s: 0.00 reconn/s: 0.00
[110 s] thds: 30 tps: 190.80 qps: 3816.33 (r/w/o: 2671.42/763.41/381.50) lat (MS, 95%): 211.60 err/s: 0.00 reconn/s: 0.00
[120 s] thds: 30 tps: 195.49 qps: 3907.36 (r/w/o: 2733.40/783.17/390.79) lat (MS, 95%): 204.11 err/s: 0.00 reconn/s: 0.00
SQL statistics:
Queries saved med:
Read: 313488 -- total number of reads
Write: 89568 -- total number of writes
Other: 44784 -- other operations (operations other than CURD, such as COMMIT)
Total: 447840 -- total number of all
Transactions: 22392 (186.27 per sec.) -- total number of transactions (transactions per second)
Queries: 447840 (3725.43 per sec.) -- total (total per second)
Ignored errors: 0 (0.00 per sec.) -- total number of ignored errors (number of ignored errors per second)
Reconnects: 0 (0.00 per sec.) -- total reconnections (reconnections per second)
General statistics:
Total time: 120.2098 s -- total time consumed
Total number of events: 22392 -- total number of transactions
Latency (MS ):
Min: 105.91 -- minimum time consumed
Avg: 160.86 -- average time consumption
Max: 850.77 -- maximum time consumed
95th percentile: 223.34 -- over 95% average time consumption
Sum: 3601892.56
Threads fairness:
Events (avg/stddev): 746.4000/4.95 -- total number of events processed/Standard Deviation
Execution time (avg/stddev): 120.0631/0.05 -- total execution time/Standard Deviation

After testing, the performance of AWS and Azure mysql is almost the same. The sysbench tool is simple and intuitive to use. It is suitable for simple Mysql performance testing. If you have time in the future, study the performance tests and usage experience of sysbench on CPU and IO.

This article permanently updates link: https://www.bkjia.com/Linux/2018-03/151546.htm