MySQL Study--mysql Stress test tool Mysqlslap
I. Introduction of MYSQLSLAP   MYSQLSLAP is a benchmark benchmark tool that comes with MySQL5.1, similar to Apache bench load generation tools, generates schemas, loads data, executes Benckmark and queries data, The syntax is simple, flexible and easy to use. The tool can simulate multiple clients concurrently issuing query updates to the server, giving performance test data and providing performance comparisons for multiple engines. Mysqlslap for MySQL performance optimization before and after providing an intuitive verification basis, the author suggested that the system operators should master some common pressure testing tools, so as to more accurately grasp the online system can support the user flow limit and the pressure resistance and so on. www.2cto.com Method Introduction You can use the Mysqlslap--help to show how to use: 1)--concurrency represents the number of concurrent, multiple can be separated by commas, for example: concurrency= 10,50,100, the number of concurrent connection threads is 10, 50, and 100 concurrency respectively. 2)--engines represents the engine to be tested, can have multiple, separated by separators. 3)--iterations represents how many times to run these tests. 4)--auto-generate-sql represents the SQL script generated by the system itself. 5) Whether the--auto-generate-sql-load-type represents a read or write or a mix of both (read,write,update,mixed) 6)--number-of-queries represents the total number of queries to run. The number of queries that are run per client can be calculated using the total number of queries/concurrency. 7)--debug-info represents the additional output of CPU and memory related information. 8)--number-int-cols: Number of int fields to create test table 9)--auto-generate-sql-add-autoincrement: Represents the automatic addition of auto_increment columns to the generated table. Starting from version 5.1.18)--number-char-cols The number of char fields that created the test table. One) the--create-schema test Schema,mysql in the schema is the database. --query Perform tests using custom scripts, such as a custom stored procedure or SQL statement that can be invoked to perform the test. --only-print If you just want to print and see what the SQL statement is, you can useThis option.
second, case analysis
[Email protected] ~]# mysqlslap-vmysqlslap Ver 1.0 distrib 5.1.66, for Redhat-linux-gnu (x86_64)
[[email protected] ~]# mysqlslap--helpmysqlslap Ver 1.0 distrib 5.1.66, for Redhat-linux-gnu (x86_64) Copyright (c) 2005, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names trademarks of their respectiveowners. Run a query multiple times against the server. Usage:mysqlslap [Options]default OPTIONS is read from the following files in the the given ORDER:/ETC/MYSQL/MY.CNF/ETC/MY.C NF ~/.MY.CNF The following groups is Read:mysqlslap clientthe following options may be given as the first Argument:--pri Nt-defaults Print The program argument list and Exit.--no-defaults Don ' t read default options from any op tion file.--defaults-file=# only read default options from the given file #.--defaults-extra-file=# read this file a fter the global files are read. -?,--Help Display this help and exit. -A,--auto-generate-sql generate SQL where noT supplied by file or command line. --auto-generate-sql-add-autoincrement add an auto_increment column to auto-generated tables. --auto-generate-sql-execute-number=# set this number to generate a Set number of queries to Run. --auto-generate-sql-guid-primary ADD GUID based primary keys to auto-generated tables. --auto-generate-sql-load-type=name Specify test load type:mixed, update, write, key, or Read Default is mixed. --auto-generate-sql-secondary-indexes=# number of secondary indexes to add to auto-generated Tables. --auto-generate-sql-unique-query-number=# number of unique queries to generate for automatic tests. --auto-generate-sql-unique-write-number=# number of unique queries to generate for Auto-generate-sql-write-number. --auto-generate-sql-write-number=# number of row inserts to perform for each thread (default is 100). --commit=# Commit records every X number of statements. -C,--compress use compression in Server/client protocol. -C,--concurrency=name number of clients to simulate for query to run. --create=name File or string to use create tables. --create-schema=name schema to Run tests in. --csv[=name] Generate csv output to named file or to stdout if no file is named. -#,--debug[=#] This is a non-debug version. Catch this and exit. --debug-check check memory and open file usage at exit. -T,--debug-info Print some debug info at exit. -F,--delimiter=name delimiter to use in SQL statements supplied in file or COM Mand line. --detach=# detach (Close and reopen) connections after X number of Requests. -E,--engine=name Storage engine to use for creating the table. -H,--host=name Connect to host. -I,--iterations=# number of times to run the tests. --no-drop does not drop the schema after the test. -X,--number-char-cols=name number of VARCHAR columns to create in table if SPE Cifying--auto-generate-sql. -Y,--number-int-cols=name number of int columns to create in table if specifying --auto-generate-sql. --number-of-queries=# Limit Each client to this number of queries exact). --only-print does not connect to the databases, but instead print out what would has been done. -P,--password[=name] password to if connecting to server. If password is not given it's asked from the TTY. -P,--port=# port number to use for CONnection. --post-query=name query to run or file containing query to execute after tests has completed. --post-system=name system () string to execute after tests has completed. --pre-query=name query to run or file containing query to execute before running tests. --pre-system=name system () string to execute before running tests. --protocol=name the protocol to use for connection (TCP, socket, pipe, memory). -Q,--query=name query to run or file containing query to run. -S,--silent Run program in silent mode-no output. -S,--socket=name the socket file to use for connection. --SSL Enable SSL for connection (automatically enabled with the other flags). Disable with--skip-ssl. --ssl-ca=name CA file in PEM format (check OpenSSL docs, implies--ssl). --ssl-capath=name CA directory (check OpenSSL docs, implies--ssl). --ssl-cert=Name X509 cert in PEM format (implies--ssl). --ssl-cipher=name SSL cipher to use (implies--ssl). --ssl-key=name X509 key in PEM format (implies--ssl). --ssl-verify-server-cert Verify server ' s "Common Name" in its cert against host Name used when connecting. This option was disabled by default. -U,--user=name user for login if is not current user. -V,--verbose more verbose output; You can use this multiple times to get even more verbose output. -V,--version Output version information and exit.
Case one:
[Email protected] ~]# mysqlslap-uroot-poracle--concurrency=100--iterations=1--auto-generate-sql-- Auto-generate-sql-load-type=mixed--auto-generate-sql-add-autoincrement--engine=myisam--number-of-queries=10-- Debug-info</span></strong>
Test information:
Benchmark
Running for Engine MyISAM
Average number of seconds to run all queries:0.473 seconds
Minimum number of seconds to run all queries:0.473 seconds
Maximum number of seconds to run all queries:0.473 seconds
Number of clients running queries:100
Average number of queries per client:0
User time 0.04, System time 0.14
Maximum resident set Size 6732, Integral resident set size 0
Non-physical PageFaults 1333, physical pagefaults 0, Swaps 0
Blocks in 0 off 0, Messages in 0 out 0, signals 0
Voluntary context Switches 2389, involuntary context switches 48
Note:This test takes 100 concurrent threads, tests 1 times, automatically generates SQL test script, read, write, update Hybrid test, self-growth field, test engine to MyISAM, run 10 queries, output CPU resource information
Case Two: [[email protected] ~]# mysqlslap-h127.0.0.1-p3306-u root-poracle--concurrency=100--iterations=1--create-schema= ' Prod '--query= ' select * from T1; '--number-of-queries=10-e InnoDB--debug-info test information:
Benchmark
Running for engine InnoDB
Average number of second s to run all queries:1.455 seconds
Minimum number of seconds to run all queries:1.455 sec Onds
Maximum number of seconds to run all queries:1.455 seconds
&N Bsp Number of clients running queries:100
Average number of queries per client:0
User time 0.03, System time 0.12
Maximum resident set size 5420, Integral resident set size 0
non-physical pagefaults 1273, P Hysical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, signals 0
Voluntary context switches 536, INVO Luntary Context Switches 151
Note: use MYSQLSLAP to specify SQL statements for testing
Case Three:
[email protected] ~]# mysqlslap--concurrency=50,100,200--iterations=20--number-int-cols=4--number-char-cols= --auto-generate-sql--auto-generate-sql-add-autoincrement--auto-generate-sql-load-type=read--engine=myisam, InnoDB--number-of-queries=200--verbose--socket=/tmp/mysql.sock-uroot-poracle
Test Information:Benchmark
Running for engine MyISAM
Average number of seconds to run all queries:1.521 seconds
Minimum number of seconds to run all queries:1.397 seconds
Maximum number of seconds to run all queries:2.092 seconds
Number of clients running QUERIES:50
Average number of queries per Client:4
Benchmark
Running for Engine MyISAM
Average number of seconds to run all queries:1.465 seconds
Minimum number of seconds to run all queries:1.279 seconds
Maximum number of seconds to run all queries:1.996 seconds
Number of clients running queries:100
Average number of queries per Client:2
Benchmark
Running for Engine MyISAM
Average number of seconds to run all queries:1.367 seconds
Minimum number of seconds to run all queries:1.260 seconds
Maximum number of seconds to run all queries:1.576 seconds
Number of clients running queries:200
Average number of queries per client:1
Benchmark
Running for engine InnoDB
Average number of seconds to run all queries:1.198 seconds
Minimum number of seconds to run all queries:1.084 seconds
Maximum number of seconds to run all queries:1.301 seconds
Number of clients running QUERIES:50
Average number of queries per Client:4
Benchmark
Running for Engine InnoDB
Average number of seconds to run all queries:1.423 seconds
Minimum number of seconds to run all queries:1.238 seconds
Maximum number of seconds to run all queries:1.761 seconds
Number of clients running queries:100
Average number of queries per Client:2
Benchmark
Running for Engine InnoDB
Average number of seconds to run all queries:1.389 seconds
Minimum number of seconds to run all queries:1.268 seconds
Maximum number of seconds to run all queries:1.653 seconds
Number of clients running queries:200
Average number of queries per client:1
remark:#系统脚本测试, add int type 4 column char type 35 column, Test 2 engine Myisam,innodb read performance, 50,100,200 client to test the server a total of 200 query statements to execute 20 queries
Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.
MySQL Study--mysql Stress test tool Mysqlslap
