Introduction to the use of MySQL official performance testing tool mysqlslap

Introduction to the use of MySQL official performance testing tool mysqlslap

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

Recommend

foreman ubuntu16 quick installation

Quickstart Guide The Foreman installer is a colle...

How to use xshell to connect to Linux in VMware (2 methods)

【Foreword】 Recently I want to stress test ITOO...

MySQL implements a function similar to connect_by_isleaf MySQL method or stored procedure

Recently, there is a particularly abnormal busine...

A comprehensive analysis of what Nginx can do

Preface This article only focuses on what Nginx c...

Solve the problem of installing Theano on Ubuntu 19

Solution: Directly in the directory where you dow...

How to use docker to deploy Django technology stack project

With the popularity and maturity of Docker, it ha...

PHP scheduled backup MySQL and mysqldump syntax parameters detailed

First, let's introduce several common operati...

Pull-down refresh and pull-up loading components based on Vue encapsulation

Based on Vue and native javascript encapsulation,...

Detailed explanation of the difference between tinyint and int in MySQL

Question: What is the difference between int(1) a...

How to check whether the graphics driver has been successfully installed in Ubuntu

Run the command: glxinfo | grep rendering If the ...

Detailed example of using useState in react

useState useState adds some internal state to a c...

How to use geoip to restrict regions in nginx

This blog is a work note environment: nginx versi...

CSS3 border effects

What is CSS# CSS (abbreviation of Cascading Style...

Axios cancels repeated requests

Table of contents Preface 1. How to cancel a requ...

mysql5.6.zip format compressed version installation graphic tutorial

Preface: MySQL is a relational database managemen...