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