PostgreSQL pgbench SQL RT 与 事务RT 浅析
背景
使用pgbench测试数据库性能时，在输出的报告中，可以输出事务的平均RT，以及单条SQL的平均RT。
那么这两个有什么分别呢？
每行代表一个线程，被填充了颜色的部分表示从客户端发起SQL到SQL返回的时间窗口，没有填充颜色的部分表示线程的空闲时间。
如何统计事务 平均RT :
执行的事务数/总的测试时长
如何统计SQL 平均RT :
每条SQL的执行时长累加/总的SQL执行次数
从计算公式以及图例来分析，显然SQL的平均RT会更低，因为没有计算线程的空闲时间。
特别是pgbench与数据库在同一主机进行测试时，全力压测（CPU吃满）的情况下，PGBENCH的线程等待（空闲）时间会更明显，SQL的RT会比事务的RT低很多。
那么哪个值更能代表数据库的处理能力呢？
SQL平均RT可以代表数据库的真实处理能力，而事务RT则是代表从客户端到数据库端作为一个整体来看待的事务处理能力（包括客户端的处理时间，数据库的处理时间，以及网络传输时间）。
pgbench 相关源码浅析
不加 -r 参数
返回样例如下
transaction type: TPC-B (sort of)
scaling factor: 100
query mode: prepared
number of clients: 10
number of threads: 10
duration: 10 s
number of transactions actually processed: 29032
latency average: 3.442 ms
latency stddev: 74.879 ms
tps = 2902.936994 (including connections establishing)
tps = 2903.710037 (excluding connections establishing)
代码如下
打印事务RT（或脚本RT）为 测试的持续时长 除以 总的事务数
/* only an average latency computed from the duration is available */
printf("latency average: %.3f ms\n",
1000.0 * duration * nclients / total->cnt);
加 -r 参数
-r, --report-latencies report average latency per command
返回样例如下
transaction type: TPC-B (sort of)
scaling factor: 100
query mode: prepared
number of clients: 10
number of threads: 10
duration: 10 s
number of transactions actually processed: 22000
latency average: 5.634 ms
latency stddev: 191.632 ms
tps = 1773.794731 (including connections establishing)
tps = 1774.193277 (excluding connections establishing)
statement latencies in milliseconds:
0.003053 \set nbranches 1 * :scale
0.000832 \set ntellers 10 * :scale
0.000661 \set naccounts 100000 * :scale
0.001120 \setrandom aid 1 :naccounts
0.000909 \setrandom bid 1 :nbranches
0.000742 \setrandom tid 1 :ntellers
0.000773 \setrandom delta -5000 5000
0.053747 BEGIN;
0.183235 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
0.092281 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
0.113678 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
0.155755 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
0.088806 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
4.929969 END;
代码浅析
参数
-r
case 'r':
benchmarking_option_set = true;
per_script_stats = true;
is_latencies = true;
break;
语句开始时间
instr_time stmt_begin; /* used for measuring statement latencies */
......
/* Record statement start time if per-command latencies are requested */
if (is_latencies)
INSTR_TIME_SET_CURRENT(st->stmt_begin);
累加语句的执行时间
/*
* command finished: accumulate per-command execution times in
* thread-local data structure, if per-command latencies are requested
*/
if (is_latencies)
{
if (INSTR_TIME_IS_ZERO(now))
INSTR_TIME_SET_CURRENT(now);
/* XXX could use a mutex here, but we choose not to */
addToSimpleStats(&commands[st->state]->stats,
INSTR_TIME_GET_DOUBLE(now) -
INSTR_TIME_GET_DOUBLE(st->stmt_begin));
}
......
/*
* Accumulate one value into a SimpleStats struct.
*/
static void
addToSimpleStats(SimpleStats *ss, double val)
{
if (ss->count == 0 || val < ss->min)
ss->min = val;
if (ss->count == 0 || val > ss->max)
ss->max = val;
ss->count++;
ss->sum += val;
ss->sum2 += val * val;
}
数据结构
/*
* Simple data structure to keep stats about something.
*
* XXX probably the first value should be kept and used as an offset for
* better numerical stability...
*/
typedef struct SimpleStats
{
int64 count; /* how many values were encountered */
double min; /* the minimum seen */
double max; /* the maximum seen */
double sum; /* sum of values */
double sum2; /* sum of squared values */
} SimpleStats;
/*
* Data structure to hold various statistics: per-thread and per-script stats
* are maintained and merged together.
*/
typedef struct StatsData
{
long start_time; /* interval start time, for aggregates */
int64 cnt; /* number of transactions */
int64 skipped; /* number of transactions skipped under --rate
* and --latency-limit */
SimpleStats latency;
SimpleStats lag;
} StatsData;
打印语句的RT （语句的累计时间 除以 语句的调用次数）
/* Report per-command latencies */
if (is_latencies)
{
Command **commands;
printf(" - statement latencies in milliseconds:\n");
for (commands = sql_script[i].commands;
*commands != NULL;
commands++)
printf(" %11.3f %s\n",
1000.0 * (*commands)->stats.sum /
(*commands)->stats.count,
(*commands)->line);
}