用sysbench简单测试mysql性能

简单测试mysql性能

1.测试机器的配置对mysql性能的影响

机器1： 8G4c 
机器2： 32g16c
my.cnf中参数配置一致
msyql版本 5.7.21

2个

机器120个线程 8G4c cpu使用率100%，tps=1166 qps=16356

[  66s] threads: 100, tps: 1176.01, reads: 16452.07, writes: 4649.69, response time: 150.21ms (95%), errors: 0.33, reconnects:  0.00
[  69s] threads: 100, tps: 1142.38, reads: 16071.96, writes: 4664.18, response time: 155.00ms (95%), errors: 2.00, reconnects:  0.00
[  72s] threads: 100, tps: 1160.00, reads: 16208.69, writes: 4610.01, response time: 152.84ms (95%), errors: 1.00, reconnects:  0.00
[  75s] threads: 100, tps: 1157.66, reads: 16203.95, writes: 4589.32, response time: 148.24ms (95%), errors: 0.33, reconnects:  0.00
[  78s] threads: 100, tps: 1166.21, reads: 16350.62, writes: 4689.16, response time: 145.95ms (95%), errors: 0.33, reconnects:  0.00
[  81s] threads: 100, tps: 1168.71, reads: 16356.93, writes: 4642.80, response time: 150.43ms (95%), errors: 1.00, reconnects:  0.00
[  84s] threads: 100, tps: 1157.73, reads: 16269.83, writes: 4688.57, response time: 158.81ms (95%), errors: 0.67, reconnects:  0.00
[  87s] threads: 100, tps: 1165.32, reads: 16315.13, writes: 4662.94, response time: 160.24ms (95%), errors: 0.67, reconnects:  0.00
[  90s] threads: 100, tps: 1165.28, reads: 16306.22, writes: 4661.78, response time: 141.52ms (95%), errors: 0.33, reconnects:  0.00
[  93s] threads: 100, tps: 1174.75, reads: 16393.51, writes: 4621.67, response time: 148.55ms (95%), errors: 0.33, reconnects:  0.00
[  96s] threads: 100, tps: 1143.89, reads: 16097.07, writes: 4662.20, response time: 147.53ms (95%), errors: 0.33, reconnects:  0.00
[  99s] threads: 100, tps: 1163.09, reads: 16281.61, writes: 4654.36, response time: 141.90ms (95%), errors: 0.33, reconnects:  0.00
[ 102s] threads: 100, tps: 1175.00, reads: 16429.99, writes: 4662.00, response time: 160.96ms (95%), errors: 0.33, reconnects:  0.00
[ 105s] threads: 100, tps: 1172.60, reads: 16419.70, writes: 4700.06, response time: 153.62ms (95%), errors: 0.00, reconnects:  0.00
[ 108s] threads: 100, tps: 1163.10, reads: 16317.07, writes: 4684.40, response time: 148.33ms (95%), errors: 1.00, reconnects:  0.00
[ 111s] threads: 100, tps: 1150.00, reads: 16076.05, writes: 4589.35, response time: 142.58ms (95%), errors: 0.67, reconnects:  0.00



32g16c cpu使用率68%  tps=4043 qps=56655  cpu和内存提升了4倍，tps和qps的性能也提升约4倍，这时候还是比较线性的
/usr/local/sysbench-0.5/bin/sysbench --test=/usr/local/sysbench-0.5/sysbench/tests/db/oltp.lua --num-threads=20 --oltp-dist-pct=1  --oltp-dist-res=75 --oltp-table-size=100000  --num-threads=100 --mysql-db=test  --mysql-user=root --oltp-tables-count=10 --max-time=3000 --max-requests=0 --report-interval=3 --mysql-password=mysql  --mysql-socket=/mysqldata/mysql/data/mysql.sock run

[  18s] threads: 100, tps: 4043.71, reads: 56655.91, writes: 16199.50, response time: 36.02ms (95%), errors: 1.33, reconnects:  0.00
[  21s] threads: 100, tps: 3913.66, reads: 54798.91, writes: 15642.31, response time: 36.94ms (95%), errors: 1.33, reconnects:  0.00
[  24s] threads: 100, tps: 4068.66, reads: 56979.25, writes: 16269.64, response time: 36.36ms (95%), errors: 2.00, reconnects:  0.00
[  27s] threads: 100, tps: 4106.96, reads: 57483.83, writes: 16424.86, response time: 35.71ms (95%), errors: 0.00, reconnects:  0.00
[  30s] threads: 100, tps: 3792.37, reads: 53137.85, writes: 15179.81, response time: 35.66ms (95%), errors: 1.33, reconnects:  0.00
[  33s] threads: 100, tps: 3995.63, reads: 55931.45, writes: 15956.18, response time: 37.79ms (95%), errors: 2.33, reconnects:  0.00
[  36s] threads: 100, tps: 4135.23, reads: 57921.53, writes: 16562.58, response time: 36.34ms (95%), errors: 1.67, reconnects:  0.00
[  39s] threads: 100, tps: 4082.79, reads: 57186.37, writes: 16322.82, response time: 37.32ms (95%), errors: 1.33, reconnects:  0.00
[  42s] threads: 100, tps: 4094.92, reads: 57335.52, writes: 16383.67, response time: 35.49ms (95%), errors: 0.67, reconnects:  0.00
[  45s] threads: 100, tps: 4128.74, reads: 57795.38, writes: 16515.97, response time: 36.10ms (95%), errors: 0.67, reconnects:  0.00
[  48s] threads: 100, tps: 4090.04, reads: 57323.86, writes: 16386.82, response time: 36.83ms (95%), errors: 0.67, reconnects:  0.00
[  51s] threads: 100, tps: 4108.91, reads: 57501.72, writes: 16415.30, response time: 37.12ms (95%), errors: 1.33, reconnects:  0.00
[  54s] threads: 100, tps: 4094.36, reads: 57327.00, writes: 16368.76, response time: 37.07ms (95%), errors: 1.00, reconnects:  0.00
[  57s] threads: 100, tps: 3935.05, reads: 55135.64, writes: 15757.85, response time: 39.62ms (95%), errors: 1.33, reconnects:  0.00
[  60s] threads: 100, tps: 4070.99, reads: 57000.59, writes: 16277.31, response time: 36.67ms (95%), errors: 1.00, reconnects:  0.00



32g16c的机器测试双1对mysql的影响，tps由4043上升到4613 提升约14%，qps由57335上升到64960 提升约13%，这里性能提升的原因是

sync_binlog >1：当进行n次事务提交后，mysql 将 binlog_cache 中的数据强制写入磁盘中。
innodb_flush_log_at_trx_commit设置为2：每次事务提交时MySQL都会把log buffer的数据写入log file，但是flush(刷到磁盘)操作并不会同时进行。该模式下,MySQL会每秒执行一次 flush(刷到磁盘)操作。

vi /etc/my.cnf
innodb_flush_log_at_trx_commit=2
sync_binlog=10

# /usr/local/sysbench-0.5/bin/sysbench --test=/usr/local/sysbench-0.5/sysbench/tests/db/oltp.lua --num-threads=20 --oltp-dist-pct=1  --oltp-dist-res=75 --oltp-table-size=100000  --num-threads=100 --mysql-db=test  --mysql-user=root --oltp-tables-count=10 --max-time=3000 --max-requests=0 --report-interval=3 --mysql-password=mysql  --mysql-socket=/mysqldata/mysql/data/mysql.sock run

[   3s] threads: 100, tps: 4613.32, reads: 64960.78, writes: 18505.61, response time: 42.15ms (95%), errors: 1.33, reconnects:  0.00
[   6s] threads: 100, tps: 4791.09, reads: 67161.87, writes: 19187.68, response time: 38.52ms (95%), errors: 2.00, reconnects:  0.00
[   9s] threads: 100, tps: 4827.38, reads: 67604.38, writes: 19330.21, response time: 37.31ms (95%), errors: 2.00, reconnects:  0.00
[  12s] threads: 100, tps: 4818.99, reads: 67526.51, writes: 19304.95, response time: 36.86ms (95%), errors: 3.00, reconnects:  0.00
[  15s] threads: 100, tps: 4906.76, reads: 68727.66, writes: 19620.38, response time: 35.75ms (95%), errors: 1.33, reconnects:  0.00
[  18s] threads: 100, tps: 4900.68, reads: 68599.85, writes: 19571.39, response time: 36.33ms (95%), errors: 1.67, reconnects:  0.00
[  21s] threads: 100, tps: 4648.67, reads: 65152.65, writes: 18621.99, response time: 41.12ms (95%), errors: 2.00, reconnects:  0.00
[  24s] threads: 100, tps: 4802.32, reads: 67211.81, writes: 19191.94, response time: 37.53ms (95%), errors: 1.33, reconnects:  0.00
[  27s] threads: 100, tps: 4738.96, reads: 66402.40, writes: 18960.83, response time: 38.82ms (95%), errors: 1.67, reconnects:  0.00
[  30s] threads: 100, tps: 4845.33, reads: 67875.31, writes: 19379.00, response time: 37.48ms (95%), errors: 5.33, reconnects:  0.00
[  33s] threads: 100, tps: 4746.97, reads: 66509.85, writes: 19004.53, response time: 38.50ms (95%), errors: 2.00, reconnects:  0.00
[  36s] threads: 100, tps: 4867.75, reads: 68201.47, writes: 19496.66, response time: 36.33ms (95%), errors: 2.33, reconnects:  0.00
[  39s] threads: 100, tps: 4617.22, reads: 64617.09, writes: 18406.88, response time: 40.46ms (95%), errors: 2.00, reconnects:  0.00
[  42s] threads: 100, tps: 4768.85, reads: 66789.59, writes: 19106.41, response time: 38.23ms (95%), errors: 3.00, reconnects:  0.00


目前redo大小1G,测试把redo加大成2G对性能的影响很小应该和测试的脚本有关系，如果是纯插入的场景对性能的提升

innodb_log_file_size                = 2G
 /usr/local/sysbench-0.5/bin/sysbench --test=/usr/local/sysbench-0.5/sysbench/tests/db/oltp.lua --num-threads=20 --oltp-dist-pct=1  --oltp-dist-res=75 --oltp-table-size=100000  --num-threads=100 --mysql-db=test  --mysql-user=root --oltp-tables-count=10 --max-time=3000 --max-requests=0 --report-interval=3 --mysql-password=mysql  --mysql-socket=/mysqldata/mysql/data/mysql.sock run


[   3s] threads: 100, tps: 4047.44, reads: 57069.51, writes: 16269.77, response time: 36.21ms (95%), errors: 0.67, reconnects:  0.00
[   6s] threads: 100, tps: 4285.12, reads: 60055.34, writes: 17178.14, response time: 34.22ms (95%), errors: 0.00, reconnects:  0.00
[   9s] threads: 100, tps: 4249.42, reads: 59485.93, writes: 16982.03, response time: 35.28ms (95%), errors: 1.67, reconnects:  0.00
[  12s] threads: 100, tps: 4154.10, reads: 58167.36, writes: 16608.05, response time: 35.00ms (95%), errors: 1.67, reconnects:  0.00
[  15s] threads: 100, tps: 4180.81, reads: 58599.73, writes: 16767.59, response time: 34.64ms (95%), errors: 1.33, reconnects:  0.00
[  18s] threads: 100, tps: 4287.59, reads: 60020.33, writes: 17129.71, response time: 33.20ms (95%), errors: 1.33, reconnects:  0.00
[  21s] threads: 100, tps: 4142.40, reads: 58055.53, writes: 16587.58, response time: 35.55ms (95%), errors: 2.67, reconnects:  0.00
[  24s] threads: 100, tps: 4154.01, reads: 58163.10, writes: 16609.70, response time: 35.82ms (95%), errors: 1.67, reconnects:  0.00
[  27s] threads: 100, tps: 4211.29, reads: 58949.44, writes: 16841.17, response time: 35.51ms (95%), errors: 0.67, reconnects:  0.00
[  30s] threads: 100, tps: 3846.28, reads: 53858.86, writes: 15379.77, response time: 42.71ms (95%), errors: 1.00, reconnects:  0.00
[  33s] threads: 100, tps: 3919.01, reads: 54888.86, writes: 15678.39, response time: 42.15ms (95%), errors: 1.33, reconnects:  0.00
[  36s] threads: 100, tps: 3987.41, reads: 55867.14, writes: 15960.66, response time: 39.82ms (95%), errors: 1.00, reconnects:  0.00
[  39s] threads: 100, tps: 4007.99, reads: 56103.83, writes: 16035.95, response time: 39.11ms (95%), errors: 0.67, reconnects:  0.0