Mysqlmysqlslap stress test

Introduction to mysqlslap

Mysql stress testing tool

Option

Option	Shorthand	Description
-- Auto-generate-SQL	-	Automatically generate MySQL test statements
-- Auto-generate-SQL-add-autoincrement		Add the AUTO_INCREMENT Field
-- Auto-generate-SQL-execute-number = #		Number of queries
-- Auto-generate-SQL-guid-primary		Add GUID Field
-- Auto-generate-SQL-load-type = name		Mixed: mixed, update: update, write: write, key: read primary key, read: Query
-- Auto-generate-SQL-secondary-indexes = #		Set the number of index fields
-- Auto-generate-SQL-unique-query-number = #		Generate N Different query SQL statements
-- Auto-generate-SQL-unique-write-number = #		Generate N Different write SQL statements
-- Auto-generate-SQL-write-number = #		Number of write SQL statements per Thread
-- Commit = #		Set the number of SQL statements to be submitted once.
-- Compress	-C	Enable compression Protocol
-- Concurrency = #	-C	Number of clients (concurrency)
-- Create = name		Test SQL statement or file
-- Create-schema = name		Test data name
-- Csv = name		Produce CSV format data files
-- Debug	-#	Write debugging logs (windows unavailable) such as: mysqlslap-a-# "d: t: o, c:/debug.txt"
-- Debug-check		Print debugging information after testing (windows unavailable)
-- Debug-info	-T	Print Information about memory and CPU
-- Default-auth = plugin		Verify the plug-in
-- Delimiter = str	-F	The default statement Delimiter is;
-- Detach = #		Disconnect after executing N statements
-- Enable-cleartext-plugin		Enable the plaintext authentication plug-in
-- Engine = engine_name	-E	Test engines such as mysqlslap-e "myisam" or-e "myisam, innodb"
-- Help		Help Information
-- Host = host_name	-H	Database host
-- Iterations = #	-I	Iterations
-- Login-path = name		For details about the login configuration file, see
-- No-drop		Do not delete the schema after the test.
-- Number-char-cols = #	-X	Set the number of VARCHAR Fields
-- Number-int-cols = #	-Y	Set the number of INT Fields
-- Number-of-queries = #		The number of SQL statements run on each client is
-- Only-print		Only the SQL statement -- delimiter option that will be run will be displayed
-- Password = password	-P	Database Password
-- Pipe		Enable MPs queue
-- Plugin-dir = path		Plug-in Directory
-- Port = port_num	-P	Database Port
-- Post-query = value		SQL statement executed after the test
-- Post-system = str		System statement executed after test
-- Pre-query = value		SQL statement executed before test
-- Pre-system = str		System statement executed before test
-- Protocol = type		Tcp, socket, and pipe
-- Query = value	-Q	The SQL statement used for testing is mysqlslap-q "SELECT * FROM t1"
-- Secure-auth		Do not send a password to the server
-- Silent	-S	Do not show test (mute test)
-- Socket = path	-S	Socket used to connect to the server
-- Ssl-ca = file_name
-- Ssl-capath = dir_name
-- Ssl-cert = file_name
-- Ssl-cipher = cipher_list
-- Ssl-crl = file_name
-- Ssl-crlpath = dir_name
-- Ssl-key = file_name
-- Ssl-verify-server-cert
-- User = user_name	-U	Database username
-- Verbose	-V	Output more information
-- Version	-V	Version Information

Example

For remote testing, for example, mysqlslap-a-h "192.168.1.139"-P3306-p specifies an SQL statement, for example, mysqlslap -- create-schema = test-q "select * from user". For example: mysqlslap-a: displays the test SQL statement, for example, mysqlslap-a -- only-print.

Reports

We test the performance of the local database engine.
Mysqlslap-a-e "myisam, innodb" -- csv = "c:/a.csv"

C:/a.csv File

Convert to chart form
We can see that myisam and innodb have better performance and less read/write time in hybrid testing.

Common options: Windows Default Configuration File Read sequence:
C: \ WINDOWS \ my. ini
C: \ WINDOWS \ my. cnf
C: \ my. ini
C: \ my. cnf
F: \ mysql \ 5.6.16 \ my. ini
F: \ mysql \ 5.6.16 \ my. cnf


-H Database host
-U database username
-P Database Password
-P database Port


-- Create-schema = name: Data name of the test
-- Create = name test SQL statement or file
-- No-drop: Do not delete the schema after the test.
-- Only-print: only displays the SQL statements to be run.


-Number of c clients (concurrency), for example, mysqlslap-c "10" or-c "10,100,100 0"
-E test engine: mysqlslap-e "myisam" or-e "myisam, innodb"
-- Number-of-queries = # Total number of SQL statement executions, for example, mysqlslap-a -- number-of-queries = "10"
-Q: the SQL statement used for testing is mysqlslap-q "SELECT * FROM t1"
-I: number of iterations, for example, mysqlslap-a-I "3"
-T displays the memory and CPU information and exits, for example, mysqlslap-a-T.
-F SQL statement Separator
-S: No output and no echo operation


-- Csv [= name]: produce CSV format data files
-- Commit = # set the number of SQL statements to be submitted once.
-- Detach = # set the number of SQL statements to be reconnected to the server once.

-A, -- auto-generate-SQL automatically generates MySQL test statements
-- Auto-generate-SQL-add-autoincrement add the AUTO_INCREMENT Field
-- Auto-generate-SQL-execute-number = # number of queries
-- Auto-generate-SQL-guid-primary Add the GUID Field
-- Auto-generate-SQL-load-type = name mixed: hybrid, update: update, write: write, key: read primary key, read: Query
-- Auto-generate-SQL-secondary-indexes = # set the number of index fields
-- Auto-generate-SQL-unique-query-number = # generate N Different query SQL statements
-- Auto-generate-SQL-unique-write-number = # generate N Different write SQL statements
-- Auto-generate-SQL-write-number = # number of write SQL statements in each thread
-X, -- number-char-cols = name: the default number of VARCHAR fields is 1.
-Y, -- number-int-cols = name: the default number of fields of the INT type is 1.