数据库性能测试与基准测试
大约 18 分钟约 5303 字
数据库性能测试与基准测试
简介
数据库性能测试是评估数据库系统在不同工作负载下表现的关键实践。它不仅帮助团队了解系统的性能边界，还为容量规划、硬件选型和架构决策提供数据支撑。基准测试（Benchmark）则是性能测试的核心方法，通过标准化的测试流程产生可重复、可比较的性能指标。
与功能测试不同，性能测试关注的是系统在压力下的行为表现——响应时间、吞吐量、资源利用率和稳定性。一个经过充分性能测试的数据库系统，能够在生产环境中提供可预测的性能表现，避免突发性能问题导致的业务中断。
特点
- 可重复性：标准化的测试流程确保结果可复现
- 可量化：通过 TPS、QPS、延迟等指标精确衡量性能
- 可对比：不同版本、配置或硬件之间的性能差异一目了然
- 预见性：通过容量规划预测未来性能需求
- 回归性：集成到 CI/CD 流水线中检测性能退化
性能测试方法论
性能测试分类
性能测试类型：
负载测试（Load Testing）
├── 模拟正常业务负载
├── 验证系统满足性能要求
└── 确定正常工作范围
压力测试（Stress Testing）
├── 逐步增加负载至系统极限
├── 发现系统瓶颈和崩溃点
└── 验证恢复能力
耐久测试（Endurance Testing / Soak Testing）
├── 长时间持续运行
├── 检测内存泄漏、资源耗尽
└── 验证稳定性
尖峰测试（Spike Testing）
├── 突发大量流量冲击
├── 验证弹性伸缩能力
└── 测试降级策略
容量测试（Volume Testing）
├── 大数据量下的性能表现
├── 确定数据容量上限
└── 测试分区和归档策略性能测试流程
测试流程：
1. 需求分析
└── 确定性能目标和SLA
2. 测试设计
├── 选择测试工具
├── 设计测试场景
└── 准备测试数据
3. 环境搭建
├── 硬件配置
├── 数据库参数调优
└── 网络环境模拟
4. 基线建立
└── 记录初始性能指标
5. 测试执行
├── 负载测试
├── 压力测试
└── 耐久测试
6. 结果分析
├── 瓶颈定位
└── 性能报告
7. 优化验证
└── 优化后回归测试性能指标详解
核心性能指标
// 性能指标数据模型
public class PerformanceMetrics
{
// 吞吐量指标
public decimal TPS { get; set; } // Transactions Per Second
public decimal QPS { get; set; } // Queries Per Second
public decimal RPS { get; set; } // Requests Per Second
// 延迟指标（毫秒）
public decimal AvgLatency { get; set; } // 平均延迟
public decimal P50Latency { get; set; } // 中位数延迟
public decimal P90Latency { get; set; } // 90百分位延迟
public decimal P95Latency { get; set; } // 95百分位延迟
public decimal P99Latency { get; set; } // 99百分位延迟
public decimal MaxLatency { get; set; } // 最大延迟
// 错误指标
public decimal ErrorRate { get; set; } // 错误率（%）
public int TimeoutCount { get; set; } // 超时次数
public int DeadlockCount { get; set; } // 死锁次数
// 资源指标
public decimal CpuUsage { get; set; } // CPU 使用率（%）
public decimal MemoryUsage { get; set; } // 内存使用率（%）
public long DiskIOPS { get; set; } // 磁盘 IOPS
public long DiskReadBytes { get; set; } // 磁盘读取字节
public long DiskWriteBytes { get; set; } // 磁盘写入字节
public long NetworkInBytes { get; set; } // 网络入流量
public long NetworkOutBytes { get; set; } // 网络出流量
// 数据库特定指标
public int ActiveConnections { get; set; } // 活跃连接数
public int WaitingConnections { get; set; } // 等待连接数
public long BufferPoolHitRatio { get; set; } // 缓冲池命中率
public long SlowQueryCount { get; set; } // 慢查询数量
}百分位数的重要性
// 百分位数计算工具
public class PercentileCalculator
{
public static double Calculate(List<double> values, double percentile)
{
if (values == null || values.Count == 0)
throw new ArgumentException("值列表不能为空");
var sorted = values.OrderBy(v => v).ToList();
int index = (int)Math.Ceiling(percentile / 100.0 * sorted.Count) - 1;
return sorted[Math.Max(0, index)];
}
public static LatencyReport GenerateReport(List<double> latencies)
{
return new LatencyReport
{
Min = latencies.Min(),
Max = latencies.Max(),
Average = latencies.Average(),
Median = Calculate(latencies, 50),
P90 = Calculate(latencies, 90),
P95 = Calculate(latencies, 95),
P99 = Calculate(latencies, 99),
P999 = Calculate(latencies, 99.9),
StdDev = CalculateStdDev(latencies)
};
}
private static double CalculateStdDev(List<double> values)
{
double avg = values.Average();
double sumSq = values.Sum(v => Math.Pow(v - avg, 2));
return Math.Sqrt(sumSq / values.Count);
}
}
public class LatencyReport
{
public double Min { get; set; }
public double Max { get; set; }
public double Average { get; set; }
public double Median { get; set; }
public double P90 { get; set; }
public double P95 { get; set; }
public double P99 { get; set; }
public double P999 { get; set; }
public double StdDev { get; set; }
}基准测试工具
sysbench
sysbench 是最常用的 MySQL/PostgreSQL 基准测试工具之一。
# 安装 sysbench
# Ubuntu/Debian
apt-get install sysbench
# CentOS/RHEL
yum install sysbench
# 准备测试数据（OLTP 读写混合测试）
sysbench oltp_read_write \
--db-driver=mysql \
--mysql-host=127.0.0.1 \
--mysql-port=3306 \
--mysql-user=root \
--mysql-password=your_password \
--mysql-db=sbtest \
--tables=10 \
--table-size=1000000 \
prepare
# 执行 OLTP 读写测试
sysbench oltp_read_write \
--db-driver=mysql \
--mysql-host=127.0.0.1 \
--mysql-port=3306 \
--mysql-user=root \
--mysql-password=your_password \
--mysql-db=sbtest \
--tables=10 \
--table-size=1000000 \
--threads=32 \
--time=300 \
--report-interval=10 \
run
# 清理测试数据
sysbench oltp_read_write \
--db-driver=mysql \
--mysql-host=127.0.0.1 \
--mysql-port=3306 \
--mysql-user=root \
--mysql-password=your_password \
--mysql-db=sbtest \
--tables=10 \
cleanup# sysbench 只读测试
sysbench oltp_read_only \
--db-driver=mysql \
--mysql-host=127.0.0.1 \
--mysql-port=3306 \
--mysql-user=root \
--mysql-password=your_password \
--mysql-db=sbtest \
--tables=10 \
--table-size=1000000 \
--threads=64 \
--time=600 \
--range-selects=false \
--skip-trx=true \
run
# sysbench 只写测试
sysbench oltp_write_only \
--db-driver=mysql \
--mysql-host=127.0.0.1 \
--mysql-port=3306 \
--mysql-user=root \
--mysql-password=your_password \
--mysql-db=sbtest \
--tables=10 \
--table-size=1000000 \
--threads=16 \
--time=300 \
run
# sysbench PostgreSQL 测试
sysbench oltp_read_write \
--db-driver=pgsql \
--pgsql-host=127.0.0.1 \
--pgsql-port=5432 \
--pgsql-user=postgres \
--pgsql-password=your_password \
--pgsql-db=sbtest \
--tables=10 \
--table-size=1000000 \
--threads=32 \
--time=300 \
prepareHammerDB
HammerDB 是支持多种数据库的基准测试工具，特别适合 TPC-C 和 TPC-H 测试。
# HammerDB TPC-C 测试脚本（MySQL）
# 以下为 HammerDB CLI 脚本示例
# 设置连接参数
dict set mysql_connection host 127.0.0.1
dict set mysql_connection port 3306
dict set mysql_connection user root
dict set mysql_connection password your_password
# 设置 TPC-C 参数
dict set tcpc count_ware 100 # 仓库数量（数据规模）
dict set tcpc num_vu 10 # 并发虚拟用户数
dict set tcpc duration 5 # 测试时长（分钟）
dict set tcpc rq_time 0 # 思考时间
# 构建 TPC-C Schema
mysql::buildschema
# 运行 TPC-C 测试
mysql::runbenchmark
# 输出结果
mysql::getresult# HammerDB 命令行模式
hammerdbcli
# 在 HammerDB CLI 中执行
hammerdb> dbset db mysql
hammerdb> dbset bm TPC-C
hammerdb> diset connection mysql_host 127.0.0.1
hammerdb> diset connection mysql_port 3306
hammerdb> diset tpcc mysql_count_ware 100
hammerdb> diset tpcc mysql_num_vu 16
hammerdb> diset tpcc mysql_duration 5
hammerdb> buildschema
hammerdb> vucreate
hammerdb> vucomplete自定义基准测试框架
// .NET 自定义数据库基准测试框架
public class DatabaseBenchmark
{
private readonly string _connectionString;
private readonly List<BenchmarkResult> _results = new();
private readonly CancellationTokenSource _cts = new();
public DatabaseBenchmark(string connectionString)
{
_connectionString = connectionString;
}
public async Task<BenchmarkReport> RunBenchmark(
BenchmarkConfig config,
Func<DbConnection, CancellationToken, Task> operation)
{
// 预热阶段
await WarmUp(config.WarmUpIterations, operation);
// 初始化结果收集
var latencies = new ConcurrentBag<double>();
var errors = new ConcurrentBag<Exception>();
var stopwatch = Stopwatch.StartNew();
long totalOperations = 0;
// 并发执行
var tasks = new List<Task>();
for (int i = 0; i < config.ConcurrentThreads; i++)
{
tasks.Add(Task.Run(async () =>
{
using var connection = new SqlConnection(_connectionString);
await connection.OpenAsync();
while (!_cts.Token.IsCancellationRequested &&
stopwatch.Elapsed < config.Duration)
{
var sw = Stopwatch.StartNew();
try
{
await operation(connection, _cts.Token);
sw.Stop();
latencies.Add(sw.Elapsed.TotalMilliseconds);
Interlocked.Increment(ref totalOperations);
}
catch (Exception ex)
{
sw.Stop();
errors.Add(ex);
}
}
}, _cts.Token));
}
await Task.WhenAll(tasks);
stopwatch.Stop();
return new BenchmarkReport
{
Duration = stopwatch.Elapsed,
TotalOperations = totalOperations,
TPS = totalOperations / stopwatch.Elapsed.TotalSeconds,
Latency = PercentileCalculator.GenerateReport(latencies.ToList()),
ErrorCount = errors.Count,
Config = config
};
}
private async Task WarmUp(
int iterations,
Func<DbConnection, CancellationToken, Task> operation)
{
using var connection = new SqlConnection(_connectionString);
await connection.OpenAsync();
for (int i = 0; i < iterations; i++)
{
await operation(connection, CancellationToken.None);
}
}
}
public class BenchmarkConfig
{
public int ConcurrentThreads { get; set; } = 16;
public TimeSpan Duration { get; set; } = TimeSpan.FromMinutes(5);
public int WarmUpIterations { get; set; } = 100;
public int RampUpSeconds { get; set; } = 30;
}
public class BenchmarkReport
{
public TimeSpan Duration { get; set; }
public long TotalOperations { get; set; }
public double TPS { get; set; }
public LatencyReport Latency { get; set; }
public int ErrorCount { get; set; }
public BenchmarkConfig Config { get; set; }
}测试场景设计
OLTP 场景
// OLTP 读写混合场景模拟
public class OltpBenchmarkScenario
{
private readonly DatabaseBenchmark _benchmark;
private readonly Random _random = new();
public OltpBenchmarkScenario(string connectionString)
{
_benchmark = new DatabaseBenchmark(connectionString);
}
public async Task RunMixedWorkload()
{
// 模拟典型 OLTP 负载：80% 读 + 20% 写
var config = new BenchmarkConfig
{
ConcurrentThreads = 32,
Duration = TimeSpan.FromMinutes(10),
WarmUpIterations = 500
};
var report = await _benchmark.RunBenchmark(config, async (conn, ct) =>
{
int operation = _random.Next(100);
if (operation < 50)
{
// 50% - 按主键查询
await PointQuery(conn, _random.Next(1, 1000000));
}
else if (operation < 70)
{
// 20% - 范围查询
await RangeQuery(conn, _random.Next(1, 900000), 100);
}
else if (operation < 85)
{
// 15% - 更新操作
await UpdateOperation(conn, _random.Next(1, 1000000));
}
else if (operation < 95)
{
// 10% - 插入操作
await InsertOperation(conn);
}
else
{
// 5% - 删除操作
await DeleteOperation(conn, _random.Next(1, 1000000));
}
});
PrintReport(report);
}
private async Task PointQuery(DbConnection conn, int id)
{
using var cmd = conn.CreateCommand();
cmd.CommandText = "SELECT * FROM Orders WHERE OrderId = @id";
((SqlCommand)cmd).Parameters.AddWithValue("@id", id);
using var reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync()) { }
}
private async Task RangeQuery(DbConnection conn, int startId, int count)
{
using var cmd = conn.CreateCommand();
cmd.CommandText = "SELECT * FROM Orders WHERE OrderId BETWEEN @start AND @end ORDER BY OrderId LIMIT 50";
((SqlCommand)cmd).Parameters.AddWithValue("@start", startId);
((SqlCommand)cmd).Parameters.AddWithValue("@end", startId + count);
using var reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync()) { }
}
private async Task UpdateOperation(DbConnection conn, int id)
{
using var cmd = conn.CreateCommand();
cmd.CommandText = "UPDATE Orders SET Status = @status, UpdatedAt = GETUTCDATE() WHERE OrderId = @id";
((SqlCommand)cmd).Parameters.AddWithValue("@id", id);
((SqlCommand)cmd).Parameters.AddWithValue("@status", "Processing");
await cmd.ExecuteNonQueryAsync();
}
private async Task InsertOperation(DbConnection conn)
{
using var cmd = conn.CreateCommand();
cmd.CommandText = @"INSERT INTO Orders (CustomerId, Amount, Status, CreatedAt)
VALUES (@customerId, @amount, @status, GETUTCDATE())";
((SqlCommand)cmd).Parameters.AddWithValue("@customerId", _random.Next(1, 10000));
((SqlCommand)cmd).Parameters.AddWithValue("@amount", _random.Next(10, 5000));
((SqlCommand)cmd).Parameters.AddWithValue("@status", "New");
await cmd.ExecuteNonQueryAsync();
}
private async Task DeleteOperation(DbConnection conn, int id)
{
using var cmd = conn.CreateCommand();
cmd.CommandText = "DELETE FROM Orders WHERE OrderId = @id AND Status = 'Cancelled'";
((SqlCommand)cmd).Parameters.AddWithValue("@id", id);
await cmd.ExecuteNonQueryAsync();
}
private void PrintReport(BenchmarkReport report)
{
Console.WriteLine("========== OLTP 基准测试报告 ==========");
Console.WriteLine($"测试时长: {report.Duration}");
Console.WriteLine($"总操作数: {report.TotalOperations}");
Console.WriteLine($"TPS: {report.TPS:F2}");
Console.WriteLine($"平均延迟: {report.Latency.Average:F2} ms");
Console.WriteLine($"P95 延迟: {report.Latency.P95:F2} ms");
Console.WriteLine($"P99 延迟: {report.Latency.P99:F2} ms");
Console.WriteLine($"最大延迟: {report.Latency.Max:F2} ms");
Console.WriteLine($"错误数: {report.ErrorCount}");
}
}OLAP 场景
-- TPC-H 风格的分析查询基准测试
-- Q1: 价格汇总报告
SELECT
l_returnflag,
l_linestatus,
SUM(l_quantity) AS sum_qty,
SUM(l_extendedprice) AS sum_base_price,
SUM(l_extendedprice * (1 - l_discount)) AS sum_disc_price,
SUM(l_extendedprice * (1 - l_discount) * (1 + l_tax)) AS sum_charge,
AVG(l_quantity) AS avg_qty,
AVG(l_extendedprice) AS avg_price,
AVG(l_discount) AS avg_disc,
COUNT(*) AS count_order
FROM lineitem
WHERE l_shipdate <= DATE_SUB(CURRENT_DATE, INTERVAL 90 DAY)
GROUP BY l_returnflag, l_linestatus
ORDER BY l_returnflag, l_linestatus;
-- Q2: 最低成本供应商
SELECT s_supplierkey, s_name, s_address, s_phone, total
FROM supplier s
JOIN (
SELECT ps.ps_suppkey,
ps.ps_supplycost,
SUM(ps.ps_supplycost * ps.ps_availqty) AS total
FROM partsupp ps
JOIN part p ON ps.ps_partkey = p.p_partkey
JOIN supplier s ON ps.ps_suppkey = s.s_suppkey
JOIN nation n ON s.s_nationkey = n.n_nationkey
JOIN region r ON n.n_regionkey = r.r_regionkey
WHERE r.r_name = 'ASIA'
AND p.p_size = 15
AND p.p_type LIKE '%BRASS'
GROUP BY ps.ps_suppkey, ps.ps_supplycost
) subq ON s.s_suppkey = subq.ps_suppkey
ORDER BY total DESC
LIMIT 100;测试数据生成
使用工具生成测试数据
-- MySQL 生成大规模测试数据
-- 使用存储过程批量生成
DELIMITER //
CREATE PROCEDURE GenerateTestOrders(
IN record_count INT,
IN batch_size INT
)
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE batch_counter INT DEFAULT 0;
WHILE i < record_count DO
START TRANSACTION;
INSERT INTO Orders (CustomerId, Amount, Status, CreatedAt)
SELECT
FLOOR(1 + RAND() * 10000),
ROUND(10 + RAND() * 4990, 2),
ELT(FLOOR(1 + RAND() * 5), 'New', 'Processing', 'Shipped', 'Delivered', 'Cancelled'),
DATE_ADD('2024-01-01', INTERVAL FLOOR(RAND() * 730) DAY)
FROM information_schema.columns c1
CROSS JOIN information_schema.columns c2
LIMIT LEAST(batch_size, record_count - i);
SET i = i + batch_size;
SET batch_counter = batch_counter + 1;
COMMIT;
-- 每批后短暂暂停，避免过载
IF batch_counter % 10 = 0 THEN
DO SLEEP(0.1);
END IF;
END WHILE;
SELECT CONCAT('已生成 ', i, ' 条测试数据') AS Result;
END //
DELIMITER ;
-- 生成 1000 万条测试数据
CALL GenerateTestOrders(10000000, 10000);// .NET 测试数据生成器
public class TestDataGenerator
{
private readonly string _connectionString;
private readonly Random _random = new();
public TestDataGenerator(string connectionString)
{
_connectionString = connectionString;
}
public async Task GenerateOrders(int totalCount, int batchSize = 5000)
{
using var connection = new SqlConnection(_connectionString);
await connection.OpenAsync();
var statuses = new[] { "New", "Processing", "Shipped", "Delivered", "Cancelled" };
for (int offset = 0; offset < totalCount; offset += batchSize)
{
using var transaction = connection.BeginTransaction();
var sb = new StringBuilder();
sb.AppendLine("INSERT INTO Orders (CustomerId, Amount, Status, CreatedAt) VALUES ");
int currentBatch = Math.Min(batchSize, totalCount - offset);
for (int i = 0; i < currentBatch; i++)
{
if (i > 0) sb.AppendLine(",");
sb.AppendFormat("(@c{0}, @a{0}, @s{0}, @d{0})",
i, _random.Next(1, 10000),
Math.Round(10 + _random.NextDouble() * 4990, 2),
statuses[_random.Next(statuses.Length)],
DateTime.Now.AddDays(-_random.Next(0, 730)).ToString("yyyy-MM-dd"));
}
using var cmd = new SqlCommand(sb.ToString(), connection, transaction);
for (int i = 0; i < currentBatch; i++)
{
cmd.Parameters.AddWithValue($"@c{i}", _random.Next(1, 10000));
cmd.Parameters.AddWithValue($"@a{i}",
Math.Round(10 + _random.NextDouble() * 4990, 2));
cmd.Parameters.AddWithValue($"@s{i}",
statuses[_random.Next(statuses.Length)]);
cmd.Parameters.AddWithValue($"@d{i}",
DateTime.Now.AddDays(-_random.Next(0, 730)));
}
await cmd.ExecuteNonQueryAsync();
await transaction.CommitAsync();
if (offset % 50000 == 0)
Console.WriteLine($"已生成 {offset + currentBatch} 条数据...");
}
Console.WriteLine($"总共生成 {totalCount} 条测试数据");
}
}结果分析与报告
// 基准测试结果分析器
public class BenchmarkAnalyzer
{
public BenchmarkAnalysis Analyze(List<BenchmarkReport> reports)
{
var analysis = new BenchmarkAnalysis();
// 吞吐量分析
analysis.AvgTPS = reports.Average(r => r.TPS);
analysis.MaxTPS = reports.Max(r => r.TPS);
analysis.MinTPS = reports.Min(r => r.TPS);
analysis.TPSStdDev = CalculateStdDev(reports.Select(r => r.TPS).ToList());
// 延迟分析
analysis.AvgP50 = reports.Average(r => r.Latency.Median);
analysis.AvgP95 = reports.Average(r => r.Latency.P95);
analysis.AvgP99 = reports.Average(r => r.Latency.P99);
// 稳定性分析（TPS变异系数）
analysis.TPSCoefficientOfVariation =
analysis.TPSStdDev / analysis.AvgTPS * 100;
// 瓶颈判断
if (analysis.TPSCoefficientOfVariation > 15)
analysis.Warnings.Add("TPS 波动较大，存在稳定性问题");
if (analysis.AvgP99 > analysis.AvgP50 * 5)
analysis.Warnings.Add("长尾延迟严重，存在慢查询或锁争用");
if (reports.Any(r => r.ErrorCount > 0))
analysis.Warnings.Add("存在错误，需要排查原因");
return analysis;
}
private double CalculateStdDev(List<double> values)
{
double avg = values.Average();
double sumSq = values.Sum(v => Math.Pow(v - avg, 2));
return Math.Sqrt(sumSq / values.Count);
}
public string GenerateMarkdownReport(BenchmarkAnalysis analysis)
{
return $@"
# 数据库基准测试报告
## 吞吐量指标
| 指标 | 值 |
|------|-----|
| 平均 TPS | {analysis.AvgTPS:F2} |
| 最大 TPS | {analysis.MaxTPS:F2} |
| 最小 TPS | {analysis.MinTPS:F2} |
| TPS 标准差 | {analysis.TPSStdDev:F2} |
| TPS 变异系数 | {analysis.TPSCoefficientOfVariation:F2}% |
## 延迟指标
| 百分位 | 延迟(ms) |
|--------|----------|
| P50 | {analysis.AvgP50:F2} |
| P95 | {analysis.AvgP95:F2} |
| P99 | {analysis.AvgP99:F2} |
## 警告
{string.Join("\n", analysis.Warnings.Select(w => $"- {w}"))}
";
}
}
public class BenchmarkAnalysis
{
public double AvgTPS { get; set; }
public double MaxTPS { get; set; }
public double MinTPS { get; set; }
public double TPSStdDev { get; set; }
public double TPSCoefficientOfVariation { get; set; }
public double AvgP50 { get; set; }
public double AvgP95 { get; set; }
public double AvgP99 { get; set; }
public List<string> Warnings { get; set; } = new();
}容量规划
// 数据库容量规划工具
public class CapacityPlanner
{
public CapacityPlan Calculate(
decimal currentQPS,
decimal growthRatePerMonth,
decimal targetP99LatencyMs,
decimal currentP99LatencyMs,
int planningHorizonMonths)
{
var plan = new CapacityPlan();
// 预测未来负载
for (int month = 0; month <= planningHorizonMonths; month++)
{
decimal projectedQPS = currentQPS * (decimal)Math.Pow(
1 + (double)growthRatePerMonth / 100, month);
plan.Projections.Add(new MonthProjection
{
Month = month,
ProjectedQPS = projectedQPS,
EstimatedP99 = currentP99LatencyMs *
(projectedQPS / currentQPS),
NeedsScaling = projectedQPS > currentQPS * 1.5
});
}
// 计算升级时间点
plan.ScaleUpMonth = plan.Projections
.FirstOrDefault(p => p.NeedsScaling)?.Month ?? planningHorizonMonths;
return plan;
}
}
public class CapacityPlan
{
public List<MonthProjection> Projections { get; set; } = new();
public int ScaleUpMonth { get; set; }
public string Recommendation { get; set; }
}
public class MonthProjection
{
public int Month { get; set; }
public decimal ProjectedQPS { get; set; }
public decimal EstimatedP99 { get; set; }
public bool NeedsScaling { get; set; }
}性能回归测试
自动化性能回归检测
// 性能回归检测器
public class PerformanceRegressionDetector
{
private readonly double _regressionThreshold;
public PerformanceRegressionDetector(double regressionThreshold = 0.15)
{
_regressionThreshold = regressionThreshold;
}
public RegressionReport Compare(
BenchmarkReport baseline,
BenchmarkReport current)
{
var report = new RegressionReport
{
BaselineDate = DateTime.Now.AddDays(-7),
CurrentDate = DateTime.Now
};
// TPS 回归检测
double tpsChange = (current.TPS - baseline.TPS) / baseline.TPS;
report.TPSChange = tpsChange;
if (tpsChange < -_regressionThreshold)
{
report.Regressions.Add(
$"TPS 下降 {Math.Abs(tpsChange) * 100:F1}%，" +
$"从 {baseline.TPS:F0} 降至 {current.TPS:F0}");
}
// P99 延迟回归检测
double p99Change = (current.Latency.P99 - baseline.Latency.P99)
/ baseline.Latency.P99;
report.P99Change = p99Change;
if (p99Change > _regressionThreshold)
{
report.Regressions.Add(
$"P99 延迟增加 {p99Change * 100:F1}%，" +
$"从 {baseline.Latency.P99:F2}ms 增至 {current.Latency.P99:F2}ms");
}
// 错误率回归
if (current.ErrorCount > baseline.ErrorCount * 2)
{
report.Regressions.Add(
$"错误数从 {baseline.ErrorCount} 增至 {current.ErrorCount}");
}
report.HasRegression = report.Regressions.Any();
return report;
}
}
public class RegressionReport
{
public DateTime BaselineDate { get; set; }
public DateTime CurrentDate { get; set; }
public double TPSChange { get; set; }
public double P99Change { get; set; }
public bool HasRegression { get; set; }
public List<string> Regressions { get; set; } = new();
}CI/CD 集成
# GitHub Actions 性能测试集成示例
name: Database Performance Test
on:
pull_request:
branches: [main]
paths:
- 'database/**'
jobs:
performance-test:
runs-on: ubuntu-latest
services:
mysql:
image: mysql:8.0
env:
MYSQL_ROOT_PASSWORD: test_password
MYSQL_DATABASE: perftest
ports:
- 3306:3306
options: >-
--health-cmd="mysqladmin ping"
--health-interval=10s
--health-timeout=5s
--health-retries=3
steps:
- uses: actions/checkout@v4
- name: Setup .NET
uses: actions/setup-dotnet@v4
with:
dotnet-version: '8.0.x'
- name: Install sysbench
run: |
sudo apt-get update
sudo apt-get install -y sysbench
- name: Prepare test data
run: |
sysbench oltp_read_write \
--db-driver=mysql \
--mysql-host=127.0.0.1 \
--mysql-port=3306 \
--mysql-user=root \
--mysql-password=test_password \
--mysql-db=perftest \
--tables=5 \
--table-size=100000 \
prepare
- name: Run benchmark
run: |
sysbench oltp_read_write \
--db-driver=mysql \
--mysql-host=127.0.0.1 \
--mysql-port=3306 \
--mysql-user=root \
--mysql-password=test_password \
--mysql-db=perftest \
--tables=5 \
--table-size=100000 \
--threads=16 \
--time=120 \
--report-interval=10 \
run > results.txt 2>&1
- name: Parse results
run: |
python3 scripts/parse_sysbench.py results.txt > metrics.json
- name: Check regression
run: |
python3 scripts/check_regression.py \
--baseline baselines/main.json \
--current metrics.json \
--threshold 0.15# 解析 sysbench 结果的 Python 脚本
#!/usr/bin/env python3
import re
import json
import sys
def parse_sysbench_output(filepath):
with open(filepath, 'r') as f:
content = f.read()
result = {}
# 提取 TPS
tps_match = re.search(r'transactions:\s+\d+\s+\((\S+)\s+per sec\)', content)
if tps_match:
result['tps'] = float(tps_match.group(1))
# 提取 QPS
qps_match = re.search(r'queries:\s+\d+\s+\((\S+)\s+per sec\)', content)
if qps_match:
result['qps'] = float(qps_match.group(1))
# 提取延迟
latency_match = re.search(r'avg:\s+(\S+)', content)
if latency_match:
result['avg_latency_ms'] = float(latency_match.group(1))
p95_match = re.search(r'95th percentile:\s+(\S+)', content)
if p95_match:
result['p95_latency_ms'] = float(p95_match.group(1))
p99_match = re.search(r'99th percentile:\s+(\S+)', content)
if p99_match:
result['p99_latency_ms'] = float(p99_match.group(1))
return result
if __name__ == '__main__':
filepath = sys.argv[1]
metrics = parse_sysbench_output(filepath)
print(json.dumps(metrics, indent=2))数据库特定性能测试
MySQL 性能测试要点
-- MySQL 测试前状态确认
SHOW GLOBAL STATUS LIKE 'Threads%';
SHOW GLOBAL STATUS LIKE 'Connections';
SHOW GLOBAL STATUS LIKE 'Innodb_row_lock%';
SHOW GLOBAL STATUS LIKE 'Slow_queries';
SHOW ENGINE InnoDB STATUS\G
-- 测试后关键指标对比
SELECT
variable_name,
variable_value
FROM performance_schema.global_status
WHERE variable_name IN (
'Innodb_row_lock_waits',
'Innodb_row_lock_time',
'Innodb_row_lock_time_avg',
'Slow_queries',
'Threads_running',
'Threads_connected'
);PostgreSQL 性能测试要点
-- PostgreSQL pgbench 工具
-- 初始化测试数据
pgbench -i -s 100 mydatabase # -s 缩放因子，100 = 1000万行
-- 执行只读测试
pgbench -c 32 -j 8 -T 300 -S mydatabase
# -c 客户端数 -j 线程数 -T 时间（秒） -S 只读模式
-- 执行读写测试
pgbench -c 32 -j 8 -T 300 mydatabase
-- 自定义测试脚本
pgbench -c 16 -T 120 -f custom_query.sql mydatabase-- PostgreSQL 测试监控查询
SELECT * FROM pg_stat_activity WHERE state = 'active';
SELECT * FROM pg_stat_database WHERE datname = 'mydatabase';
SELECT * FROM pg_locks WHERE NOT granted;SQL Server 性能测试要点
-- SQL Server 使用 ostress 工具
-- ostress 是 RML Utilities 的一部分
-- 执行并发查询测试
ostress -S localhost -d MyDatabase -Q "SELECT * FROM Orders WHERE OrderId = 1" -n 50 -r 1000
-- -n 并发连接数 -r 每连接执行次数
-- 执行存储过程测试
ostress -S localhost -d MyDatabase -Q "EXEC GetCustomerOrders @CustomerId = 1" -n 32 -r 500
-- 监控等待统计
SELECT
wait_type,
waiting_tasks_count,
wait_time_ms / 1000.0 AS wait_time_sec,
max_wait_time_ms / 1000.0 AS max_wait_sec
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
'REQUEST_FOR_DEADLOCK_SEARCH', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
'LAZYWRITER_SLEEP', 'XE_TIMER_EVENT', 'FT_IFTS_SCHEDULER_IDLE_WAIT'
)
ORDER BY wait_time_ms DESC;耐久测试（Soak Testing）
// 耐久测试框架
public class SoakTestRunner
{
private readonly string _connectionString;
private readonly ILogger _logger;
public SoakTestRunner(string connectionString, ILogger logger)
{
_connectionString = connectionString;
_logger = logger;
}
public async Task RunSoakTest(TimeSpan duration, int concurrentThreads)
{
_logger.LogInformation("开始耐久测试，持续时间: {Duration}，并发数: {Threads}",
duration, concurrentThreads);
var cts = new CancellationTokenSource(duration);
var metrics = new ConcurrentBag<TimeSeriesMetric>();
var monitorTask = MonitorResourceUsage(cts.Token, metrics);
var workers = Enumerable.Range(0, concurrentThreads)
.Select(i => RunWorker(i, cts.Token, metrics))
.ToArray();
await Task.WhenAll(workers);
cts.Cancel();
try
{
await monitorTask;
}
catch (OperationCanceledException) { }
// 分析耐久测试结果
AnalyzeSoakResults(metrics.ToList());
}
private async Task RunWorker(
int workerId,
CancellationToken ct,
ConcurrentBag<TimeSeriesMetric> metrics)
{
using var connection = new SqlConnection(_connectionString);
await connection.OpenAsync();
var random = new Random(workerId);
while (!ct.IsCancellationRequested)
{
try
{
var sw = Stopwatch.StartNew();
using var cmd = connection.CreateCommand();
cmd.CommandText = "SELECT TOP 10 * FROM Orders WHERE CustomerId = @id ORDER BY CreatedAt DESC";
((SqlCommand)cmd).Parameters.AddWithValue("@id", random.Next(1, 10000));
using var reader = await cmd.ExecuteReaderAsync(ct);
while (await reader.ReadAsync(ct)) { }
sw.Stop();
metrics.Add(new TimeSeriesMetric
{
Timestamp = DateTime.UtcNow,
WorkerId = workerId,
LatencyMs = sw.Elapsed.TotalMilliseconds,
Operation = "SELECT"
});
}
catch (Exception ex) when (ex is not OperationCanceledException)
{
metrics.Add(new TimeSeriesMetric
{
Timestamp = DateTime.UtcNow,
WorkerId = workerId,
IsError = true,
ErrorMessage = ex.Message
});
}
}
}
private void AnalyzeSoakResults(List<TimeSeriesMetric> metrics)
{
// 检测延迟趋势（是否有逐渐恶化的趋势）
var timeBuckets = metrics
.Where(m => !m.IsError)
.GroupBy(m => m.Timestamp.ToString("HH:mm"))
.OrderBy(g => g.Key)
.Select(g => new
{
Time = g.Key,
AvgLatency = g.Average(m => m.LatencyMs),
P99Latency = PercentileCalculator.Calculate(
g.Select(m => m.LatencyMs).ToList(), 99),
ErrorCount = metrics.Count(m =>
m.IsError && m.Timestamp.ToString("HH:mm") == g.Key)
})
.ToList();
// 检测趋势
double firstHalfAvg = timeBuckets.Take(timeBuckets.Count / 2)
.Average(b => b.AvgLatency);
double secondHalfAvg = timeBuckets.Skip(timeBuckets.Count / 2)
.Average(b => b.AvgLatency);
double degradation = (secondHalfAvg - firstHalfAvg) / firstHalfAvg * 100;
if (degradation > 20)
{
_logger.LogWarning("检测到性能退化: 后半段平均延迟比前半段高 {Degradation:F1}%",
degradation);
}
// 检测内存泄漏迹象（错误率趋势）
var errorRate = (double)metrics.Count(m => m.IsError) / metrics.Count * 100;
if (errorRate > 1)
{
_logger.LogError("错误率 {ErrorRate:F2}% 超过阈值", errorRate);
}
}
}
public class TimeSeriesMetric
{
public DateTime Timestamp { get; set; }
public int WorkerId { get; set; }
public double LatencyMs { get; set; }
public string Operation { get; set; }
public bool IsError { get; set; }
public string ErrorMessage { get; set; }
}总结
数据库性能测试是保障系统可靠运行的关键实践。通过系统化的基准测试，团队可以：
- 建立性能基线，量化系统容量
- 在代码变更时自动检测性能回归
- 为容量规划提供数据支撑
- 发现隐藏的性能瓶颈和稳定性问题
关键知识点
- TPS 和 QPS 是衡量吞吐量的核心指标
- 百分位延迟比平均延迟更能反映真实用户体验
- 基准测试需要包含预热阶段以消除冷启动影响
- 耐久测试可以暴露内存泄漏等长期运行问题
- 性能测试结果需要考虑统计显著性
常见误区
误区1：只关注平均延迟
平均延迟掩盖了长尾问题。P99 延迟才是用户体验的关键指标，一个平均 10ms 但 P99 为 2s 的系统远不如平均 20ms 但 P99 为 50ms 的系统。
误区2：忽略测试环境与生产环境的差异
测试环境的硬件、数据量、网络条件应尽可能模拟生产环境，否则测试结果没有参考价值。
误区3：一次性测试就足够
数据库性能会随着数据量增长、碎片化加剧而退化，需要定期执行基准测试。
误区4：只测峰值不测持续负载
峰值测试只能验证短时间承受能力，耐久测试才能发现内存泄漏、连接泄漏等长期问题。
进阶路线
- TPC 基准标准：深入学习 TPC-C、TPC-H、TPC-DS 标准
- 混沌工程：注入故障观察数据库表现
- 分布式追踪：将数据库调用纳入全链路追踪
- AI 驱动调优：使用机器学习自动优化数据库参数
- 云数据库性能：理解云托管数据库的性能特性
适用场景
- 系统上线前：验证数据库能否支撑预估负载
- 版本升级：对比新旧版本的性能差异
- 硬件选型：对比不同硬件配置的性能
- 参数调优：验证配置变更的效果
- 日常巡检：定期检测性能退化
落地建议
- 建立标准化的性能测试环境和流程
- 将基准测试集成到 CI/CD 流水线中
- 维护性能基线历史数据，支持趋势分析
- 设定性能阈值，超过阈值自动告警
- 定期进行耐久测试（至少每季度一次）
- 将性能测试结果纳入技术决策依据
排错清单
复盘问题
- 你的数据库性能基线是什么？上次更新是什么时候？
- 你的系统在峰值负载下的 P99 延迟是多少？
- 你是否在 CI/CD 中集成了性能回归检测？
- 你的数据库耐久测试间隔是多长？
- 你是否知道当前数据库的容量上限？
延伸阅读
- TPC Benchmark Standards
- sysbench Documentation
- HammerDB Documentation
- pgbench Documentation
- 《数据库性能优化方法论》- 盖国强
- 《Designing Data-Intensive Applications》- 第八章 分布式系统的麻烦