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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Sun, Jan 27, 2019 at 2:39 AM Saurabh Nanda <saurabhnanda@xxxxxxxxx> 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 

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux