From: | Jeff Janes <jeff(dot)janes(at)gmail(dot)com> |
---|---|
To: | Saurabh Nanda <saurabhnanda(at)gmail(dot)com> |
Cc: | pgsql-performance(at)lists(dot)postgresql(dot)org |
Subject: | Re: Benchmarking: How to identify bottleneck (limiting factor) and achieve "linear scalability"? |
Date: | 2019-01-27 19:41:57 |
Message-ID: | CAMkU=1wn1Hfm+wKxx1sRZ_hEVbt6M5yAVrxXYZA8+B8q2D7-Zg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On Sun, Jan 27, 2019 at 2:39 AM Saurabh Nanda <saurabhnanda(at)gmail(dot)com>
wrote:
>
>> PGOPTIONS="-c synchronous_commit=off" pgbench -T 3600 -P 10 ....
>>
>>
>> I am currently running all my benchmarks with synchronous_commit=off and
>> will get back with my findings.
>>
>
>
> It seems that PGOPTIONS="-c synchronous_commit=off" has a significant
> impact.
>
It is usually not acceptable to run applications with
synchronous_commit=off, so once you have identified that the bottleneck is
in implementing synchronous_commit=on, you probably need to take a deep
dive into your hardware to figure out why it isn't performing the way you
need/want/expect it to. Tuning the server under synchronous_commit=off
when you don't intend to run your production server with that setting is
unlikely to be fruitful.
> However, I still can not understand why the TPS for the optimised case is
LOWER than the default for higher concurrency levels!
In case you do intend to run with synchronous_commit=off, or if you are
just curious: running with a very high number of active connections often
reveals subtle bottlenecks and interactions, and is very dependent on your
hardware. Unless you actually intend to run our server with
synchronous_commit=off and with a large number of active connections, it is
probably not worth investigating this. You can make a hobby of it, of
course, but it is a time consuming hobby to have. If you do want to, I
think you should start out with your optimized settings and revert them one
at a time to find the one the caused the performance regression.
I'm more interested in the low end, you should do much better than those
reported numbers when clients=1 and synchronous_commit=off with the data on
SSD. I think you said that pgbench is running on a different machine than
the database, so perhaps it is just network overhead that is keeping this
value down. What happens if you run them on the same machine?
> +--------+---------------------+------------------------+
> > | client | Mostly defaults [1] | Optimised settings [2] |
> > +--------+---------------------+------------------------+
> > | 1 | 80-86 | 169-180 |
> > +--------+---------------------+------------------------+
>
Cheers,
Jeff
From | Date | Subject | |
---|---|---|---|
Next Message | Saurabh Nanda | 2019-01-28 04:52:17 | Re: Benchmarking: How to identify bottleneck (limiting factor) and achieve "linear scalability"? |
Previous Message | Jeff Janes | 2019-01-27 17:48:12 | Re: Benchmarking: How to identify bottleneck (limiting factor) and achieve "linear scalability"? |