测试环境 ：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之后，性能提升并不明显。
