Postgresql – What to expect from a benchmark

benchmarkperformancepostgresqlpostgresql-performance

I'm using pgbench to test a cloud SAN (similar to EBS). The problem is, I don't really know what to expect given any system configuration. Meaning, that even before I start tuning PostgreSQL configuration, I would like to understand if what I'm seeing in the benchmark results correlates to the physical reality of the hardware.

I would really, really love if there was a table somewhere showing transactions per second as function of disk speed and/or available memory and/or CPU speed / number of cores. Or, a formula to calculate the best performance for the test, with hardware values plugged into it.

If there isn't a formula, but you still feel confident that you can tell what's a "good performance" given these specs, I'd still want to hear from you:

  • 2 vCPUs Intel(R) Xeon(R) CPU E5-2676 v3 @ 2.40GHz
  • 4 GB of RAM
  • SAN should be comparable to 5000 IOPS 100 GB EBS (NVMe)

What I'm getting right now is around 500 TPS on the TPC-B-like built-in test. Does this number make any sense? What would you hope to achieve given this sort of system? (Again, this system is not supposed to be a production database server, it is used to benchmark the SAN). If it doesn't make sense to benchmark on a less capable system (you cannot extrapolate from the results the behavior on a real system), then what kind of hardware (virtual of course) would you suggest for the more realistic test?

Best Answer

I'm not a hardware expert, but why do you think a SAN should be comparable to a NVMe?

Sorry, there is no simple formula.

At what scale did you initialize the benchmark tables? that is, what number for pgbench -i -s 20? How many clients did you run the benchmark? (What numbers did you provide for -jand -c?) Did you run pgbench on the same server as was running the database? For how long did you run it? The system can absorb write requests very fast for a while. Then either checkpointing kicks in, or RAM becomes clogged with dirty pages. For write-heavy benchmarks, I'd be hesitant to draw conclusions from any run less than 30 minutes.

I suspect that what you are benchmarking here is how fast your storage can complete (or claim to complete) a sync request. Does it get much faster if you do the below?

PGOPTIONS='-c synchronous_commit=off' pgbench ....

If you want to strip out the database itself and just test the underlying sync speed, you can use "pg_test_fsync". Make use the file it uses gets placed on the correct storage, either by changing directories or by using "-f" option.

Related Question