Introduction to mysqlslap stress Test

Mysqlslap is a stress testing tool that simulates multiple clients to access the MySQL service and records the time spent at each stage.

The main parameters are as follows:

-- host=host_name,-h host_name MySQL server host

-- user=user_name,-u user_name user name used to connect to the MySQL server

-- password [= password],-p [password] password used to connect to the MySQL server

-- SQL statement executed by query=value,-Q value

-- number-of-queries=N the number of times each client executes SQL statements. It is important to note that the; between each SQL counts as once, for example-- query= "use test;insert into t values (null)" is twice.

The statement mysqlslap-- delimiter= ";"-- number-of-queries=10-- query= "use test;insert into t values (null)" performs five INSERT operations.

-- concurrency=N,-CN simulates the parallelism of the client

-- iterations=N,-I N number of times the stress test was performed

Under which user does create-schema=value perform the stress test?

-- how many SQL are executed by commit=N to submit? there is no submission by default.

-- auto-generate-sql,-an automatically generate SQL statements when no SQL statement is specified

-- number-char-cols=N,-x N automatically generate the number of VARCHAR fields in the SQL statement

-- number-int-cols=N,-y N automatically generate the number of INT fields in the SQL statement

-- add a self-increasing sequence to the table used by auto-generate-sql-add-autoincrement to automatically generate SQL statements

-- the number of queries that auto-generate-sql-execute-number=N automatically generates SQL statements

-- auto-generate-sql-guid-primary adds a GUID-based primary key to the automatically generated table

-- auto-generate-sql-load-type=type specifies the type of stress test. The types that can be set are read (table scan), write (insert statement), key (read primary key), update (update primary key) or mixed (half insert operation, half query operation). The default type is mixed.

-- auto-generate-sql-secondary-indexes=N specifies the number of non-primary key indexes in automatically generated tables. No non-primary key indexes are created by default.

-- auto-generate-sql-unique-query-number=N specifies a different number of queries to be executed against automatically generated tables, with a default value of 10

-- auto-generate-sql-unique-write-number=N specifies a different number of writes to automatically generated tables. The default value is 10.

Example:

Log in with neo users, simulate 30 user connections and execute a total of 100000 requests

[root@localhost] # / software/bin/mysqlslap-h 192.168.78.141-user=system-- password=system-- query= "use fire;select * from test2"-- number-of-queries=100000-c 30-I 10-- create-schema=neo

-- execution result

Benchmark

Average number of seconds to run all queries: 6.779 seconds

Minimum number of seconds to run all queries: 6.607 seconds

Maximum number of seconds to run all queries: 7.310 seconds

Number of clients running queries: 30

Average number of queries per client: 3333

Using the method of automatically generating SQL, 30 users are simulated to connect concurrently, execute 10000 different queries, and commit transactions for every 10000 SQL statements executed.

[root@localhost] # / software/bin/mysqlslap-h 192.168.78.141-user=system-password=system-auto-generate-sql--auto-generate-sql-add-autoincrement-auto-generate-sql-execute-number=100000--

Commit=10000-create-schema=neo

Benchmark

Average number of seconds to run all queries: 16.332 seconds

Minimum number of seconds to run all queries: 16.332 seconds

Maximum number of seconds to run all queries: 16.332 seconds

Number of clients running queries: 1

Average number of queries per client: 100000