DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type:content-transfer-encoding;
	b=f9dmGhBkvkvfsdJuCsxDHXqfxcejUd9LnFEm6gvjiUXirPZfw8O9d6ZzGh2dYyCGSW
	mHhv7wypfiZdJ+57oUsl4LMpIk+ArZCUUraWy6ne5Q//idv5/nU1SHvzLgHRtE/EsW2h
	URaTfbTliXnx3If78IzoRXaDbzfMl8dthK1oQ=
MIME-Version: 1.0
In-Reply-To: <y2l72dbd3151004071950kfe09a98fqf0c9a136375260e2@mail.gmail.com>
References: <k2pda0294261004061437p9cd3810dqe9ce528b6ddb0873@mail.gmail.com>
	<m2m603c8f071004071413w1f2d2497hbbe07d9c6491848f@mail.gmail.com>
	<4BBCFB12.1010306@emolecules.com>
	<y2r72dbd3151004071556j22244233l8fa1db295b7f9cd3@mail.gmail.com>
	<r2q603c8f071004071747n3e56d12bo11c49885b8c2eb2e@mail.gmail.com>
	<4BBD3A17.90209@emolecules.com>
	<y2l72dbd3151004071950kfe09a98fqf0c9a136375260e2@mail.gmail.com>
Date: Wed, 7 Apr 2010 23:07:18 -0400
Message-ID: <v2u603c8f071004072007t323fe469lfdd8fa5f2a283890@mail.gmail.com>
Subject: Re: Occasional giant spikes in CPU load
From: Robert Haas <robertmhaas@gmail.com>
To: David Rees <drees76@gmail.com>
Cc: Craig James <craig_james@emolecules.com>, pgsql-performance@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

On Wed, Apr 7, 2010 at 10:50 PM, David Rees <drees76@gmail.com> wrote:
> On Wed, Apr 7, 2010 at 7:06 PM, Craig James <craig_james@emolecules.com> =
wrote:
>> On 4/7/10 5:47 PM, Robert Haas wrote:
>>> On Wed, Apr 7, 2010 at 6:56 PM, David Rees<drees76@gmail.com> =A0wrote:
>>>>> synchronous_commit =3D off
>>>>
>>>> You are playing with fire here. =A0You should never turn this off unle=
ss
>>>> you do not care if your data becomes irrecoverably corrupted.
>>>
>>> That is not correct. =A0Turning off synchronous_commit is sensible if
>>> you don't mind losing the last few transactions on a crash. =A0What wil=
l
>>> corrupt your database is if you turn off fsync.
>
> Whoops, you're right.
>
>> A bit off the original topic, but ...
>>
>> I set it this way because I was advised that with a battery-backed RAID
>> controller, this was a safe setting. =A0Is that not the case?
>
> Robert has it right - with synchronous_commit off, your database will
> always be consistent, but you may lose transactions in the event of a
> crash.
>
> Doesn't matter if you have a BBU or not - all the BBU does is give the
> controller the ability to acknowledge a write without the data
> actually having been written to disk.
>
> According to the documentation, with synchronous_commit off and a
> default wal_writer_delay of 200ms, it's possible to lose up to a
> maximum of 600ms of data you thought were written to disk.

So, IOW, if you're running a social networking web site and your
database is full of status updates sent by teenagers to other
teenagers, you might judge that turning off synchronous_commit is a
reasonable thing to do, if you need the performance.  If you're
running a bank and your database is full of information on wire
transfers sent and received, not so much.

...Robert