Skip site navigation (1) Skip section navigation (2)

Re: Reduction in WAL for UPDATEs

From: "Simon Riggs" <simon(at)2ndquadrant(dot)com>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Reduction in WAL for UPDATEs
Date: 2007-03-28 07:07:14
Message-ID: 1175065634.4386.267.camel@silverbirch.site (view raw or flat)
Thread:
Lists: pgsql-hackers
On Tue, 2007-03-27 at 20:48 -0400, Tom Lane wrote:
> "Simon Riggs" <simon(at)2ndquadrant(dot)com> writes:
> > It seems possible to reduce overall WAL volume by roughly 25% on common
> > workloads by optimising the way UPDATE statements generate WAL.
> 
> This seems a huge amount of work to optimize *one* benchmark.  

Please don't beat me with that. I wouldn't suggest it if I didn't think
it would help real users. The analysis of the WAL volume was done using
a benchmark, but only as a guide to indicate likely usage patterns.
There aren't many real world heavy UPDATE scenarios to analyze right now
because people have previously actively avoided such usage.

> If it
> weren't so narrowly focused on the properties of a particular benchmark
> (mostly UPDATE, mostly a few columns in wide tuples), I'd get more
> excited. 

Updating the current balance on a Customer Account is one of the main
focus areas for HOT. Those records are typically at least 250 bytes
long, so we can save ~200 bytes of WAL per UPDATE for the most frequent
types of UPDATE. Sure, not all UPDATEs would be optimised, but then they
are much less frequent.

As I mentioned, the WAL volume is disproportionately generated by
UPDATEs of longer rows, so optimising WAL for just a few tables can make
a big difference to the overall volume.

>  The extra time spent holding exclusive lock on the page
> doesn't sound attractive either ...

Agreed, thats why I set a fairly high bar for when this would kick in.
The fewer rows on a page, the less contention.

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



In response to

Responses

pgsql-hackers by date

Next:From: Magnus HaganderDate: 2007-03-28 08:36:54
Subject: Re: O_DIRECT support for Windows
Previous:From: Andrew - SupernewsDate: 2007-03-28 07:05:24
Subject: Re: Arrays of Complex Types

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group