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

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 (view raw or flat)
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

pgsql-hackers by date

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

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