目录
一、测试方案
- 通过 Sysbench 导入 16 张表，每张表有 1000 万行数据。
- 分别对每个表执行 analyze table 命令。
- 备份数据，用于不同并发测试前进行数据恢复，以保证每次数据一致。
- 启动 Sysbench 客户端，进行 point_select、read_write、update_index 和 update_non_index 测试。通过 HAProxy 向 TiDB 加压，每种负载每个并发数各测试 20 分钟。
- 每轮完成后停止集群，使用之前的备份的数据覆盖，再启动集群。
二、准备测试数据
执行以下命令来准备测试数据：
sysbench oltp_common \
--threads=16 \
--rand-type=uniform \
--db-driver=mysql \
--mysql-db=sbtest \
--mysql-host=192.168.192.51 \
--mysql-port=4000 \
--mysql-user=*** \
--mysql-password=*** \
prepare --tables=16 --table-size=10000000
三、oltp_point_select
指标详解：
根据你提供的指标，这些是针对一个SQL压力测试的统计数据，我来解释每个部分的含义和重点指标：
SQL statistics:
queries performed:
read: 76151928
write: 0
other: 0
total: 76151928
#read: 读操作次数为76151928次。
#write: 写操作次数为0次。
#other: 其他类型的操作次数为0次。
#total: 总查询次数为76151928次。
transactions: 76151928 (63458.91 per sec.)
queries: 76151928 (63458.91 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
#transactions: 总事务数，即数据库事务的执行次数。
#queries: 总查询数，包括所有类型的查询。
#ignored errors: 忽略的错误数，指在执行过程中被忽略的错误次数。
#reconnects: 重新连接次数，数据库重新连接的次数。
General statistics:
total time: 1200.0180s
total number of events: 76151928
#total time: 总执行时间，所有操作的累计时间。
#total number of events: 总事件数，所有数据库操作的总次数。
Latency (ms):
min: 0.42
avg: 1.58
max: 2872.39
95th percentile: 2.57
sum: 119967985.58
#min: 最小延迟，所有操作中最短的响应时间。
#avg: 平均延迟，所有操作的平均响应时间。
#max: 最大延迟，所有操作中最长的响应时间。
#95th percentile: 95th 百分位延迟，即95% 的查询响应时间低于这个值。
#sum: 所有延迟时间的总和。
Threads fairness:
events (avg/stddev): 761519.2800/23760.08
execution time (avg/stddev): 1199.6799/0.01
#events (avg/stddev): 每个线程平均处理的事件数及标准差。
#execution time (avg/stddev): 每个线程执行任务的平均时间及标准差。
重点指标:
每秒事务数 (transactions per sec.): 表示数据库每秒可以处理的事务数量，是衡量数据库吞吐量的关键指标。
平均延迟时间 (avg): 表示事务处理的平均响应时间，反映了数据库处理请求的效率。
95%分位数延迟时间 (95th percentile): 表示95%的事务处理时间都小于这个值，是衡量用户体验和性能的重要指标。
最大延迟时间 (max): 表示最慢的事务处理时间，可以用来识别可能的性能瓶颈。
线程公平性 (Threads fairness): 反映了系统在多线程环境下的负载均衡情况，对于多线程测试非常重要。
这些指标共同提供了对数据库性能的综合评估，帮助识别性能瓶颈和优化
1、100个线程
sysbench /usr/share/sysbench/oltp_point_select.lua \
--threads=100 \
--time=1200 \
--report-interval=1 \
--rand-type=uniform \
--db-driver=mysql \
--mysql-db=sbtest \
--mysql-host=192.168.192.51 \
--mysql-port=4000 \
--mysql-user=*** \
--mysql-password=*** \
run --tables=16 --table-size=10000000
SQL statistics:
queries performed:
read: 76151928
write: 0
other: 0
total: 76151928
transactions: 76151928 (63458.91 per sec.)
queries: 76151928 (63458.91 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 1200.0180s
total number of events: 76151928
Latency (ms):
min: 0.42
avg: 1.58
max: 2872.39
95th percentile: 2.57
sum: 119967985.58
Threads fairness:
events (avg/stddev): 761519.2800/23760.08
execution time (avg/stddev): 1199.6799/0.01
2、200个线程
sysbench /usr/share/sysbench/oltp_point_select.lua \
--threads=200 \
--time=1200 \
--report-interval=1 \
--rand-type=uniform \
--db-driver=mysql \
--mysql-db=sbtest \
--mysql-host=192.168.192.51 \
--mysql-port=4000 \
--mysql-user=*** \
--mysql-password=*** \
run --tables=16 --table-size=10000000
SQL statistics:
queries performed:
read: 126223574
write: 0
other: 0
total: 126223574
transactions: 126223574 (105181.15 per sec.)
queries: 126223574 (105181.15 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 1200.0574s
total number of events: 126223574
Latency (ms):
min: 0.46
avg: 1.90
max: 295.23
95th percentile: 3.25
sum: 239949979.16
Threads fairness:
events (avg/stddev): 631117.8700/22194.99
execution time (avg/stddev): 1199.7499/0.01
3、300个线程
sysbench /usr/share/sysbench/oltp_point_select.lua \
--threads=300 \
--time=1200 \
--report-interval=1 \
--rand-type=uniform \
--db-driver=mysql \
--mysql-db=sbtest \
--mysql-host=192.168.192.51 \
--mysql-port=4000 \
--mysql-user=*** \
--mysql-password=*** \
run --tables=16 --table-size=10000000
SQL statistics:
queries performed:
read: 153997322
write: 0
other: 0
total: 153997322
transactions: 153997322 (128324.95 per sec.)
queries: 153997322 (128324.95 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 1200.0560s
total number of events: 153997322
Latency (ms):
min: 0.47
avg: 2.34
max: 360.62
95th percentile: 4.18
sum: 359927355.22
Threads fairness:
events (avg/stddev): 513324.4067/14491.21
execution time (avg/stddev): 1199.7579/0.03
四、oltp_read_write
1、100个线程
sysbench /usr/share/sysbench/oltp_read_write.lua \
--threads=100 \
--time=1200 \
--report-interval=1 \
--rand-type=uniform \
--db-driver=mysql \
--mysql-db=sbtest \
--mysql-host=192.168.192.51 \
--mysql-port=4000 \
--mysql-user=*** \
--mysql-password=*** \
run --tables=16 --table-size=10000000
SQL statistics:
queries performed:
read: 37545676
write: 10682593
other: 5408411
total: 53636680
transactions: 2681834 (2234.69 per sec.)
queries: 53636680 (44693.83 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 1200.0900s
total number of events: 2681834
Latency (ms):
min: 24.26
avg: 44.74
max: 601.89
95th percentile: 66.84
sum: 119997805.61
Threads fairness:
events (avg/stddev): 26818.3400/902.59
execution time (avg/stddev): 1199.9781/0.02
2、200个线程
sysbench /usr/share/sysbench/oltp_read_write.lua \
--threads=200 \
--time=1200 \
--report-interval=1 \
--rand-type=uniform \
--db-driver=mysql \
--mysql-db=sbtest \
--mysql-host=192.168.192.51 \
--mysql-port=4000 \
--mysql-user=*** \
--mysql-password=*** \
run --tables=16 --table-size=10000000
SQL statistics:
queries performed:
read: 38297658
write: 10897141
other: 5516141
total: 54710940
transactions: 2735547 (2279.39 per sec.)
queries: 54710940 (45587.79 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 1200.1213s
total number of events: 2735547
Latency (ms):
min: 22.72
avg: 87.73
max: 920.16
95th percentile: 142.39
sum: 240000849.06
Threads fairness:
events (avg/stddev): 13677.7350/335.14
execution time (avg/stddev): 1200.0042/0.02
3、300个线程
sysbench /usr/share/sysbench/oltp_read_write.lua \
--threads=300 \
--time=1200 \
--report-interval=1 \
--rand-type=uniform \
--db-driver=mysql \
--mysql-db=sbtest \
--mysql-host=192.168.192.51 \
--mysql-port=4000 \
--mysql-user=*** \
--mysql-password=*** \
run --tables=16 --table-size=10000000
SQL statistics:
queries performed:
read: 35917896
write: 10220583
other: 5172801
total: 51311280
transactions: 2565564 (2137.70 per sec.)
queries: 51311280 (42753.99 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 1200.1504s
total number of events: 2565564
Latency (ms):
min: 23.54
avg: 140.32
max: 1231.55
95th percentile: 227.40
sum: 360011922.53
Threads fairness:
events (avg/stddev): 8551.8800/161.27
execution time (avg/stddev): 1200.0397/0.04
五、oltp_update_index
1、100个线程
sysbench /usr/share/sysbench/oltp_update_index.lua \
--threads=100 \
--time=1200 \
--report-interval=1 \
--rand-type=uniform \
--db-driver=mysql \
--mysql-db=sbtest \
--mysql-host=192.168.192.51 \
--mysql-port=4000 \
--mysql-user=*** \
--mysql-password=*** \
run --tables=16 --table-size=10000000
SQL statistics:
queries performed:
read: 0
write: 20740126
other: 123129
total: 20863255
transactions: 20863255 (17385.80 per sec.)
queries: 20863255 (17385.80 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 1200.0159s
total number of events: 20863255
Latency (ms):
min: 0.82
avg: 5.75
max: 225.55
95th percentile: 9.73
sum: 119989299.59
Threads fairness:
events (avg/stddev): 208632.5500/3260.71
execution time (avg/stddev): 1199.8930/0.00
2、200个线程
sysbench /usr/share/sysbench/oltp_update_index.lua \
--threads=200 \
--time=1200 \
--report-interval=1 \
--rand-type=uniform \
--db-driver=mysql \
--mysql-db=sbtest \
--mysql-host=192.168.192.51 \
--mysql-port=4000 \
--mysql-user=*** \
--mysql-password=*** \
run --tables=16 --table-size=10000000
SQL statistics:
queries performed:
read: 0
write: 24816379
other: 147295
total: 24963674
transactions: 24963674 (20802.63 per sec.)
queries: 24963674 (20802.63 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 1200.0238s
total number of events: 24963674
Latency (ms):
min: 0.84
avg: 9.61
max: 324.52
95th percentile: 18.61
sum: 239988306.39
Threads fairness:
events (avg/stddev): 124818.3700/1145.85
execution time (avg/stddev): 1199.9415/0.01
3、300个线程
sysbench /usr/share/sysbench/oltp_update_index.lua \
--threads=300 \
--time=1200 \
--report-interval=1 \
--rand-type=uniform \
--db-driver=mysql \
--mysql-db=sbtest \
--mysql-host=192.168.192.51 \
--mysql-port=4000 \
--mysql-user=*** \
--mysql-password=*** \
run --tables=16 --table-size=10000000
SQL statistics:
queries performed:
read: 0
write: 24842999
other: 147255
total: 24990254
transactions: 24990254 (20824.45 per sec.)
queries: 24990254 (20824.45 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 1200.0424s
total number of events: 24990254
Latency (ms):
min: 0.92
avg: 14.41
max: 769.84
95th percentile: 28.16
sum: 359991220.53
Threads fairness:
events (avg/stddev): 83300.8467/229.26
execution time (avg/stddev): 1199.9707/0.01
六、oltp_update_non_index
1、100个线程
sysbench /usr/share/sysbench/oltp_update_non_index.lua \
--threads=100 \
--time=1200 \
--report-interval=1 \
--rand-type=uniform \
--db-driver=mysql \
--mysql-db=sbtest \
--mysql-host=192.168.192.51 \
--mysql-port=4000 \
--mysql-user=*** \
--mysql-password=*** \
run --tables=16 --table-size=10000000
SQL statistics:
queries performed:
read: 0
write: 32461084
other: 204973
total: 32666057
transactions: 32666057 (27221.36 per sec.)
queries: 32666057 (27221.36 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 1200.0142s
total number of events: 32666057
Latency (ms):
min: 0.88
avg: 3.67
max: 233.08
95th percentile: 6.79
sum: 119984449.56
Threads fairness:
events (avg/stddev): 326660.5700/3188.52
execution time (avg/stddev): 1199.8445/0.01
2、200个线程
sysbench /usr/share/sysbench/oltp_update_non_index.lua \
--threads=400 \
--time=1200 \
--report-interval=1 \
--rand-type=uniform \
--db-driver=mysql \
--mysql-db=sbtest \
--mysql-host=192.168.192.51 \
--mysql-port=4000 \
--mysql-user=*** \
--mysql-password=*** \
run --tables=16 --table-size=10000000
SQL statistics:
queries performed:
read: 0
write: 49843020
other: 315100
total: 50158120
transactions: 50158120 (41797.30 per sec.)
queries: 50158120 (41797.30 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 1200.0308s
total number of events: 50158120
Latency (ms):
min: 0.96
avg: 4.78
max: 552.91
95th percentile: 7.84
sum: 239979346.26
Threads fairness:
events (avg/stddev): 250790.6000/659.08
execution time (avg/stddev): 1199.8967/0.01
3、300个线程
sysbench /usr/share/sysbench/oltp_update_non_index.lua \
--threads=300 \
--time=1200 \
--report-interval=1 \
--rand-type=uniform \
--db-driver=mysql \
--mysql-db=sbtest \
--mysql-host=192.168.192.51 \
--mysql-port=4000 \
--mysql-user=*** \
--mysql-password=*** \
run --tables=16 --table-size=10000000
SQL statistics:
queries performed:
read: 0
write: 51908575
other: 328555
total: 52237130
transactions: 52237130 (43529.53 per sec.)
queries: 52237130 (43529.53 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 1200.0375s
total number of events: 52237130
Latency (ms):
min: 0.91
avg: 6.89
max: 623.85
95th percentile: 12.30
sum: 359980030.03
Threads fairness:
events (avg/stddev): 174123.7667/328.23
execution time (avg/stddev): 1199.9334/0.01
310
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
