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
Thread:
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,
Michael

[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

etc.

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

Responses

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