From barai@cs.ucr.edu Sat Jan  14 18:13:41 2006
X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id D89939DCA38
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Sat, 14 Jan 2006 14:13:41 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 17226-08
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Sat, 14 Jan 2006 14:13:41 -0400 (AST)
Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159])
	by postgresql.org (Postfix) with ESMTP id 71A269DC9FA
	for <pgsql-performance@postgresql.org>;
	Sat, 14 Jan 2006 14:13:39 -0400 (AST)
Received: from mailrelay.t-mobile.com (m6f095e42.tmodns.net [66.94.9.111])
	by svr4.postgresql.org (Postfix) with ESMTP id 1976A5AF0BA
	for <pgsql-performance@postgresql.org>;
	Sat, 14 Jan 2006 18:13:40 +0000 (GMT)
Received: from localhost (localhost [127.0.0.1])
	by mailrelay.t-mobile.com (Postfix) with ESMTP id 8AF2499DF
	for <pgsql-performance@postgresql.org>;
	Sat, 14 Jan 2006 10:13:35 -0800 (PST)
Received: from mailrelay.t-mobile.com ([127.0.0.1])
	by localhost (mailrelay.t-mobile.com [127.0.0.1]) (amavisd-new,
	port 10024)
	with ESMTP id 01237-06 for <pgsql-performance@postgresql.org>;
	Sat, 14 Jan 2006 10:13:34 -0800 (PST)
Received: from uni (250.72.253.10.in-addr.arpa [10.253.72.250])
	by mailrelay.t-mobile.com (Postfix) with ESMTP
	for <pgsql-performance@postgresql.org>;
	Sat, 14 Jan 2006 10:13:34 -0800 (PST)
From: "Benjamin Arai" <barai@cs.ucr.edu>
To: <pgsql-performance@postgresql.org>
Subject: Ensuring data integrity with fsync=off
Date: Sat, 14 Jan 2006 10:13:27 -0800
Message-ID: <000601c61936$38548770$fa48fd0a@uni>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_0007_01C618F3.2A314770"
X-Mailer: Microsoft Office Outlook 11
Thread-Index: AcYZNjdPT1qBlKsyRba3b2Qw0tKXbA==
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=1.333 required=5 tests=[HTML_MESSAGE=0.001,
	RCVD_IN_BL_SPAMCOP_NET=1.332]
X-Spam-Score: 1.333
X-Spam-Level: *
X-Archive-Number: 200601/188
X-Sequence-Number: 16666

This is a multi-part message in MIME format.

------=_NextPart_000_0007_01C618F3.2A314770
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit

I have been working on optimizing a PostgreSQL server for weekly updates
where data is only updated once a week then for the remaining portion of the
week the data is static.  So far I have set fsync to off and increased the
segment size among other things.  I need to ensure that at the end of the
update each week the data is in state where a crash will not kill the
database.  
 
Right now I run "sync" afte the updates have finished to ensure that the
data is synced to disk but I am concerned about the segment data and
anything else I am missing that PostgreSQL explicitly handles.  Is there
something I can do in addition to sync to tell PostgreSQL exlplicitly that
it is time to ensure everything is stored in its final destionation and etc?
 
Benjamin

------=_NextPart_000_0007_01C618F3.2A314770
Content-Type: text/html;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Dus-ascii">
<META content=3D"MSHTML 6.00.2900.2802" name=3DGENERATOR></HEAD>
<BODY>
<DIV><SPAN class=3D361280818-14012006><FONT face=3DArial size=3D2>I have =
been working=20
on optimizing a PostgreSQL server for weekly updates where data is only =
updated=20
once a week then for the remaining portion of the week the data is =
static.&nbsp;=20
So far I have set fsync to off and increased the segment size among =
other=20
things.&nbsp; I need to ensure that at the end of the update each =
week&nbsp;the=20
data&nbsp;is in&nbsp;state where a crash will not kill the =
database.&nbsp;=20
</FONT></SPAN></DIV>
<DIV><SPAN class=3D361280818-14012006><FONT face=3DArial=20
size=3D2></FONT></SPAN>&nbsp;</DIV>
<DIV><SPAN class=3D361280818-14012006><FONT face=3DArial size=3D2>Right =
now I run=20
"sync" afte the updates have&nbsp;finished to ensure that the data is =
synced to=20
disk but I am concerned about the segment data and anything else I am =
missing=20
that PostgreSQL explicitly handles.&nbsp; Is there something I can do in =

addition to sync to tell PostgreSQL exlplicitly that it is time to =
ensure=20
everything is stored in its final destionation and =
etc?</FONT></SPAN></DIV>
<DIV><SPAN class=3D361280818-14012006><FONT face=3DArial=20
size=3D2></FONT></SPAN>&nbsp;</DIV>
<DIV><SPAN class=3D361280818-14012006><FONT face=3DArial=20
size=3D2>Benjamin</FONT></SPAN></DIV></BODY></HTML>

------=_NextPart_000_0007_01C618F3.2A314770--




From tgl@sss.pgh.pa.us Sat Jan  14 18:41:46 2006
X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id E7AD99DD8D4
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Sat, 14 Jan 2006 14:41:46 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 23262-06
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Sat, 14 Jan 2006 14:41:46 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by postgresql.org (Postfix) with ESMTP id AB31A9DD8D9
	for <pgsql-performance@postgresql.org>;
	Sat, 14 Jan 2006 14:41:44 -0400 (AST)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id k0EIfhaX020898;
	Sat, 14 Jan 2006 13:41:43 -0500 (EST)
To: "Benjamin Arai" <barai@cs.ucr.edu>
cc: pgsql-performance@postgresql.org
Subject: Re: Ensuring data integrity with fsync=off 
In-reply-to: <000601c61936$38548770$fa48fd0a@uni> 
References: <000601c61936$38548770$fa48fd0a@uni>
Comments: In-reply-to "Benjamin Arai" <barai@cs.ucr.edu>
	message dated "Sat, 14 Jan 2006 10:13:27 -0800"
Date: Sat, 14 Jan 2006 13:41:43 -0500
Message-ID: <20897.1137264103@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.088 required=5 tests=[AWL=0.088]
X-Spam-Score: 0.088
X-Spam-Level: 
X-Archive-Number: 200601/190
X-Sequence-Number: 16668

"Benjamin Arai" <barai@cs.ucr.edu> writes:
> Right now I run "sync" afte the updates have finished to ensure that the
> data is synced to disk but I am concerned about the segment data and
> anything else I am missing that PostgreSQL explicitly handles.  Is there
> something I can do in addition to sync to tell PostgreSQL exlplicitly that
> it is time to ensure everything is stored in its final destionation and etc?

You need to give PG a CHECKPOINT command to flush stuff out of its
internal buffers.  After that finishes, a manual "sync" commnd will
push everything down to disk.

You realize, of course, that a system failure while the updates are
running might leave your database corrupt?  As long as you are prepared
to restore from scratch, this might be a good tradeoff ... but don't
let yourself get caught without an up-to-date backup ...

			regards, tom lane



From jnasby@pervasive.com Tue Jan  17 20:40:45 2006
X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id CD5FF9DC98B
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue, 17 Jan 2006 16:40:45 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 06940-01
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue, 17 Jan 2006 16:40:46 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from noel.decibel.org (noel.decibel.org [67.100.216.10])
	by postgresql.org (Postfix) with ESMTP id 1E2C59DC988
	for <pgsql-performance@postgresql.org>;
	Tue, 17 Jan 2006 16:40:43 -0400 (AST)
Received: by noel.decibel.org (Postfix, from userid 1001)
	id 3CE7739830; Tue, 17 Jan 2006 14:40:44 -0600 (CST)
Date: Tue, 17 Jan 2006 14:40:44 -0600
From: "Jim C. Nasby" <jnasby@pervasive.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Benjamin Arai <barai@cs.ucr.edu>, pgsql-performance@postgresql.org
Subject: Re: Ensuring data integrity with fsync=off
Message-ID: <20060117204044.GD17896@pervasive.com>
References: <000601c61936$38548770$fa48fd0a@uni>
	<20897.1137264103@sss.pgh.pa.us>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20897.1137264103@sss.pgh.pa.us>
X-Operating-System: FreeBSD 6.0-RELEASE amd64
X-Distributed: Join the Effort!  http://www.distributed.net
User-Agent: Mutt/1.5.11
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.093 required=5 tests=[AWL=0.093]
X-Spam-Score: 0.093
X-Spam-Level: 
X-Archive-Number: 200601/262
X-Sequence-Number: 16740

On Sat, Jan 14, 2006 at 01:41:43PM -0500, Tom Lane wrote:
> "Benjamin Arai" <barai@cs.ucr.edu> writes:
> > Right now I run "sync" afte the updates have finished to ensure that the
> > data is synced to disk but I am concerned about the segment data and
> > anything else I am missing that PostgreSQL explicitly handles.  Is there
> > something I can do in addition to sync to tell PostgreSQL exlplicitly that
> > it is time to ensure everything is stored in its final destionation and etc?
> 
> You need to give PG a CHECKPOINT command to flush stuff out of its
> internal buffers.  After that finishes, a manual "sync" commnd will
> push everything down to disk.
> 
> You realize, of course, that a system failure while the updates are
> running might leave your database corrupt?  As long as you are prepared
> to restore from scratch, this might be a good tradeoff ... but don't
> let yourself get caught without an up-to-date backup ...

Another alternative that may (or may not) be simpler would be to run
everything in one transaction and just let that commit at the end. Also,
there is ongoing work towards allowing certain operations to occur
without generating any log writes. Currently there is code submitted
that allows COPY into a table that was created in the same transaction
to go un-logged, though I think it's only in HEAD. In any case, there
should be some features that could be very useful to you in 8.2.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461