Summary of Mysql common benchmark commands

mysqlslap

Common parameter description

–auto-generate-sql The system automatically generates SQL scripts for testing
–auto-generate-sql-add-autoincrement Add auto-increment ID to the generated table
–auto-generate-sql-load-type Specify the query type used in the test
–auto-generate-sql-write-number specifies the amount of data generated when initializing data
–concurrency specifies the number of concurrent threads
–engine specifies the storage engine of the table to be tested, separate multiple with commas
–no-drop Do not clean up test data (usually not set)
–iterations specifies the number of times the test is run
--number-of-queries specifies the number of queries to execute per thread
--debug-info specifies to output additional memory and cpu statistics
--number-int-cols Specifies the number of int columns to be included in the test table
–number-char-cols specifies the number of varchar types contained in the test table
--create-schema specifies the name of the database to be used for executing the test
–query is used to specify a custom sql script
--only-print does not run the test script, but prints the generated script

mysqlslap --concurrency=1,50,100,200 --iterations=3 --number-int-cols=5 --number-char-cols=5 --auto-generate-sql --auto-generate-sql-add-autoincrement --engine=myisam,innodb --number-of-queries=10 --create-schema=sbtest

sysbench

Installation Instructions

# Download https://gihub.com/akopytov/sysbench/archive/0.5.zip# Unzip sysbench-0.5zip
cd sysbench
./autogen.sh# Configure your own mysql include and lib directories./configure --with-mysql-includes=/usr/local/mysql/include/ --with-mysql-libs=/usr/local/mysql/lib/
make && make install

Common parameters

–test is used to specify the type of test to be performed. The following parameters are supported:

Fileio file system I/O performance test

cpu cpu performance test

memory memory performance test

–mysql-db is used to specify the database name for executing the benchmark
–mysql-table-engine is used to specify the storage engine to be used
--oltp-tables-count Number of tables to perform the test on
–oltp-table-size specifies the number of rows in each table
–num-threads specifies the number of concurrent threads to test
–max-time specifies the maximum test time in seconds
–report-interval specifies the interval at which statistics are output
–mysql-user specifies the mysql user to perform the test
--mysql-password password

prepare is used to prepare test data

Run the test

cleanup clears test data

# Test the CPUsysbench --test=cpu --cpu-max-prime=10000 run# Disk I/O testFirst prepare the data to be larger than the memorysysbench --test=Fileio --file-total-size=1Gpreparesysbench --test=Fileio --num--threads=8 --init-rgn=on -file-total-size=1G --file-test-mode=rndrw --report-interval=1# Database performance testSet up the user and password data for the benchmark test, then enter the test directory of sysbench and use the lua script to testsysbench --test=./oltp.lua --mysql-table-engine=innodb --oltp-table-size=10000 --mysql-db=db_name --mysql-user=user_name --mysql-password=password --oltp-tables-count=10 --mysql-socket=/usr/local/mysql/data/mysql.sock prepare# Start the test and change the prepare in the above command to run