X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 4EDD19DCBB6 for ; Tue, 29 Nov 2005 09:53:17 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 28486-05 for ; Tue, 29 Nov 2005 09:53:20 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from zproxy.gmail.com (zproxy.gmail.com [64.233.162.193]) by postgresql.org (Postfix) with ESMTP id 4F7A49DCB8D for ; Tue, 29 Nov 2005 09:53:13 -0400 (AST) Received: by zproxy.gmail.com with SMTP id 13so132008nzn for ; Tue, 29 Nov 2005 05:53:16 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=TQg74eoRIZ/ap2tsABgEypGcCyO8VZqAtB0TwC0im5yOwLf1aw1+U84AXU/qXC8ZDH4+7G+cNgSe1PIRS+BqhDF0uzfsP+C/OD3dXjbU10LB9La3FQFOQUkzX8AnWNblS62QiBeQSUqhu8Lwg/x7MwW4DUo+F1Qxu9ZRl2O9UkE= Received: by 10.36.61.7 with SMTP id j7mr4569281nza; Tue, 29 Nov 2005 05:53:16 -0800 (PST) Received: by 10.36.68.1 with HTTP; Tue, 29 Nov 2005 05:53:15 -0800 (PST) Message-ID: <674d1f8a0511290553m6bd93124je247807e4bd4e559@mail.gmail.com> Date: Tue, 29 Nov 2005 19:23:15 +0530 From: Gourish Singbal To: Colton Smith Subject: Re: pgstattuple, vacuum and free_space Cc: pgsql-admin@postgresql.org In-Reply-To: <43860F20.5080709@skio.peachnet.edu> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_51932_22544731.1133272395962" References: <43860F20.5080709@skio.peachnet.edu> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.001 required=5 tests=[HTML_MESSAGE=0.001] X-Spam-Score: 0.001 X-Spam-Level: X-Archive-Number: 200511/322 X-Sequence-Number: 19695 ------=_Part_51932_22544731.1133272395962 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline 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=3D# 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 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 > ------=_Part_51932_22544731.1133272395962 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline
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=3D# select * from pgstattuple('table1');
-[ RECORD 1= ]------+----------
table_len       &= nbsp;  | 105537536
tuple_count      &= nbsp; | 126420
tuple_len        =   | 41581608
tuple_percent      | 39.4
dead_tuple_count   | 9792
dead_tuple_len   &nbs= p; | 3284784
dead_tuple_percent | 3.11
free_space   &n= bsp;     | 58950944
free_percent   &n= bsp;   | 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 a= fter installing the pgstattuple contrib code:

select * from pgstattu= ple('wind');
-[ RECORD 1 ]------+----------
table_len    &nbs= p;     | 224854016
tuple_count   = ;     | 1492601
tuple_len   &nbs= p;      | 207535124
tuple_percent &nb= sp;    | 92.3
dead_tuple_count   | 11569dead_tuple_len     | 1361848
dead_tuple_percent | 0.61
free_space     &n= bsp;   | 3311416
free_percent     &nb= sp; | 1.47


vacuum verbose wind;
INFO:  vacuuming &q= uot;public.wind"
INFO:  index "wind_pkey" now c= ontains 1492601 row versions in 4152 pages
DETAIL:  11569 index row versions were removed.
0 index pa= ges have been deleted, 0 are currently reusable.
CPU 0.56s/1.77u sec ela= psed 36.73 sec.
INFO:  index "wind_measurement_date_index= " now contains 1492601 row
versions in 4156 pages
DETAIL:  11569 index row versions w= ere removed.
0 index pages have been deleted, 0 are currently reusable.<= br>CPU 0.43s/1.83u sec elapsed 35.90 sec.
INFO:  "wind&qu= ot;: 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 ver= sions
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
dea= d_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
g= eneral use? Or is it reserved just for the database? If the latter, is
i= t reserved just for 'wind' (in this case)?

Thanks again!




---------------------------(end of broadcast)------------------= ---------
TIP 2: Don't 'kill -9' the postmaster


------=_Part_51932_22544731.1133272395962--