Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to use mysqlslap and sysbench to stress test MySQL. The content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

Mysqlslap is a benchmark tool that comes with mysql, which queries data with simple syntax and is flexible and easy to use. The tool can simulate multiple clients to send query updates to the server at the same time, gives the performance test data and provides the performance comparison of multiple engines. Mysqlslap provides an intuitive verification basis for mysql before and after performance optimization. System operation and maintenance and DBA personnel should master some common stress testing tools in order to accurately grasp the upper limit of user traffic supported by online database and its pressure resistance.

Change its default maximum number of connections

Before stress testing MySQL, you need to change its default maximum number of connections, as follows:

[root@mysql] # vim / etc/my.cnf. [mysqld] max_connections=1024 [root@mysql ~] # systemctl restart mysqld

Log in to MySQL to check whether the maximum number of connections is valid.

# check the maximum number of connections mysql > show variables like 'max_connections'; +-+ | Variable_name | Value | +-+-+ | max_connections | 1024 | +-+-+ 1 row in set (0.00 sec)

Conduct a stress test:

[root@mysql] # mysqlslap-defaults-file=/etc/my.cnf-- concurrency=100200-- iterations=1-- number-int-cols=20-- number-char-cols=30-- auto-generate-sql--auto-generate-sql-add-autoincrement-- auto-generate-sql-load-type=mixed-- engine=myisam,innodb-- number-of-queries=2000-uroot-p123-- verbose

The above command test description:

The simulation test reads and writes twice, the first time 100,200, and the second 200. the SQL script is automatically generated. The test table contains 20 init fields and 30 char fields, and each query request is executed 2000. The test engine is myisam,innodb. (many of the above options are default values, which can be omitted. If you want to know the interpretation of each option, you can use mysqlslap-- help to query)

The above command returns the following result:

The test results show that the first 100 client of Myisam initiates 0.557 Innodb at the same time, and the second 200 client initiates 0.256max Innodb at the same time, and the second 200 client initiates 0.303max.

Stress testing can be carried out by slightly increasing the number of concurrency according to the actual demand.

Stress testing using third-party sysbench tools

Install sysbench tools

[root@mysql ~] # yum-y install epel-release # install third party epel source [root@mysql ~] # yum-y install sysbench # install sysbench tool [root@mysql ~] # sysbench-- version # make sure the tool has sysbench 1.0.17 installed

Sysbench can perform the following tests:

1. CPU computing performance test 2. Disk IO performance test 3. Scheduler performance test 4. Memory allocation and transfer speed test 5. POSIX thread performance test 6. Database performance testing (OLTP benchmark, which needs to be executed through Lua scripts in the / usr/share/sysbench/ directory, such as oltp_read_only.lua scripts to perform read-only tests) 7. Sysbench can also customize the test by specifying its own Lua script when running the command.

View help options for the sysbench tool

[root@mysql] # sysbench-- help Usage: sysbench [options]. [testname] [command] Commands implemented by most tests: prepare run cleanup help # available commands, four General options: # common options-- number of threads to be used by threads=N, default 1 [1]-- maximum number of events allowed by events=N [0]-- maximum total execution time of time=N In seconds [10]-- the number of seconds forced-shutdown=STRING will wait before shutting down after the-- time time limit is reached. By default, "off" disables (number of seconds to wait after the-- time limit before forcing shutdown, or 'off' to disable) [off]-- thread-stack-size=SIZE stack size per thread [64K]-- rate=N average transfer rate. 0 then unlimited [0]-- report-interval=N periodically reports intermediate statistics with specified intervals in seconds 0 disables intermediate reports [0]-- report-checkpoints= [LIST,...] Dumps the complete statistics and resets all counters at the specified point in time. Parameter is a comma-separated list of values indicating that a report checkpoint (in seconds) must be performed when this amount of time has elapsed since the start of the test. Report checkpoints are turned off by default. []-- debug [= on | off] print more debug information [off]-- validate [= on | off] perform verification check as far as possible [off]-- help [= on | off] display help information and exit [off]-- version [= on | off] display version information and exit [off]-- config -file=FILENAME files with command line options-- tx-rate=N obsolete Use-- rate [0]-- max-requests=N discarded, replaced-- events [0]-- max-time=N discarded, switched to-- time [0]-- num-threads=N discarded Use-- threads [1] Pseudo-Random Numbers Generator options: # pseudorandom number generator option-- rand-type=STRING random numbers distribution {uniform,gaussian,special instead 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: # logging 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: # generic database option-- db-driver=STRING specifies the database driver to use ('help' to get list of available drivers)-- 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: # built-in database driver MySQL and PostgreSQL mysql- MySQL driver pgsql-PostgreSQL driver mysql options are supported by default: # MySQL database specific option-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: # PostgreSQL database specific 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: # The built-in test type fileio-File I bind O test cpu-CPU performance test memory-Memory functions speed test threads-Threads subsystem performance test mutex-Mutex performance test See 'sysbench help' for a list of options for each test.

Sysbench testing MySQL database performance

1. Prepare test data

# View the usage of lua scripts included in sysbench [root@mysql ~] # sysbench/ usr/share/sysbench/oltp_common.lua help # you must create a sbtest library. The default library name used by sbtest sysbench is [root@mysql ~] # mysqladmin-uroot-p123 create sbtest; # then prepare the tables used for testing, which are placed in the test library sbtest. The lua script used here is / usr/share/sysbench/oltp_common.lua [root@mysql ~] # sysbench-- mysql-host=127.0.0.1\-- mysql-port=3306\-- mysql-user=root\-- mysql-password=123\ / usr/share/sysbench/oltp_common.lua\-- tables=10\-- table_size=100000\ prepare # where-tables=10 means to create 10 test tables, and #-- table_size=100000 means to insert 10W rows of data into each table # prepare indicates that this is the process of preparing a count.

two。 Confirm that the test data exists

[root@mysql ~] # mysql-uroot-p123 sbtest; # Log in to sbtest library mysql > show tables # View the corresponding table +-+ | Tables_in_sbtest | +-+ | sbtest1 | | sbtest10 | | sbtest2 | | sbtest3 | | sbtest4 | | sbtest5 | | sbtest6 | | sbtest7 | | sbtest8 | | sbtest9 | +-+ 10 rows in set (0.00 sec) mysql > select count (*) from sbtest1 | # randomly select a table and confirm that it has 100000 pieces of data +-+ | count (*) | +-+ | 100000 | +-+ 1 row in set (sec)

3. Database testing and result analysis

Slightly modify the statement that prepared the data before, and you can use it for testing. It is important to note that the previously used lua script is oltp_common.lua, which is a generic script that is called by other lua scripts and cannot be tested directly. So, I use oltp_read_write.lua scripts to do read and write tests here. There are many other types of tests, such as read-only tests, write-only tests, delete tests, mass insert tests, and so on. You can find the corresponding lua script to call.

# execute the test command as follows: [root@mysql ~] # sysbench-- threads=4\-- time=20\-- report-interval=5\-- mysql-host=127.0.0.1\-- mysql-port=3306\-- mysql-user=root\-- mysql-password=123\ / usr/share/sysbench/oltp_read_write.lua\-- tables=10\-- table_size=100000\ run

The above command returns the following results:

[root@mysql] # sysbench-- threads=4-- time=20-- report-interval=5-- mysql-host=127.0.0.1-- mysql-port=3306-- mysql-user=root-- mysql-password=123 / usr/share/sysbench/oltp_read_write.lua-- tables=10-- table_size=100000 run sysbench 1.0.17 (using system LuaJIT 2.0.4) Running the test with following options: Number of threads: 4 Report intermediate results every 5 second (s) Initializing random number generator from current time Initializing worker threads... Threads started! # the following is the result returned every 5 seconds Statistical metrics include: # threads, tps (transactions per second), qps (queries per second), # read / write / other times per second, latency, errors per second, reconnections per second [5s] thds: 4 tps: 1040.21 qps: 20815.65 (r/w/o: 14573.17 r/w/o 4161.25 2081.22) lat (ms) Err/s: 0.00 reconn/s: 0.00 [10s] thds: 4 tps: 1083.34 qps: 21667.15 (r/w/o: 15165.93 reconn/s 4334.55) lat (ms,95%): 6.55 err/s: 0.00 reconn/s: 0.00 [15s] thds: 4 tps: 1121.57 qps: 22429.09 (r/w/o: 15700.64) lat (ms: 4485.30) 95%): 6.55 err/s: 0.00 reconn/s: 0.00 [20s] thds: 4 tps: 1141.69 qps: 22831.98 (r/w/o: 15982.65 qps 4566.16) lat (ms Err/s: 0.00 reconn/s: 0.00 SQL statistics: queries performed: read: 307146 # number of read operations performed write: 87756 # number of write operations performed other: 43878 # number of other operations performed total: 438780 transactions: 21939 (1096.57 per sec.) # average rate of transactions executed queries: 438780 (21931.37 per sec.) # average per second How many times can the query ignored errors: 0 (0.00 per sec.) Reconnects: 0 (0.00 per sec.) General statistics: total time: 20.0055s # Total elapsed time total number of events: 21939 # Total number of requests (read, write, Other) Latency (ms): min: 1.39 avg: 3.64 max: 192.05 95th percentile: 6.67 # average delay of sampling calculation sum: 79964.26 Threads fairness: events (avg/stddev): 5484.7500 execution time (avg/stddev): 19.9911

Several test metrics built into the test sysbench, such as 4.cpu/io/ memory, are as follows:

[root@mysql] # sysbench-- help. # omit part of the content Compiled-in tests: fileio-File I CPU performance test memory O test cpu-CPU performance test memory-Memory functions speed test threads-Threads subsystem performance test mutex-Mutex performance test

You can output test methods directly from help, for example, fileio tests:

[root@mysql] # sysbench fileio help sysbench 1.0.17 (using system LuaJIT 2.0.4) fileio options:-- file-num=N number of files to create [128]-- file-block-size=N block size to use in all IO operations [16384]-- file-total-size=SIZE total size of files to create [2G]-- file-test-mode=STRING test mode {seqwr, seqrewr, seqrd, rndrd, rndwr Rndrw}-- file-io-mode=STRING file operations mode {sync,async,mmap} [sync]-- file-async-backlog=N number of asynchronous operatons to queue per thread [128]-- file-extra-flags= [LIST,...] List of additional flags to use to open files {sync,dsync,direct} []-- file-fsync-freq=N do fsync () after this number of requests (0-don't use fsync ()) [100]-- file-fsync-all [= on | off] do fsync () after each write operation [off]-- file-fsync-end [= on | off] do fsync () at the end of test [on]-- file-fsync-mode=STRING which method to use for synchronization {fsync Fdatasync} [fsync]-- file-merged-requests=N merge at most this number of IO requests if possible (0-don't merge) [0]-- file-rw-ratio=N reads/writes ratio for combined test [1.5]

5. Test io performance for example, create 5 files, a total of 2G, each about 400m.

[root@mysql ~] # sysbench fileio-- file-num=5-- file-total-size=2G prepare [root@mysql ~] # ll-lh test*-rw- 1 root root 410M May 26 16:05 test_file.0-rw- 1 root root 410M May 26 16:05 test_file.1-rw- 1 root root 410M May 26 16:05 test_file.2-rw- 1 root root 410m May 26 16:05 test_file.3-rw- 1 root root 410M May 26 16:05 test_file.4

Then run the test:

[root@mysql] # sysbench-- events=5000\-- threads=16\ fileio\-- file-num=5\-- file-total-size=2G\-- file-test-mode=rndrw\-- file-fsync-freq=0\-- file-block-size=16384\ run

The result returned is as follows:

Running the test with following options: Number of threads: 16 Initializing random number generator from current time Extra file open flags: (none) 5 files, 409.6MiB each 2GiB total file size Block size 16KiB Number of IO requests: 5000 Read/Write ratio for combined random IO test: 1.50 Calling fsync () at the end of test, Enabled. Using synchronous I/O mode Doing random r/w test Initializing worker threads... Threads started! File operations: reads/s: 9899.03 writes/s: 6621.38 fsyncs/s: 264.33 Throughput: # Throughput read, MiB/s: 154.66 # indicates read bandwidth written MiB/s: 103.46 # indicates the write bandwidth General statistics: total time: 0.3014s total number of events: 5000 Latency (ms): min: 0.00 avg: 0.81 max: 53.56 95th percentile: 4.10 sum: 4030.48 Threads fairness: events (avg/stddev): 312.5000 avg/stddev 27.64 execution time (avg/stddev): 0.2519

6. Test the performance of cpu

[root@mysql] # sysbench cpu--threads=40-- events=10000-- cpu-max-prime=20000 run is here about how to use mysqlslap and sysbench to perform stress testing on 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.