Mysql common benchmark testing tools

mysqlslap

Common parameter description

–auto-generate-sql The sql script is automatically generated by the system for testing
–auto-generate-sql-add-autoincrement Add auto-increment ID to the generated table
–auto-generate-sql-load-type specifies 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, commas separate multiple
–no-drop does not clean test data (generally not set)
–iterations specifies the number of test runs
–number-of-queries specifies the number of queries executed by each thread
–debug-info specifies the output of additional memory and cpu statistics
–number-int-cols specifies the number of int type columns contained in the test table
–number-char-cols specifies the number of varchar types contained in the test table
–create-schema specifies the database name used to execute tests
–query script used to specify custom sql
–only-print does not run the test script, but prints out the production 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

# 下载https://gihub.com/akopytov/sysbench/archive/0.5.zip# 解压unzip sysbench-0.5zip
cd sysbench
./autogen.sh# 配置自己的mysql include 和 lib目录./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 test type to be executed and supports the following parameters

• Fileio file system I/O performance test

• cpu cpu performance test

• memory memory performance test

–mysql-db is used to specify the database to execute the benchmark test name
–mysql-table-engine is used to specify the storage engine used
–oltp-tables-count Number of tables to execute the test
–oltp-table-size specifies the number of data rows in each table
–num-threads specifies the number of concurrent threads for testing
–max-time specifies the maximum test time s
–report-interval specifies how often to output statistical information
–mysql-user specifies the mysql user to execute the test
–mysql-password password
prepare is used to prepare test data
run run test
cleanup Clear test data

# 对cpu进行测试sysbench --test=cpu --cpu-max-prime=10000 run# 磁盘I/O测试 首先准备数据要大于内存sysbench --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# 数据库性能测试 建立基准测试的用户密码数据,然后进入sysbench的test目录下，利用lua脚本测试sysbench --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# 开始测试，将上述命令的prepare改为run

mysqlslap

Common parameter description

–auto-generate-sql Automatically generate sql by the system Script for testing
–auto-generate-sql-add-autoincrement Add auto-increment ID to the generated table
–auto-generate-sql-load-type specifies 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, commas separate multiple
–no-drop does not clean test data (generally not set)
–iterations specifies the number of test runs
–number-of-queries specifies the number of queries executed by each thread
–debug-info specifies the output of additional memory and cpu statistics
–number-int-cols specifies the number of int type columns contained in the test table
–number-char-cols specifies the number of varchar types contained in the test table
–create-schema specifies the database name used to execute tests
–query script used to specify custom sql
–only-print does not run the test script, but prints out the production 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

# 下载https://gihub.com/akopytov/sysbench/archive/0.5.zip# 解压unzip sysbench-0.5zip
cd sysbench
./autogen.sh# 配置自己的mysql include 和 lib目录./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 test type to be executed and supports the following parameters

• Fileio file system I/O performance test

• cpu cpu performance test

• memory memory performance test

–mysql-db 用于指定执行基准测试的数据库名
 –mysql-table-engine 用于指定所使用的存储引擎
 –oltp-tables-count 执行测试的表数量
 –oltp-table-size 指定每个表中的数据行数
 –num-threads 制定测试的并发线程数量
 –max-time 指定测试最大时间 s
 –report-interval 指定间隔多长时间输出一次统计信息
 –mysql-user 指定执行测试的mysql用户
 –mysql-password 密码
 prepare 用于准备测试数据
 run 运行测试
 cleanup 清除测试数据

# 对cpu进行测试sysbench --test=cpu --cpu-max-prime=10000 run# 磁盘I/O测试 首先准备数据要大于内存sysbench --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# 数据库性能测试 建立基准测试的用户密码数据,然后进入sysbench的test目录下，利用lua脚本测试sysbench --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# 开始测试，将上述命令的prepare改为run

本文讲解了Mysql常用基准测试工具的相关内容，更多香瓜知识请关注php中文网。

相关推荐：

Mysql函数 的相关讲解

SQLLite相关内容

Spark SQL实现日志离线批处理

The above is the detailed content of Mysql common benchmark testing tools. For more information, please follow other related articles on the PHP Chinese website!