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

This article mainly explains "what is the easy-to-use official stress testing tool for MySQL". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "what is the easy-to-use official MySQL stress testing tool"?

1. Mysqlslap, a stress testing tool included in MySQL

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.

1. Change the 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

View 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 twice read and write concurrently, the first time 100,200, automatically generate SQL script, the test table contains 20 init fields, 30 char fields, each execution of 2000 query requests. The test engine is myisam,innodb. (many of the above options are default values, which can be omitted, and if you want to know the interpretation of each option, you can use mysqlslap-- help to query.)

The test results show that:

For the first time, the Myisam 100 clients simultaneously initiated an additional check using 0.557 Universe, and the second 200 clients simultaneously initiated an additional check using 0.522 Universe.

For the first time, Innodb 100 clients simultaneously initiated the use of 0.256 Universe, and the second 200 clients simultaneously initiated the use of 0.303 Universe.

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

Second, use third-party sysbench tools for stress testing

1. Install the sysbench tool

[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:

Performance Test of CPU Operation

Disk IO performance test

Performance testing of scheduler

Memory allocation and transmission speed test

POSIX thread performance test

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).

Sysbench can also customize tests by specifying its own Lua script when running the command.

2. View the 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: # general 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]-- number of seconds before forced-shutdown=STRING is forced to close after the-- time time limit is reached The default "off" is disabled (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 validation 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 is discarded and-- rate [0]-max-requests=N is discarded Use-- 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: # built-in test type fileio-File I pgsql-password=STRING PostgreSQL password 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.

3. Sysbench tests the performance of MySQL database

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. Sbtest is the library name used by sysbench by default [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 indicates the creation of 10 test tables, #-- table_size=100000 indicates the insertion of 10W rows of data in each table, and # prepare indicates the process of preparing the number.

2) 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 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 number of reads performed per second How many times to 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 requests (read, write, other) Latency (ms): min: 1.39 avg: 3.64 max: 192.05 95th percentile: 6.67 # average delay calculated by sampling sum: 79964.26 Threads fairness: events (avg/stddev): 5484.7500 execution time 15.12 execution time (avg/stddev): 19.9911max 0.00

4. Cpu/io/ memory test

Several test metrics built into sysbench 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]

1) Test the performance of io

For example, create five files for 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 bandwidth of the write 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.5000avg 27.64 execution time (avg/stddev): 0.2519

2) Test the performance of cpu

[root@mysql] # sysbench cpu--threads=40-- events=10000-- cpu-max-prime=20000 run here, I believe you have a better understanding of "what is the easy-to-use official MySQL stress testing tool". You might as well do it in practice! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.