Re: huge difference in TPS depending of synchornous_commit setting

On 9 August 2012 07:44, Sergey Kirillov <sergey(dot)kirillov(at)gmail(dot)com> wrote:

> I'm having problems with PostgreSQL performance.
>
> My server has good CPU and lots of memory, but just two SATA 7200 hard
> drives in software RAID1.
>
> When running pgbench I'm getting 68 TPS with synchronous_commit turned on,
> and 3100 TPS with synchronous commit turned off.
>
> Can somebody tell me why there is such big difference? Is it normal to have
> it like this?

synchronous_commit = off is a performance feature, so yes it is normal
to experience a large gain in performance when using it. It is a trade
off between performance and durability. With setting off you don't
need to wait for the disk, with setting on then you'll go at the speed
of your disks.

The setting can be set differently for each transaction, so if you
know which transactions you want to favour you can achieve 80% of the
performance while losing only 20% of the durability.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services