0

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?

1

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.

  • NVMe is the protocol used by the device (i.e. it supports queuing / high degree of concurrency). I run pgbench on the same server that runs the database. It's used by 8 clients, and it was initialized with 10 as scale factor (I've copied these defaults from the documentation page). The test is written to run 2 minutes, but the results are consistent with longer runs (i.e. 20 minutes or 2 hours look similar). In case of this SAN, the test doesn't need a warmup, I control its caches etc. – wvxvw Dec 25 '19 at 9:21
  • And, yes, essentially, this is an fsync test :) but I'd prefer to give people a number in TPS, rather than a number of fsyncs per number of writes or any such cooked-up metric they won't understand. I didn't know about pg_test_fsync. Thanks for the info! – wvxvw Dec 25 '19 at 9:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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