Use mysqlslap for MySQL stress testing

MySQL starts from version 5.1.4 with a stress testing tool mysqlslap. It is very easy to use by simulating multiple concurrent clients to access mysql. You can use mysqlslaphelp to obtain available options. Here are some of the main parameters. For more information, see the official manual. -- Auto-generate-SQL,-a automatically generates the test table and

MySQL starts from version 5.1.4 with a stress testing tool mysqlslap. It is very easy to use by simulating multiple concurrent clients to access mysql. You can use mysqlslap help to obtain available options. Here are some of the main parameters. For more information, see the official manual. -- Auto-generate-SQL,-a automatically generates the test table and

MySQL starts from version 5.1.4 with a stress testing tool mysqlslap. It is very easy to use by simulating multiple concurrent clients to access mysql. You can use mysqlslap-help to obtain available options. Here are some of the main parameters. For more information, see the official manual.

-- Auto-generate-SQL,-a automatically generates test tables and data -- auto-generate-SQL-load-type = type test statement type. Optional values: read, key, write, update, and mixed (default ). -- Number-char-cols = N,-x N: The number of columns in the test table that are automatically generated. The default value is 1 -- number-int-cols = N, -y N: the number of numeric columns in the automatically generated test table. The default value is 1 -- number-of-queries = N total number of test queries (number of concurrent customers × number of queries per customer) -- query = name,-q: Use a custom script to execute the test. For example, you can call a custom stored procedure or SQL statement to execute the test. -- Create-schema: schema in MySQL, that is, the number of DML entries in database -- commint = N, and then submit it once -- compress.-C if both the server and client support compression, then the compressed information is transmitted-concurrency = N,-c N concurrency, that is, the number of clients that are simulated to execute the select statement at the same time. You can specify multiple values. Use a comma or the value specified by the-delimiter parameter as the separator -- engine = engine_name, and-e engine_name as the storage engine used to create the test table. You can specify multiple values, separated by delimiters. -- Iterations = N,-I N test execution iterations -- detach = N executes N statements and then disconnects again -- debug-info, -T print memory and CPU information -- only-print only prints test statements but not actually executes -- defaults-file configuration file storage location -- socket = name,-S specifies the socket file Location

Some Instances

Use the automatically generated test data for testing and only print the actual test process.

# /usr/local/webserver/mysql/bin/mysqlslap  -a --only-printDROP SCHEMA IF EXISTS `mysqlslap`;CREATE SCHEMA `mysqlslap`;use mysqlslap;CREATE TABLE `t1` (intcol1 INT(32) ,charcol1 VARCHAR(128));INSERT INTO t1 VALUES (1804289383,'mxvtvmC9127qJNm06sGB8R92q2j7vTiiITRDGXM9ZLzkdekbWtmXKwZ2qG1llkRw5m9DHOFilEREk3q7oce8O3BEJC0woJsm6uzFAEynLH2xCsw1KQ1lT4zg9rdxBL');INSERT INTO t1 VALUES (822890675,'97RGHZ65mNzkSrYT3zWoSbg9cNePQr1bzSk81qDgE4Oanw3rnPfGsBHSbnu1evTdFDe83ro9w4jjteQg4yoo9xHck3WNqzs54W5zEm92ikdRF48B2oz3m8gMBAl11W');......INSERT INTO t1 VALUES (100669,'qnMdipW5KkXdTjGCh2PNzLoeR0527frpQDQ8uw67Ydk1K06uuNHtkxYBxT5w8plb2BbpzhwYBgPNYX9RmICWGkZD6fAESvhMzH3yqzMtXoH4BQNylbK1CmEIPGYlC6');SELECT intcol1,charcol1 FROM t1;INSERT INTO t1 VALUES (73673339,'BN3152Gza4GW7atxJKACYwJqDbFynLxqc0kh30YTwgz3FktQ43XTrqJ4PQ25frn7kXhfXD8RuzN1j8Rf3y8ugKy6es3IbqPJM6ylCyD6xS7YcQCfHKZxYNvB7yTahm');DROP SCHEMA IF EXISTS `mysqlslap`;

100 concurrent queries and 200 concurrent queries

$mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --debug-info # /usr/local/webserver/mysql/bin/mysqlslap  -a --concurrency=100,200 --number-of-queries 1000 --debug-info BenchmarkAverage number of seconds to run all queries: 0.232 secondsMinimum number of seconds to run all queries: 0.232 secondsMaximum number of seconds to run all queries: 0.232 secondsNumber of clients running queries: 100Average number of queries per client: 10 BenchmarkAverage number of seconds to run all queries: 0.268 secondsMinimum number of seconds to run all queries: 0.268 secondsMaximum number of seconds to run all queries: 0.268 secondsNumber of clients running queries: 200Average number of queries per client: 5  User time 0.02, System time 0.49Maximum resident set size 6100, Integral resident set size 0Non-physical pagefaults 3165, Physical pagefaults 0, Swaps 0Blocks in 0 out 0, Messages in 0 out 0, Signals 0Voluntary context switches 3102, Involuntary context switches 7541

200 concurrent queries, 400 total queries, and 8 iterations respectively

# /usr/local/webserver/mysql/bin/mysqlslap -a   --concurrency=200,400 --number-of-queries 2000 --iterations=8  --debug-info BenchmarkAverage number of seconds to run all queries: 0.657 secondsMinimum number of seconds to run all queries: 0.603 secondsMaximum number of seconds to run all queries: 0.736 secondsNumber of clients running queries: 200Average number of queries per client: 10 BenchmarkAverage number of seconds to run all queries: 0.711 secondsMinimum number of seconds to run all queries: 0.648 secondsMaximum number of seconds to run all queries: 0.860 secondsNumber of clients running queries: 400Average number of queries per client: 5  User time 0.58, System time 9.12Maximum resident set size 30764, Integral resident set size 0Non-physical pagefaults 92471, Physical pagefaults 0, Swaps 0Blocks in 0 out 0, Messages in 0 out 0, Signals 0Voluntary context switches 99629, Involuntary context switches 157741

Test different storage engines at the same time

# /usr/local/webserver/mysql/bin/mysqlslap -a   --concurrency=200,400 --number-of-queries 2000 --iterations=8 　--engine=myisam,innodb  --debug-info BenchmarkAverage number of seconds to run all queries: 0.740 secondsMinimum number of seconds to run all queries: 0.678 secondsMaximum number of seconds to run all queries: 0.827 secondsNumber of clients running queries: 200Average number of queries per client: 10 BenchmarkAverage number of seconds to run all queries: 0.696 secondsMinimum number of seconds to run all queries: 0.661 secondsMaximum number of seconds to run all queries: 0.775 secondsNumber of clients running queries: 400Average number of queries per client: 5  User time 0.62, System time 9.27Maximum resident set size 33368, Integral resident set size 0Non-physical pagefaults 89594, Physical pagefaults 0, Swaps 0Blocks in 0 out 0, Messages in 0 out 0, Signals 0Voluntary context switches 98328, Involuntary context switches 153787

Test the specified database

# /usr/local/webserver/mysql/bin/mysqlslap  --concurrency=500 --iterations=8 --create-schema=test --query=/root/test.sql BenchmarkAverage number of seconds to run all queries: 0.343 secondsMinimum number of seconds to run all queries: 0.283 secondsMaximum number of seconds to run all queries: 0.525 secondsNumber of clients running queries: 500Average number of queries per client: 1

Original article address: Use mysqlslap for MySQL stress testing. Thank you for sharing it with me.