MySQL 性能测试工具 Sysbench

1、工具安装

?

1	dnf install sysbench -y

2、准备数据

?

1	sysbench /usr/share/sysbench/oltp_common.lua --time=300 --mysql-host=localhost --mysql-port=3306 --mysql-user=root --mysql-password=rootroot --mysql-db=sbtest --table-size=1000000 --tables=10--threads=32 --events=999999999 prepare

3、压测

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68

# sysbench /usr/share/sysbench/oltp_read_write.lua --time=300 --mysql-host=localhost --mysql-port=3306 --mysql-user=root --mysql-password=rootroot --mysql-db=sbtest --table-size=1000000 --tables=10 --threads=16 --events=999999999 --report-interval=10 run sysbench 1.0.20 (using system LuaJIT 2.1.0-beta3)   Running the test with following options: Number of threads: 16 Report intermediate results every 10 second(s) Initializing random number generator from current time     Initializing worker threads...   Threads started!   [ 10s ] thds: 16 tps: 470.00 qps: 9418.17 (r/w/o: 6594.95/1881.61/941.61) lat (ms,95%): 137.35 err/s: 0.00 reconn/s: 0.00 [ 20s ] thds: 16 tps: 391.10 qps: 7823.51 (r/w/o: 5476.91/1564.50/782.10) lat (ms,95%): 161.51 err/s: 0.00 reconn/s: 0.00 [ 30s ] thds: 16 tps: 526.01 qps: 10526.22 (r/w/o: 7367.56/2106.64/1052.02) lat (ms,95%): 65.65 err/s: 0.00 reconn/s: 0.00 [ 40s ] thds: 16 tps: 517.99 qps: 10360.66 (r/w/o: 7254.43/2070.15/1036.08) lat (ms,95%): 68.05 err/s: 0.00 reconn/s: 0.00 [ 50s ] thds: 16 tps: 568.80 qps: 11373.71 (r/w/o: 7963.21/2272.90/1137.60) lat (ms,95%): 51.94 err/s: 0.00 reconn/s: 0.00 [ 60s ] thds: 16 tps: 600.40 qps: 12005.51 (r/w/o: 8400.61/2404.10/1200.80) lat (ms,95%): 66.84 err/s: 0.00 reconn/s: 0.00 [ 70s ] thds: 16 tps: 640.00 qps: 12796.32 (r/w/o: 8957.91/2558.40/1280.00) lat (ms,95%): 44.17 err/s: 0.00 reconn/s: 0.00 [ 80s ] thds: 16 tps: 690.92 qps: 13825.55 (r/w/o: 9677.14/2766.57/1381.83) lat (ms,95%): 39.65 err/s: 0.00 reconn/s: 0.00 [ 90s ] thds: 16 tps: 708.28 qps: 14170.19 (r/w/o: 9918.79/2834.84/1416.57) lat (ms,95%): 38.94 err/s: 0.00 reconn/s: 0.00 [ 100s ] thds: 16 tps: 678.50 qps: 13565.87 (r/w/o: 9498.98/2709.89/1357.00) lat (ms,95%): 36.24 err/s: 0.00 reconn/s: 0.00 [ 110s ] thds: 16 tps: 728.66 qps: 14571.52 (r/w/o: 10197.55/2916.64/1457.32) lat (ms,95%): 43.39 err/s: 0.00 reconn/s: 0.00 [ 120s ] thds: 16 tps: 692.72 qps: 13846.90 (r/w/o: 9692.98/2768.48/1385.44) lat (ms,95%): 41.10 err/s: 0.00 reconn/s: 0.00 [ 130s ] thds: 16 tps: 701.83 qps: 14039.26 (r/w/o: 9827.46/2808.13/1403.67) lat (ms,95%): 41.85 err/s: 0.00 reconn/s: 0.00 [ 140s ] thds: 16 tps: 790.99 qps: 15819.40 (r/w/o: 11074.66/3162.76/1581.98) lat (ms,95%): 38.25 err/s: 0.00 reconn/s: 0.00 [ 150s ] thds: 16 tps: 726.20 qps: 14524.65 (r/w/o: 10166.54/2905.71/1452.41) lat (ms,95%): 41.10 err/s: 0.00 reconn/s: 0.00 [ 160s ] thds: 16 tps: 809.31 qps: 16190.38 (r/w/o: 11334.13/3237.64/1618.62) lat (ms,95%): 36.24 err/s: 0.00 reconn/s: 0.00 [ 170s ] thds: 16 tps: 799.69 qps: 15987.75 (r/w/o: 11190.59/3197.77/1599.38) lat (ms,95%): 37.56 err/s: 0.00 reconn/s: 0.00 [ 180s ] thds: 16 tps: 791.99 qps: 15841.99 (r/w/o: 11089.12/3169.08/1583.79) lat (ms,95%): 35.59 err/s: 0.00 reconn/s: 0.00 [ 190s ] thds: 16 tps: 811.56 qps: 16232.19 (r/w/o: 11363.40/3245.46/1623.33) lat (ms,95%): 36.89 err/s: 0.00 reconn/s: 0.00 [ 200s ] thds: 16 tps: 732.33 qps: 14648.92 (r/w/o: 10255.14/2929.12/1464.66) lat (ms,95%): 38.94 err/s: 0.00 reconn/s: 0.00 [ 210s ] thds: 16 tps: 783.31 qps: 15663.78 (r/w/o: 10961.93/3135.44/1566.42) lat (ms,95%): 41.10 err/s: 0.00 reconn/s: 0.00 [ 220s ] thds: 16 tps: 796.69 qps: 15931.37 (r/w/o: 11152.84/3184.95/1593.58) lat (ms,95%): 36.24 err/s: 0.00 reconn/s: 0.00 [ 230s ] thds: 16 tps: 770.40 qps: 15405.97 (r/w/o: 10784.48/3080.89/1540.60) lat (ms,95%): 37.56 err/s: 0.00 reconn/s: 0.00 [ 240s ] thds: 16 tps: 850.50 qps: 17017.49 (r/w/o: 11913.97/3402.32/1701.21) lat (ms,95%): 36.89 err/s: 0.00 reconn/s: 0.00 [ 250s ] thds: 16 tps: 799.01 qps: 15976.13 (r/w/o: 11182.09/3196.03/1598.01) lat (ms,95%): 39.65 err/s: 0.00 reconn/s: 0.00 [ 260s ] thds: 16 tps: 766.48 qps: 15332.29 (r/w/o: 10733.78/3065.54/1532.97) lat (ms,95%): 38.25 err/s: 0.00 reconn/s: 0.00 [ 270s ] thds: 16 tps: 800.71 qps: 16018.12 (r/w/o: 11211.06/3205.64/1601.42) lat (ms,95%): 36.89 err/s: 0.00 reconn/s: 0.00 [ 280s ] thds: 16 tps: 753.39 qps: 15065.80 (r/w/o: 10547.13/3011.88/1506.79) lat (ms,95%): 38.25 err/s: 0.00 reconn/s: 0.00 [ 290s ] thds: 16 tps: 778.11 qps: 15558.50 (r/w/o: 10889.94/3112.34/1556.22) lat (ms,95%): 38.94 err/s: 0.00 reconn/s: 0.00 [ 300s ] thds: 16 tps: 776.70 qps: 15544.54 (r/w/o: 10879.73/3111.41/1553.40) lat (ms,95%): 35.59 err/s: 0.00 reconn/s: 0.00 SQL statistics:     queries performed:         read:                            2975602         write:                           850172         other:                           425086         total:                           4250860     transactions:                        212543 (708.11 per sec.)     queries:                             4250860 (14162.22 per sec.)     ignored errors:                      0      (0.00 per sec.)     reconnects:                          0      (0.00 per sec.)   General statistics:     total time:                          300.1535s     total number of events:              212543   Latency (ms):          min:                                    3.09          avg:                                   22.59          max:                                 1351.54          95th percentile:                       41.85          sum:                              4801839.28   Threads fairness:     events (avg/stddev):           13283.9375/109.82     execution time (avg/stddev):   300.1150/0.00

4、清理数据

?

1	sysbench /usr/share/sysbench/oltp_read_write.lua --time=300 --mysql-host=localhost --mysql-port=3306 --mysql-user=root --mysql-password=rootroot --mysql-db=sbtest --table-size=1000000 --tables=10 --threads=16 --events=999999999 --report-interval=10 cleanup

5、压测结果分析

?

1 2 3 4 5 6 7 8 9 10 11 12 13

整体数据库性能评估： 良好。   根据 sysbench 压测结果，数据库在以下方面表现良好： 吞吐量 (TPS): 最高达到 850.50 TPS，平均在 776.70 TPS 左右，总体上较为稳定。 响应时间 (Latency): 平均响应时间为 22.59 ms，95% 响应时间为 41.85 ms，都处于较低水平，说明数据库能够快速响应查询。 事务处理能力: 每秒处理 708.11 个事务，吞吐量和响应时间都比较理想。 错误率: 没有错误发生，重连次数为 0，数据库稳定性良好。 指标分析： TPS: 整体稳定，在压力增加的情况下也有较好的表现，说明数据库能够承受一定的负载。 Latency: 平均和 95% 响应时间都较低，说明数据库查询速度快，用户体验流畅。 Queries: 每秒执行 14162.22 个查询，数据库处理能力强。 Threads fairness: 线程公平性良好，每个线程的事件和执行时间分布比较均匀。