thread-index: AcfHf4zc/YCoABjRRNS6HseEW8Seag==
Content-Class: urn:content-classes:message
Importance: normal
Subject: Re: Estimating WAL volume
From: "Simon Riggs" <simon@2ndquadrant.com>
To: "Greg Smith" <gsmith@gregsmith.com>
Cc: <pgsql-performance@postgresql.org>
In-Reply-To: <Pine.GSO.4.64.0707112254480.17954@westnet.com>
References: <Pine.GSO.4.64.0707112254480.17954@westnet.com>
Content-Type: text/plain;
	charset="iso-8859-1"
Date: Mon, 16 Jul 2007 09:02:08 +0100
Message-ID: <1184572928.5125.4.camel@ebony.site>
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit

On Wed, 2007-07-11 at 23:10 -0400, Greg Smith wrote:
> All this talk of WAL writing lately has me wondering something I haven't 
> spent enough time looking at the source to figure out myself this 
> week...any good rules of thumb out there for estimating WAL volume?  I'm 
> used to just measuring it via benchmarking but it strikes me a formula 
> would be nice to have for pre-planning.
> 
> For example, if I have a table where a typical row is X bytes wide, and 
> I'm updating Y of those per second, what's the expected write rate of WAL 
> volume?  Some % of those writes are going to be full pages; what's 
> typical? How much does the number and complexity of indexes factor into 
> things--just add the width of the index in bytes to the size of the 
> record, or is it worse than that?

I published an analysis of WAL traffic from Greg Stark earlier, based
upon xlogdump.

http://archives.postgresql.org/pgsql-hackers/2007-03/msg01589.php

Other details of WAL volumes are in the code. Further analysis would be
welcome, to assist discussions of where to optimize next.

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