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: (view raw or whole 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,


[2] E.g.:

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


Optimal usage seems to be 136 tuples per page for the table in question.


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-2015 The PostgreSQL Global Development Group