Use of MySQL performance test tool Mysqlslap

Developer on Alibaba Coud: Build your first app with APIs, SDKs, and tutorials on the Alibaba Cloud. Read more ＞

Pay special attention to the false concurrency of the next mysqlslap, because the pressure does not waste some time.
The main point is that the concurrency of the –concurrency parameter is thread concurrency, at the beginning again?? Luo?/p> of the Locust

1.mysql Performance Testing Tool

MySQL's performance testing tools are commonly used in four kinds: the MySQL Benchmark Suite, MySQL super-smack, Mybench, and the Mysqlslap. In addition to the first MySQL performance test tool, the other three are stress testing tools

(1) The MySQL Benchmark Suite: Based on the Perl language and two of these modules: DBI and Benchmark, discarding the software because it does not support multiple CPUs and is not a pressure tool

(2) MySQL super-smack industry reputation is good, installed a compression package compiled installation, but also need YACC and Lex support, installation error can not find Lex and yacc ah what. Linux under the use of flex and bison instead, you can use the Apt-get install Bison flex or similar command installation, the problem is to find the machine can connect to the extranet, the machine can connect the external network does not necessarily support the pressure, give up the software

(3) Mybench

Also a Perl module, although easy to install, but the use of test data is more cumbersome, drop it.

(4) Mysqlslap

Finally chose the Mysqlslap. Installation cost is small: The reason is mysql5.1.4 version of the belt, installed MySQL can be used, no additional configuration required. Scenario deployment multiple: With multiple parameter options, test data is also easy to use. By simulating multiple concurrent clients accessing MySQL concurrently to perform the stress test, the results are analyzed: A more detailed performance report of SQL execution data is provided, and the performance difference between multiple storage engines (MYISAM,INNODB, etc.) under the same concurrent pressure is well contrasted.

Mysqlslap's official website: http://dev.mysql.com/doc/refman/5.6/en/mysqlslap.html

Installation of 2.mysqlslap

1 View the current version of MySQL

./bin/mysql-v

2 if the version below 5.1.4, please install the MySQL high version

You must first close the current client during the installation process. Use Netstat-pan|grep MySQL to see if the current MySQL port is running. To avoid port conflicts, it is a good idea to shut down the MySQL process before installing

Use of 3.mysqlslap

(1) Use steps

See the descend network for the use of the steps to explain:

Create schema, table, and optionally any stored programs or data to use for the test. This is stage uses a single client connection.

Run the load test. This stage can use many client connections.

Clean up (Disconnect, drop table if specified). This is stage uses a single client connection

The main steps of Mysqlslap are: 1. Create schema, prepare test statement (schema is database in MySQL), 2. Run a load test, you can use multiple concurrent client connections, (scripting, multiple-process concurrency) 3. Test environment cleanup, shutdown process, clean data, etc.

(2) Parameter description
The following is the parameter meaning of Mysqlslap

View Plaincopy to Clipboardprint?

./bin/mysqlslap--help can see the explanation of the parameters of the help, here are some of the more commonly used

--no-defaults indicates that the settings in the default parameter file are not used.

--debug-info,-t print memory and CPU information;

--auto-generate-sql, -a automatically generate test tables and data;

--auto-generate-sql-load-type=type the type of the test statement. Values include read, key, write, update, and mixed (default);

--number-char-cols=n, -x

N the number of character type columns in the automatically generated test table, default 1;

--number-int-cols=n,-y N the number of columns in the automatically generated test table that contains the numeric types, default 1;

--number-of-queries=n total number of test queries (number of concurrent clients x per client query);

--query=name,-q perform tests using custom scripts, such as a custom stored procedure or SQL statement that can be invoked to execute the test.

--create-schema test Schema, MySQL schema is the database;

--commint=n How many DML is submitted once;

--compress,-c if the server and client support are compressed, the compression of information transmission;

--concurrency=n, -c N concurrency, which is to simulate how many clients execute a select at the same time. Multiple values can be specified, with a comma or a value specified by the--delimiter parameter as a separator;

--engine=engine_name, -e Engine_name the storage engine used to create the test table, specifying multiple;

--iterations=n, -i N The number of iterations performed by the test;

--detach=n executes N statements and disconnects the connection;

--only-print only prints test statements without actually executing them;

Connection parameters:

-u,--user=name user for login if not current user.
-p,--password=name password to use when connecting to server. If the password is not given, it will be requested through the TTY terminal.
-h,--host=name Connect to host.
-P,--port= port number to use for connection.

What needs to be explained is that –concurrency was found in the actual test tuning parameter, which is a false concurrency parameter. It is not multiple client concurrency, but a parameter that specifies the use of multiple threads in a client process. Adding this value does not make the pressure go up, perhaps you find it. Increasing the number of threads can sometimes bring down the pressure that falls on every MySQL machine.

In the actual test, if you want to pressure up, you need to start multiple mysqlslap processes to test.

(3) Example description
Batch processing of data with scripts before testing to generate a SELECT statement with no cache

View Plaincopy to Clipboardprint?
<pre>select Sql_no_cache Url_key, value from Structqa where Url_key in (5793176823383938934,1549359593866465909,46398175249572291 39,6858231871203830826); </pre >

<PRE>DB plus cache and no cache pressure is about 1.5 times times. </pre>

<pre> through the monitoring diagram can see the single process +cache and single process without cache comparison: The figure is four machine curve fitting, considering the dbproxy piecewise pressure uneven </pre>
<pre><a href= "/wp-content/uploads/2013/06/mysql+cache.png" ></a>
<pre><a href= "/wp-content/uploads/2013/06/mysql+cache.png" ></a> <a style=" Font-size:1.5em "href="/wp-content/uploads/2013/06/mysql-cache1.png "></a></pre>
</pre>

The statements that start a single process when used are as follows:

<pre>/home/iknow/local/mysql/bin/mysqlslap-u*****-p*****-h***.***.***.***-P3300--create-schema=****-- Query=select_ddbs.sql--concurrency=2--number-of-queries=6000000--iterations=2000--debug-info--engine=innodb; </pre>
Parameter description: –create-schema indicate the database you want to test, or you will get an error./bin/mysqlslap:error when connecting to server:1045 Auth failed, check your Userna Me, password or db

–query generated SQL statement file –concurrency use two threads to process –number-of-queries the number of queries used in this pressure –iterations the loop 2000 times, because it takes a while to kill it manually- Engine is the InnoDB database engine –debug-info will print some CPU and memory information at the end of execution.

4. Performance test

(1) Performance factors
1, test environment

If it's an offline machine, try to be as consistent as possible.

2, test data

MySQL is divided into two kinds of query with cache and without cache, and the request with no caching under the line construction

3, Pressure request

(2) Performance index
"Database performance metrics"

QPS (TPS): Number of request/transactions per second
Concurrency: Number of request/transactions processed concurrently by the system
Response time: General average response time

"System performance Metrics"

Cpu:
Idle: Shows the percentage of time that the CPU is in idle state
WA value: The WA column shows the percentage of CPU time occupied by the IO wait.
The reference value for WA is 30%, and if WA exceeds 30%, the IO Wait is serious, which may be caused by a large amount of random access to the disk or the bandwidth bottleneck of the disk or disk access controller

Memory: MySQL do not pay much attention to memory, mainly read and write operations, involving the C module to pay special attention, memory leaks the most intuitive performance is the memory over time.

IO: Focus on the following metrics: The WA value of the CPU mentioned above, and
util%: How much time per second is spent on I/O operations, or how much time I/O queues are non-null in a second. That is, Delta (use)/s/1000 (because use is in milliseconds) if%util is close to 100%, it indicates that too many I/O requests are generated, i/ o The system is full load and there may be a bottleneck on the disk.

(3) Performance scene
Comprehensive performance factors and measurement indicators, as well as the current testing purposes, the construction of test scenarios need to simulate the scene on the line, the construction of the scene

1, analog line on the small flow

-----Simple Press, look at the current system performance indicators, if the small flow is not good, indicating that the system has serious performance bottlenecks.

2, Analog online full flow

---the full flow of the simulation to meticulous, the test out of the full flow of testing, but also can be stressed one night, to see system stability, additional stability testing.

3, analog line on the double flow

--Considering that the peak is twice times the average flow rate, and the flow of the line-cutting machine room

4, limit pressure test

Need to be specific with DBAs and Rd to confirm that the current machine does not provide services, only for testing, and the limit pressure time should not be too long, generally about 1-2 hours

Here's a look at Mysqlslap's pressure test on MySQL.

Let me explain some of the common options.
Here are a few options to note:
--concurrency represents concurrent quantity, multiple can be separated by commas, of course you can also use your own separator, this time to use the--delimiter switch.
--engines represents the engine to be tested, can have multiple, separated by delimiters.
The--iterations represents how many times you want to run these tests.
The--auto-generate-sql represents a test of SQL scripts generated by the system itself.
--auto-generate-sql-load-type represents whether the test is to be read or written or mixed (read,write,update,mixed)
--number-of-queries represents the total number of queries to run. The number of queries that each customer runs can be calculated with the total number of queries/concurrency. Like the penultimate result 2=200/100.
The--debug-info represents information about the extra CPU and memory to be exported.
There are several instances of the integer type in the--number-int-cols representation table.
--number-char-cols meaning ditto.
--create-schema represents the pattern that you define (in MySQL, the library).
The--query represents your own SQL script.
--only-print You can use this option if you want to print only to see what the SQL statement is.

Now let's look at some examples of my tests.

1, with the SQL script with the test.
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
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&N Bsp 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 Quer ies:100
        Average number of queries per Client:2

Benchmark
        Running for engine MyISAM
         Average number of seconds to run all queries:0.092 seconds
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&N Bsp 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 Quer ies:200
        Average number of queries per client:1

Benchmark
        Running for engine InnoDB
         Average number of seconds to run all queries:0.115 seconds
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&N Bsp 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 Quer IES:50
        Average number of queries per Client:4

Benchmark
        Running for engine InnoDB
         Average number of seconds to run all queries:0.134 seconds
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&N Bsp 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 Quer ies: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 is 5.0.45
Take a look at the data on the two tables first.
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 definer= ' root ' @ '% ' PROCEDURE ' sp_get_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

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.

This article is an English version of an article which is originally in the Chinese language on aliyun.com and is provided for information purposes only. This website makes no representation or warranty of any kind, either expressed or implied, as to the accuracy, completeness ownership or reliability of the article or any translations thereof. If you have any concerns or complaints relating to the article, please send an email, providing a detailed description of the concern or complaint, to info-contact@alibabacloud.com. A staff member will contact you within 5 working days. Once verified, infringing content will be removed immediately.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

Get Started for Free

Sales Support

1 on 1 presale consultation

Chat Contact Sales
After-Sales Support

24/7 Technical Support 6 Free Tickets per Quarter Faster Response

Open a Ticket
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.

Learn More

Use of MySQL performance test tool Mysqlslap

Contact Us

What's Trending

Top 10 Tags

Top 10 Keywords

A Free Trial That Lets You Build Big!

Sales Support

After-Sales Support

Use of MySQL performance test tool Mysqlslap

Contact Us

What's Trending

Top 10 Tags

Top 10 Keywords

Trending Topic

A Free Trial That Lets You Build Big!

Sales Support

After-Sales Support