Received: from localhost (maia-5.hub.org [200.46.204.182])
	by postgresql.org (Postfix) with ESMTP id 7BA449FA4D4;
	Tue, 10 Apr 2007 14:31:36 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024)
	with ESMTP id 39710-04; Tue, 10 Apr 2007 14:31:11 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by postgresql.org (Postfix) with ESMTP id C1BEC9FA641;
	Tue, 10 Apr 2007 14:29:08 -0300 (ADT)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id l3AHSpBw027694;
	Tue, 10 Apr 2007 13:28:51 -0400 (EDT)
To: Koichi Suzuki <suzuki.koichi@oss.ntt.co.jp>
cc: Simon Riggs <simon@2ndquadrant.com>, josh@agliodbs.com,
	pgsql-hackers@postgresql.org, pgsql-patches@postgresql.org
Subject: Re: [HACKERS] Full page writes improvement, code update 
In-reply-to: <460C611E.6090800@oss.ntt.co.jp> 
References: <460886E8.6040907@oss.ntt.co.jp> <4609CAC3.7060008@oss.ntt.co.jp>
	<1175072848.4386.323.camel@silverbirch.site>
	<200703291145.28574.josh@agliodbs.com>
	<1175200117.4386.616.camel@silverbirch.site>
	<460C611E.6090800@oss.ntt.co.jp>
Comments: In-reply-to Koichi Suzuki <suzuki.koichi@oss.ntt.co.jp>
	message dated "Fri, 30 Mar 2007 10:00:14 +0900"
Date: Tue, 10 Apr 2007 13:28:51 -0400
Message-ID: <27693.1176226131@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200704/299
X-Sequence-Number: 1866

Koichi Suzuki <suzuki.koichi@oss.ntt.co.jp> writes:
> My proposal is to remove unnecessary full page writes (they are needed 
> in crash recovery from inconsistent or partial writes) when we copy WAL 
> to archive log and rebuilt them as a dummy when we restore from archive 
> log.
> ...
> Benchmark: DBT-2
> Database size: 120WH (12.3GB)
> Total WAL size: 4.2GB (after 60min. run)
> Elapsed time:
>    cp:            120.6sec
>    gzip:          590.0sec
>    pg_compresslog: 79.4sec
> Resultant archive log size:
>    cp:             4.2GB
>    gzip:           2.2GB
>    pg_compresslog: 0.3GB
> Resource consumption:
>    cp:   user:   0.5sec system: 15.8sec idle:  16.9sec I/O wait: 87.7sec
>    gzip: user: 286.2sec system:  8.6sec idle: 260.5sec I/O wait: 36.0sec
>    pg_compresslog:
>          user:   7.9sec system:  5.5sec idle:  37.8sec I/O wait: 28.4sec

What checkpoint settings were used to make this comparison?  I'm
wondering whether much of the same benefit can't be bought at zero cost
by increasing the checkpoint interval, because that translates directly
to a reduction in the number of full-page images inserted into WAL.

Also, how much was the database run itself slowed down by the increased
volume of WAL (due to duplicated information)?  It seems rather
pointless to me to measure only the archiving effort without any
consideration for the impact on the database server proper.

			regards, tom lane

PS: there's something fishy about the gzip numbers ... why all the idle
time?