您的位置: 首页> 数据库

MySQL性能测试工具 mysqlslap

匿名上传

发布时间:2025-08-27 17:54:02

先看参数介绍

Format	Option File	Description	Introduced
		Generate SQL statements automatically when they are not supplied in files or using command options
		Add AUTO_INCREMENT column to automatically generated tables
		Specify how many queries to generate automatically
		Add a GUID-based primary key to automatically generated tables
		Specify how many queries to generate automatically
		Specify how many secondary indexes to add to automatically generated tables
		How many different queries to generate for automatic tests.
		How many different queries to generate for --auto-generate-sql-write-number
		How many row inserts to perform on each thread
		How many statements to execute before committing.
		Compress all information sent between the client and the server
		The number of clients to simulate when issuing the SELECT statement
		The file or string containing the statement to use for creating the table
		The schema in which to run the tests; dropped at the end of the test run	5.5.12
		The schema in which to run the tests
		Generate output in comma-separated values format
		Write a debugging log
		Print debugging information when the program exits
		Print debugging information, memory and CPU statistics when the program exits
		The authentication plugin to use	5.5.10
		The delimiter to use in SQL statements
		Detach (close and reopen) each connection after each N statements
		The storage engine to use for creating the table
		Display help message and exit
		Connect to the MySQL server on the given host
		The number of times to run the tests
		The number of VARCHAR columns to use if --auto-generate-sql is specified
		The number of INT columns to use if --auto-generate-sql is specified
		Limit each client to approximately this number of queries
		Do not connect to databases. mysqlslap only prints what it would have done
		The password to use when connecting to the server
		On Windows, connect to server using a named pipe
		The directory where plugins are located	5.5.10
		The TCP/IP port number to use for the connection
		The file or string containing the statement to execute after the tests have completed
		The string to execute using system() after the tests have completed
		The file or string containing the statement to execute before running the tests
		The string to execute using system() before running the tests
		The connection protocol to use
		The file or string containing the SELECT statement to use for retrieving data
		Silent mode
		For connections to localhost
		The path to a file that contains a list of trusted SSL CAs
		The path to a directory that contains trusted SSL CA certificates in PEM format
		The name of the SSL certificate file to use for establishing a secure connection
		A list of allowable ciphers to use for SSL encryption
		The name of the SSL key file to use for establishing a secure connection
		The server's Common Name value in its certificate is verified against the host name used when connecting to the server
		MySQL user name to use when connecting to server
		Verbose mode
		Display version information and exit

1.使用自动生成脚本测试数据库性能

mysqlslap -uroot -p123 -a --debug-info --concurrency=50,100 --number-of-queries=200 --engine=myisam,innodb

参数介绍

-u : 用户名

-p : 密码

-a (--auto-generate-sql) : 自动生成sql脚本测试数据库

--debug-info : 输出debug信息,包括内存,CPU等

--concurrency : 用户并发数,可填写多个

--number-of-queries : 每个用户的查询次数

--engine : 需要测试的数据库引擎

输出结果:

Benchmark
Running for engine myisam
Average number of seconds to run all queries: 2.092 seconds
Minimum number of seconds to run all queries: 2.092 seconds
Maximum number of seconds to run all queries: 2.092 seconds
Number of clients running queries: 50
Average number of queries per client: 4

Benchmark
Running for engine myisam
Average number of seconds to run all queries: 3.107 seconds
Minimum number of seconds to run all queries: 3.107 seconds
Maximum number of seconds to run all queries: 3.107 seconds
Number of clients running queries: 100
Average number of queries per client: 2

Benchmark
Running for engine innodb
Average number of seconds to run all queries: 2.248 seconds
Minimum number of seconds to run all queries: 2.248 seconds
Maximum number of seconds to run all queries: 2.248 seconds
Number of clients running queries: 50
Average number of queries per client: 4

Benchmark
Running for engine innodb
Average number of seconds to run all queries: 3.514 seconds
Minimum number of seconds to run all queries: 3.514 seconds
Maximum number of seconds to run all queries: 3.514 seconds
Number of clients running queries: 100
Average number of queries per client: 2

2.使用自定义SQL语句测试指定数据库

mysqlslap -uroot -plewis0913 --create-schema="test" --query="select * from user;" --concurrency=50,100 --debug-info

参数介绍

--create-schema : 需要测试的数据库

--query : 需要测试的sql语句

输出结果:

Benchmark
Average number of seconds to run all queries: 2.029 seconds
Minimum number of seconds to run all queries: 2.029 seconds
Maximum number of seconds to run all queries: 2.029 seconds
Number of clients running queries: 50
Average number of queries per client: 1

Benchmark
Average number of seconds to run all queries: 2.544 seconds
Minimum number of seconds to run all queries: 2.544 seconds
Maximum number of seconds to run all queries: 2.544 seconds
Number of clients running queries: 100
Average number of queries per client: 1