1. Sysbench – 全能型基准测试工具
– CPU性能测试
– 内存性能测试
– 磁盘I/O测试
– 数据库OLTP测试

2. mysqlslap – MySQL自带的负载模拟工具
– 模拟多客户端并发
– 自动生成测试SQL
– 快速压力测试

3. TPC-C – 行业标准OLTP测试
– 模拟仓库订单处理
– 复杂事务处理
– 标准化测试流程

4. TPC-H – 决策支持系统测试
– 复杂查询测试
– 大数据量分析
– 多表关联查询

5. YCSB – Yahoo! Cloud Serving Benchmark
– 云数据库测试
– 多种工作负载
– 可扩展性测试

1. 安装Sysbench
yum install sysbench -y

输出示例：
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
* base: mirrors.aliyun.com
* extras: mirrors.aliyun.com
* updates: mirrors.aliyun.com
Resolving Dependencies
–> Running transaction check
—> Package sysbench.x86_64 0:1.0.20-6.el7 will be installed
–> Processing Dependency: libmysqlclient.so.18 for package: sysbench-1.0.20-6.el7.x86_64
…
Installed:
sysbench.x86_64 0:1.0.20-6.el7

Complete!

2. 验证安装
sysbench –version

输出示例：
sysbench 1.0.20

3. 查看帮助
sysbench –help

输出示例：
Usage:
sysbench [options]… [testname] [command]

Commands implemented by most tests: prepare run cleanup help

General options:
–threads=N number of threads to use [1]
–events=N limit for total number of events [0]
–time=N limit for total execution time in seconds [10]
–forced-shutdown=STRING number of seconds to wait after the –time limit before forcing shutdown, or ‘off’ to disable [off]
–thread-stack-size=SIZE size of stack per thread [64K]
–rate=N average transactions rate. 0 for unlimited rate [0]
–report-interval=N periodically report intermediate statistics with a specified interval in seconds. 0 disables intermediate reports [0]
–report-checkpoints=[LIST,…] dump full statistics and reset all counters at specified points in time. The argument is a list of comma-separated values representing the amount of time in seconds elapsed from start of test when report checkpoint(s) must be performed. Report checkpoints are off by default. []
–debug[=on|off] print more debugging info [off]
–validate[=on|off] perform validation checks where possible [off]
–help[=on|off] print help and exit [off]
–version[=on|off] print version and exit [off]
–config-file=FILENAME File containing command line options
–tx-rate=N deprecated alias for –rate [0]
–max-requests=N deprecated alias for –events [0]
–max-time=N deprecated alias for –time [0]
–num-threads=N deprecated alias for –threads [1]

Log options:
–verbosity=N verbosity level [3]

–percentile=N percentile to calculate in latency statistics (1-100). Use the special value of 0 to disable percentile calculations [95]
–histogram[=on|off] print latency histogram in report [off]

General database options:

–db-driver=STRING specifies database driver to use (‘help’ to get list of available drivers) [mysql]
–db-ps-mode=STRING prepared statements usage mode {auto, disable} [auto]
–db-debug[=on|off] print database-specific debug information [off]

mysql options:
–mysql-host=[LIST,…] MySQL server host [localhost]
–mysql-port=[LIST,…] MySQL server port [3306]
–mysql-socket=[LIST,…] MySQL socket
–mysql-user=STRING MySQL user [sbtest]
–mysql-password=STRING MySQL password []
–mysql-db=STRING MySQL database name [sbtest]
–mysql-ssl=[on|off|REQUIRED] use SSL connections [off]
–mysql-ssl-cipher=STRING use specific cipher for SSL connections []
–mysql-compression[=on|off] use compression [off]
–mysql-debug[=on|off] trace all MySQL queries [off]
–mysql-ignore-errors=[LIST,…] list of errors to ignore, or “all” [1213,1020,1205]
–mysql-dry-run[=on|off] Dry run, pretend that all MySQL client API calls are successful without executing them [off]

Compiled-in database drivers:
mysql – MySQL driver

Compiled-in tests:
fileio – File I/O test
cpu – CPU performance test
memory – Memory functions speed test
threads – Threads subsystem performance test
mutex – Mutex performance test

1. 准备测试数据
sysbench oltp_read_write \
–mysql-host=localhost \
–mysql-port=3306 \
–mysql-user=root \
–mysql-password=password \
–mysql-db=sbtest \
–tables=10 \
–table-size=100000 \
prepare

输出示例：
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)

Creating table ‘sbfgtest1’…
Inserting 100000 records into ‘sbfgtest1’
Creating a secondary index on ‘sbfgtest1’…
Creating table ‘sbfgtest2’…
Inserting 100000 records into ‘sbfgtest2’
Creating a secondary index on ‘sbfgtest2’…
…
Creating table ‘sbfgtest10’…
Inserting 100000 records into ‘sbfgtest10’
Creating a secondary index on ‘sbfgtest10’…

2. 执行OLTP测试
sysbench oltp_read_write \
–mysql-host=localhost \
–mysql-port=3306 \
–mysql-user=root \
–mysql-password=password \
–mysql-db=sbtest \
–tables=10 \
–table-size=100000 \
–threads=16 \
–time=60 \
–report-interval=10 \
run

输出示例：
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)

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: 2345.67 qps: 46913.45 (r/w/o: 32839.42/9382.69/4691.34) lat (ms,95%): 8.43 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 16 tps: 2389.12 qps: 47782.34 (r/w/o: 33447.64/9556.47/4778.23) lat (ms,95%): 8.12 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 16 tps: 2412.34 qps: 48246.78 (r/w/o: 33772.75/9649.36/4824.68) lat (ms,95%): 7.98 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 16 tps: 2398.56 qps: 47971.23 (r/w/o: 33579.86/9594.25/4797.12) lat (ms,95%): 8.05 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 16 tps: 2423.89 qps: 48477.89 (r/w/o: 33934.52/9695.58/4847.79) lat (ms,95%): 7.89 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 16 tps: 2401.23 qps: 48024.56 (r/w/o: 33617.19/9604.91/4802.46) lat (ms,95%): 8.01 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 2012801
write: 575086
other: 287543
total: 2875430
transactions: 143771 (2395.67 per sec.)
queries: 2875430 (47913.45 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)

General statistics:
total time: 60.0012s
total number of events: 143771

Latency (ms):
min: 1.23
avg: 6.67
max: 123.45
95th percentile: 8.12
sum: 959234.56

Threads fairness:
events (avg/stddev): 8985.6875/123.45
execution time (avg/stddev): 59.9521/0.01

3. 清理测试数据
sysbench oltp_read_write \
–mysql-host=localhost \
–mysql-port=3306 \
–mysql-user=root \
–mysql-password=password \
–mysql-db=sbtest \
–tables=10 \
cleanup

输出示例：
Dropping table ‘sbfgtest1’…
Dropping table ‘sbfgtest2’…
…
Dropping table ‘sbfgtest10’…

1. CPU性能测试
sysbench cpu –cpu-max-prime=20000 –threads=16 run

输出示例：
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:
Number of threads: 16
Initializing random number generator from current time

Prime numbers limit: 20000

Initializing worker threads…

Threads started!

CPU speed:
events per second: 1234.56

General statistics:
total time: 10.0001s
total number of events: 12346

Latency (ms):
min: 0.81
avg: 12.95
max: 45.67
95th percentile: 15.83
sum: 159876.54

Threads fairness:
events (avg/stddev): 771.6250/23.45
execution time (avg/stddev): 9.9923/0.01

2. 内存性能测试
sysbench memory –memory-block-size=1K –memory-total-size=10G –threads=16 run

输出示例：
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:
Number of threads: 16
Block size: 1KiB
Total size: 10240MiB
Operation: write
Scope: global

Initializing worker threads…

Threads started!

Total operations: 10485760 (1234567.89 per second)

10240.00 MiB transferred (1205.67 MiB/sec)

General statistics:
total time: 8.4932s
total number of events: 10485760

Latency (ms):
min: 0.00
avg: 0.01
max: 5.67
95th percentile: 0.01
sum: 123456.78

Threads fairness:
events (avg/stddev): 655360.0000/1234.56
execution time (avg/stddev): 7.7160/0.12

3. 磁盘I/O测试
sysbench fileio –file-total-size=10G –file-test-mode=rndrw –threads=16 prepare
sysbench fileio –file-total-size=10G –file-test-mode=rndrw –threads=16 run
sysbench fileio –file-total-size=10G –file-test-mode=rndrw cleanup

输出示例：
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:
Number of threads: 16
Initializing random number generator from current time

Extra file open flags: (none)
128 files, 80MiB each
10GiB total file size
Block size 16KiB
Number of IO requests: 0
Read/Write ratio for combined random IO test: 1.50
Periodic FSYNC enabled, calling fsync() each 100 requests.
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random r/w test
Initializing worker threads…

Threads started!

File operations:
reads/s: 12345.67
writes/s: 8230.45
fsyncs/s: 2633.89

Throughput:
read, MiB/s: 192.90
written, MiB/s: 128.60

General statistics:
total time: 100.0001s
total number of events: 2312345

Latency (ms):
min: 0.01
avg: 0.69
max: 23.45
95th percentile: 1.89
sum: 1592345.67

Threads fairness:
events (avg/stddev): 144521.5625/1234.56
execution time (avg/stddev): 99.5216/0.23

1. 自动生成测试
mysqlslap –host=localhost –port=3306 –user=root –password=password \
–auto-generate-sql –number-of-queries=10000 –concurrency=50 \
–iterations=3 –debug-info

输出示例：
Benchmark
Average number of seconds to run all queries: 2.345 seconds
Minimum number of seconds to run all queries: 2.123 seconds
Maximum number of seconds to run all queries: 2.567 seconds
Number of clients running queries: 50
Average number of queries per client: 200

User time 1.23, System time 0.45
Maximum resident set size 12345, Integral resident set size 0
Non-physical pagefaults 1234, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 12345, Involuntary context switches 567

2. 指定数据库测试
mysqlslap –host=localhost –port=3306 –user=root –password=password \
–concurrency=50 –iterations=3 \
–query=”SELECT * FROM users WHERE id = 1″ \
–create-schema=production_db

输出示例：
Benchmark
Average number of seconds to run all queries: 1.234 seconds
Minimum number of seconds to run all queries: 1.123 seconds
Maximum number of seconds to run all queries: 1.345 seconds
Number of clients running queries: 50
Average number of queries per client: 200

3. 复杂查询测试
mysqlslap –host=localhost –port=3306 –user=root –password=password \
–concurrency=100 –iterations=5 \
–query=”SELECT u.*, o.* FROM users u JOIN orders o ON u.id = o.user_id WHERE u.status = ‘active'” \
–create-schema=production_db

输出示例：
Benchmark
Average number of seconds to run all queries: 5.678 seconds
Minimum number of seconds to run all queries: 5.234 seconds
Maximum number of seconds to run all queries: 6.123 seconds
Number of clients running queries: 100
Average number of queries per client: 200

4. 混合负载测试
mysqlslap –host=localhost –port=3306 –user=root –password=password \
–concurrency=50,100,200 \
–iterations=3 \
–number-of-queries=10000 \
–auto-generate-sql-load-type=mixed \
–auto-generate-sql-add-autoincrement \
–auto-generate-sql-write-number=100 \
–auto-generate-sql-guid-primary

输出示例：
Benchmark
Average number of seconds to run all queries: 8.901 seconds
Minimum number of seconds to run all queries: 8.234 seconds
Maximum number of seconds to run all queries: 9.567 seconds
Number of clients running queries: 200
Average number of queries per client: 50

1. 关键性能指标
– TPS (Transactions Per Second) – 每秒事务数
– QPS (Queries Per Second) – 每秒查询数
– 延迟 (Latency) – 响应时间
– 吞吐量 (Throughput) – 数据处理能力

2. 性能指标解读
# Sysbench结果示例分析

transactions: 143771 (2395.67 per sec.)
# TPS = 2395.67，表示每秒处理2395个事务

queries: 2875430 (47913.45 per sec.)
# QPS = 47913.45，表示每秒执行47913个查询

Latency (ms):
min: 1.23 # 最小延迟1.23毫秒
avg: 6.67 # 平均延迟6.67毫秒
max: 123.45 # 最大延迟123.45毫秒
95th percentile: 8.12 # 95%的请求在8.12毫秒内完成

3. 性能瓶颈识别
# 查看MySQL性能指标
mysql> SHOW GLOBAL STATUS LIKE ‘Threads_running’;
mysql> SHOW GLOBAL STATUS LIKE ‘Threads_connected’;
mysql> SHOW GLOBAL STATUS LIKE ‘Innodb_buffer_pool_reads’;
mysql> SHOW GLOBAL STATUS LIKE ‘Innodb_buffer_pool_read_requests’;

# 计算缓冲池命中率
mysql> SELECT
-> (1 – (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)) * 100
-> AS buffer_pool_hit_ratio
-> FROM performance_schema.global_status
-> WHERE variable_name IN (‘Innodb_buffer_pool_reads’, ‘Innodb_buffer_pool_read_requests’);

输出示例：
+———————–+
| buffer_pool_hit_ratio |
+———————–+
| 98.56 |
+———————–+
# 命中率98.56%，说明缓冲池配置合理

4. 性能对比分析
# 不同配置的性能对比

配置A (16GB内存):
– TPS: 2395.67
– QPS: 47913.45
– 平均延迟: 6.67ms

配置B (32GB内存):
– TPS: 3456.78
– QPS: 69135.67
– 平均延迟: 4.56ms

性能提升:
– TPS提升: (3456.78 – 2395.67) / 2395.67 * 100% = 44.3%
– QPS提升: (69135.67 – 47913.45) / 47913.45 * 100% = 44.3%
– 延迟降低: (6.67 – 4.56) / 6.67 * 100% = 31.6%

1. 测试环境准备
– 使用生产环境相似的硬件配置
– 确保测试数据量与生产环境相当
– 关闭不必要的后台进程
– 预热数据库缓存

2. 测试场景设计
– 模拟真实的业务负载
– 包含读写混合操作
– 考虑并发用户数
– 设计合理的测试时长

3. 数据收集
– 记录系统资源使用情况
– 收集MySQL性能指标
– 保存测试结果和配置
– 记录测试环境和条件

4. 结果分析
– 多次测试取平均值
– 分析性能瓶颈
– 对比不同配置的性能
– 制定优化方案

5. 持续监控
– 定期进行基准测试
– 跟踪性能变化趋势
– 及时发现性能退化
– 验证优化效果