MySQL Study -- MySQL stress testing tool mysqlslap, -- mysqlmysqlslap

MySQL Study -- MySQL stress testing tool mysqlslap, -- mysqlmysqlslap
MySQL Study-MySQL stress testing tool mysqlslap1. Introduction to MysqlslapMysqlslap is a benchmark testing tool provided after MySQL5.1. It is similar to the Apache benchmark load generation tool, which generates a schema, loads data, executes benckmark and queries data. The syntax is simple, flexible, and easy to use. This tool can simulate multiple clients to concurrently send query updates to the server, and provides performance test data and Performance Comparison of multiple engines. Mysqlslap provides an intuitive verification basis before and after mysql performance optimization. The author suggests that system O & M personnel should master some common stress testing tools, in this way, the upper limit of user traffic supported by the online system and its adequacy can be grasped more accurately. Www.2cto.com 2. Usage Description: You can use mysqlslap -- help to display usage: 1) -- concurrency indicates the number of concurrent tasks. multiple tasks can be separated by commas (,), for example, concurrency = 100, the number of concurrent connection threads is 10, 50, and 100 respectively. 2) -- engines indicates the engine to be tested. There can be multiple engines separated by separators. 3) -- iterations indicates how many times to run these tests. 4) -- auto-generate-SQL indicates testing with the SQL script generated by the system. 5) -- auto-generate-SQL-load-type indicates whether to test the read/write operations or whether the two are mixed (read, write, update, mixed) 6) -- number-of-queries indicates the total number of queries to be run. The number of queries run on each client can be calculated by the total number of queries/number of concurrent queries. 7) -- debug-info indicates that the CPU and memory information needs to be output. 8) -- number-int-cols: number of int fields in the test table created 9) -- auto-generate-SQL-add-autoincrement: indicates that the auto_increment column is automatically added to the generated table, from version 5.1.18 to version 10) -- number-char-cols create the number of char fields in the test table. 11) -- create-schema test schema. The schema in MySQL is also the database. 12) -- query uses a custom script to execute the test. For example, you can call a custom stored procedure or SQL statement to execute the test. 13) -- only-print if you only want to print and see what the SQL statement is, you can use this option.Ii. Case Analysis

[root@mysrv ~]# mysqlslap -Vmysqlslap  Ver 1.0 Distrib 5.1.66, for redhat-linux-gnu (x86_64)

[root@mysrv ~]# mysqlslap --helpmysqlslap  Ver 1.0 Distrib 5.1.66, for redhat-linux-gnu (x86_64)Copyright (c) 2005, 2012, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Run a query multiple times against the server.Usage: mysqlslap [OPTIONS]Default options are read from the following files in the given order:/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf The following groups are read: mysqlslap clientThe following options may be given as the first argument:--print-defaults        Print the program argument list and exit.--no-defaults           Don't read default options from any option file.--defaults-file=#       Only read default options from the given file #.--defaults-extra-file=# Read this file after 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                      command 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           Do not drop the schema after the test.  -x, --number-char-cols=name                       Number of VARCHAR columns to create in table if                      specifying --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 (this is not                      exact).  --only-print        Do not connect to the databases, but instead print out                      what would have been done.  -p, --password[=name]                       Password to use when 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 have completed.  --post-system=name  system() string to execute after tests have 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                      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                      hostname used when connecting. This option is disabled by                      default.  -u, --user=name     User for login if 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 1:
[Root @ mysrv ~] # Mysqlslap-uroot-poracle -- concurrency = 100 -- iterations = 1 -- 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 out 0, Messages in 0 out 0, Signals 0
Voluntary context switcheles 2389, Involuntary context switches 48
Note: This test takes 100 concurrent threads and is tested once, automatically generate SQL test script, read, write, update hybrid test, auto-increment field, myisam test engine, run 10 queries in total, and output cpu resource information Case 2: [Root @ mysrv ~] # 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 seconds to run all queries: 1.455 seconds
Minimum number of seconds to run all queries: 1.455 seconds
Maximum number of seconds to run all queries: 1.455 seconds
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, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 536, Involuntary context switches 151
Note: Use mysqlslap to specify an SQL statement for testing. Case 3: [Root @ mysrv ~] # Mysqlslap-concurrency = 50,100,200 -- iterations = 20 -- number-int-cols = 4 -- number-char-cols = 35 -- 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
Note:# System Script test: Four int columns and 35 char columns are added to test the read performance of two engines, myisam and innodb, test the server with 50,100,200 clients, and execute 20 queries for a total of 200 query statements.

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.