MySQL stress testing tool

MySQL stress testing tool mysqlslap -- create-schema = example -- query = "SELECT * FROM group_message force index (group_message_author_subject) WHERE author = '3' subject LIKE 'weiurazs % '"-- iterations = 10000 the test table needs to be generated during the test process and the test data needs to be inserted. This mysqlslap can be automatically generated, A schema of mysqlslap is generated by default. If the schema already exists, delete it first. Note that you should not use-create-schema to specify an existing database. Otherwise, the consequences may be severe. You can use-only-print to print the actual test process: www.2cto.com $ mysqlslap-a-only-printDROP schema if exists mysqlslap; create schema mysqlslap; use mysqlslap; create table g_user (user_id INT (32), user_name VARCHAR (128); insert into g_user VALUES (1, 'liuxiaobin ');... SELECT user_id, user_name FROM g_user; insert into g_user VALUES (2, 'liudabin'); drop schema if exists mysqlslap; you can see the schema action created at the beginning of the deletion, after the test is completed, no trace is left in the database. If we perform a test with 50 and 100 concurrent queries and execute 1000 total queries, then: $ mysqlslap-a-concurrency = 50,100-number-of-queries 1000-debug-infoBenchmarkAverage number of seconds to run all queries: 0.375 secondsMinimum number of seconds to run all queries: 0.375 secondsMaximum number of seconds to run all queries: 0.375 secondsNumber of clients running queries: 50 Average number of queries per client: 20 www.2cto.com BenchmarkAverage Number of seconds to run all queries: 0.453 secondsMinimum number of seconds to run all queries: 0.453 secondsMaximum number of seconds to run all queries: 0.453 secondsNumber of clients running queries: 100 Average number of queries per client: 10 User time 0.29, System time 0.11 Maximum resident set size 0, Integral resident set size 0Non-physical pagefaults 4032, Physical pagefaults 0, Swaps 0Blo Cks in 0 out 0, Messages in 0 out 0, Signals 0 Voluntary context switches 7319, Involuntary context switches 681, we can see that, 50 and 100 concurrency get a test result (Benchmark) respectively. The more concurrency, the longer the query execution time. For accuracy, you can perform multiple iteration tests: $ mysqlslap-a-concurrency = 50,100-number-of-queries 1000-iterations = 5-debug-infoBenchmarkAverage number of seconds to run all queries: 0.380 secondsMinimum number of seconds to run all queries: 0.377 secondsMaximum number of seconds to run all queries: 0.385 secondsNumber of clients running queries: 50 Average number of queries per client: 20 BenchmarkAverage number of seconds To run all queries: 0.447 secondsMinimum number of seconds to run all queries: 0.444 secondsMaximum number of seconds to run all queries: 0.451 secondsNumber of clients running queries: 100 Average number of queries per client: 10 www.2cto.com User time 1.44, System time 0.67 Maximum resident set size 0, Integral resident set size 0Non-physical pagefaults 17922, Physical pagefaults 0, Swaps 0 Blocks In 0 out 0, Messages in 0 out 0, Signals 0 Voluntary context switches 36796, Involuntary context switches 4093 test the performance of different storage engines at the same time: $ mysqlslap-a-concurrency = 50,100-number-of-queries 1000-iterations = 5-engine = myisam, innodb-debug-infoBenchmarkRunning for engine myisamAverage number of seconds to run all queries: 0.200 secondsMinimum number of seconds to run all queries: 0.188 secondsMaximum numb Er of seconds to run all queries: 0.210 secondsNumber of clients running queries: 50 Average number of queries per client: 20 BenchmarkRunning for engine myisamAverage number of seconds to run all queries: 0.238 secondsMinimum number of seconds to run all queries: 0.228 secondsMaximum number of seconds to run all queries: 0.251 secondsNumber of clients running queries: 100 Average number of queries Per client: 10 BenchmarkRunning for engine innodbAverage number of seconds to run all queries: 0.375 secondsMinimum number of seconds to run all queries: 0.370 secondsMaximum number of seconds to run all queries: 0.379 secondsNumber of clients running queries: 50 Average number of queries per client: 20 www.2cto.com BenchmarkRunning for engine innodbAverage number of seconds to run all queries: 0.4 43 secondsMinimum number of seconds to run all queries: 0.440 secondsMaximum number of seconds to run all queries: 0.447 secondsNumber of clients running queries: 100 Average number of queries per client: 10 User time 2.83, system time 1.66 Maximum resident set size 0, Integral resident set size 0Non-physical pagefaults 34692, Physical pagefaults 0, Swaps 0 Blocks in 0 out 0, Messages in 0 out 0, Sig Nals 0 Voluntary context switches 87306, Involuntary context switches 10326 MySQL comes with a stress testing tool mysqlslap starting from 5.1.4. It simulates multiple concurrent clients to access mysql for testing, it is very easy to use. You can use mysqlslap-help to obtain available options, such as-auto-generate-SQL, -a automatically generates the test table and data www.2cto.com-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: the schema in MySQL, that is, the number of DML entries in database-commint = N, is submitted once-compress.-C if both the server and client support compression, then the compressed information is transmitted-concurrency = N,-c N concurrency, that is, to simulate how many clients execute select simultaneously. Multiple values can be specified. The storage engine used to create a test table is separated by commas (,) or the value specified by the-delimiter parameter as the separator www.2cto.com-engine = engine_name, you can specify multiple-iterations = N,-I N test iterations-detach = N to execute N statements and then disconnect the reconnection-debug-info, -T print memory and CPU information-only-print only prints test statements without actually executing the author bengda