第四章：OLTP 标准测试
第四章：OLTP 标准测试
4.1 概述
Sysbench 内置了多个 OLTP（Online Transaction Processing，在线事务处理）标准测试脚本，这些脚本模拟了典型的数据库工作负载。OLTP 测试是 Sysbench 最核心的功能，广泛用于数据库性能评估和调优。
4.1.1 可用的 OLTP 测试
| 测试名称 | 操作类型 | 说明 |
|---|---|---|
oltp_read_write | 读写混合 | 最常用，模拟典型 OLTP 负载（75% 读 + 25% 写） |
oltp_read_only | 只读 | 模拟读密集场景（报表、分析） |
oltp_write_only | 只写 | 模拟写密集场景（日志写入、数据导入） |
oltp_point_select | 点查询 | SELECT WHERE id=?，测试索引查询性能 |
oltp_range_select | 范围查询 | SELECT WHERE id BETWEEN ? AND ? |
oltp_insert | 插入 | INSERT 操作 |
oltp_update_index | 索引更新 | UPDATE WHERE id=?（更新索引列） |
oltp_update_non_index | 非索引更新 | UPDATE WHERE id=?（更新非索引列） |
oltp_delete | 删除 | DELETE WHERE id=? |
oltp_begin_commit | 事务提交 | BEGIN + COMMIT，测试事务开销 |
oltp_begin_rollback | 事务回滚 | BEGIN + ROLLBACK，测试回滚开销 |
4.2 oltp_read_write 详解
oltp_read_write 是最常用的 OLTP 测试，它在一个事务中混合了多种操作。
4.2.1 事务结构
每个事务包含以下操作（按顺序执行）：
| 步骤 | 操作 | SQL 示例 |
|---|---|---|
| 1 | BEGIN | BEGIN |
| 2 | 点查询 | SELECT c FROM sbtest1 WHERE id = ? |
| 3 | 范围查询 | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ? |
| 4 | 范围 SUM | SELECT SUM(k) FROM sbtest1 WHERE id BETWEEN ? AND ? |
| 5 | 范围 ORDER BY | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ? ORDER BY c |
| 6 | 范围 DISTINCT | SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ? ORDER BY c |
| 7 | 索引更新 | UPDATE sbtest1 SET k = k + 1 WHERE id = ? |
| 8 | 非索引更新 | UPDATE sbtest1 SET c = ? WHERE id = ? |
| 9 | 删除 | DELETE FROM sbtest1 WHERE id = ? |
| 10 | 插入 | INSERT INTO sbtest1 VALUES (?, ?, ?, ?) |
| 11 | COMMIT | COMMIT |
4.2.2 表结构
Sysbench 自动创建名为 sbtest1、sbtest2 … 的表，结构如下：
CREATE TABLE sbtest1 (
id INT NOT NULL AUTO_INCREMENT,
k INT NOT NULL DEFAULT 0,
c CHAR(120) NOT NULL DEFAULT '',
pad CHAR(60) NOT NULL DEFAULT '',
PRIMARY KEY (id),
KEY k (k)
) ENGINE=InnoDB;
| 字段 | 类型 | 说明 |
|---|---|---|
id | INT (PK, AUTO_INCREMENT) | 主键 |
k | INT | 索引列，用于更新操作 |
c | CHAR(120) | 120 字符随机字符串 |
pad | CHAR(60) | 60 字符随机字符串 |
4.2.3 基本用法
# 1. 准备数据（创建 16 张表，每张表 100 万行）
sysbench oltp_read_write \
--mysql-host=127.0.0.1 \
--mysql-port=3306 \
--mysql-user=root \
--mysql-password=secret \
--mysql-db=sbtest \
--tables=16 \
--table-size=1000000 \
prepare
# 2. 运行测试
sysbench oltp_read_write \
--mysql-host=127.0.0.1 \
--mysql-port=3306 \
--mysql-user=root \
--mysql-password=secret \
--mysql-db=sbtest \
--tables=16 \
--table-size=1000000 \
--threads=16 \
--time=300 \
--histogram \
--percentile=99 \
run
# 3. 清理
sysbench oltp_read_write \
--mysql-host=127.0.0.1 \
--mysql-user=root \
--mysql-password=secret \
--mysql-db=sbtest \
--tables=16 \
cleanup
4.2.4 输出示例
SQL statistics:
queries performed:
read: 456789 ← 读查询总数
write: 130567 ← 写查询总数
other: 65234 ← 其他查询（BEGIN/COMMIT）
total: 652590 ← 总查询数
transactions: 32617 (1087.23 per sec.) ← 事务总数 (TPS)
queries: 652590 (21754.60 per sec.) ← 查询总数 (QPS)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 30.0034s
total number of events: 32617
Latency (ms):
min: 2.89
avg: 14.71
max: 128.45
95th percentile: 22.69
sum: 479812.34
Threads fairness:
events (avg/stddev): 2038.5625/12.34
execution time (avg/stddev): 29.9883/0.01
4.2.5 关键指标解读
| 指标 | 含义 | 示例值 |
|---|---|---|
| transactions/sec | 每秒事务数（TPS） | 1087.23 |
| queries/sec | 每秒查询数（QPS） | 21754.60 |
| read / write / other | 读/写/其他操作比例 | 70% / 20% / 10% |
| 95th percentile | P95 延迟 | 22.69 ms |
| avg latency | 平均延迟 | 14.71 ms |
4.3 oltp_read_only 详解
oltp_read_only 只包含读操作，适用于评估只读工作负载下的数据库性能。
4.3.1 事务结构
| 步骤 | 操作 | SQL |
|---|---|---|
| 1 | BEGIN | BEGIN |
| 2 | 点查询 | SELECT c FROM sbtest WHERE id = ? |
| 3 | 范围查询 | SELECT c FROM sbtest WHERE id BETWEEN ? AND ? |
| 4 | 范围 SUM | SELECT SUM(k) FROM sbtest WHERE id BETWEEN ? AND ? |
| 5 | 范围 ORDER BY | SELECT c FROM sbtest WHERE id BETWEEN ? AND ? ORDER BY c |
| 6 | 范围 DISTINCT | SELECT DISTINCT c FROM sbtest WHERE id BETWEEN ? AND ? ORDER BY c |
| 7 | COMMIT | COMMIT |
4.3.2 用法
# 准备数据（如果还未准备）
sysbench oltp_read_only \
--mysql-host=127.0.0.1 \
--mysql-user=root \
--mysql-password=secret \
--tables=16 \
--table-size=1000000 \
prepare
# 运行只读测试
sysbench oltp_read_only \
--mysql-host=127.0.0.1 \
--mysql-user=root \
--mysql-password=secret \
--tables=16 \
--table-size=1000000 \
--threads=32 \
--time=300 \
--histogram \
run
4.3.3 适用场景
| 场景 | 说明 |
|---|---|
| 读写分离测试 | 评估只读从库的处理能力 |
| 缓存命中率测试 | 数据量 < Buffer Pool 时，几乎全缓存命中 |
| 查询优化验证 | 验证索引优化、SQL 优化的效果 |
| 只读副本评估 | 云数据库只读副本的性能基准 |
4.4 其他 OLTP 测试
4.4.1 oltp_write_only
只包含写操作，评估纯写入性能：
sysbench oltp_write_only \
--mysql-host=127.0.0.1 \
--mysql-user=root \
--mysql-password=secret \
--tables=16 \
--table-size=1000000 \
--threads=16 \
--time=300 \
run
事务结构：BEGIN → DELETE → INSERT → UPDATE(索引) → UPDATE(非索引) → COMMIT
4.4.2 oltp_point_select
最简单的点查询测试，适合评估索引查询的极限性能：
sysbench oltp_point_select \
--mysql-host=127.0.0.1 \
--mysql-user=root \
--mysql-password=secret \
--tables=16 \
--table-size=1000000 \
--threads=64 \
--time=60 \
run
每条操作：SELECT c FROM sbtest WHERE id = ?
提示：
oltp_point_select通常能达到很高的 QPS（数万到数十万），适合作为数据库响应能力的极端基准。
4.4.3 oltp_update_index vs oltp_update_non_index
# 更新索引列 k（更慢，因为需要更新索引）
sysbench oltp_update_index \
--mysql-host=127.0.0.1 \
--mysql-user=root \
--tables=16 \
--table-size=1000000 \
--threads=16 \
--time=60 \
run
# 更新非索引列 c（更快，不需要更新索引）
sysbench oltp_update_non_index \
--mysql-host=127.0.0.1 \
--mysql-user=root \
--tables=16 \
--table-size=1000000 \
--threads=16 \
--time=60 \
run
对比意义：两者的性能差异反映了索引维护的开销。
4.4.4 oltp_insert
sysbench oltp_insert \
--mysql-host=127.0.0.1 \
--mysql-user=root \
--mysql-password=secret \
--tables=16 \
--threads=32 \
--time=300 \
run
适用场景：
- 评估 INSERT 性能上限
- 测试自增主键的竞争（auto-increment lock contention）
- 对比不同存储引擎的写入能力
4.4.5 oltp_begin_commit 与 oltp_begin_rollback
这两个测试纯粹测量事务开销：
# 测量 BEGIN + COMMIT 开销
sysbench oltp_begin_commit \
--mysql-host=127.0.0.1 \
--mysql-user=root \
--threads=32 \
--time=60 \
run
# 测量 BEGIN + ROLLBACK 开销
sysbench oltp_begin_rollback \
--mysql-host=127.0.0.1 \
--mysql-user=root \
--threads=32 \
--time=60 \
run
4.5 OLTP 通用选项
4.5.1 表与数据选项
| 选项 | 默认值 | 说明 |
|---|---|---|
--tables=N | 1 | 测试表数量 |
--table-size=N | 10000 | 每张表的行数 |
--table-engine=STRING | innodb | 存储引擎（innodb / myisam / tokudb 等） |
--auto-inc[=on/off] | on | 是否使用自增主键 |
--create-secondary[=on/off] | on | 是否创建 k 列的索引 |
--create-table-options=STRING | - | 额外的建表选项 |
# 创建 32 张表，每张 500 万行，使用 InnoDB
sysbench oltp_read_write \
--mysql-host=127.0.0.1 \
--mysql-user=root \
--tables=32 \
--table-size=5000000 \
--table-engine=innodb \
prepare
# 禁用二级索引（测试无索引场景）
sysbench oltp_read_write \
--create-secondary=off \
--tables=16 \
--table-size=1000000 \
prepare
# 额外的建表选项
sysbench oltp_read_write \
--create-table-options="ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8" \
--tables=16 \
prepare
4.5.2 查询选项
| 选项 | 默认值 | 说明 |
|---|---|---|
--range-size=N | 100 | 范围查询的范围大小 |
--point-selects=N | 10 | 每个事务中的点查询数量 |
--simple-ranges=N | 1 | 每个事务中的简单范围查询数量 |
--sum-ranges=N | 1 | 每个事务中的 SUM 范围查询数量 |
--order-ranges=N | 1 | 每个事务中的 ORDER BY 范围查询数量 |
--distinct-ranges=N | 1 | 每个事务中的 DISTINCT 范围查询数量 |
--index-updates=N | 1 | 每个事务中的索引更新数量 |
--non-index-updates=N | 1 | 每个事务中的非索引更新数量 |
--delete-inserts=N | 1 | 每个事务中的删除-插入对数量 |
--skip-trx[=on/off] | off | 跳过 BEGIN/COMMIT（自动提交模式） |
--secondary[=on/off] | off | 使用二级索引作为辅助索引查找 |
--create-secondary[=on/off] | on | 是否创建辅助索引 |
# 自定义读写比例
# 增加点查询数量，减少写操作
sysbench oltp_read_write \
--mysql-host=127.0.0.1 \
--mysql-user=root \
--point-selects=20 \
--simple-ranges=5 \
--sum-ranges=5 \
--order-ranges=5 \
--distinct-ranges=5 \
--index-updates=1 \
--non-index-updates=1 \
--delete-inserts=1 \
--tables=16 \
--table-size=1000000 \
--threads=16 \
--time=300 \
run
# 自动提交模式（不使用事务）
sysbench oltp_read_write \
--skip-trx=on \
--mysql-host=127.0.0.1 \
--mysql-user=root \
--tables=16 \
--table-size=1000000 \
--threads=16 \
--time=60 \
run
4.6 综合测试方案
4.6.1 全面性能评估方案
#!/bin/bash
# full_oltp_benchmark.sh - 全面 OLTP 性能评估
MYSQL_HOST="127.0.0.1"
MYSQL_USER="root"
MYSQL_PASS="secret"
TABLES=16
TABLE_SIZE=1000000
DURATION=300
RESULT_DIR="./results_$(date +%Y%m%d_%H%M%S)"
mkdir -p "$RESULT_DIR"
# 测试列表
TESTS="oltp_read_write oltp_read_only oltp_write_only oltp_point_select oltp_update_index oltp_update_non_index oltp_insert"
# 准备数据
echo ">>> Preparing data..."
sysbench oltp_read_write \
--mysql-host=$MYSQL_HOST --mysql-user=$MYSQL_USER --mysql-password=$MYSQL_PASS \
--tables=$TABLES --table-size=$TABLE_SIZE prepare
# 预热
echo ">>> Warming up..."
sysbench oltp_read_write \
--mysql-host=$MYSQL_HOST --mysql-user=$MYSQL_USER --mysql-password=$MYSQL_PASS \
--tables=$TABLES --table-size=$TABLE_SIZE \
--threads=8 --time=60 run > /dev/null 2>&1
# 运行各种测试
for test in $TESTS; do
for threads in 1 4 8 16 32 64; do
echo ">>> $test with $threads threads"
sysbench $test \
--mysql-host=$MYSQL_HOST --mysql-user=$MYSQL_USER --mysql-password=$MYSQL_PASS \
--tables=$TABLES --table-size=$TABLE_SIZE \
--threads=$threads --time=$DURATION \
--percentile=99 --histogram \
--json="$RESULT_DIR/${test}_t${threads}.json" \
run 2>&1 | tee "$RESULT_DIR/${test}_t${threads}.txt"
sleep 10
done
done
# 清理
sysbench oltp_read_write \
--mysql-host=$MYSQL_HOST --mysql-user=$MYSQL_USER --mysql-password=$MYSQL_PASS \
--tables=$TABLES cleanup
echo "Results saved to $RESULT_DIR"
4.6.2 渐进式压力测试
#!/bin/bash
# progressive_load.sh - 渐进式加压测试
for threads in 1 2 4 8 16 32 64 128 256; do
echo "============================================"
echo "Threads: $threads"
echo "============================================"
sysbench oltp_read_write \
--mysql-host=127.0.0.1 \
--mysql-user=root \
--mysql-password=secret \
--tables=16 \
--table-size=1000000 \
--threads=$threads \
--time=60 \
run 2>&1 | grep -E "(transactions|queries|avg|95th|total time)"
echo ""
sleep 5
done
预期结果趋势：
线程数 TPS P95延迟(ms)
1 ~200 ~5
2 ~400 ~5
4 ~780 ~5
8 ~1400 ~6
16 ~2200 ~8 ← 性能拐点附近
32 ~2800 ~15 ← 延迟开始上升
64 ~3000 ~30 ← 接近系统极限
128 ~2900 ~60 ← 开始过载，TPS 下降
256 ~2500 ~120 ← 严重过载
4.7 不同 OLTP 测试的性能对比
| 测试类型 | 预期相对 TPS | 典型瓶颈 |
|---|---|---|
oltp_point_select | ⭐⭐⭐⭐⭐ 最高 | 索引查找、Buffer Pool 命中率 |
oltp_read_only | ⭐⭐⭐⭐ 高 | 范围扫描、排序、临时表 |
oltp_write_only | ⭐⭐⭐ 中 | Redo Log、Undo Log、锁竞争 |
oltp_read_write | ⭐⭐⭐ 中 | 读写混合，综合瓶颈 |
oltp_insert | ⭐⭐⭐⭐ 较高 | 自增锁、B+Tree 分裂 |
oltp_update_index | ⭐⭐ 较低 | 索引维护、锁竞争 |
oltp_delete | ⭐⭐ 较低 | B+Tree 合并、页分裂 |
4.8 OLTP 测试与数据库配置验证
4.8.1 Buffer Pool 大小验证
# 测试 1: Buffer Pool = 1GB
# my.cnf: innodb_buffer_pool_size = 1G
sysbench oltp_read_write \
--mysql-host=127.0.0.1 --mysql-user=root \
--tables=32 --table-size=1000000 \
--threads=16 --time=300 run | tee bp_1g.txt
# 测试 2: Buffer Pool = 8GB
# my.cnf: innodb_buffer_pool_size = 8G
sysbench oltp_read_write \
--mysql-host=127.0.0.1 --mysql-user=root \
--tables=32 --table-size=1000000 \
--threads=16 --time=300 run | tee bp_8g.txt
# 对比
echo "=== 1GB Buffer Pool ===" && grep -E "transactions|95th" bp_1g.txt
echo "=== 8GB Buffer Pool ===" && grep -E "transactions|95th" bp_8g.txt
4.8.2 Redo Log 大小验证
# 对比不同 innodb_log_file_size 的影响
# MySQL 8.0.30+ 使用 innodb_redo_log_capacity
# 测试小 Redo Log
mysql -e "ALTER INSTANCE DISABLE INNODB REDO_LOG;"
mysql -e "SET GLOBAL innodb_redo_log_capacity = '512M';"
sysbench oltp_write_only --mysql-host=127.0.0.1 --mysql-user=root \
--tables=16 --table-size=1000000 --threads=32 --time=120 run | tee redo_512m.txt
# 测试大 Redo Log
mysql -e "SET GLOBAL innodb_redo_log_capacity = '4G';"
sysbench oltp_write_only --mysql-host=127.0.0.1 --mysql-user=root \
--tables=16 --table-size=1000000 --threads=32 --time=120 run | tee redo_4g.txt
4.9 注意事项
4.9.1 数据一致性
- OLTP 测试会修改数据，
prepare后的数据分布会随着run而变化 - 多次测试前建议重新
prepare，确保数据一致性 oltp_read_write中的 DELETE 会删除行，导致表数据逐渐减少
4.9.2 prepare 性能
大量数据的 prepare 可能耗时较长：
# 优化 prepare 速度的 MySQL 参数
mysql -e "SET GLOBAL innodb_flush_log_at_trx_commit = 0;"
mysql -e "SET GLOBAL sync_binlog = 0;"
mysql -e "SET GLOBAL innodb_autoinc_lock_mode = 2;"
# 准备完成后恢复
mysql -e "SET GLOBAL innodb_flush_log_at_trx_commit = 1;"
mysql -e "SET GLOBAL sync_binlog = 1;"
4.9.3 多表数量
- 表数量应至少等于线程数，避免单表热点
- 推荐：
--tables >= --threads - 太多的表（>100）会增加 prepare 时间和元数据开销
4.10 小结
| 测试名称 | 用途 | 推荐场景 |
|---|---|---|
oltp_read_write | 读写混合 | 全面性能评估（首选） |
oltp_read_only | 只读 | 读副本/报表查询评估 |
oltp_write_only | 只写 | 写入性能/Redo Log 评估 |
oltp_point_select | 点查询 | 索引性能极限测试 |
oltp_update_index | 索引更新 | 索引维护开销评估 |
oltp_insert | 插入 | 写入吞吐量测试 |