mysqlslap工具压测mysql

介绍：

mysqlslap是mysql自带的一个性能压测工具，而mysqlslap只需要使用命令就能触发大并发，模拟负载，压测mysql,官方说明https://dev.mysql.com/doc/refman/8.0/en/mysqlslap.html

官方说明：mysqlslap是一个诊断程序，旨在模拟 MySQL 服务器的客户端负载并报告每个阶段的时间。它就像多个客户端正在访问服务器一样工作。

 

 

Option Name	Description	Introduced	Deprecated
--auto-generate-sql	Generate SQL statements automatically when they are not supplied in files or using command options 未在文件中提供或使用命令选项时自动生成 SQL 语句
--auto-generate-sql-add-autoincrement	Add AUTO_INCREMENT column to automatically generated tables 将 AUTO_INCREMENT 列添加到自动生成的表
--auto-generate-sql-execute-number	Specify how many queries to generate automatically 指定要自动生成的查询数量
--auto-generate-sql-guid-primary	Add a GUID-based primary key to automatically generated tables 为自动生成的表添加基于 GUID 的主键
--auto-generate-sql-load-type	Specify the test load type 指定测试负载类型
--auto-generate-sql-secondary-indexes	Specify how many secondary indexes to add to automatically generated tables 指定要添加到自动生成的表中的二级索引的数量
--auto-generate-sql-unique-query-number	How many different queries to generate for automatic tests 为自动测试生成多少个不同的查询
--auto-generate-sql-unique-write-number	How many different queries to generate for --auto-generate-sql-write-number 为 --auto-generate-sql-write-number 生成多少个不同的查询
--auto-generate-sql-write-number	How many row inserts to perform on each thread 在每个线程上执行多少行插入
--commit	How many statements to execute before committing 提交前要执行多少条语句
--compress	Compress all information sent between client and server 压缩客户端和服务器之间发送的所有信息		8.0.18
--compression-algorithms	Permitted compression algorithms for connections to server	8.0.18
--concurrency	Number of clients to simulate when issuing the SELECT statement
--create	File or string containing the statement to use for creating the table
--create-schema	Schema in which to run the tests
--csv	Generate output in comma-separated values format
--debug	Write debugging log
--debug-check	Print debugging information when program exits
--debug-info	Print debugging information, memory, and CPU statistics when program exits
--default-auth	Authentication plugin to use
--defaults-extra-file	Read named option file in addition to usual option files
--defaults-file	Read only named option file
--defaults-group-suffix	Option group suffix value
--delimiter	Delimiter to use in SQL statements
--detach	Detach (close and reopen) each connection after each N statements
--enable-cleartext-plugin	Enable cleartext authentication plugin
--engine	Storage engine to use for creating the table
--get-server-public-key	Request RSA public key from server
--help	Display help message and exit
--host	Host on which MySQL server is located
--iterations	Number of times to run the tests
--login-path	Read login path options from .mylogin.cnf
--no-defaults	Read no option files
--no-drop	Do not drop any schema created during the test run
--number-char-cols	Number of VARCHAR columns to use if --auto-generate-sql is specified
--number-int-cols	Number of INT columns to use if --auto-generate-sql is specified
--number-of-queries	Limit each client to approximately this number of queries
--only-print	Do not connect to databases. mysqlslap only prints what it would have done
--password	Password to use when connecting to server
--password1	First multifactor authentication password to use when connecting to server	8.0.27
--password2	Second multifactor authentication password to use when connecting to server	8.0.27
--password3	Third multifactor authentication password to use when connecting to server	8.0.27
--pipe	Connect to server using named pipe (Windows only)
--plugin-dir	Directory where plugins are installed
--port	TCP/IP port number for connection
--post-query	File or string containing the statement to execute after the tests have completed
--post-system	String to execute using system() after the tests have completed
--pre-query	File or string containing the statement to execute before running the tests
--pre-system	String to execute using system() before running the tests
--print-defaults	Print default options
--protocol	Transport protocol to use
--query	File or string containing the SELECT statement to use for retrieving data
--server-public-key-path	Path name to file containing RSA public key
--shared-memory-base-name	Shared-memory name for shared-memory connections (Windows only)
--silent	Silent mode
--socket	Unix socket file or Windows named pipe to use
--sql-mode	Set SQL mode for client session
--ssl-ca	File that contains list of trusted SSL Certificate Authorities
--ssl-capath	Directory that contains trusted SSL Certificate Authority certificate files
--ssl-cert	File that contains X.509 certificate
--ssl-cipher	Permissible ciphers for connection encryption
--ssl-crl	File that contains certificate revocation lists
--ssl-crlpath	Directory that contains certificate revocation-list files
--ssl-fips-mode	Whether to enable FIPS mode on client side
--ssl-key	File that contains X.509 key
--ssl-mode	Desired security state of connection to server
--tls-ciphersuites	Permissible TLSv1.3 ciphersuites for encrypted connections	8.0.16
--tls-version	Permissible TLS protocols for encrypted connections 加密连接允许协议
--user	MySQL user name to use when connecting to server 连接到服务器时使用的 MySQL 用户名
--verbose	Verbose mode 详细模式
--version	Display version information and exit 显示版本信息并退出
--zstd-compression-level	Compression level for connections to server that use zstd compression 连接到使用 zstd 压缩的服务器的压缩级别	8.0.18

 

 2.使用方式:

    使用sql文件方式读取　

mysqlslap -h172.XX.XX.XX  -PXXXX -uroot  --concurrency=1 --iterations=1 --create-schema=XX --query=/home/zhangjun/test.sql --engine=innodb --number-of-queries=1 --debug-info

执行方式解释：连接的客户端   concurrency执行的客户端多少个   iterations迭代次数        --create-schema：使用的数据库   --query可以使用写好的sql,也可以使用一个sql语句，如：--query='select * from test_zj;'  

执行效果：

使用读取文件的方式读取数据，

 

数据解释说明

以100个为例：

Benchmark
Running for engine innodb
Average number of seconds to run all queries: 0.351 seconds 　　　　 100个客户端（并发）同时运行这些SQL语句平均要花0.351秒
Minimum number of seconds to run all queries: 0.351 seconds
Maximum number of seconds to run all queries: 0.351 seconds
Number of clients running queries: 100 　　　　　　　　　　　　  总共100个客户端（并发）运行这些sql查询
Average number of queries per client:50 　　　　　　　　　　　　每个客户端（并发）平均运行50次查询（对应--concurrency=100，--number-of-queries=5000；5000/100=50）与迭代有关