MySQL Performance Testing Tool mysqlslap

First look at the parameter Introduction

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 does 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 does 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 =	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 wowould 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. Use the automatically generated script to test the database performance

Mysqlslap-uroot-p123-A -- debug-info -- concurrency = 50,100 -- number-of-queries = 200 -- engine = MyISAM, InnoDB

Parameter Introduction

-U: User Name

-P: Password

-A (-- auto-generate-SQL): automatically generates an SQL script to test the database.

-- Debug-Info: outputs debug information, including memory and CPU.

-- Concurrency: number of concurrent users. You can enter multiple

-- Number-of-queries: number of queries per user

-- Engine: database engine to be tested

Output result:

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. Use custom SQL statements to test the specified database

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

Parameter Introduction

-- Create-schema: database to be tested

-- Query: SQL statement to be tested

Output result:

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