Re: Benchmarking: How to identify bottleneck (limiting factor) and achieve "linear scalability"?

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?

[1]
https://dba.stackexchange.com/questions/227790/pgbench-20-30-variation-in-benchmark-results-non-repeatable-benchmarks

-- Saurabh.

On Thu, Jan 24, 2019 at 12:46 AM Saurabh Nanda <saurabhnanda(at)gmail(dot)com>
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 QUESTION
>
> pgbench -c 1 achieves approx 80 TPS
> pgbench -c 6 should achieve approx 480 TPS, but only achieves 360 TPS
> pgbench -c 12, should achieve approx 960 TPS, but only achieves 610 TPS
>
> If 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 QUESTION
>
> *At 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?
>
> ## CONTEXT
>
> I 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=400
> 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 # 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-B
>
> Thanks,
> Saurabh.
>

--
http://www.saurabhnanda.com