From: Keaton Adams <kadams@mxlogic.com>
To: Heikki Linnakangas <heikki@enterprisedb.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: WAL log performance/efficiency question
Date: Thu, 17 May 2007 09:34:43 -0600
Message-ID: <1179416083.26604.15.camel@MXLRMT-208.corp.mxlogic.com> (raw)
In-Reply-To: <464C6544.9070104@enterprisedb.com>
References: <1179410671.26604.3.camel@MXLRMT-208.corp.mxlogic.com>
<464C6544.9070104@enterprisedb.com>
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.
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: kadams@mxlogic.com, heikki@enterprisedb.com
Subject: Re: WAL log performance/efficiency question
In-Reply-To: <1179416083.26604.15.camel@MXLRMT-208.corp.mxlogic.com>
References: <1179410671.26604.3.camel@MXLRMT-208.corp.mxlogic.com> <464C6544.9070104@enterprisedb.com> <1179416083.26604.15.camel@MXLRMT-208.corp.mxlogic.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox