MySQL Performance Testing Tool-mysqlslap

MySQL Performance Testing Tool-mysqlslap

Mysqlslap is a benchmark tool provided by mysql. It has the following advantages: Data Query, simple syntax, and 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. msqlslap provides an intuitive verification basis before and after mysql performance optimization. It is recommended that system O & M and DBA personnel master some common stress testing tools, in order to accurately master the user traffic ceiling supported by online databases and their adequacy.

Mysqlslap stress testing tool

Detailed description of MySQL performance stress testing tool mysqlslap

MySQL stress testing tool mysqlslap usage Summary

1. Detailed tool parameters

-- Help: Display help

-- Concurrency indicates the number of concurrent tasks. multiple concurrent tasks can be separated by commas (,).

-- Engines test engine. Multiple engines are separated by delimiters.

-- How many times does iterations run these tests?

-- Auto-generate-SQL script generated by the SQL System

-- Auto-generate-SQL-load-type: whether to test whether the read or write operations are mixed or not (read, write, update, mixed)

-- Number-of-queries

-- Debug-info: Additional CPU and memory information output

-- Number-int-cols: number of int fields in the test table

-- Auto-generate-SQL-add-autoincrement automatically adds the auto_increment column to the table generated

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

-- Create-schema Test database

-- Query: run the test with a custom script

-- Only-print: If you only want to print and see what the SQL statement is, use this option.

2. Test the instance (single concurrency)

[Root @ node2 bin] #. /mysqlslap-uroot-psystem -- concurrency = 100 -- iterations = 1 -- auto-generate-SQL-load-type = mixed -- auto-generate-SQL-add -autoincrement -- engine = myisam -- number-of-queries = 10 -- debug-info
Warning: Using a password on the command line interface can be insecure.
Benchmark
Running for engine myisam
Average number of seconds to run all queries: 0.209 seconds
Minimum number of seconds to run all queries: 0.209 seconds
Maximum number of seconds to run all queries: 0.209 seconds
Number of clients running queries: 100
Average number of queries per client: 0

User time 0.02, System time 0.09
Maximum resident set size 4684, Integral resident set size 0
Non-physical pagefaults 1476, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switcheles 2797, Involuntary context switches 25
[Root @ node2 bin] #

Note: To test 100 concurrent threads, the test times are once. The SQL test script, read, write, and update hybrid test are automatically generated. The test engine is myisam, runs 10 queries and outputs cpu resource information.

3. test instances (multiple concurrency)

[Root @ node2 bin] #. /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-psystem
Warning: Using a password on the command line interface can be insecure.
Benchmark
Running for engine myisam
Average number of seconds to run all queries: 0.758 seconds
Minimum number of seconds to run all queries: 0.671 seconds
Maximum number of seconds to run all queries: 0.870 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.840 seconds
Minimum number of seconds to run all queries: 0.645 seconds
Maximum number of seconds to run all queries: 1.117 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.959 seconds
Minimum number of seconds to run all queries: 0.802 seconds
Maximum number of seconds to run all queries: 1.192 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: 0.701 seconds
Minimum number of seconds to run all queries: 0.632 seconds
Maximum number of seconds to run all queries: 0.778 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.754 seconds
Minimum number of seconds to run all queries: 0.560 seconds
Maximum number of seconds to run all queries: 0.920 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.860 seconds
Minimum number of seconds to run all queries: 0.709 seconds
Maximum number of seconds to run all queries: 1.173 seconds
Number of clients running queries: 200
Average number of queries per client: 1

[Root @ node2 bin] #

Note: Test 50,100,200 concurrency, run 20 times, storage engine myisam and innodb, run 200 queries

4. test instances (custom SQL statements)

[Root @ node2 bin] #. /mysqlslap -- concurrency = 100 -- iterations = 1 -- query = 'select * from tong. t; '-- number-of-queries = 10 -- debug-info-uroot-psystem

Benchmark
Average number of seconds to run all queries: 2.211 seconds
Minimum number of seconds to run all queries: 2.211 seconds
Maximum number of seconds to run all queries: 2.211 seconds
Number of clients running queries: 100
Average number of queries per client: 0

User time 0.05, System time 0.27
Maximum resident set size 7324, Integral resident set size 0
Non-physical pagefaults 1686, Physical pagefaults 33, Swaps 0
Blocks in 7104 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 3449, Involuntary context switches 40
[Root @ node2 bin] #

5. Test the instance (check script)

[Root @ node2 bin] #. /mysqlslap -- create =/yourpath/Test1. SQL -- query =/yourpath/Test2. SQL -- concurrency = 50,100,200 -- iterations = 20 -- engine = myisam, innodb-u root-psystem

This article permanently updates the link address: