From: Josh Berkus <josh@agliodbs.com>
To: pgsql-hackers@postgresql.org
Cc: Zeugswetter Andreas ADI SD <ZeugswetterA@spardat.at>
Cc: Koichi Suzuki <suzuki.koichi@oss.ntt.co.jp>
Cc: pgsql-patches@postgresql.org
Subject: Re: [HACKERS] Full page writes improvement, code update
Date: Tue, 24 Apr 2007 08:31:42 -0700
Message-ID: <200704240831.42873.josh@agliodbs.com> (raw)
In-Reply-To: <E1539E0ED7043848906A8FF995BDA57901F3FFCC@m0143.s-mxs.net>
References: <460886E8.6040907@oss.ntt.co.jp>
<462D5A23.9060706@oss.ntt.co.jp>
<E1539E0ED7043848906A8FF995BDA57901F3FFCC@m0143.s-mxs.net>
Koichi, Andreas,
> 1) To deal with partial/inconsisitent write to the data file at crash
> recovery, we need full page writes at the first modification to pages
> after each checkpoint. It consumes much of WAL space.
We need to find a way around this someday. Other DBs don't do this; it may be
becuase they're less durable, or because they fixed the problem.
> I don't think there should be only one setting. It depend on how
> database is operated. Leaving wal_add_optiomization_info = off default
> does not bring any change in WAL and archive log handling. I
> understand some people may not be happy with additional 3% or so
> increase in WAL size, especially people who dosn't need archive log at
> all. So I prefer to leave the default off.
Except that, is there any reason to turn this off if we are archiving? Maybe
it should just be slaved to archive_command ... if we're not using PITR, it's
off, if we are, it's on.
> > 1) is there any throughput benefit for platforms with fast CPU but
> > contrained I/O (e.g. 2-drive webservers)? Any penalty for servers with
> > plentiful I/O?
>
> I've only run benchmarks with archive process running, because
> wal_add_optimization_info=on does not make sense if we don't archive
> WAL. In this situation, total I/O decreases because writes to archive
> log decreases. Because of 3% or so increase in WAL size, there will be
> increase in WAL write, but decrease in archive writes makes it up.
Yeah, I was just looking for a way to make this a performance feature. I see
now that it can't be. ;-)
> > 3) How is this better than command-line compression for log-shipping?
> > e.g. why do we need it in the database?
>
> I don't fully understand what command-line compression means. Simon
> suggested that this patch can be used with log-shipping and I agree.
> If we compare compression with gzip or other general purpose
> compression, compression ratio, CPU usage and I/O by pg_compresslog are
> all quite better than those in gzip.
OK, that answered my question.
> This is why I don't like Josh's suggested name of wal_compressable
> eighter.
> WAL is compressable eighter way, only pg_compresslog would need to be
> more complex if you don't turn off the full page optimization. I think a
> good name would tell that you are turning off an optimization.
> (thus my wal_fullpage_optimization on/off)
Well, as a PG hacker I find the name wal_fullpage_optimization quite baffling
and I think our general user base will find it even more so. Now that I have
Koichi's explanation of the problem, I vote for simply slaving this to the
PITR settings and not having a separate option at all.
--
Josh Berkus
PostgreSQL @ Sun
San Francisco
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: josh@agliodbs.com, ZeugswetterA@spardat.at, suzuki.koichi@oss.ntt.co.jp, pgsql-patches@postgresql.org
Subject: Re: [HACKERS] Full page writes improvement, code update
In-Reply-To: <200704240831.42873.josh@agliodbs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox