pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
pgstattuple, vacuum and free_space
4+ messages / 4 participants
[nested] [flat]

* pgstattuple, vacuum and free_space
@ 2005-11-24 19:06 Colton Smith <smith@skio.peachnet.edu>
  2005-11-26 10:39 ` Re: pgstattuple, vacuum and free_space hubert depesz lubaczewski <depesz@gmail.com>
  2005-11-29 13:53 ` Re: pgstattuple, vacuum and free_space Gourish Singbal <gourish@gmail.com>
  0 siblings, 2 replies; 4+ messages in thread

From: Colton Smith @ 2005-11-24 19:06 UTC (permalink / raw)
  To: pgsql-admin

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!






^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: pgstattuple, vacuum and free_space
  2005-11-24 19:06 pgstattuple, vacuum and free_space Colton Smith <smith@skio.peachnet.edu>
@ 2005-11-26 10:39 ` hubert depesz lubaczewski <depesz@gmail.com>
  2005-11-28 20:32   ` Re: pgstattuple, vacuum and free_space Jim C. Nasby <jnasby@pervasive.com>
  1 sibling, 1 reply; 4+ messages in thread

From: hubert depesz lubaczewski @ 2005-11-26 10:39 UTC (permalink / raw)
  To: Colton Smith <smith@skio.peachnet.edu>; +Cc: pgsql-admin

On 11/24/05, Colton Smith <smith@skio.peachnet.edu> wrote:
>
> 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)?
>


as far as i know this space is mapped as free witinh data-files of table
"wind".
thus effectivelyu - your OS doesnt get more free space, nor is (generally
speaking) the database.
benefit of this "free space" is that next inserts to wind table will fit
inside of this free space - thus stopping bloat of datafiles of this table.

if you would like to reclaim the disk-space, you should consider using
vacuum full.

depesz

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: pgstattuple, vacuum and free_space
  2005-11-24 19:06 pgstattuple, vacuum and free_space Colton Smith <smith@skio.peachnet.edu>
  2005-11-26 10:39 ` Re: pgstattuple, vacuum and free_space hubert depesz lubaczewski <depesz@gmail.com>
@ 2005-11-28 20:32   ` Jim C. Nasby <jnasby@pervasive.com>
  0 siblings, 0 replies; 4+ messages in thread

From: Jim C. Nasby @ 2005-11-28 20:32 UTC (permalink / raw)
  To: hubert depesz lubaczewski <depesz@gmail.com>; +Cc: Colton Smith <smith@skio.peachnet.edu>; pgsql-admin

On Sat, Nov 26, 2005 at 11:39:22AM +0100, hubert depesz lubaczewski wrote:
> On 11/24/05, Colton Smith <smith@skio.peachnet.edu> wrote:
> >
> > 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)?
> >
> 
> 
> as far as i know this space is mapped as free witinh data-files of table
> "wind".
> thus effectivelyu - your OS doesnt get more free space, nor is (generally
> speaking) the database.
> benefit of this "free space" is that next inserts to wind table will fit
> inside of this free space - thus stopping bloat of datafiles of this table.

Keep in mind that the free space needs to be recorded in the Free Space
Map for it to be re-used. If your FSM is too small then some of the free
space will not be used until you vacuum again.

Also, it is possible over time for tables to shrink. Vacuum will try to
remove any pages from the end of the table that are empty. But it's
difficult to make it so that pages at the end of the table are empty,
and AFAIK indexes can never shrink, so ultimately something like a
vacuum full/reindex or a cluster are your best bets for reclaiming disk
space.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: pgstattuple, vacuum and free_space
  2005-11-24 19:06 pgstattuple, vacuum and free_space Colton Smith <smith@skio.peachnet.edu>
@ 2005-11-29 13:53 ` Gourish Singbal <gourish@gmail.com>
  1 sibling, 0 replies; 4+ messages in thread

From: Gourish Singbal @ 2005-11-29 13:53 UTC (permalink / raw)
  To: Colton Smith <smith@skio.peachnet.edu>; +Cc: pgsql-admin

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
>

^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2005-11-29 13:53 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-11-24 19:06 pgstattuple, vacuum and free_space Colton Smith <smith@skio.peachnet.edu>
2005-11-26 10:39 ` hubert depesz lubaczewski <depesz@gmail.com>
2005-11-28 20:32   ` Jim C. Nasby <jnasby@pervasive.com>
2005-11-29 13:53 ` Gourish Singbal <gourish@gmail.com>

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