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

Re: WAL log performance/efficiency question

From: Keaton Adams <kadams(at)mxlogic(dot)com>
To: Heikki Linnakangas <heikki(at)enterprisedb(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: WAL log performance/efficiency question
Date: 2007-05-17 15:34:43
Message-ID: 1179416083.26604.15.camel@MXLRMT-208.corp.mxlogic.com (view raw or flat)
Thread:
Lists: pgsql-performance
So for every data page there is a 20 byte header, for every row there is
a 4 byte identifier (offset into the page), AND there is also a 28 byte
fixed-size header (27 + optional null bitmap)?? (I did find the section
in the 8.1 manual that give the physical page layout.)  The other RDBMS
platforms I have worked with have a header in the 28 byte range and a
row pointer of 4 bytes, and that's it.  I find it a bit surprising that
PostgreSQL would need another 28 bytes per row to track its contents.

I'll try the pg_relpages function as you suggest and recalculate from
there.

Thanks for the info,

-Keaton



On Thu, 2007-05-17 at 15:23 +0100, Heikki Linnakangas wrote:

> Keaton Adams wrote:
> > Using an 8K data page:
> > 
> > 8K data page (8192 bytes)
> > Less page header and row overhead leaves ~8000 bytes
> > At 100 bytes per row = ~80 rows/page
> > Rows loaded: 250,000 / 80 = 3125 data pages * 8192 = 25,600,000 bytes /
> > 1048576 = ~ 24.4 MB of data page space.
> 
> That's not accurate. There's 32 bytes of overhead per row, and that 
> gives you just 61 tuples per page. Anyhow, I'd suggest measuring the 
> real table size with pg_relpages function (from contrib/pgstattuple) or 
> from pg_class.relpages column (after ANALYZE).
> 
> > We are running on PostgreSQL 8.1.4 and are planning to move to 8.3 when
> > it becomes available.  Are there space utilization/performance
> > improvements in WAL logging in the upcoming release?
> 
> One big change in 8.3 is that COPY on a table that's been created or 
> truncated in the same transaction doesn't need to write WAL at all, if 
> WAL archiving isn't enabled.
> 

In response to

Responses

pgsql-performance by date

Next:From: Heikki LinnakangasDate: 2007-05-17 15:52:11
Subject: Re: WAL log performance/efficiency question
Previous:From: Tom LaneDate: 2007-05-17 14:55:52
Subject: Re: WAL log performance/efficiency question

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