Detailed introduction to mysql cluster stress testing

This article brings you a detailed introduction to mysql cluster stress testing. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

mysql stress test

Mysql comes with a stress testing tool called mysqlslap, which performs stress testing by simulating multiple concurrent clients accessing MySQL, and can well compare multiple The difference in concurrency pressure performance of storage engines in the same environment. The available options can be obtained through mysqlslap –help. Here are some main parameters. For more detailed instructions, refer to the official manual. If it is mysql that comes with the system or is installed using the rpm package, the MySQL-client package has the tool mysqlslap. (Recommended course: MySQL Tutorial)

Test environment introduction:

2 virtual machines 2Gmysql read and write separation

The usage syntax is as follows:

# mysqlslap [options]

Common parameters [options] Detailed description:

--auto-generate-sql, -a automatically generates test tables and data, indicating that the mysqlslap tool itself Generated SQL script to test concurrency stress.

--auto-generate-sql-load-type=type Type of test statement. Indicates whether the environment to be tested is a read operation, a write operation, or a mixture of the two. Values ​​include: read, key, write, update and mixed (default).

--auto-generate-sql-add-auto-increment means automatically adding the auto_increment column to the generated table. It is supported starting from version 5.1.18.

--number-char-cols=N, -x N How many character type columns are included in the automatically generated test table, default 1

--number-int-cols=N , -y N How many numeric columns are included in the automatically generated test table, default 1

--number-of-queries=N Total number of test queries (number of concurrent customers × number of queries per customer)

--query=name,-q Use a custom script to execute the test. For example, you can call a custom stored procedure or sql statement to execute the test.

--create-schema represents the custom test library name and test schema. The schema in MySQL is also the database.

--commint=N How many DMLs should be submitted once.

--compress, -C If both the server and client support compression, compressed information is passed.

--concurrency=N, -c N represents the amount of concurrency, that is, how many clients are simulated to execute select at the same time. Multiple values ​​can be specified, using commas or the 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. There can be multiple engines, separated by separators. For example: --engines=myisam,innodb.

--iterations=N, -i N The number of iterations of test execution, which represents how many times the test needs to be run in different concurrent environments.

--only-print only prints the test statement without actually executing it.

--detach=N Disconnect and reconnect after executing N statements.

--debug-info, -T Print memory and CPU related information.

Note:

The testing process requires generating a test table and inserting test data. This mysqlslap can be automatically generated. A mysqlslap schema is generated by default. If it already exists, it will be deleted first. You can use --only-print to print the actual test process, and no traces will be left in the database after the entire test is completed.

mysqlslap -a -c 150 -i 10 -uroot -proot1234 When testing the number of mysql connections to 150

When the stress test concurrency count reached 200, the connection collapsed

Test example:

Single thread test.

# mysqlslap -a -uroot -proot1234

##Multi-thread testing, test concurrency.

# mysqlslap -a -c 100 -uroot -proot1234

When the number of concurrency reaches 100, the search query time is more than 1s

Iterative testing. Used to perform tests multiple times to get the average.

# mysqlslap -a -i 10 -uroot -proot1234

Test and compare the performance of different storage engines at the same time:

# mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --iterations=5 --engine=myisam,innodb -uroot -proot1234

Execute a test, 50 and 100 concurrency respectively, execute 1000 total queries:

It is obvious that innodb performance query is better

# mysqlslap -a --concurrency=50,100 --number-of-queries 1000 -uroot -proot1234

50 and 100 concurrency will get one test result (Benchmark) respectively. The more concurrency, the longer it will take to execute all queries. For the sake of accuracy, you can iterate the test several times:

# mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --iterations=5 -uroot -proot1234

The above is the detailed content of Detailed introduction to mysql cluster stress testing. For more information, please follow other related articles on the PHP Chinese website!