Go MySQL comes with a performance stress test tool Mysqlslap Detailed

Mysqlslap is a stress test tool from Mysql that can simulate a large number of clients operating the database at the same time, through the results of information to understand the performance status of the database

A major work scenario for MYSQLSLAP is benchmarking the database server

For example, we get a server, ready to be a database server, then the hardware resources of this server can support how much access pressure? Has the operating system's kernel parameters been optimized to improve performance? How much performance is affected by tuning the MySQL configuration parameters? ......

Through a series of tuning work, with the benchmark test, the server can be adjusted to the best state, but also mastered the health of the performance indicators

Later in the actual operation process, when the monitoring data close to the benchmark, the database server is almost full load, need to analyze the database structure design, SQL statements such usage problems, or hardware resources is not enough, and then the corresponding processing

The database server may also require a hardware upgrade, and benchmark tests will be required after the upgrade, comparing with previous test results to ensure that the upgraded performance is improved to prevent inappropriate upgrades or incorrect configuration resulting in degraded performance

Understand the usefulness of mysqlslap, see below how to use Mysqlslap

Mysqlslap Example

01 Simple usage

Make a simple automated test of the database

Mysqlslap–user=root–password=111111–auto-generate-sql

–auto-generate-sql function is to generate test SQL automatically

The meanings of the results:

Average Number of ...

Average number of seconds to run all statements

Minimum Number of ...

Minimum number of seconds to run all statements

Maximum Number of ...

Maximum number of seconds to run all statements

Number of clients ...

Number of clients

Average number of queries per client

Average number of queries run per client

02 Adding concurrency

Mysqlslap–user=root–password=111111–concurrency=100–number-of-queries=1000–auto-generate-sql

–CONCURRENCY=100 specifies that there are 100 client connections at a time

–number-of-queries=1000 Specify the total number of test queries (number of concurrent clients * Number of queries per client)

03 Automatic generation of complex tables

When automated testing, the table structure created is very simple, only two columns, the actual product environment will certainly be more complex, you can use parameters to specify the number and type of columns, such as

Mysqlslap–user=root–password=111111–concurrency=50–number-int-cols=5–number-char-cols=20–auto-generate-sql

–NUMBER-INT-COLS=5 specifies that a column of 5 int types is generated

–NUMBER-CHAR-COLS=20 specifies that a column of 20 char types is generated

04 using your own test libraries and test statements

Automated testing can help us understand the hardware level of the situation, for our product-specific situation, or to use their own library to test the better, you can copy a product library, and then to this library testing, such as

mysqlslap–user=root–password=111111–concurrency=50–create-schema=employees–query= "SELECT * from Dept_emp;"

–create-schema used to specify the test library name

–query is a custom test statement

When actually used, it is usually to test multiple complex statements, you can define a script file, for example

echo "SELECT * from Employees; SELECT * from titles; SELECT * from Dept_emp; SELECT * from Dept_manager; SELECT * from departments; "> ~/select_query.sql

Writes multiple query statements to a SQL file and then executes the test using this file

Mysqlslap–user=root–password=111111–concurrency=20–number-of-queries=1000–create-schema=employees–query= " Select_query.sql "–delimiter="; "

SQL file specified in –query

–delimiter describes what the delimiter between statements in a SQL file is

PS: Today a colleague asked me to have a more reliable MySQL stress test tool available. In fact, MySQL comes with a pressure test tool called Mysqlslap, or a good simulation. Let me give you an example. 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 on "High-load attack MySQL". And it is very good to compare the concurrency pressure performance difference of multiple storage engines in the same environment. The available options can be obtained through MYSQLSLAP–HELP, where the main parameters are listed, and the official manuals are described in more detail. If the system comes with or uses the RPM package installs the MySQL, installs the mysql-client side the package to have mysqlslap this tool.

is the number of connection processes for MySQL running mysqlslap-a-c 500-i 10-uroot-p123456 test:


Use the following syntax:
# Mysqlslap [Options]

common parameters [options] Detailed description:

--auto-generate-sql,-a automatically generates test tables and data, indicating that the concurrency pressure is tested using the SQL script generated by the Mysqlslap tool itself. The type of the
--auto-generate-sql-load-type=type test statement. Represents whether the environment to be tested is a read or write operation or a mixture of the two. The values are: Read,key,write,update and mixed (default). The
--auto-generate-sql-add-auto-increment represents the automatic addition of the Auto_increment column to the generated table, starting with version 5.1.18.
--number-char-cols=n,-x N auto-generated test table contains the number of character types of the column, the default 1
--number-int-cols=n, the y-N auto-generated test table contains how many number types of columns, the default 1
-- Number-of-queries=n total number of test queries (number of concurrent customers x per customer query)
--query=name,-q executes the test using a custom script, such as a custom stored procedure or SQL statement that can be invoked to perform the test. The
--create-schema represents a custom test library name, and the Schema,mysql schema in the test is database.
--commint=n How many DML are submitted once.
--compress,-c compressed information is passed if both server and client support are compressed.
--concurrency=n,-c N represents the concurrency, which is how many clients perform a select at the same time. You can specify more than one value, either as a delimiter or as a comma or as the value specified by the--delimiter parameter. 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 tests perform, represents how many times to run each test in a different concurrency environment. The
--only-print only prints test statements and does not actually execute.
--detach=n breaks the re-connection after executing N statements.
--debug-info,-t print memory and CPU related information.


Description
The test process needs to generate a test table, insert the test data, the MYSQLSLAP can be generated automatically, by default generate a Mysqlslap schema, if already exist, first delete. Can be used --only-print to print the actual test process, the entire test will not leave a trace in the database.

Various test parameter instances (-P followed by the root password of MySQL):

Single thread testing. What the test did.
# mysqlslap -a -uroot -p123456
Multithreaded testing. Use –concurrency to simulate concurrent connections.
# mysqlslap -a -c 100 -uroot -p123456
Iterative testing. Used to perform tests that require multiple executions to get an average.
# 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 the performance of the different storage engines at the same time:
# mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --iterations=5 --engine=myisam,innodb --debug-info -uroot -p123456

Perform a test, respectively, 50 and 100 concurrently, and perform 1000 total queries:
# mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --debug-info -uroot -p123456

50 and 100 concurrently get a test result (Benchmark), the more concurrent the number, the longer it takes to complete all queries. For the sake of accuracy, multiple iterations can be tested several times:
# mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --iterations=5 --debug-info -uroot -p123456

Reference

• Percona Sysbench Oltp.lua

• Sysbench Manual 0.4

• Sysbench Introduction and use

• Sysbench testing MySQL Performance

• Sysbench 0.5 User Manual

Go MySQL comes with a performance stress test tool Mysqlslap Detailed