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

Re: [PATCHES] pg_freespacemap question

From: Tatsuo Ishii <ishii(at)sraoss(dot)co(dot)jp>
To: tgl(at)sss(dot)pgh(dot)pa(dot)us
Cc: markir(at)paradise(dot)net(dot)nz, ishii(at)sraoss(dot)co(dot)jp,alvherre(at)commandprompt(dot)com, peter_e(at)gmx(dot)net,pgsql-hackers(at)postgresql(dot)org, pgsql-patches(at)postgresql(dot)org
Subject: Re: [PATCHES] pg_freespacemap question
Date: 2006-03-13 02:56:37
Message-ID: 20060313.115637.95906902.t-ishii@sraoss.co.jp (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patches
> > Tatsuo Ishii wrote:
> >> BTW, I noticed difference of outputs from pg_freespacemap and
> >> pgstattuple.
> >> 
> >> I ran pgbench and inspected "accounts" table by using these tools.
> >> 
> >> pg_freespacemap:
> >> sum of bytes: 250712
> >> 
> >> pgstattuple:
> >> free_space: 354880
> >> 
> >> Shouldn't they be identical?
> 
> No, because (a) pgbench vacuums at the start of the run not the end,

I ran VACUUM after pbench run and still got the differece.

> and (b) vacuum/fsm disregard pages with "uselessly small" amounts of
> free space (less than the average tuple size, IIRC).

That sounds strange to me. Each record of accounts tables is actually
exactly same, i.e fixed size. So it should be possible that UPDATE
reuses any free spaces made by previous UPDATE. If FSM neglects those
free spaces "because they are uselessly small", then the unrecycled
pages are getting grow even if they are regulary VACUUMed, no?

> I do notice a rather serious shortcoming of pg_freespacemap in its
> current incarnation, which is that it *only* shows you the per-page free
> space data, and not any of the information that would let you determine
> what the FSM is doing to filter the raw data.  The per-relation
> avgRequest and lastPageCount fields would be interesting for instance.
> Perhaps there should be a second view with one row per relation to
> carry the appropriate data.
--
Tatsuo Ishii
SRA OSS, Inc. Japan

In response to

Responses

pgsql-hackers by date

Next:From: Qingqing ZhouDate: 2006-03-13 03:02:34
Subject: Re: About Buffer Flushing Function
Previous:From: Mark KirkwoodDate: 2006-03-13 01:51:24
Subject: Re: [PATCHES] pg_freespacemap question

pgsql-patches by date

Next:From: Tom LaneDate: 2006-03-13 03:15:07
Subject: Re: [PATCHES] pg_freespacemap question
Previous:From: Mark KirkwoodDate: 2006-03-13 01:51:24
Subject: Re: [PATCHES] pg_freespacemap question

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