Method for implementing performance testing of MySQL database through sysbench tool

1. Background

Sysbench is a stress testing tool that can test the hardware performance of the system and can also be used to benchmark the database. The tests supported by sysbench include CPU computing performance test, memory allocation and transmission speed test, disk IO performance test, POSIX thread performance test, mutual exclusion test, and database performance test (OLTP benchmark test). The databases currently supported are mainly MySQL database and PG database.

When a new server goes online, it is recommended to test the server's performance. It is best to make a horizontal comparison with the performance test reports of previous servers of the same type to identify potential problems. Before a new machine goes online, do a physical check on the server.

For the database, we can use the sysbench tool to implement database benchmarking. In the current system architecture, the front end is relatively easy to expand horizontally, while the database is relatively difficult. Therefore, benchmark testing plays a very important role for the database. The purpose of database benchmarking is to analyze the performance of the database under the current configuration (including hardware configuration, OS, database settings, etc.), so as to find the performance threshold of MySQL and adjust the configuration according to the actual system requirements.

2. Installation of sysbench

1) Installation command

yum -y install sysbench

2) Check the installed version

sysbench --version

3) View the information of installed software (mainly through the rpm command).

Query the installation information of sysbench. When testing mysql, you need to use the lua script that comes with sysbench for testing. If you use the quick installation method, the default script path is: /usr/share/sysbench .

If it is not in this command, we execute the following command to check and find all the sysbench software programs installed on the local Linux system:

rpm -qa sysbench

List the complete file names of all files and directories of the software (list):

rpm -ql sysbench

3.sysbench syntax

sysbench --help
Usage:
 sysbench [options]... [testname] [command]
Commands implemented by most tests: prepare run cleanup help
General options:
 --threads=N number of threads to use [1]
 --events=N limit for total number of events [0]
 --time=N limit for total execution time in seconds [10]
 --forced-shutdown=STRING number of seconds to wait after the --time limit before forcing shutdown, or 'off' to disable [off]
 --thread-stack-size=SIZE size of stack per thread [64K]
 --rate=N average transactions rate. 0 for unlimited rate [0]
 --report-interval=N periodically report intermediate statistics with a specified interval in seconds. 0 disables intermediate reports [0]
 --report-checkpoints=[LIST,...] dump full statistics and reset all counters at specified points in time. The argument is a list of comma-separated values ​​representing the amount of time in seconds elapsed from start of test when report checkpoint(s) must be performed. Report checkpoints are off by default. []
 --debug[=on|off] print more debugging info [off]
 --validate[=on|off] perform validation checks where possible [off]
 --help[=on|off] print help and exit [off]
 --version[=on|off] print version and exit [off]
 --config-file=FILENAME File containing command line options
 --tx-rate=N deprecated alias for --rate [0]
 --max-requests=N deprecated alias for --events [0]
 --max-time=N deprecated alias for --time [0]
 --num-threads=N deprecated alias for --threads [1]
Pseudo-Random Numbers Generator options:
 --rand-type=STRING random numbers distribution {uniform,gaussian,special,pareto} [special]
 --rand-spec-iter=N number of iterations used for numbers generation [12]
 --rand-spec-pct=N percentage of values ​​to be treated as 'special' (for special distribution) [1]
 --rand-spec-res=N percentage of 'special' values ​​to use (for special distribution) [75]
 --rand-seed=N seed for random number generator. When 0, the current time is used as a RNG seed. [0]
 --rand-pareto-h=N parameter h for pareto distribution [0.2]
Log options:
 --verbosity=N verbosity level {5 - debug, 0 - only critical messages} [3]
 --percentile=N percentile to calculate in latency statistics (1-100). Use the special value of 0 to disable percentile calculations [95]
 --histogram[=on|off] print latency histogram in report [off]
General database options:
 --db-driver=STRING specifies database driver to use ('help' to get list of available drivers) [mysql]
 --db-ps-mode=STRING prepared statements usage mode {auto, disable} [auto]
 --db-debug[=on|off] print database-specific debug information [off]
Compiled-in database drivers:
 MySQL driver
 pgsql - PostgreSQL driver
mysql options:
 --mysql-host=[LIST,...] MySQL server host [localhost]
 --mysql-port=[LIST,...] MySQL server port [3306]
 --mysql-socket=[LIST,...] MySQL socket
 --mysql-user=STRING MySQL user [sbtest]
 --mysql-password=STRING MySQL password []
 --mysql-db=STRING MySQL database name [sbtest]
 --mysql-ssl[=on|off] use SSL connections, if available in the client library [off]
 --mysql-ssl-cipher=STRING use specific cipher for SSL connections []
 --mysql-compression[=on|off] use compression, if available in the client library [off]
 --mysql-debug[=on|off] trace all client library calls [off]
 --mysql-ignore-errors=[LIST,...] list of errors to ignore, or "all" [1213,1020,1205]
 --mysql-dry-run[=on|off] Dry run, pretend that all MySQL client API calls are successful without executing them [off]
pgsql options:
 --pgsql-host=STRING PostgreSQL server host [localhost]
 --pgsql-port=N PostgreSQL server port [5432]
 --pgsql-user=STRING PostgreSQL user [sbtest]
 --pgsql-password=STRING PostgreSQL password []
 --pgsql-db=STRING PostgreSQL database name [sbtest]
Compiled-in tests:
 fileio - File I/O test
 cpu - CPU performance test
 memory - Memory functions speed test
 threads - Threads subsystem performance test
 mutex - Mutex performance test

The basic syntax is as follows:

sysbench [options]... [testname] [command]

command is the command to be executed by sysbench, including prepare, run, and cleanup. prepare is to prepare data for testing in advance, run is to execute formal tests, and cleanup is to clean up the database after the test is completed.

testname specifies the test to be performed. In the old version of sysbench, you can use the --test parameter to specify the test script. In the new version, the --test parameter has been declared obsolete. You can specify the script directly instead of using --test. The script used in the test is a Lua script. You can use the script that comes with sysbench or develop your own.

options about MySQL mainly include MySQL connection information parameters and MySQL execution related parameters.

4 Testing

Step 1: Prepare stress test data

sysbench /usr/share/sysbench/oltp_insert.lua --mysql-host=XXX.XXX.XXX.XXX --mysql-port=3306 --mysql-user=testsbuser --mysql-password='textpwd' --mysql-db=tssysbench --db-driver=mysql --tables=15 --table-size=500000 --report-interval=10 --threads=128 --time=120 prepare

Step 2: Stress Testing

sysbench /usr/share/sysbench/oltp_insert.lua --mysql-host=XXX.XXX.XXX.XXX --mysql-port=3306 --mysql-user=testsbuser --mysql-password='textpwd' --mysql-db=tssysbench --db-driver=mysql --tables=15 --table-size=500000 --report-interval=10 --threads=128 --time=120 run

The test results can also be exported to a file for subsequent analysis.

 sysbench /usr/share/sysbench/oltp_insert.lua --mysql-host=XXX.XXX.XXX.XXX --mysql-port=3306 --mysql-user=testsbuser --mysql-password='testpwd' --mysql-db=tssysbench --db-driver=mysql --tables=15 --table-size=500000 --report-interval=10 --threads=128 --time=120 run >> ./mysysbench.log 

Step 3: Clean up stress test data

sysbench /usr/share/sysbench/oltp_insert.lua --mysql-host=XXX.XXX.XXX.XXX --mysql-port=3306 --mysql-user=testsbuser --mysql-password='testpwd' --mysql-db=tssysbench --db-driver=mysql --tables=15 --table-size=500000 --report-interval=10 --threads=128 --time=120 cleanup

5. Notes

(1) The test database needs to be created in advance, and the test account must have permission to create the database.

The --mysql-db parameter specifies the test data, the default is sbtest.

If it is not created in advance, the error message is as follows;

FATAL: `sysbench.cmdline.call_command' function failed: /usr/share/sysbench/oltp_common.lua:83: connection creation failed
(last message repeated 3 times)
FATAL: error 1049: Unknown database 'sysbench_db'
FATAL: `sysbench.cmdline.call_command' function failed: /usr/share/sysbench/oltp_common.lua:83: connection creation failed
FATAL: unable to connect to MySQL server on host 'XXX.XXX.XXX.XXX', port 3306, aborting...
(last message repeated 1 times)
FATAL: error 1049: Unknown database 'sysbench_db'
(last message repeated 1 times)

or (without specifying a database)

FATAL: `sysbench.cmdline.call_command' function failed: /usr/share/sysbench/oltp_common.lua:83: connection creation failed
FATAL: error 1049: Unknown database 'sbtest'
FATAL: unable to connect to MySQL server on host 'XXX.XXX.XXX.XXX', port 3306, aborting...

(2) Do not test on the machine where the MySQL server is running. On the one hand, the impact of the network (even the LAN) may not be reflected. On the other hand, the operation of sysbench (especially when the concurrency is set to a high value) will affect the performance of the MySQL server.

(3) Gradually increase the number of concurrent client connections (--thread parameter) and observe the performance of the MySQL server under different numbers of connections.

(4) If multiple tests are performed consecutively, make sure that the data from the previous tests has been cleaned up.

(5) If the generated report is a graphical analysis, it can be analyzed using the gnuplot tool.

Summarize

The above is the method I introduced to you to implement performance testing of MySQL database through the sysbench tool. I hope it will be helpful to you. If you have any questions, please leave me a message and I will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!
If you find this article helpful, please feel free to reprint it and please indicate the source. Thank you!