Is there any material on how to benchmark Postgres meaningfully? I'm getting very frustrated with the numbers that `pgbench` is reporting:
-- allocating more resources to Postgres seems to be randomly dropping performance
-- there seems to be no repeatability in the benchmarking numbers [1]
-- there is no to figure out what is causing a bottleneck and which knob/setting is going to alleviate it.
How do the PG wizards figure all this out?
-- Saurabh.
On Thu, Jan 24, 2019 at 12:46 AM Saurabh Nanda <saurabhnanda@xxxxxxxxx> wrote:
Hi,Please pardon me if this question is already answered in the documentation, Wiki, or the mailing list archive. The problem is, that I don't know the exact term to search for - I've tried searching for "linear scalability" and "concurrency vs performance" but didn't find what I was looking for.## MAIN QUESTIONpgbench -c 1 achieves approx 80 TPSpgbench -c 6 should achieve approx 480 TPS, but only achieves 360 TPSpgbench -c 12, should achieve approx 960 TPS, but only achieves 610 TPSIf pgbench is being run on a 4c/8t machine and pg-server is being run on a 6c/12t machine with 32GB RAM [1], and the two servers are connected with 1 Gbit/s connection, I don't think either pgbench or pg-server is being constrained by hardware, right?In that case why is it not possible to achieve linear scalability, at least till 12 concurrent connections (i.e. the thread-count of pg-server)? What is an easy way to identify the limiting factor? Is it network connectivity? Disk IOPS? CPU load? Some config parameter?## SECONDARY QUESTIONAt what level of concurrent connections should settings like shared_buffers, effective_cache_size, max_wal_size start making a difference? With my hardware [1], I'm seeing a difference only after 48 concurrent connections. And that too it's just a 15-30% improvement over the default settings that ship with the Ubuntu 18.04 package. Is this expected? Isn't this allocating too many resources for too little gain?## CONTEXTI am currently trying to benchmark PG 11 (via pgbench) to figure out the configuration parameters that deliver optimum performance for my hardware [1] and workload [2]Based on https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server I've made the following relevant changes to the default PG config on Ubuntu 18.04:max_connection=400work_mem=4MBmaintenance_work_mem=64MBshared_buffers=12288MBtemp_buffers=8MBeffective_cache_size=16GBwal_buffers=-1wal_sync_method=fsyncmax_wal_size=5GBautovacuum=off # NOTE: Only for benchmarking[1] 32 GB RAM - 6 core/12 thread - 2x SSD in RAID1[2] SaaS webapp -- it's a mixed workload which looks a lot like TPC-BThanks,Saurabh.
