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-27 16:33:46
Message-ID: F3CBFBA88397EA498B22A05FFA9EC49D60A40D7A@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" did 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 Ben Chobot 2011-12-27 17:34:34 Re: invalid memory alloc request size
Previous Message bricklen 2011-12-27 16:03:33 Re: Example of a pg_gethostname() function. Feedback?