先看参数介绍
| 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: 4Benchmark
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: 2Benchmark
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: 4Benchmark
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: 1Benchmark
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
