From: | Saurabh Nanda <saurabhnanda(at)gmail(dot)com> |
---|---|
To: | pgsql-performance(at)lists(dot)postgresql(dot)org |
Subject: | Benchmarking: How to identify bottleneck (limiting factor) and achieve "linear scalability"? |
Date: | 2019-01-23 19:16:06 |
Message-ID: | CAPz=2oGdmvirLNX5kys+uiY7LKzCP4sTiXXob39qq6eDkEuk2Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | legrand legrand | 2019-01-23 19:37:24 | RE:SELECT performance drop |
Previous Message | Jan Nielsen | 2019-01-23 17:28:52 | Re: SELECT performance drop |