pgbench -基准结果的20%-30%的变化(不可重复的基准)

Question

问题

我试图使用pgbench来帮助我调整Postgres的硬件，但我看到了一个非常奇怪的行为。在连续运行pgbench时，我似乎无法获得稳定的TPS数。因为我运行了60秒的pgbench，所以我认为这是因为检查点和自动空分，所以我对我的配置做了以下更改：

• autovacuum=off
• max_wal_size=5GB

但这导致了更大的变化数字！例如，下面是完全相同的pgbench命令的输出，该命令连续运行：

输出1

starting vacuum...end.
progress: 5.0 s, 566.0 tps, lat 10.577 ms stddev 2.788
progress: 10.0 s, 513.0 tps, lat 11.689 ms stddev 2.907
progress: 15.0 s, 513.8 tps, lat 11.680 ms stddev 2.995
progress: 20.0 s, 519.6 tps, lat 11.546 ms stddev 2.969
progress: 25.0 s, 518.4 tps, lat 11.576 ms stddev 2.929
progress: 30.0 s, 518.2 tps, lat 11.576 ms stddev 2.978
progress: 35.0 s, 522.8 tps, lat 11.472 ms stddev 2.966
progress: 40.0 s, 521.0 tps, lat 11.516 ms stddev 2.962
progress: 45.0 s, 521.2 tps, lat 11.510 ms stddev 2.909
progress: 50.0 s, 581.6 tps, lat 10.313 ms stddev 2.636
progress: 55.0 s, 520.8 tps, lat 11.526 ms stddev 2.919
progress: 60.0 s, 522.2 tps, lat 11.494 ms stddev 2.927
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 2000
query mode: simple
number of clients: 6
number of threads: 6
duration: 60 s
number of transactions actually processed: 31699
latency average = 11.357 ms
latency stddev = 2.938 ms
tps = 528.185674 (including connections establishing)
tps = 528.269291 (excluding connections establishing)

输出2

starting vacuum...end.
progress: 5.0 s, 528.4 tps, lat 11.318 ms stddev 2.940
progress: 10.0 s, 526.0 tps, lat 11.418 ms stddev 2.884
progress: 15.0 s, 522.8 tps, lat 11.473 ms stddev 2.892
progress: 20.0 s, 525.6 tps, lat 11.409 ms stddev 3.008
progress: 25.0 s, 528.0 tps, lat 11.366 ms stddev 2.858
progress: 30.0 s, 525.6 tps, lat 11.412 ms stddev 2.893
progress: 35.0 s, 521.8 tps, lat 11.503 ms stddev 2.973
progress: 40.0 s, 524.4 tps, lat 11.439 ms stddev 2.966
progress: 45.0 s, 736.6 tps, lat 8.152 ms stddev 3.801
progress: 50.0 s, 1101.2 tps, lat 5.447 ms stddev 0.738
progress: 55.0 s, 1012.2 tps, lat 5.929 ms stddev 0.609
progress: 60.0 s, 723.4 tps, lat 8.285 ms stddev 2.969
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 2000
query mode: simple
number of clients: 6
number of threads: 6
duration: 60 s
number of transactions actually processed: 38886
latency average = 9.257 ms
latency stddev = 3.629 ms
tps = 647.993705 (including connections establishing)
tps = 648.099359 (excluding connections establishing)

这是一个20%的变化TPS完全相同的配置！

我在这里错过了什么？

硬件设置

• postgres服务器: 32 GB内存/6-核心(12个线程)/ SSD与RAID1
• pgbench服务器: 32 GB RAM /4核(8个线程)/ SSD

相关的Postgres配置，用于上面的输出

max_connection=100
work_mem=4MB
maintenance_work_mem=64MB
shared_buffers=12288MB
temp_buffers=8MB
effective_cache_size=16GB
wal_buffers=-1
wal_sync_method=fsync
max_wal_size=5GB
autovacuum=off

pgbench设置

Initialisation

pgbench 
  --initialize 
  --init-steps=dtgpf 
  --scale=2000  # Results in approx 30-32GB of data
  --username=benchmarking

Benchmarking

pgbench 
  --builtin=tpcb-like 
  --client=6 
  --jobs=6 
  --time=60 
  --progress=5 
  --username=benchmarking

两台服务器之间的网络连接

# iperf -t 60 -c [IP-ADDRESS]

------------------------------------------------------------
Client connecting to [IP-ADDRESS], TCP port 5001
TCP window size: 85.0 KByte (default)
------------------------------------------------------------
[  3] local [IP-ADDRESS] port 40494 connected with [IP-ADDRESS] port 5001
[ ID] Interval       Transfer     Bandwidth
[  3]  0.0-60.0 sec  3.47 GBytes   496 Mbits/sec

Answer 1

-f选项未提供自定义事务脚本文件。因此，pgbench的每次运行都将在默认的pgbench_*表上运行一组查询。不必每次运行pgbench时，都会执行相同的默认查询集。因此，响应时间可能会根据每次运行时正在执行的查询而变化。执行的查询也会打印在pgbench的控制台中。您可以查看正在运行的查询。