not-always-full vacuuming in 9.0 ?

From: <david(dot)sahagian(at)emc(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: not-always-full vacuuming in 9.0 ?
Date: 2011-12-22 16:57:04
Message-ID: F3CBFBA88397EA498B22A05FFA9EC49D60A409CB@MX22A.corp.emc.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

select version()
"PostgreSQL 9.0.1 on x86_64-unknown-linux-gnu,
compiled by GCC gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-46), 64-bit"

I ran the [check_bloat] query from check_postgres.pl (v 2.18.0) twice, doing a VACUUM FULL in between:
-- the RS
db, schemaname, tablename, tups, pages, otta, tbloat, wastedpages, wastedbytes, wastedsize,
iname, itups, ipages, iotta, ibloat, wastedipages, wastedibytes, wastedisize

-- I ran the first check_bloat
"mydb";"myschema";"MyTbl";131370;32863;28101;1.2;4762;39010304;"39010304 bytes";
"idx_mytbl";131370;361;27313;0.0;0;0;"0 bytes"

-- then I ran
VACUUM FULL verbose analyze myschema.MyTbl ;
INFO: vacuuming "myschema.MyTbl"
INFO: analyzing "myschema.MyTbl"
INFO: "MyTbl": scanned 30000 of 32795 pages, containing 120178 live rows and 0 dead rows; 30000 rows in sample, 131375 estimated total rows
Query returned successfully with no result in 12172 ms.

-- then I ran the second check_bloat
"mydb";"myschema";"MyTbl";131375;32795;28102;1.2;4693;38445056;"38445056 bytes";
"idx_mytbl";131376;509;27314;0.0;0;0;"0 bytes"

Can anybody suggest why "wastedbytes" does not go down to zero ?

Note that for another table, it does . . .

"mydb";"myschema";" otherTbl";499999;20619;16887;1.2;3732;30572544;"30572544 bytes";
"pk_otherTbl";499999;4548;14073;0.3;0;0;"0 bytes"

VACUUM FULL verbose analyze myschema.otherTbl ;
INFO: vacuuming " myschema.otherTbl "
INFO: analyzing " myschema.otherTbl "
INFO: " otherTbl ": scanned 16667 of 16667 pages, containing 499999 live rows and 0 dead rows; 30000 rows in sample, 499999 estimated total rows
Query returned successfully with no result in 6781 ms.

"mydb";"myschema";" otherTbl";499999;16667;16887;1.0;0;0;"0 bytes";
"pk_otherTbl ";499999;3607;14073;0.3;0;0;"0 bytes"

Thanks,
-dvs-

Browse pgsql-general by date

  From Date Subject
Next Message Bèrto ëd Sèra 2011-12-22 16:57:26 Re: How to create database with default system locale is set to et_EE.UTF-8
Previous Message Jacques Lamothe 2011-12-22 16:32:33 Cannot connect to 2nd cluster database remotely