Postgres Pro
Downloads
Home   >   mailing lists

Thread: huge difference in TPS depending of synchornous_commit setting

huge difference in TPS depending of synchornous_commit setting

From
Sergey Kirillov
Date:
Hi. 

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?

PS: During run of pgbench with synchronous commit on system is mostly idle, iowait is 0.12%

--
Best regards,
  Sergey

Re: huge difference in TPS depending of synchornous_commit setting

From
Jesper Krogh
Date:
That is to be expected see

http://momjian.us/main/blogs/pgblog/2012.html#August_1_2012

Jesper

Re: huge difference in TPS depending of synchornous_commit setting

From
Sergey Kirillov
Date:
Thanks. I was thinking that is is related to rotational delay too. But 68 TPS still look a bit slow for me.

On the another (slower) server with 4 SATA disks in RAID-10, I'm getting ~250 TPS, which is almost 4X of my new server.

And this is weird for me since, if I'm getting everything right — 4 disks in RAID-10 should be only 2x faster than 2 disks in RAID-1

On Thu, Aug 9, 2012 at 10:07 AM, Jesper Krogh <jesper@krogh.cc> wrote:
That is to be expected see

http://momjian.us/main/blogs/pgblog/2012.html#August_1_2012

Jesper



--
Best regards,
  Sergey

Re: huge difference in TPS depending of synchornous_commit setting

From
Simon Riggs
Date:
On 9 August 2012 07:44, Sergey Kirillov <sergey.kirillov@gmail.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