MySQL性能测试工具 mysqlslap

先看参数介绍

Format	Option File	Description	Introduced
--auto-generate-sql	auto-generate-sql	Generate SQL statements automatically when they are not supplied in files or using command options
--auto-generate-sql-add-autoincrement	auto-generate-sql-add-autoincrement	Add AUTO_INCREMENT column to automatically generated tables
--auto-generate-sql-execute-number=#	auto-generate-sql-execute-number	Specify how many queries to generate automatically
--auto-generate-sql-guid-primary	auto-generate-sql-guid-primary	Add a GUID-based primary key to automatically generated tables
--auto-generate-sql-load-type=type	auto-generate-sql-load-type	Specify how many queries to generate automatically
--auto-generate-sql-secondary-indexes=#	auto-generate-sql-secondary-indexes	Specify how many secondary indexes to add to automatically generated tables
--auto-generate-sql-unique-query-number=#	auto-generate-sql-unique-query-number	How many different queries to generate for automatic tests.
--auto-generate-sql-unique-write-number=#	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=#	commit	How many statements to execute before committing.
--compress	compress	Compress all information sent between the client and the server
--concurrency=#	concurrency	The number of clients to simulate when issuing the SELECT statement
--create=value	create	The file or string containing the statement to use for creating the table
--create-and-drop-schema=value	create-and-drop-schema	The schema in which to run the tests; dropped at the end of the test run	5.5.12
--create-schema=value	create-schema	The schema in which to run the tests
--csv=[file]	csv	Generate output in comma-separated values format
--debug[=debug_options]	debug	Write a debugging log
--debug-check	debug-check	Print debugging information when the program exits
--debug-info	debug-info	Print debugging information, memory and CPU statistics when the program exits
--default-auth=plugin	default-auth=plugin	The authentication plugin to use	5.5.10
--delimiter=str	delimiter	The delimiter to use in SQL statements
--detach=#	detach	Detach (close and reopen) each connection after each N statements
--engine=engine_name	engine	The storage engine to use for creating the table
--help		Display help message and exit
--host=host_name	host	Connect to the MySQL server on the given host
--iterations=#	iterations	The number of times to run the tests
--number-char-cols=#	number-char-cols	The number of VARCHAR columns to use if --auto-generate-sql is specified
--number-int-cols=#	number-int-cols	The number of INT columns to use if --auto-generate-sql is specified
--number-of-queries=#	number-of-queries	Limit each client to approximately this number of queries
--only-print	only-print	Do not connect to databases. mysqlslap only prints what it would have done
--password[=password]	password	The password to use when connecting to the server
--pipe		On Windows, connect to server using a named pipe
--plugin-dir=path	plugin-dir=path	The directory where plugins are located	5.5.10
--port=port_num	port	The TCP/IP port number to use for the connection
--post-query=value	post-query	The file or string containing the statement to execute after the tests have completed
--post-system=str	post-system	The string to execute using system() after the tests have completed
--pre-query=value	pre-query	The file or string containing the statement to execute before running the tests
--pre-system=str	pre-system	The string to execute using system() before running the tests
--protocol=type	protocol	The connection protocol to use
--query=value	query	The file or string containing the SELECT statement to use for retrieving data
--silent	silent	Silent mode
--socket=path	socket	For connections to localhost
--ssl-ca=file_name	ssl-ca	The path to a file that contains a list of trusted SSL CAs
--ssl-capath=dir_name	ssl-capath	The path to a directory that contains trusted SSL CA certificates in PEM format
--ssl-cert=file_name	ssl-cert	The name of the SSL certificate file to use for establishing a secure connection
--ssl-cipher=cipher_list	ssl-cipher	A list of allowable ciphers to use for SSL encryption
--ssl-key=file_name	ssl-key	The name of the SSL key file to use for establishing a secure connection
--ssl-verify-server-cert	ssl-verify-server-cert	The server's Common Name value in its certificate is verified against the host name used when connecting to the server
--user=user_name,	user	MySQL user name to use when connecting to server
--verbose		Verbose mode
--version		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