X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 0816652A5B for ; Wed, 9 Mar 2005 01:02:38 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 65390-01 for ; Wed, 9 Mar 2005 01:02:28 +0000 (GMT) Received: from pns.mm.eutelsat.org (pns.mm.eutelsat.org [194.214.173.227]) by svr1.postgresql.org (Postfix) with ESMTP id 09493563F6 for ; Wed, 9 Mar 2005 01:02:24 +0000 (GMT) Received: from nts-03.mm.eutelsat.org (localhost [127.0.0.1]) by pns.mm.eutelsat.org (8.11.6/linuxconf) with ESMTP id j2925TS20586 for ; Wed, 9 Mar 2005 03:05:29 +0100 Received: from [127.0.0.1] (accesspoint.mm.eutelsat.org [194.214.173.4]) by nts-03.mm.eutelsat.org (8.11.6/linuxconf) with ESMTP id j2912RR05617 for ; Wed, 9 Mar 2005 02:02:27 +0100 Message-ID: <422E4B15.60806@bigfoot.com> Date: Wed, 09 Mar 2005 02:02:13 +0100 From: Gaetano Mendola User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 To: "pgsql-performance@postgresql.org" Subject: vacuum full, why multiple times ? X-Enigmail-Version: 0.90.1.1 X-Enigmail-Supports: pgp-inline, pgp-mime Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.403 tagged_above=0 required=5 tests=AWL, TO_ADDRESS_EQ_REAL X-Spam-Level: X-Archive-Number: 200503/147 X-Sequence-Number: 10852 -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hi all, this is the third email that I post but I do not see it in archives, the email was too long I believe so this time I will limit the rows. Basically I'm noticing that a simple vacuum full is not enough to shrink completelly the table: # vacuum full verbose url; INFO: vacuuming "public.url" INFO: "url": found 268392 removable, 21286 nonremovable row versions in 8563 pages DETAIL: 22 dead row versions cannot be removed yet. Nonremovable row versions range from 104 to 860 bytes long. There were 13924 unused item pointers. Total free space (including removable row versions) is 63818404 bytes. 4959 pages are or will become empty, including 7 at the end of the table. 8296 pages containing 63753840 free bytes are potential move destinations. CPU 0.33s/0.12u sec elapsed 9.55 sec. [SNIPPED] INFO: "url": moved 2 row versions, truncated 8563 to 8550 pages and after 4 vacuum full: empdb=# vacuum full verbose url; INFO: vacuuming "public.url" INFO: "url": found 13 removable, 21264 nonremovable row versions in 8504 pages DETAIL: 0 dead row versions cannot be removed yet. Nonremovable row versions range from 104 to 860 bytes long. There were 280528 unused item pointers. Total free space (including removable row versions) is 63349188 bytes. 4913 pages are or will become empty, including 0 at the end of the table. 8234 pages containing 63340628 free bytes are potential move destinations. CPU 0.17s/0.04u sec elapsed 0.49 sec. [SNIPPED] INFO: "url": moved 5666 row versions, truncated 8504 to 621 pages anyone knows why ? I had the same behaviour with a 46000 rows table with 46000 pages! It was reduced to 3000 pages after 7 vacuum full. Regards Gaetano Mendola -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.5 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCLksV7UpzwH2SGd4RAoz3AKDvXSx3w/jRz/NR1pgtrxIZs8cJcwCg/0xm zSr0sPDBkp8V1WXjREoVdLk= =EHv2 -----END PGP SIGNATURE-----