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

Re: Unexpected page allocation behavior on insert-only tables

From: Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>
To: Michael Renner <michael(dot)renner(at)amd(dot)co(dot)at>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Unexpected page allocation behavior on insert-only tables
Date: 2010-05-17 20:14:45
Message-ID: 1274127000-sup-992@alvh.no-ip.org (view raw or flat)
Thread:
Lists: pgsql-hackers
Excerpts from Michael Renner's message of sáb may 15 20:24:36 -0400 2010:
> On 16.05.2010 02:16, Tom Lane wrote:
> > Michael Renner<michael(dot)renner(at)amd(dot)co(dot)at>  writes:
> >> I've written a simple tool to generate traffic on a database [1], which
> >> did about 30 TX/inserts per second to a table. Upon inspecting the data
> >> in the table, I noticed the expected grouping of tuples which came from
> >> a single backend to matching pages [2]. The strange part was that the
> >> pages weren't completely filled but the backends seemed to jump
> >> arbitrarily from one page to the next [3]. For the table in question
> >> this resulted in about 10% wasted space.
> >
> > Which table would that be?  The trigger-driven updates to "auction",
> > in particular, would certainly guarantee some amount of "wasted" space.
> 
> Yeah, the auction table receives heavy updates and gets vacuumed regularly.
> 
> The behavior I showed was for the "bid" table, which only gets inserts 
> (and triggers the updates for the auction table).

I think this may be related to the smgr_targblock stuff; if the relcache
entry gets invalidated at the wrong time for whatever reason, the
"current page" could be abandoned in favor of extending the rel.  This
has changed since 8.4, but a quick perusal suggests that it should be
less likely on 9.0 than 8.4 but maybe there's something weird going on.
-- 

In response to

Responses

pgsql-hackers by date

Next:From: Simon RiggsDate: 2010-05-17 20:33:02
Subject: Re: Hot Standby tuning for btree_xlog_vacuum()
Previous:From: Tom LaneDate: 2010-05-17 20:10:26
Subject: Re: Hot Standby tuning for btree_xlog_vacuum()

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