sysbench - 数据库基准测试工具

——————————————————————————————————————————————
WARN：通篇皆为MacOS操作，WindowsOS/LinuxOS等需自行变更为同等操作。
——————————————————————————————————————————————

安装

# 安装sysbench
brew install sysbench -g

支持的测试模式

1、CPU运算性能
2、磁盘IO性能
3、调度程序性能
4、内存分配及传输速度
5、POSIX线程性能
6、数据库性能(OLTP基准测试)
目前sysbench主要支持 mysql,drizzle,pgsql,oracle 等几种数据库

测试数据库(OLTP基准测试)

# 准备数据库
mysqladmin create test

# 准备测试数据
# 准备十万条测试数据的量
sysbench --test=/usr/local/Cellar/sysbench/1.0.17/share/sysbench/oltp_common.lua --table-size=100000 --mysql-host=k8s-n1 --mysql-port=3307 --mysql-user=root --mysql-password=123456 --mysql-db=test prepare

# 进行插入性能测试
# 插入10万条数据，8个线程执行，每10秒中报告一次 
sysbench --test=/usr/local/Cellar/sysbench/1.0.17/share/sysbench/oltp_insert.lua --table-size=100000 --mysql-host=k8s-n1 --mysql-port=3307 --mysql-user=root --mysql-password=123456 --mysql-db=test --threads=8 --max-requests=0 --report-interval=10  --time=300 run

# 结果解读
Running the test with following options:
Number of threads: 8
Report intermediate results every 10 second(s)
Random number generator seed is 0 and will be ignored


Threads started!
-- 每10秒钟报告一次测试结果，tps、每秒读、每秒写、99%以上的响应时长统计
[  10s] threads: 8, tps: 1111.51, reads/s: 15568.42, writes/s: 4446.13, response time: 9.95ms (99%)
[  20s] threads: 8, tps: 1121.90, reads/s: 15709.62, writes/s: 4487.80, response time: 9.78ms (99%)
[  30s] threads: 8, tps: 1120.00, reads/s: 15679.10, writes/s: 4480.20, response time: 9.84ms (99%)
[  40s] threads: 8, tps: 1114.20, reads/s: 15599.39, writes/s: 4456.30, response time: 9.90ms (99%)
[  50s] threads: 8, tps: 1114.00, reads/s: 15593.60, writes/s: 4456.70, response time: 9.84ms (99%)
[  60s] threads: 8, tps: 1119.30, reads/s: 15671.60, writes/s: 4476.50, response time: 9.99ms (99%)
OLTP test statistics:
    queries performed:
        read:                            938224    -- 读总数
        write:                           268064    -- 写总数
        other:                           134032    -- 其他操作总数(SELECT、INSERT、UPDATE、DELETE之外的操作，例如COMMIT等)
        total:                           1340320    -- 全部总数
    transactions:                        67016  (1116.83 per sec.)    -- 总事务数(每秒事务数)
    deadlocks:                           0      (0.00 per sec.)    -- 发生死锁总数
    read/write requests:                 1206288 (20103.01 per sec.)    -- 读写总数(每秒读写次数)
    other operations:                    134032 (2233.67 per sec.)    -- 其他操作总数(每秒其他操作次数)

General statistics:    -- 一些统计结果
    total time:                          60.0053s    -- 总耗时
    total number of events:              67016    -- 共发生多少事务数
    total time taken by event execution: 479.8171s    -- 所有事务耗时相加(不考虑并行因素)
    response time:    -- 响应时长统计
         min:                                  4.27ms    -- 最小耗时
         avg:                                  7.16ms    -- 平均耗时
         max:                                 13.80ms    -- 最长耗时
         approx.  99 percentile:               9.88ms    -- 超过99%平均耗时

Threads fairness:
    events (avg/stddev):           8377.0000/44.33
    execution time (avg/stddev):   59.9771/0.00

测试CPU

# cpu运算性能测试
sysbench cpu --cpu-max-prime=10000 run

测试IO

# 新建10g的测试文件
sysbench --test=fileio --file-total-size=10G prepare

# 随机读写300s
sysbench --test=fileio --file-total-size=10G --file-test-mode=rndrw --time=300 --max-requests=0 run

# 删除测试文件
sysbench --test=fileio --file-total-size=10G cleanup

# 查看脚本的参数和语法
sysbench --test=/usr/local/Cellar/sysbench/1.0.17/share/sysbench/oltp_common.lua help