MySQL database benchmark stress testing tool-MySQLSlap instance

MySQL database benchmark stress testing tool-MySQLSlap use Example 1. Mysqlslap introduction mysqlslap is a benchmark testing tool that comes with benchmark after MySQL5.1. Similar to Apache benchmark load generation tool, it generates schema and loads data, execute benckmark and query 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. Iii. Demo instances below we use several demo instances for Testing 1. Demo1: [root @ localhost ~] # Mysqlslap-uroot-p123abc -- concurrency = 100 -- iterations = 1 -- auto-generate-SQL-load-type = mixed -- auto-generate-SQL-add -autoincrement -- engine = myisam -- number-of-queries = 10 -- debug-info # note that this test is performed once with 100 concurrent threads, automatically generate SQL test script, read, write, update hybrid test, self-incrementing field, test engine is myisam, and a total of 10 queries are run. The output cpu resource information is returned as follows: rement -- engine = myisam -- number-of-queries = 10 -- debug-infoBenchmark Running for engine myisam Average number of seconds to run all queries: 0.129 seconds Minimum number of seconds to run all queries: 0.107 seconds Maximum number of seconds to run all queries: 0.264 seconds Number of clients running queries: 100 Average number of queries per client: 0 User time 0.16, System time 0.25 Maximum resident set size 4624, Integral resident set size 0Non-physical pagefaults 7346, Physical pagefaults 0, Swaps 0 Blocks in 0 out 0, Messages in 0 out 0, signals 0 Voluntary context switches 27221, Involuntary context switches 42412, Demo2: specify the database and SQL statement mysqlslap-h192.168.202.84-P3309 -- concurrency = 100 -- iterations = 1 -- create-schema = 'mms _ sdmtv '-- query = 'select * from role; '-- number-of-queries = 10 -- debug-info-uroot-p123abc # Remarks use the specified SQL statement of mysqlslap for testing 3. Demo3: test Case [root @ localhost/] # mysqlslap -- concurrency = 50,100,200 -- iterations = 20 -- number-int-cols = 4 -- number-char-cols = 35 -- auto-generate-SQL -- auto-generate-SQL-add-autoincrement -- auto-generate-SQL-load-type = read -- engine = myisam, innodb -- number-of-queries = 200 -- verbose -- socket =/var/lib/mysql. sock-uroot-p123abc # system Script test. Four int columns and 35 char columns are added to test the read performance of the two engines myisam and innodb, test the server with 50,100,200 clients respectively. A total of 200 query statements are executed for 20 queries. Benchmark Running for engine myisam Average number of seconds to run all queries: 0.666 seconds Minimum number of seconds to run all queries: 0.223 seconds Maximum number of seconds to run all queries: 4.889 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: 0.620 seconds Minimum number of seconds to run all queries: 0.231 seconds Maximum number of seconds to run all queries: 4.898 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: 0.503 seconds Minimum number of seconds to run all queries: 0.257 seconds Maximum number of seconds to run all queries: 4.269 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.049 seconds Minimum number of seconds to run all queries: 0.244 seconds Maximum number of seconds to run all queries: 5.292 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: 0.712 seconds Minimum number of seconds to run all queries: 0.246 seconds Maximum number of seconds to run all queries: 6.585 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: 0.269 seconds Minimum number of seconds to run all queries: 0.175 seconds Maximum number of seconds to run all queries: 0.328 seconds Number of clients running queries: 200 Average number of queries per client: 14. self-built SQL test case mysqlslap -- create =/yourpath/Test1. SQL -- query =/yourpath/Test2. SQL -- concurrency = 50,100,200 -- iterations = 20 -- engine = myisam, innodb-u root-p123abc # create your test SQL file Test1 and Test2 in the Set yourpath directory, and perform simulated concurrency tests of 50, 100, and 200.