Common tools for MySQL performance benchmarking and stress testing include: 1. sysbench, suitable for OLTP-type pressure testing, the process is to install, prepare data, perform pressure testing, clean up data, and pay attention to TPS, QPS, delay and other indicators; 2. mysqlslap, MySQL comes with tools, simulate multi-client concurrent queries, suitable for rapid testing of concurrency capabilities; 3. tpcc-mysql, based on the TPC-C model, close to real business, simulate orders, payment and other operations, and the key indicator is the tPM of NewOrder transactions; 4. Auxiliary tools such as SHOW STATUS, Prometheus Grafana, and slow query logs are used to analyze stress testing results. It is recommended to conduct tests in an isolated environment to avoid affecting production services.

MySQL's performance benchmarking and stress testing are important means to optimize database performance and evaluate system bottlenecks. If you want to know that MySQL can withstand it in high concurrency or large data volume scenarios, then you have to rely on these tools to "force it".

1. sysbench: one of the most commonly used pressure testing tools

sysbench is a lightweight but powerful multi-threaded performance testing tool that supports multiple test types such as CPU, memory, disk IO and database. For MySQL, it is particularly suitable for stress testing of the OLTP (online transaction processing) class.

The usage process is roughly as follows:

• Install sysbench (can be installed through package manager or source code)

• Prepare test data (such as creating test tables and filling data)

  

• Execute stress test commands, for example:

   sysbench --db-driver=mysql --mysql-host=127.0.0.1 --mysql-port=3306 \
  --mysql-user=root --mysql-password=yourpass --mysql-db=testdb \
  --tables=10 --table-size=100000 oltp_read_write run

• Check the output results and focus on indicators such as transactions per second (TPS), query per second (QPS), average delay and other indicators.

It is recommended that you run the prepare phase to generate test data before the formal pressure test, and then run cleanup to clean it up after the pressure test.

2. mysqlslap: a built-in stress testing expert

mysqlslap is a stress testing tool that comes with MySQL and does not require additional installation. It simulates multiple clients to execute SQL queries concurrently, and is suitable for quickly understanding the concurrency capabilities of current MySQL instances.

You can use it to test this way:

 mysqlslap --concurrency=50,100,200 --iterations=3 --number-int-cols=5 \
--number-char-cols=5 --auto-generate-sql --engine=innodb --verbose

This command will simulate 50, 100, 200 concurrent connections, run three times, automatically generate SQL and use the InnoDB engine.

The advantage of mysqlslap is that it is simple and easy to use, but its disadvantage is that it is not as flexible as sysbench, and is suitable for temporary testing or entry-level use.

3. tpcc-mysql: a test tool that is closer to real business

tpcc-mysql is an open source project based on the TPC-C benchmark test model. It simulates typical operations in the warehouse management system, including complex transactions such as order creation, payment, and delivery. Compared with sysbench, it is closer to the actual business logic.

The process of using tpcc-mysql is a little more complicated:

• Download and compile the tpcc-mysql source code
• Create test database and table structure
• Load the initial data (load phase)
• Start pressure testing, specify parameters such as the number of warehouses, number of concurrent threads, etc.

Sample command:

 tpcc_start -h127.0.0.1 -P3306 -uroot -p'yourpass' -dtestdb -w10 -c32 -r10 -l120

Where -w10 represents 10 repositories, -c32 represents 32 concurrent threads, -l120 represents 120 seconds of running.

The result of the tpcc output contains the number of completions per minute (tPM) of the NewOrder transaction, which is a key measure of performance.

4. Other auxiliary tools and precautions

In addition to the main tools mentioned above, there are some auxiliary means to help you better analyze stress test results:

• MySQL comes with SHOW STATUS : For example, SHOW STATUS LIKE 'Threads_connected'; or SHOW ENGINE INNODB STATUS; it can help you observe the database status in real time.
• Monitoring tools such as Prometheus Grafana : used to visualize the changing trends of indicators such as CPU, memory, IO, QPS, and slow query.
• Slow query log : Turning on slow query log during stress testing can help discover potential performance problems SQL.

In addition, be careful not to mix pressure testing environments with production environments to avoid affecting normal services. If you are just testing and tuning, you can quickly build an isolated environment with a virtual machine or Docker.

Basically that's it. Stress testing is not to "destroy" the database, but to discover problems in advance and verify the effectiveness of the architecture design. Only by choosing the right tools and understanding indicators can we truly exert their value.

The above is the detailed content of MySQL Performance Benchmarking and Stress Testing Tools. For more information, please follow other related articles on the PHP Chinese website!