Introduction to the use of MySQL official performance testing tool mysqlslap

Introduction
Instructions
Actual experience
summary

Introduction

As the most popular open source database, MySQL is widely used in various fields. As a MySQL DBA, you often perform some performance tests on the database to actively (or passively) evaluate the business pressure and determine the current database load and the highest performance capacity.

Common performance testing tools include sysbench and tpcc. Both are excellent stress testing tools, but they require special compilation or installation, and certain development capabilities to modify specific test statements.

mysqlslap is automatically installed when MySQL is installed, and mysqlslap encapsulates many custom test functions externally. Users only need to provide SQL statement scripts externally to customize test statements, which makes it easier to use.

Instructions

mysqlslap provides a lot of parameters to configure the type of test items. Here we only select some common parameters for explanation. For detailed information, please refer to the help information of mysqlslap itself.

Parameter name	illustrate
login-path=#	Login methods provided by the new version of MySQL
-a, --auto-generate-sql	Automatically generate SQL statements
--auto-generate-sql-add-autoincrement	Add an auto-increment column to the automatically generated table
--auto-generate-sql-execute-number=#	The total number of SQL statements executed during the test
--auto-generate-sql-guid-primary	Generate GUID-based primary keys
--auto-generate-sql-load-type=name	The load model of the test includes mixed, update, write, key, and read. The default is mixed.
--auto-generate-sql-secondary-indexes=#	The number of secondary indexes in automatically generated tables
--auto-generate-sql-unique-query-number=#	The number of queries using unique indexes in the test
--auto-generate-sql-unique-write-number=#	The number of DML statements using unique indexes during the test
--auto-generate-sql-write-number=#	The number of insert statements executed by each thread during the test is 100 by default.
--commit=#	During the test, commit is executed every few statements
-c, --concurrency=name	The number of concurrent threads/clients in the test
--create=name	Custom table creation statement, or the address of the SQL file
--create-schema=name	The database name used in the test
--detach=#	During the test, reconnect after executing a certain number of statements
-e, --engine=name	Specify the storage engine when creating a table
-h, --host=name	Specify the host address of the test instance
-u, --user=name	Specify the user name for the test instance
-p, --password=name	Specify the password for the test instance
-P, --port=#	Specify the port of the test instance
-i, --iterations=#	Specify the number of times to repeat the test
--no-drop	After the test is completed, the library table used for the test will not be deleted.
-x, --number-char-cols=name	Specifies the number of varchar columns in the test table
-y, --number-int-cols=name	Specifies the number of int columns in the test table
--number-of-queries=#	Specifies the upper limit on the number of SQL statements executed by each thread (inexact)
--only-print	Similar to dry run, the output will be performed, but it will not be actually executed
-F, --delimiter=name	When using SQL statements provided in a file, explicitly specify the delimiter between statements
--post-query=name	Specify the query statement to be executed after the test is completed, or the file of SQL statements
--pre-query=name	Specify the query statement to be executed before the test starts, or the file of SQL statements
-q, --query=name	Specifies the query statement to be executed during the test, or the file of SQL statements

Actual experience

Here is a simple trial of mysqlslap. The target instance uses Tencent Cloud Database MySQL. The simplest test example is as follows:

root@VM-64-10-debian:~# mysqlslap --concurrency=100 --iterations 10 -a --auto-generate-sql-add-autoincrement --engine=innodb --number-of-queries=1000 -h172.1.100.10 -uroot -p
Enter password:
Benchmark
	Running for engine innodb
	Average number of seconds to run all queries: 0.046 seconds
	Minimum number of seconds to run all queries: 0.042 seconds
	Maximum number of seconds to run all queries: 0.049 seconds
	Number of clients running queries: 100
	Average number of queries per client: 10

root@VM-64-10-debian:~#

This example uses the simplest test model, in which mysqlslap generates all test statements and table creation statements by itself.

If you want to compare the performance under different concurrency conditions, you can test it in this way:

root@VM-64-10-debian:~# mysqlslap --concurrency=100,200 --iterations 10 -a --auto-generate-sql-add-autoincrement --engine=innodb --number-of-queries=1000 -h172.16.0.40 -uroot -p
Enter password:
Benchmark
	Running for engine innodb
	Average number of seconds to run all queries: 0.046 seconds
	Minimum number of seconds to run all queries: 0.045 seconds
	Maximum number of seconds to run all queries: 0.048 seconds
	Number of clients running queries: 100
	Average number of queries per client: 10

Benchmark
	Running for engine innodb
	Average number of seconds to run all queries: 0.037 seconds
	Minimum number of seconds to run all queries: 0.034 seconds
	Maximum number of seconds to run all queries: 0.052 seconds
	Number of clients running queries: 200
	Average number of queries per client: 5

root@VM-64-10-debian:~#

The output result is time, so the smaller the time, the better the overall performance.

If you want to customize the test statement, you can write the SQL statement in the file, for example:

root@VM-64-10-debian:~# cat create.sql
CREATE TABLE a (b int); INSERT INTO a VALUES (23);
root@VM-64-10-debian:~# cat query.sql
SELECT * FROM a;
SELECT b FROM a;
root@VM-64-10-debian:~#
root@VM-64-10-debian:~# mysqlslap --concurrency=100,200 --query=query.sql --create=create.sql --delimiter=";" --engine=innodb --number-of-queries=1000 -h172.16.0.40 -uroot -p
Enter password:
Benchmark
	Running for engine innodb
	Average number of seconds to run all queries: 0.045 seconds
	Minimum number of seconds to run all queries: 0.045 seconds
	Maximum number of seconds to run all queries: 0.045 seconds
	Number of clients running queries: 100
	Average number of queries per client: 10

Benchmark
	Running for engine innodb
	Average number of seconds to run all queries: 0.023 seconds
	Minimum number of seconds to run all queries: 0.023 seconds
	Maximum number of seconds to run all queries: 0.023 seconds
	Number of clients running queries: 200
	Average number of queries per client: 5

root@VM-64-10-debian:~#

summary

There are many MySQL stress testing tools available on the market, and each tool has its own advantages and disadvantages. By learning more about the tools and then choosing the most suitable tool according to actual needs, you can complete the required stress testing in the most efficient way.

The above is the detailed introduction to the use of MySQL's official performance testing tool mysqlslap. For more information on the use of mysqlslap, please pay attention to other related articles on 123WORDPRESS.COM!

You may also be interested in:

Use of MySQL stress testing tool Mysqlslap
Detailed explanation of the mysqlslap command and syntax for the built-in stress test in MySQL 5.7
Introduction and use of MySQL stress testing tool mysqlslap
MySQL stress testing method How to use mysqlslap to test MySQL stress?
Mysqlslap MySQL stress testing tool simple tutorial

<<: How to successfully retrieve VMware Esxi root password after forgetting it

>>: 3 ways to create JavaScript objects

How to deeply understand React's ref attribute

Introduction to the use of MySQL official performance testing tool mysqlslap

Table of contents

Introduction

Instructions

Actual experience

summary

How to deeply understand React's ref attribute

How to run a project with docker

Summary of several common logs in MySQL

Two tools for splitting the screen in the Linux command line terminal

Detailed tutorial for installing MySQL 8.0.22 on Redhat 7.3 (binary installation)

Detailed explanation on reasonable settings of MySQL sql_mode

HTML+CSS to achieve layered pyramid example

docker-maven-plugin packages the image and uploads it to a private warehouse

Detailed explanation and summary of the URL for database connection

Detailed explanation of Nginx status monitoring and log analysis

Recommend

jQuery realizes dynamic particle effect

Vue-router example code for dynamically generating navigation menus based on backend permissions

How to insert weather forecast into your website

Solution for Vue routing this.route.push jump page not refreshing

js to implement collision detection

How to clean up data in MySQL online database

mysql implements adding time automatically adding and updating time automatically updating operation

Using Docker Enterprise Edition to build your own private registry server

Nginx implements https website configuration code example

Why does your height:100% not work?

MySQL Server 8.0.13.0 Installation Tutorial with Pictures and Text

JS version of the picture magnifying glass effect

Summary of the use of vue Watch and Computed

The difference between VOLUME and docker -v in Dockerfile

Java example code to generate random characters