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

1. Background

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

When the new server is online, it is recommended to do a test on the performance of the server, and it is best to make a horizontal comparison with the previous performance test reports of the same type of server to find potential problems. Before the new machine goes online, do a physical examination on the server.

As for the database, we can benchmark the database through the sysbench tool. In the current system architecture, the front end is relatively easy to expand flexibly, and the database is relatively difficult. Therefore, benchmarking plays a very important role in the database. The function of the benchmark test of the database is to analyze the performance of the database under the current configuration (including hardware configuration, OS, database settings, etc.), so as to find out the performance threshold of MySQL, and adjust the configuration according to the requirements of the actual system.

Installation of 2.sysbench

1) install command

Yum-y install sysbench

2) View the installed version

Sysbench-version

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

When querying the installation information of sysbench, you need to use the lua script that comes with sysbench to test mysql. If you use a quick install, the default script path is: / usr/share/sysbench.

If not in this command, we execute the following command to check to find all the sysbench software programs that have been installed on the native Linux system:

Rpm-qa sysbench

List the complete file name (list) where all the files and directories of the software are located:

Rpm-ql sysbench

3.sysbench syntax

Sysbench-helpUsage: sysbench [options]. [testname] [command] Commands implemented by most tests: prepare run cleanup helpGeneral 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. 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- MySQL driver pgsql-PostgreSQL drivermysql 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 all]-- 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 Compiled-in tests 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 prepares data in advance for testing, run executes formal tests, and cleanup cleans up the database after the test is completed.

Testname specifies the test to be done, and in the old version of sysbench, you can specify the script to test with the-- test parameter; in the new version, the-- test parameter has been declared obsolete and you can specify the script directly instead of using-- test. The script used in the test is the lua script, which can be used with sysbench's own script or developed by yourself.

Options about MySQL mainly includes MySQL connection information parameters and MySQL execution related parameters.

4 Test

Step 1 prepares pressure 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 pressure test

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

You can also export the test results 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 cleans up pressure 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. Matters needing attention

(1) the test database needs to be created in advance, and the test account has the permission to create the database in time.

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

Do not create 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 (no database specified)

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 running on the MySQL server, on the one hand, it may not reflect the influence of the network (even the local area network), on the other hand, the operation of sysbench (especially when the number of concurrency is high) will affect the performance of the MySQL server.

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

(4) if multiple tests are carried out in a row, make sure that the previously tested data has been cleaned up.

(5) if the generated report is graphically analyzed, it can be analyzed by gnuplot tool.

Summary

The above is the editor to introduce to you through the sysbench tool to achieve MySQL database performance testing methods, I hope to help you, if you have any questions, please leave me a message, the editor will reply to you in time. Thank you very much for your support to the website!

If you think this article is helpful to you, you are welcome to reprint it, please indicate the source, thank you!

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.