Subject: Re: PITR performance costs
From: Simon Riggs <simon@2ndquadrant.com>
To: Dave Cramer <pg@fastcrypt.com>
Cc: postgresql performance list <pgsql-performance@postgresql.org>
In-Reply-To: <81165B3E-1099-4388-88F1-FA27A3AD92BC@fastcrypt.com>
References: <81165B3E-1099-4388-88F1-FA27A3AD92BC@fastcrypt.com>
Content-Type: text/plain
Date: Mon, 28 May 2007 21:40:08 +0100
Message-Id: <1180384809.26297.11.camel@silverbirch.site>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

On Mon, 2007-05-28 at 08:45 -0400, Dave Cramer wrote:

> Since PITR has to enable archiving does this not increase the amount  
> of disk I/O required ?

As Heikki says, some operations need logging when PITR is on; these are
now documented in the performance tips section of the latest dev docs:
http://developer.postgresql.org/pgdocs/postgres/populate.html#POPULATE-PITR

This isn't additional logging because of PITR, its just that we've had
to exclude PITR from some recent tuning operations. I'll be looking at
ways of making that optional in certain cases.

Overall, the cost of shipping WAL files away has been measured in large
scale tests by Mark Wong to be around 1% drop in measured peak
transaction throughput, tests about ~2 years ago now on 8.0. It's
possible that has increased as we have further tuned the server, but I'm
thinking its still fairly negligible overall.

Replication solutions currently weigh in significantly more than this
overhead, which is one reason to make me start thinking about log based
replication in future releases.

-- 
  Simon Riggs             
  EnterpriseDB   http://www.enterprisedb.com