Subject: Re: [HACKERS] Full page writes improvement, code update
From: Hannu Krosing <hannu@skype.net>
To: "Joshua D. Drake" <jd@commandprompt.com>
Cc: Koichi Suzuki <suzuki.koichi@oss.ntt.co.jp>,
	Tom Lane <tgl@sss.pgh.pa.us>, Simon Riggs <simon@2ndquadrant.com>,
	josh@agliodbs.com, pgsql-hackers@postgresql.org,
	pgsql-patches@postgresql.org
In-Reply-To: <461C3732.20006@commandprompt.com>
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>
Content-Type: text/plain; charset=utf-8
Date: Wed, 11 Apr 2007 08:33:32 +0300
Message-Id: <1176269612.3962.11.camel@localhost.localdomain>
Mime-Version: 1.0
Content-Transfer-Encoding: 8bit

Ühel kenal päeval, T, 2007-04-10 kell 18:17, kirjutas Joshua D. Drake:
> > In terms of idle time for gzip and other command to archive WAL offline,
> > 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, it
> > 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 the
> > 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 the
> > storage.
> 
> Considering the relatively little amount of storage a transaction log
> takes, it would seem to me that the performance angle is more appropriate.

As I understand it it's not about transaction log but about write-ahead
log.

and the amount of data in WAL can become very important once you have to
keep standby servers in different physical locations (cities, countries
or continents) where channel throughput and cost comes into play.

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.

> Is it more efficient in other ways besides negligible tps? Possibly more
> efficient memory usage? Better restore times for a crashed system?

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, except
perhaps from better disk cache usage. 

For me pg_compresslog seems to be a winner even if it just does not
degrade performance.

-- 
----------------
Hannu Krosing
Database Architect
Skype Technologies OÜ
Akadeemia tee 21 F, Tallinn, 12618, Estonia

Skype me:  callto:hkrosing
Get Skype for free:  http://www.skype.com