Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 16F0F9FA15F for ; Thu, 17 May 2007 12:34:48 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 81817-01-7 for ; Thu, 17 May 2007 12:34:44 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from p02c11o146.mxlogic.net (p02c11o146.mxlogic.net [208.65.144.79]) by postgresql.org (Postfix) with ESMTP id 451099FA3B3 for ; Thu, 17 May 2007 12:34:43 -0300 (ADT) Received: from unknown [208.65.151.10] (EHLO p02c11o146.mxlogic.net) by p02c11o146.mxlogic.net (mxl_mta-5.0.0-2) with ESMTP id 3167c464.2570013616.244603.00-502.p02c11o146.mxlogic.net (envelope-from ); Thu, 17 May 2007 09:34:43 -0600 (MDT) Received: from unknown [208.65.151.10] (EHLO mxlhq-exch01.corp.mxlogic.com) by p02c11o146.mxlogic.net (mxl_mta-5.0.0-2) with ESMTP id f067c464.2507074480.244593.00-022.p02c11o146.mxlogic.net (envelope-from ); Thu, 17 May 2007 09:34:39 -0600 (MDT) Received: from 10.30.1.68 ([10.30.1.68]) by mxlhq-exch01.corp.mxlogic.com ([10.30.0.246]) via Exchange Front-End Server webmail.mxlogic.com ([10.30.0.241]) with Microsoft Exchange Server HTTP-DAV ; Thu, 17 May 2007 15:34:39 +0000 Received: from MXLRMT-208 by webmail.mxlogic.com; 17 May 2007 09:34:43 -0600 Subject: Re: WAL log performance/efficiency question From: Keaton Adams Reply-To: kadams@mxlogic.com To: Heikki Linnakangas Cc: pgsql-performance@postgresql.org In-Reply-To: <464C6544.9070104@enterprisedb.com> References: <1179410671.26604.3.camel@MXLRMT-208.corp.mxlogic.com> <464C6544.9070104@enterprisedb.com> Content-Type: multipart/alternative; boundary="=-f50GO5IA576snEjI077y" Organization: MX Logic, Inc. Date: Thu, 17 May 2007 09:34:43 -0600 Message-Id: <1179416083.26604.15.camel@MXLRMT-208.corp.mxlogic.com> Mime-Version: 1.0 X-Mailer: Evolution 2.10.1 X-Spam: [F=0.0100000000; S=0.010(2007050201); SS=0.500] X-MAIL-FROM: X-SOURCE-IP: [208.65.151.10] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200705/301 X-Sequence-Number: 24691 --=-f50GO5IA576snEjI077y Content-Type: text/plain Content-Transfer-Encoding: 7bit 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. > --=-f50GO5IA576snEjI077y Content-Type: text/html; charset=utf-8 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.

--=-f50GO5IA576snEjI077y--