MySQL benchmarking tool-mysqlslap

Mysqlslap is a benchmark testing tool that comes with benchmark after MySQL5.1. This tool can simulate multiple clients simultaneously and concurrently send query updates to the server, the performance test data is provided and the Performance Comparison of multiple engines is provided.

Usage:

-- The number of concurrency concurrent threads can be separated by commas), for example, concurrency = 10, 50, 100 concurrent connection threads are 10, 50, 100

-- Storage engines to be tested by engines, separated by commas

-- How many times does iterations run? The result is displayed;

-- Auto-generate-SQL automatically generates an SQL script for testing;

-- Auto-generate-SQL-load-type: whether the test type is read, write, or mixed. You can use -- only-print to check which SQL statements are used)

-- Number-of-queries: the number of queries executed,

-- Debug-info: outputs CPU, memory, and other system-related information

-- Number-int-cols test table int field type quantity;

-- Auto-generate-SQL-add-autoincrement: automatically generates columns containing the auto_increment attribute for the test table generated;

-- Number-the number of char fields in the char-cols test table;

-- Create-schema = db: create a test database;

-- Query is tested using a custom script, which can be a stored procedure or SQL statement;

-- Only-print: only the SQL statements involved in the test are printed and not actually executed;

Example:

Test the performance of 200 concurrent writes. The test is performed 20 times and an SQL script is automatically generated. The test table contains 35 int fields and 20 char fields. The test engine is innodb and myisam; execute 3000 queries each time;

./bin/mysqlslap --socket=/usr/local/mysql/mysql.sock --port=3306 --concurrency=200  --iterations=20 --auto-generate-sql --auto-generate-sql-load-type=write  --auto-generate-sql-add-autoincrement --number-int-cols=35 --number-char-cols=20  --engine=innodb,myisam   --create-schema=sysbench  --number-of-queries=3000 --debug-info

Result:

Benchmark        Running for engine innodb        Average number of seconds to run all queries: 6.385 seconds        Minimum number of seconds to run all queries: 3.021 seconds        Maximum number of seconds to run all queries: 12.969 seconds        Number of clients running queries: 200        Average number of queries per client: 15Benchmark        Running for engine myisam        Average number of seconds to run all queries: 0.621 seconds        Minimum number of seconds to run all queries: 0.442 seconds        Maximum number of seconds to run all queries: 1.134 seconds        Number of clients running queries: 200        Average number of queries per client: 15User time 0.66, System time 1.97Maximum resident set size 10060, Integral resident set size 0Non-physical pagefaults 50486, Physical pagefaults 5, Swaps 0Blocks in 584 out 96, Messages in 0 out 0, Signals 0Voluntary context switches 298373, Involuntary context switches 1469

Custom SQL test script:

mysqlslap --create=/yourpath/test.sql --query=/yourpath/test1.sql --concurrency=50,100,200 --iterations=20 --engine=myisam,innodb

This article from the "Technical Achievement dream" blog, please be sure to keep this source http://weipengfei.blog.51cto.com/1511707/1249295