1

Problem

I'm trying to use pgbench to help me tune Postgres for my hardware, but I'm seeing a very strange behaviour. I cannot seem to get a stable number for TPS in consecutive runs of pgbench. Since I was running pgbench for 60 secs, I assumed that this is because of checkpointing and auto-vacuuming, so I made the following changes to my config:

  • autovacuum=off
  • max_wal_size=5GB

But this led to even more wildly varying numbers! For example, here's the output of the exact same pgbench command run consecutively:

Output 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)

Output 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)

That's a 20% variation in TPS for the exact same configuration!

What am I missing here?

Hardware setup

  • postgres server: 32 GB RAM / 6-core (12 thread) / SSD with RAID1
  • pgbench server: 32 GB RAM / 4-core (8 thread) / SSD

Relevant Postgres config for the above output

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 settings

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

Network connectivity between the two servers

# 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
5
  • Why do you want to tune your server to a benchmark which is unlikely to be relevant to your actual workload, and with settings which you would never run a production machine under? Also, you can't really draw a conclusion from 2 one-minute long tests. Output 2 looked quite uniform for half its range, and there is no reason to think the deviations later on came from the changes to the config, rather than something else going on with the OS which just happened to land in that block.
    – jjanes
    Commented Jan 24, 2019 at 3:59
  • @jjanes I think the TPC-B (sort of) benchmark that ships with pgbench is pretty representative of most common SaaS webapps. You are actually right about the timing duration of the benchmarks. The jitters have reduced to +/- 5% by running the benchmarks for 10minutes each. However, the numbers still don't make sense. Allocating more resources to PG seems to be giving worse performance! Commented Jan 25, 2019 at 12:17
  • tpc-b-like does 1 select for every 3 updates, I think that is extremely rare for real-world use case. Based on your TPS, it looks like you are bottlenecked by how fast your can sync the wal files to disk. If that is the case, other resources aren't going to matter much. Giving more resources (which ones?) to PostgreSQL often means taking those same resources away from the OS, so this can easily be counterproductive.
    – jjanes
    Commented Jan 25, 2019 at 15:11
  • @jjanes If I'm not mistaken, you've replied to my email on the PG mailing list as well, right? Do you prefer keeping the communication on the mailing list or on Stackexchange? Commented Jan 27, 2019 at 7:41
  • stackexchange isn't suited for extended discussion, so the mailing list please.
    – jjanes
    Commented Jan 28, 2019 at 14:00

1 Answer 1

-1

A custom transaction script file has not been provided with the -f option. Hence, every run of the pgbench will run a set of queries on the default pgbench_* tables. It is not necessary that each time pgbench is run, the same set of default queries will be executed. So, the response time may vary based on the queries that are being executed at every run. The queries that get executed are also printed in the console of pgbench. You can have a look at what queries are getting run.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.