Unexpected page allocation behavior on insert-only tables

From: Michael Renner <michael(dot)renner(at)amd(dot)co(dot)at>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Unexpected page allocation behavior on insert-only tables
Date: 2010-05-15 23:53:49
Message-ID: 4BEF340D.1010909@amd.co.at
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

While preparing a replication test setup with 9.0beta1 I noticed strange
page allocation patterns which Andrew Gierth found interesting enough to
report here.

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.

After issuing a VACUUM on the table the free space map got updated (or
initialized?) and the backends used the remaining space in the pages,
though the spurious page allocation continued.

best regards,

[1] https://workbench.amd.co.at/hg/pgworkshop/file/dc5ab49c99bb/pgexerciser

[2] E.g.:

(0,1) TX1
(0,2) TX5
(0,3) TX7
(1,1) TX2
(1,2) TX6
(1,3) TX9


[3] http://nopaste.narf.at/show/55/
Optimal usage seems to be 136 tuples per page for the table in question.


Browse pgsql-hackers by date

  From Date Subject
Next Message Robert Haas 2010-05-15 23:58:49 Re: [PATCH] Add SIGCHLD catch to psql
Previous Message David Fetter 2010-05-15 23:46:07 Re: [PATCH] Add SIGCHLD catch to psql