Skip site navigation (1) Skip section navigation (2)

Re: huge difference in TPS depending of synchornous_commit setting

From: Simon Riggs <simon(at)2ndQuadrant(dot)com>
To: Sergey Kirillov <sergey(dot)kirillov(at)gmail(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: huge difference in TPS depending of synchornous_commit setting
Date: 2012-08-09 07:38:18
Message-ID: CA+U5nMKKSXC3dscLSV4VQN1w5q2YWVgm_nA+SbVAufeAWUyTTQ@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
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

In response to

pgsql-novice by date

Next:From: Anne WainwrightDate: 2012-08-09 09:07:49
Subject: how to get desired html format output?
Previous:From: Sergey KirillovDate: 2012-08-09 07:19:10
Subject: Re: huge difference in TPS depending of synchornous_commit setting

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group