测试环境 ：MySQL 5.7.26
测试数据：10张表，每张表500万行数据，共12G的数据。
innodb_buffer_pool_instances = 1不变，每次测试只修改innodb_buffer_pool_size大小，分别为256M，1G，6G，12G，24G。
测试脚本如下：
# /usr/local/bin/sysbench /tmp/sysbench-master/src/lua/oltp_read_write.lua --mysql-user=sysbenchuser --mysql-password=sysbenchuser --mysql-port=3307 --mysql-host=localhost --mysql-socket=/tmp/mysqld.sock --mysql-db=sysbenchtest --tables=10 --table-size=5000000 --threads=30 --report-interval=5 --time=180 run
sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3)
Running the test with following options:
Number of threads: 30
Report intermediate results every 5 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 5s ] thds: 30 tps: 1742.72 qps: 34918.91 (r/w/o: 24455.21/6972.27/3491.43) lat (ms,95%): 24.38 err/s: 0.00 reconn/s: 0.00
[ 10s ] thds: 30 tps: 407.27 qps: 8172.42 (r/w/o: 5723.19/1634.68/814.54) lat (ms,95%): 147.61 err/s: 0.00 reconn/s: 0.00
[ 15s ] thds: 30 tps: 581.63 qps: 11623.71 (r/w/o: 8134.76/2325.70/1163.25) lat (ms,95%): 95.81 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 30 tps: 710.40 qps: 14205.91 (r/w/o: 9943.34/2841.78/1420.79) lat (ms,95%): 75.82 err/s: 0.00 reconn/s: 0.00
[ 25s ] thds: 30 tps: 858.00 qps: 17147.96 (r/w/o: 12003.17/3428.99/1715.80) lat (ms,95%): 65.65 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 30 tps: 827.85 qps: 16586.74 (r/w/o: 11611.66/3319.19/1655.89) lat (ms,95%): 73.13 err/s: 0.00 reconn/s: 0.00
[ 35s ] thds: 30 tps: 595.95 qps: 11921.72 (r/w/o: 8345.25/2384.58/1191.89) lat (ms,95%): 101.13 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 30 tps: 685.43 qps: 13700.72 (r/w/o: 9593.16/2736.70/1370.85) lat (ms,95%): 89.16 err/s: 0.00 reconn/s: 0.00
[ 45s ] thds: 30 tps: 706.43 qps: 14120.07 (r/w/o: 9878.27/2828.93/1412.87) lat (ms,95%): 84.47 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 30 tps: 768.80 qps: 15391.26 (r/w/o: 10774.84/3078.81/1537.61) lat (ms,95%): 77.19 err/s: 0.00 reconn/s: 0.00
[ 55s ] thds: 30 tps: 825.49 qps: 16508.12 (r/w/o: 11556.00/3301.14/1650.97) lat (ms,95%): 75.82 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 30 tps: 707.58 qps: 14125.33 (r/w/o: 9886.67/2823.91/1414.75) lat (ms,95%): 87.56 err/s: 0.00 reconn/s: 0.00
[ 65s ] thds: 30 tps: 722.91 qps: 14468.58 (r/w/o: 10133.52/2888.84/1446.22) lat (ms,95%): 86.00 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 30 tps: 727.91 qps: 14578.14 (r/w/o: 10201.70/2920.63/1455.81) lat (ms,95%): 87.56 err/s: 0.00 reconn/s: 0.00
[ 75s ] thds: 30 tps: 771.42 qps: 15424.96 (r/w/o: 10798.65/3083.47/1542.84) lat (ms,95%): 80.03 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 30 tps: 871.15 qps: 17414.42 (r/w/o: 12192.11/3480.00/1742.30) lat (ms,95%): 71.83 err/s: 0.00 reconn/s: 0.00
[ 85s ] thds: 30 tps: 862.85 qps: 17268.60 (r/w/o: 12085.10/3457.80/1725.70) lat (ms,95%): 74.46 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 30 tps: 798.41 qps: 15942.54 (r/w/o: 11154.50/3191.43/1596.61) lat (ms,95%): 78.60 err/s: 0.00 reconn/s: 0.00
[ 95s ] thds: 30 tps: 744.39 qps: 14911.31 (r/w/o: 10442.20/2980.14/1488.97) lat (ms,95%): 87.56 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 30 tps: 791.29 qps: 15817.78 (r/w/o: 11074.64/3160.55/1582.58) lat (ms,95%): 84.47 err/s: 0.00 reconn/s: 0.00
[ 105s ] thds: 30 tps: 825.33 qps: 16514.89 (r/w/o: 11560.28/3303.94/1650.67) lat (ms,95%): 75.82 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 30 tps: 863.27 qps: 17261.89 (r/w/o: 12084.05/3451.30/1726.55) lat (ms,95%): 75.82 err/s: 0.00 reconn/s: 0.00
[ 115s ] thds: 30 tps: 853.95 qps: 17077.88 (r/w/o: 11957.16/3412.82/1707.91) lat (ms,95%): 78.60 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 30 tps: 861.95 qps: 17242.68 (r/w/o: 12067.56/3451.22/1723.91) lat (ms,95%): 75.82 err/s: 0.00 reconn/s: 0.00
[ 125s ] thds: 30 tps: 840.22 qps: 16804.86 (r/w/o: 11762.92/3361.49/1680.45) lat (ms,95%): 78.60 err/s: 0.00 reconn/s: 0.00
[ 130s ] thds: 30 tps: 814.41 qps: 16288.53 (r/w/o: 11401.89/3257.83/1628.81) lat (ms,95%): 80.03 err/s: 0.00 reconn/s: 0.00
[ 135s ] thds: 30 tps: 850.72 qps: 16997.96 (r/w/o: 11890.45/3407.07/1700.44) lat (ms,95%): 75.82 err/s: 0.00 reconn/s: 0.00
[ 140s ] thds: 30 tps: 861.36 qps: 17245.84 (r/w/o: 12078.67/3443.45/1723.72) lat (ms,95%): 77.19 err/s: 0.00 reconn/s: 0.00
[ 145s ] thds: 30 tps: 826.32 qps: 16524.38 (r/w/o: 11566.67/3305.08/1652.64) lat (ms,95%): 78.60 err/s: 0.00 reconn/s: 0.00
[ 150s ] thds: 30 tps: 839.83 qps: 16797.26 (r/w/o: 11759.46/3358.13/1679.67) lat (ms,95%): 80.03 err/s: 0.00 reconn/s: 0.00
[ 155s ] thds: 30 tps: 785.96 qps: 15713.53 (r/w/o: 10999.79/3141.83/1571.91) lat (ms,95%): 86.00 err/s: 0.00 reconn/s: 0.00
[ 160s ] thds: 30 tps: 686.95 qps: 13747.74 (r/w/o: 9620.86/2752.99/1373.89) lat (ms,95%): 84.47 err/s: 0.00 reconn/s: 0.00
[ 165s ] thds: 30 tps: 835.54 qps: 16704.11 (r/w/o: 11695.70/3337.34/1671.07) lat (ms,95%): 80.03 err/s: 0.00 reconn/s: 0.00
[ 170s ] thds: 30 tps: 805.01 qps: 16101.00 (r/w/o: 11269.14/3221.84/1610.02) lat (ms,95%): 84.47 err/s: 0.00 reconn/s: 0.00
[ 175s ] thds: 30 tps: 835.45 qps: 16711.66 (r/w/o: 11699.14/3341.61/1670.91) lat (ms,95%): 77.19 err/s: 0.00 reconn/s: 0.00
[ 180s ] thds: 30 tps: 854.08 qps: 17088.49 (r/w/o: 11957.18/3423.14/1708.17) lat (ms,95%): 77.19 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 2026850
write: 579100
other: 289550
total: 2895500
transactions: 144775 (804.14 per sec.)
queries: 2895500 (16082.85 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
Throughput:
events/s (eps): 804.1427
time elapsed: 180.0364s
total number of events: 144775
Latency (ms):
min: 2.94
avg: 37.30
max: 880.19
95th percentile: 81.48
sum: 5400006.31
Threads fairness:
events (avg/stddev): 4825.8333/35.17
execution time (avg/stddev): 180.0002/0.01
测试结果：
TPS如下图：
QPS如下图：
从TPS和QPS图可以看出，在当前条件下，当 innodb_buffer_pool_size大于6G之后，性能提升并不明显。
887
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
