Message-ID: <461D9090.60808@oss.ntt.co.jp>
Date: Thu, 12 Apr 2007 10:51:12 +0900
From: Koichi Suzuki <suzuki.koichi@oss.ntt.co.jp>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
To: Hannu Krosing <hannu@skype.net>
CC: "Joshua D. Drake" <jd@commandprompt.com>, Tom Lane <tgl@sss.pgh.pa.us>,
	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
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> <27693.1176226131@sss.pgh.pa.us>	
	<461C3557.6040201@oss.ntt.co.jp> <461C3732.20006@commandprompt.com>
	<1176269612.3962.11.camel@localhost.localdomain>
In-Reply-To: <1176269612.3962.11.camel@localhost.localdomain>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: quoted-printable

I don't fully understand what "transaction log" means.   If it means=20
"archived WAL", the current (8.2) code handle WAL as follows:

1) If full_page_writes=3Doff, then no full page writes will be written to=
=20
WAL, except for those during onlie backup (between pg_start_backup and=20
pg_stop_backup).   The WAL size will be considerably small but it cannot =

recover from partial/inconsistent write to the database files.  We have=20
to go back to the online backup and apply all the archive log.

2) If full_page_writes=3Don, then full page writes will be written at the=
=20
first update of a page after each checkpoint, plus full page writes at=20
1).   Because we have no means (in 8.2) to optimize the WAL so far, what =

we can do is to copy WAL or gzip it at archive time.

If we'd like to keep good chance of recovery after the crash, 8.2=20
provides only the method 2), leaving archive log size considerably=20
large.  My proposal maintains the chance of crash recovery the same as=20
in the case of full_page_writes=3Don and reduces the size of archived log=
=20
as in the case of full_page_writes=3Doff.

Regards;

Hannu Krosing wrote:
> =C3=9Chel kenal p=C3=A4eval, T, 2007-04-10 kell 18:17, kirjutas Joshua =
D. Drake:
>>> In terms of idle time for gzip and other command to archive WAL offli=
ne,
>>> no difference in the environment was given other than the command to
>>> archive.   My guess is because the user time is very large in gzip, i=
t
>>> has more chance for scheduler to give resource to other processes.   =
In
>>> the case of cp, idle time is more than 30times longer than user time.=

>>> Pg_compresslog uses seven times longer idle time than user time.  On =
the
>>> other hand, gzip uses less idle time than user time.   Considering th=
e
>>> total amount of user time, I think it's reasonable measure.
>>>
>>> Again, in my proposal, it is not the issue to increase run time
>>> performance.   Issue is to decrease the size of archive log to save t=
he
>>> storage.
>> Considering the relatively little amount of storage a transaction log
>> takes, it would seem to me that the performance angle is more appropri=
ate.
>=20
> As I understand it it's not about transaction log but about write-ahead=

> log.
>=20
> and the amount of data in WAL can become very important once you have t=
o
> keep standby servers in different physical locations (cities, countries=

> or continents) where channel throughput and cost comes into play.
>=20
> With simple cp (scp/rsync) the amount of WAL data needing to be copied
> is about 10x more than data collected by trigger based solutions
> (Slony/pgQ). With pg_compresslog WAL-shipping seems to have roughly the=

> same amount and thus becomes a viable alternative again.
>=20
>> Is it more efficient in other ways besides negligible tps? Possibly mo=
re
>> efficient memory usage? Better restore times for a crashed system?
>=20
> I think that TPS is more affected by number of writes than size of each=

> block written, so there is probably not that much to gain in TPS, excep=
t
> perhaps from better disk cache usage.=20
>=20
> For me pg_compresslog seems to be a winner even if it just does not
> degrade performance.
>=20


--=20
Koichi Suzuki