使用sysbench对mysql进行基准测试

测试环境说明

硬件

• 腾讯云标准型SA2
  • 1核 2GB 1Mbps
  • 系统盘：高性能云硬盘(50G)

软件

• centos 7.5 64位
• mysql 5.7.31
• sysbench 1.0.20

测试过程

使用sysbench。

1. 测试innodb 100w条记录的读写性能

1.1 生成100w数据

此处偷懒了，直接使用了sysbench默认的脚本创建数据库表，如果需要测试特定的表，需要修改/usr/share/sysbench下的脚本，基本只需要修改oltp_common.lua脚本即可，这个需要花时间细致的修改，这次就不在这方面多花时间了。本次主要目的是做基准测试、看看Mysql性能。

sysbench --db-driver=mysql --mysql-host=localhost --mysql-port=3306 --mysql-user=root --mysql-password=1qaz@WSX --mysql-db=sbtest --table_size=1000000 --tables=10 --events=0 --time=300  --threads=20  oltp_read_write prepare

1.2 进行读写测试

1.2.1 第一次直接执行100w次读写

执行命令：

sysbench --db-driver=mysql --mysql-host=localhost --mysql-port=3306 --mysql-user=root --mysql-password=1qaz@WSX --mysql-db=sbtest --table_size=1000000 --tables=10 --events=0 --time=300  --threads=20 --percentile=95 --report-interval=10 oltp_read_write run

参数说明：

• 指定测试mysql，默认存储引擎是innodb，指定mysql地址、端口、用户等信息
• 选取测试的数据库为sbtest，每个表的大小为100w条数据，创建10张表
• 测试时间300s, 20个线程同时测试
• 输出测试结果95分位
• 每隔10s输出一次当前测试情况
• 选取sysbench中的oltp_read_write.lua，测试读写情况

注意此处没有预热，先观察下没有预热情况下的读写性能，得到如下执行结果：

[root@VM_0_5_centos ~]# sysbench --db-driver=mysql --mysql-host=localhost --mysql-port=3306 --mysql-user=root --mysql-password=1qaz@WSX --mysql-db=sbtest --table_size=1000000 --tables=10 --events=0 --time=300  --threads=20 --percentile=95 --report-interval=10 oltp_read_write run
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:
Number of threads: 20
Report intermediate results every 10 second(s)
Initializing random number generator from current time


Initializing worker threads...

Threads started!

[ 10s ] thds: 20 tps: 115.28 qps: 2327.70 (r/w/o: 1633.92/461.22/232.56) lat (ms,95%): 337.94 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 20 tps: 107.65 qps: 2154.96 (r/w/o: 1508.64/431.01/215.31) lat (ms,95%): 350.33 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 20 tps: 107.57 qps: 2155.23 (r/w/o: 1509.13/430.97/215.13) lat (ms,95%): 337.94 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 20 tps: 102.08 qps: 2027.14 (r/w/o: 1414.88/408.11/204.16) lat (ms,95%): 369.77 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 20 tps: 111.17 qps: 2239.64 (r/w/o: 1571.11/446.19/222.34) lat (ms,95%): 337.94 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 20 tps: 118.82 qps: 2376.16 (r/w/o: 1663.62/474.89/237.65) lat (ms,95%): 297.92 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 20 tps: 118.57 qps: 2369.40 (r/w/o: 1657.88/474.48/237.04) lat (ms,95%): 325.98 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 20 tps: 125.53 qps: 2512.64 (r/w/o: 1758.58/502.91/251.15) lat (ms,95%): 287.38 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 20 tps: 115.44 qps: 2298.71 (r/w/o: 1609.27/458.76/230.68) lat (ms,95%): 314.45 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 20 tps: 120.28 qps: 2412.81 (r/w/o: 1689.53/482.52/240.76) lat (ms,95%): 314.45 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 20 tps: 123.70 qps: 2460.59 (r/w/o: 1719.30/494.00/247.30) lat (ms,95%): 287.38 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 20 tps: 121.74 qps: 2455.40 (r/w/o: 1720.96/490.86/243.58) lat (ms,95%): 287.38 err/s: 0.00 reconn/s: 0.00
[ 130s ] thds: 20 tps: 129.32 qps: 2580.95 (r/w/o: 1808.11/514.19/258.64) lat (ms,95%): 292.60 err/s: 0.00 reconn/s: 0.00
[ 140s ] thds: 20 tps: 125.38 qps: 2508.77 (r/w/o: 1756.50/501.51/250.76) lat (ms,95%): 282.25 err/s: 0.00 reconn/s: 0.00
[ 150s ] thds: 20 tps: 128.26 qps: 2564.90 (r/w/o: 1794.64/513.74/256.52) lat (ms,95%): 287.38 err/s: 0.00 reconn/s: 0.00
[ 160s ] thds: 20 tps: 129.90 qps: 2596.47 (r/w/o: 1817.98/518.79/259.70) lat (ms,95%): 267.41 err/s: 0.00 reconn/s: 0.00
[ 170s ] thds: 20 tps: 130.30 qps: 2597.18 (r/w/o: 1816.16/520.32/260.71) lat (ms,95%): 262.64 err/s: 0.00 reconn/s: 0.00
[ 180s ] thds: 20 tps: 134.10 qps: 2685.40 (r/w/o: 1881.30/535.90/268.20) lat (ms,95%): 267.41 err/s: 0.00 reconn/s: 0.00
[ 190s ] thds: 20 tps: 131.38 qps: 2636.25 (r/w/o: 1842.79/530.91/262.56) lat (ms,95%): 282.25 err/s: 0.00 reconn/s: 0.00
[ 200s ] thds: 20 tps: 134.40 qps: 2687.60 (r/w/o: 1883.30/535.30/269.00) lat (ms,95%): 282.25 err/s: 0.00 reconn/s: 0.00
[ 210s ] thds: 20 tps: 130.51 qps: 2610.40 (r/w/o: 1827.61/521.76/261.03) lat (ms,95%): 292.60 err/s: 0.00 reconn/s: 0.00
[ 220s ] thds: 20 tps: 134.91 qps: 2687.87 (r/w/o: 1880.32/537.73/269.82) lat (ms,95%): 272.27 err/s: 0.00 reconn/s: 0.00
[ 230s ] thds: 20 tps: 131.30 qps: 2633.77 (r/w/o: 1845.48/525.69/262.60) lat (ms,95%): 277.21 err/s: 0.00 reconn/s: 0.00
[ 240s ] thds: 20 tps: 133.78 qps: 2676.67 (r/w/o: 1872.87/536.23/267.57) lat (ms,95%): 253.35 err/s: 0.00 reconn/s: 0.00
[ 250s ] thds: 20 tps: 134.10 qps: 2681.82 (r/w/o: 1876.34/537.28/268.19) lat (ms,95%): 292.60 err/s: 0.00 reconn/s: 0.00
[ 260s