pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Gourish Singbal <gourish@gmail.com>
To: Colton Smith <smith@skio.peachnet.edu>
Cc: pgsql-admin@postgresql.org
Subject: Re: pgstattuple, vacuum and free_space
Date: Tue, 29 Nov 2005 19:23:15 +0530
Message-ID: <674d1f8a0511290553m6bd93124je247807e4bd4e559@mail.gmail.com> (raw)
In-Reply-To: <43860F20.5080709@skio.peachnet.edu>
References: <43860F20.5080709@skio.peachnet.edu>

hi all,

I have a question :-
when we should consider vacuuming the table
is there an average *dead_tuple_percent* that should be taken into account
before vacuuming any table ?. i have

rpt_production=# select * from pgstattuple('table1');
-[ RECORD 1 ]------+----------
table_len          | 105537536
tuple_count        | 126420
tuple_len          | 41581608
tuple_percent      | 39.4
dead_tuple_count   | 9792
dead_tuple_len     | 3284784
dead_tuple_percent | 3.11
free_space         | 58950944
free_percent       | 55.86

Should i vacuum this table ?.

regards
Gourish

On 11/25/05, Colton Smith <smith@skio.peachnet.edu> wrote:
>
> Hi:
>
> I did the following after installing the pgstattuple contrib code:
>
> select * from pgstattuple('wind');
> -[ RECORD 1 ]------+----------
> table_len          | 224854016
> tuple_count        | 1492601
> tuple_len          | 207535124
> tuple_percent      | 92.3
> dead_tuple_count   | 11569
> dead_tuple_len     | 1361848
> dead_tuple_percent | 0.61
> free_space         | 3311416
> free_percent       | 1.47
>
>
> vacuum verbose wind;
> INFO:  vacuuming "public.wind"
> INFO:  index "wind_pkey" now contains 1492601 row versions in 4152 pages
> DETAIL:  11569 index row versions were removed.
> 0 index pages have been deleted, 0 are currently reusable.
> CPU 0.56s/1.77u sec elapsed 36.73 sec.
> INFO:  index "wind_measurement_date_index" now contains 1492601 row
> versions in 4156 pages
> DETAIL:  11569 index row versions were removed.
> 0 index pages have been deleted, 0 are currently reusable.
> CPU 0.43s/1.83u sec elapsed 35.90 sec.
> INFO:  "wind": removed 11569 row versions in 371 pages
> DETAIL:  CPU 0.03s/0.03u sec elapsed 2.38 sec.
> INFO:  "wind": found 11569 removable, 1492601 nonremovable row versions
> in 27448 pages
> DETAIL:  0 dead row versions cannot be removed yet.
> There were 0 unused item pointers.
> 0 pages are entirely empty.
> CPU 2.01s/3.95u sec elapsed 79.39 sec.
> VACUUM
>
> select * from pgstattuple('wind');
> -[ RECORD 1 ]------+----------
> table_len          | 224854016
> tuple_count        | 1492601
> tuple_len          | 207535124
> tuple_percent      | 92.3
> dead_tuple_count   | 0
> dead_tuple_len     | 0
> dead_tuple_percent | 0
> free_space         | 4673256
> free_percent       | 2.08
>
>
> My question: when you vacuum a table and generate 'free_space', who is
> allowed to consume this 'free_space'? Is it released to the OS for
> general use? Or is it reserved just for the database? If the latter, is
> it reserved just for 'wind' (in this case)?
>
> Thanks again!
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>

view thread (4+ messages)

Message-ID: <674d1f8a0511290553m6bd93124je247807e4bd4e559@mail.gmail.com>
Permalink:  ../674d1f8a0511290553m6bd93124je247807e4bd4e559@mail.gmail.com/
Also on:    postgresql.org/message-id/674d1f8a0511290553m6bd93124je247807e4bd4e559@mail.gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: gourish@gmail.com, smith@skio.peachnet.edu
  Subject: Re: pgstattuple, vacuum and free_space
  In-Reply-To: <674d1f8a0511290553m6bd93124je247807e4bd4e559@mail.gmail.com>
  References: <43860F20.5080709@skio.peachnet.edu> <674d1f8a0511290553m6bd93124je247807e4bd4e559@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox