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

This article mainly shows you how to use stress testing tools in MySQL, the content is simple and easy to understand, I hope you can learn, after learning, there will be a harvest, the following let the editor to take a look at it.

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 the maximum number of connections mysql > show variables like 'max_connections' +-+-+ | Variable_name | Value | +-+-+ | max_connections | 1024 | +-+-+ 1 row in set (1024 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 concurrently twice, the first time 100,200, automatically generates the SQL script, the test table contains 20 init fields, 30

A char field of 2000 query requests is executed each time. 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 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.

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 is installed sysbench 1.0.17

Sysbench can perform the following tests:

CPU Computing performance Test disk IO performance Test Scheduler performance Test memory allocation and transfer Speed Test POSIX Thread performance Test Database performance Test (OLTP benchmark, which needs to be executed through Lua scripts in the / usr/share/sysbench/ directory, for example, oltp_read_only.lua scripts execute 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-- helpUsage: 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 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 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 drivermysql 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 Igamot O test cpu-CPU performance Test memory-Memory functions speed test threads-Threads subsystem performance test mutex-Mutex performance testSee '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# must create a sbtest library, and 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 creating 10 test tables, and #-- table_size=100000 means inserting 10W rows of data into each table. # prepare indicates that this is the process of preparing a count.

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 runsysbench 1.0.17 (using system LuaJIT 2.0.4) Running the test with following options:Number of threads: 4Report intermediate results every 5 second (s) Initializing random number generator from current timeInitializing worker threads...Threads starter 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.00SQL statistics: queries performed: read: 307146 # number of reads performed write: 87756 # number of writes 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 queries per second ignored errors: 0 (21931.37 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 sum: 79964.26Threads fairness: events (avg/stddev): 5484.7500 execution time ( Avg/stddev): 19.9911 Compact 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 helpsysbench 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: 16Initializing random number generator from current timeExtra file open flags: (none) 5 files, 409.6MiB each2GiB total file sizeBlock size 16KiBNumber of IO requests: 5000Read/Write ratio for combined random IO test: 1.50Calling fsync () at the end of test Enabled.Using synchronous I modeDoing random O modeDoing random r written testInitializing worker threads...Threads startedbandwidth File operations: reads/s: 9899.03 writes/s: 6621.38 fsyncs/s: 264.33Throughput: # Throughput read, MiB/s: 154.66 # indicates read bandwidth MiB/s: 103.46 # indicates the write bandwidth General statistics: total time: 0.3014s total number of events: 5000Latency (ms): min: 0.00 avg: 0.81 max: 53.56 95th percentile: 4.10 sum: 4030.48Threads fairness: events (avg/stddev): 312.5000 execution time 27.64 (avg/stddev): 0.2519 Universe 0.02

2) Test the performance of cpu

[root@mysql] # sysbench cpu--threads=40-- events=10000-- cpu-max-prime=20000 run above is about how to use stress testing tools in MySQL. If you have learned knowledge or skills, 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.