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

Implementation of master-slave replication in docker compose deployment

Table of contents Configuration parsing Service C...

Analysis of MySQL's method of exporting to Excel

This article describes how to use MySQL to export...

When the interviewer asked the difference between char and varchar in mysql

Table of contents Difference between char and var...

How to query duplicate data in mysql table

INSERT INTO hk_test(username, passwd) VALUES (...

8 Reasons Why You Should Use Xfce Desktop Environment for Linux

For several reasons (including curiosity), I star...

Web Design Tips: Simple Rules for Page Layout

Repetition: Repeat certain page design styles thr...

Ubuntu 18.0.4 installs mysql and solves ERROR 1698 (28000): Access denied for user ''root''@''localhost''

Preface I recently learned Linux, and then change...

Pure CSS free implementation code for websites to have dark mode switching function

Preface The concept of dark mode originated from ...

Detailed explanation of Vue's live broadcast function

Recently, the company happened to be doing live b...

Summary of JavaScript JSON.stringify() usage

Table of contents 1. Usage 1. Basic usage 2. The ...

How to Clear Disk Space on CentOS 6 or CentOS 7

Following are the quick commands to clear disk sp...

Solution to the ineffective margin of div nested in HTML

Here's a solution to the problem where margin...

A brief introduction to JavaScript arrays

Table of contents Introduction to Arrays Array li...

Detailed explanation of the principle and function of JavaScript closure

Table of contents Introduction Uses of closures C...

Code comment writing standards during web page production

<br />I have summarized the annotation writi...