MySQL Pressure measuring tool mysqlslap tool usage method detailed

Mysqlslap is an official MySQL-provided stress testing tool starting with version 5.1.4. Perform stress testing by simulating multiple concurrent client access to MySQL while providing detailed data performance reports for "High load attack MySQL". And can be a good comparison of multiple storage engine in the same environment of concurrent pressure performance differences. Available options can be obtained through mysqlslap–help, with some of the main parameters listed here, and a more detailed description of the official manual. If it is the system itself or using the RPM package installed MySQL, install the mysql-client end of the package has mysqlslap this tool. If the source is installed Mysqlslap directly in the bin directory, if the RPM installation, you need to install the RPM package separately.

Mysqlslap parameter Detailed Description:

–auto-generate-sql,-a automatically generates test tables and data that represent the SQL scripts that you generate with the Mysqlslap tool to test concurrent pressure. The
–auto-generate-sql-load-type=type the type of the test statement. Represents whether the environment you want to test is a read or write operation or a mixture of both. Values include: Read,key,write,update and mixed (default). The
–auto-generate-sql-add-auto-increment represents the automatic addition of auto_increment columns to the generated table and is supported from version 5.1.18.
–number-char-cols=n, X-n automatically generated test table contains the number of characters in the column, default 1
–number-int-cols=n, the Y-n automatically generated test table contains the number of numeric types of columns, default 1
– Number-of-queries=n total number of test queries (number of concurrent clients x per customer query)
–query=name,-q perform tests using custom scripts, such as a custom stored procedure or SQL statement that can be invoked to perform the test. The
–create-schema represents the custom test library name, and the Schema,mysql schema in the test is database.
–commint=n How many DML is submitted once.
–compress,-c compresses information delivery if both the server and client support are compressed.
–concurrency=n, C-N indicates the amount of concurrency, that is, how many clients execute the select at the same time. You can specify more than one value, with a comma or a value specified by the –delimiter parameter as the separator. For example: –concurrency=100,200,500.
–engine=engine_name,-e engine_name represents the engine to be tested, can have multiple, separated by delimiters. For example: –engines=myisam,innodb. The number of iterations that the
–iterations=n, I-N test executes, represents how many times each test will run in a different concurrency environment. The
–only-print only prints test statements without actually executing them. The
–detach=n disconnects after executing N statements.
–debug-info,-t prints information about memory and CPU.

Mysqlslap Test Examples

Single-thread testing. What the test did.

# mysqlslap-a-uroot-p123456

Multithreading test. Use –concurrency to simulate concurrent connections.

# MYSQLSLAP-A-C 100-uroot-p123456

Iterative testing. For averages that require multiple tests to be performed.

# mysqlslap-a-I. 10-uroot-p123456
# mysqlslap-auto-generate-sql-add-autoincrement-a-uroot-p123456
# mysqlslap-a–auto-generate-sql-load-type=read-uroot-p123456
# mysqlslap-a–auto-generate-secondary-indexes=3-uroot-p123456
# mysqlslap-a–auto-generate-sql-write-number=1000-uroot-p123456
# Mysqlslap–create-schema World-q "SELECT COUNT (*) from City"-uroot-p123456
# MYSQLSLAP-A-E innodb-uroot-p123456
# mysqlslap-a–number-of-queries=10-uroot-p123456

Test performance comparisons for different storage engines:

# mysqlslap-a–concurrency=50,100–number-of-queries 1000–iterations=5–engine=myisam,innodb–debug-info-uroot- p123456

Perform a test, 50 and 100 concurrent, and perform a 1000-time total query:

# mysqlslap-a–concurrency=50,100–number-of-queries 1000–debug-info-uroot-p123456

50 and 100 concurrent results of a test result (Benchmark), the more the number of concurrent, the longer the execution of all queries. For accuracy, multiple iterations can be tested several times:

# mysqlslap-a–concurrency=50,100–number-of-queries 1000–iterations=5–debug-info-uroot-p123456

The following figure is the execution of/usr/local/mysql/bin/mysqlslap-a-C 500-i 10-uroot-p

Add some test instances: Test with your own SQL script.

MySQL version for 5.1.23

[Root@localhost ~]# mysqlslap--defaults-file=/usr/local/mysql-maria/my.cnf--concurrency=50,100,200--iterations=1 --number-int-cols=4--number-char-cols=35--auto-generate-sql--auto-generate-sql-add-autoincrement-- Auto-generate-sql-load-type=mixed--engine=myisam,innodb--number-of-queries=200--debug-info-uroot-p1-s/tmp/ Mysql_3310.sock


Benchmark


Running for Engine MyISAM


Average number of seconds to run all queries:0.063 seconds


Minimum number of seconds to run all queries:0.063 seconds


Maximum number of seconds to run all queries:0.063 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.070 seconds


Minimum number of seconds to run all queries:0.070 seconds


Maximum number of seconds to run all queries:0.070 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.092 seconds


Minimum number of seconds to run all queries:0.092 seconds


Maximum number of seconds to run all queries:0.092 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.115 seconds


Minimum number of seconds to run all queries:0.115 seconds


Maximum number of seconds to run all queries:0.115 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.134 seconds


Minimum number of seconds to run all queries:0.134 seconds


Maximum number of seconds to run all queries:0.134 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.192 seconds


Minimum number of seconds to run all queries:0.192 seconds


Maximum number of seconds to run all queries:0.192 seconds


Number of clients running queries:200


Average number of queries per client:1


User time 0.06, System time 0.15


Maximum resident Set Size 0, Integral resident set size 0


Non-physical pagefaults 5803, physical pagefaults 0, Swaps 0


Blocks in 0 out of 0, Messages in 0 out 0, signals 0


Voluntary context Switches 8173, involuntary context switches 528

Let me explain the meaning of the result. Take the last benchmark example of each engine. For the InnoDB engine, it takes an average of 0.192 seconds for 200 clients to run these SQL statements at the same time. The corresponding MyISAM is 0.092 seconds.

2, with our own definition of the SQL script test.

This data is on another MySQL instance. Version for 5.0.45 First look at the data on the two tables.
1), the total number of records.

Mysql> Select Table_rows as rows from information_schema.tables where table_schema= ' t_girl ' and table_name= ' article ';
+--------+
| Rows |
+--------+
| 296693 |
+--------+
1 row in Set (0.01 sec)
Mysql> Select Table_rows as rows from information_schema.tables where table_schema= ' t_girl ' and table_name= ' category ' ;
+------+
| Rows |
+------+
| 113 |
+------+
1 row in Set (0.00 sec)

2), the total number of columns.

Mysql> Select COUNT (*) as column_total from information_schema.columns where table_schema = ' t_girl ' and table_name = ' Article ';
+--------------+
| Column_total |
+--------------+
| 32 |
+--------------+
1 row in Set (0.01 sec)
Mysql> Select COUNT (*) as column_total from information_schema.columns where table_schema = ' t_girl ' and table_name = ' Category ';
+--------------+
| Column_total |
+--------------+
| 9 |
+--------------+
1 row in Set (0.01 sec)

3), the stored procedure called

DELIMITER $$
DROP PROCEDURE IF EXISTS ' t_girl ' sp_get_article '
CREATE $$ ' root ' @ '% ' definer= ' PROCEDURE _article ' (in f_category_id int,
in f_page_size int, in f_page_no int
)
BEGIN
  Set @stmt = ' Select a. * from article as a inner join ';
  Set @stmt = Concat (@stmt, ' (select A.aid from article as a ');
  If f_category_id!= 0 then
     Set @stmt = concat (@stmt, ' INNER join (select CID from category where cid = ', f_category_id, ' or parent_id = ', f_category_id, ') as B on a.category_id = B.cid ');
  End If;
  If f_page_size >0 && f_page_no > 0 Then
    set @stmt = concat (@stmt, ' limit ', (f_page_no-1) *f_page_size, ', ', f_page_size);
  End if; 

Set @stmt = Concat (@stmt, ') as B on (a.aid = B.aid) ');
Prepare S1 from @stmt;
Execute S1;
deallocate prepare S1;
Set @stmt = NULL;
end$$
DELIMITER;

4), we use MYSQLSLAP to test

The following example represents a call stored procedure that uses MYSQLSLAP to test concurrent numbers as 25,50,100, and is called 5,000 times altogether.

[Root@localhost ~]# mysqlslap--defaults-file=/usr/local/mysql-maria/my.cnf--concurrency=25,50,100--iterations=1- -query= ' Call t_girl.sp_get_article (2,10,1); '--number-of-queries=5000--debug-info-uroot-p-S/tmp/mysql50.sock
Enter Password:
Benchmark
Average number of seconds to run all queries:3.507 seconds
Minimum number of seconds to run all queries:3.507 seconds
Maximum number of seconds to run all queries:3.507 seconds
Number of clients running QUERIES:25
Average number of queries per client:200

Averaging 200 queries per concurrent operation takes 3.507 seconds.

Benchmark


Average number of seconds to run all queries:3.742 seconds


Minimum number of seconds to run all queries:3.742 seconds


Maximum number of seconds to run all queries:3.742 seconds


Number of clients running QUERIES:50


Average number of queries per client:100


Benchmark


Average number of seconds to run all queries:3.697 seconds


Minimum number of seconds to run all queries:3.697 seconds


Maximum number of seconds to run all queries:3.697 seconds


Number of clients running queries:100


Average number of queries per client:50


User time 0.87, System time 0.33


Maximum resident Set Size 0, Integral resident set size 0


Non-physical pagefaults 1877, physical pagefaults 0, Swaps 0


Blocks in 0 out of 0, Messages in 0 out 0, signals 0


Voluntary context Switches 27218, involuntary context switches 3100

Take a look at show processlist results

Mysql> show Processlist;


+------+------+--------------------+--------------------+---------+-------+--------------------+--------------- ---------------------------------------------------------------------------------------+


| Id | User | Host | db | Command | Time | State | Info |


+------+------+--------------------+--------------------+---------+-------+--------------------+--------------- ---------------------------------------------------------------------------------------+


............


| 3177 | Root | %                  | T_girl |     Query | 0 | NULL | Select A.* from article as a INNER join (select A.aid from article as a INNER join (select CID from |


| 3178 | Root | %                  | T_girl |     Query | 0 | NULL | Select A.* from article as a INNER join (select A.aid from article as a INNER join (select CID from |


| 3179 | Root | %                  | T_girl |     Query | 0 | NULL | Select A.* from article as a INNER join (select A.aid from article as a INNER join (select CID from |


| 3181 | Root | %                  | T_girl |     Query | 0 | NULL | Select A.* from article as a INNER join (select A.aid from article as a INNER join (select CID from |


| 3180 | Root | %                  | T_girl |     Query | 0 | NULL | Select A.* from article as a INNER join (select A.aid from article as a INNER join (select CID from |


| 3182 | Root | %                  | T_girl |     Query | 0 | NULL | Select A.* from article as a INNER join (select A.aid from article as a INNER join (select CID from |


| 3183 | Root | %                  | T_girl |     Query | 0 | NULL | Select A.* from article as a INNER join (select A.aid from article as a INNER join (select CID from |


| 3187 | Root | %                  | T_girl |     Query | 0 | removing TMP table | Select A.* from article as a INNER join (select A.aid from article as a INNER join (select CID from |


| 3186 | Root | %                  | T_girl |     Query | 0 | NULL | Select A.* from article as a INNER join (select A.aid from article as a INNER join (select CID from |


| 3194 | Root | %                  | T_girl |     Query | 0 | NULL | Select A.* from article as a INNER join (select A.aid from article as a INNER join (select CID from |


| 3203 | Root | %                  | T_girl |     Query | 0 | NULL | deallocate Prepare S1 |


............


| 3221 | Root | %                  | T_girl |     Query | 0 | NULL | Select A.* from article as a INNER join (select A.aid from article as a INNER join (select CID from |


| 3222 | Root | %                  | T_girl |     Query | 0 | NULL | Select A.* from article as a INNER join (select A.aid from article as a INNER join (select CID from |


| 3223 | Root | %                  | T_girl |     Query | 0 | NULL | Select A.* from article as a INNER join (select A.aid from article as a INNER join (select CID from |


| 3224 | Root | %                  | T_girl |     Query | 0 | removing TMP table | Select A.* from article as a INNER join (select A.aid from article as a INNER join (select CID from |


| 3225 | Root | %                  | T_girl |     Query | 0 | NULL | Select A.* from article as a INNER join (select A.aid from article as a INNER join (select CID from |


| 3226 | Root | %                  | T_girl |     Query | 0 | NULL | Select A.* from article as a INNER join (select A.aid from article as a INNER join (select CID from |


+------+------+--------------------+--------------------+---------+-------+--------------------+--------------- ---------------------------------------------------------------------------------------+


Rows in Set (0.00 sec)

The test statement above can also be written in this way

[Root@localhost ~]# mysqlslap--defaults-file=/usr/local/mysql-maria/my.cnf--concurrency=25,50,100--iterations=1- -create-schema= ' t_girl '--query= ' call sp_get_article (2,10,1); '--number-of-queries=5000--debug-info-uroot-p /mysql50.sock

small sum up. Mysqlslap for the simulation of multiple users at the same time to launch the MySQL "attack" to provide convenience. At the same time provides detailed data report of "High load attack MySQL". And if you want performance for multiple engines. This tool is no better.

For programmers and DBAs, you can write your own SQL statements into a script, and then through the Mysqlslap tool to test these statements high concurrency, you can report to find out in the high concurrency caused MySQL slow running, this tool is very useful yo