Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id F23D4650185 for ; Mon, 21 Jul 2008 11:56:32 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 81354-08 for ; Mon, 21 Jul 2008 11:56:23 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.168]) by postgresql.org (Postfix) with ESMTP id CB4E7650182 for ; Mon, 21 Jul 2008 11:56:29 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id z27so291757ugc.49 for ; Mon, 21 Jul 2008 07:56:28 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to :subject:cc:in-reply-to:mime-version:content-type :content-transfer-encoding:content-disposition:references; bh=BrRJplzQETQ/ctSvFBXbUjq4fJePpo2z4kLNeY63wBE=; b=iL3f79VlABf7qd25GVBfAVJHyZ880t0H/wyBi2sxLA3a4e99aCzvYo6SJ08JfJFP/9 tY4wjwP4Ofk3xHatA1vgf8I5FvbrmVsultF7HE93Q8HwiasqXOW73D0g4GYiBtsYWZOc GARV/WIUZcpSTRXY7N0b9NLqumPKTAm0WUSu0= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version :content-type:content-transfer-encoding:content-disposition :references; b=xVd8GokIWOu6wHD149GEB58ZUlwMM9mnM2yi/pYWPxPSC3ETNhOXFL/p7rXwxVQBYd p5ltQTGA0VSMGuMVdqsXv4b5jJTH739KT3yY1ZwslYqXPr99g7aBROBN/nigsv04ZVyz Eya39H4/KQy643xegMbUjIyySR2Ig5Ks3/E60= Received: by 10.67.10.8 with SMTP id n8mr1690398ugi.23.1216652187694; Mon, 21 Jul 2008 07:56:27 -0700 (PDT) Received: by 10.67.97.7 with HTTP; Mon, 21 Jul 2008 07:56:27 -0700 (PDT) Message-ID: <7be3f35d0807210756t6accf928qf7aeca4d7a4fe222@mail.gmail.com> Date: Mon, 21 Jul 2008 16:56:27 +0200 From: "Harald Armin Massa" To: "Andreas Hartmann" Subject: Re: Less rows -> better performance? Cc: pgsql-performance@postgresql.org In-Reply-To: <4884A0F6.2000501@apache.org> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: <48846A02.7090702@apache.org> <48846DA3.2090308@archonet.com> <4884722A.1000405@apache.org> <1d4e0c10807210432v45c6272awb1ea13974747de54@mail.gmail.com> <4884A0F6.2000501@apache.org> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200807/183 X-Sequence-Number: 30783 Andreas, > I just verified that the autovacuum property is enabled. I did the follow= ing > to prepare the tests: "autovacuum property is enabled" Did you also check the logs, if autovacuum is working? > - setup two test databases, let's call them db_all and db_current > - import the dump from the live DB into both test DBs > - delete the old semester data from db_current, leaving only the current > data > > Both test DBs were 600 MB large after this. I did a VACUUM FULL ANALYZE o= n > both of them now. db_all didn't shrink significantly (only 1 MB), db_curr= ent > shrunk to 440 MB. Your test is not testing if vacuum is done on your production database! With pg_dump + pg_restore you removed next to all database bloat. (theoretically all) After loading a fresh dump, vacuuming ideally has to do nearly nothing; after deleting some data VACUUM reclaims the memory of the deleted rows, thats the shrinking you see after delete + vacuum. The bload in your production system may be the result of updates and deletes in that system; dumping and restoring removes that bloat. If your life DB is ~2,5Gig, and your dumped / restored DB is only 600MB, that 2500MB minus 600MB is some bloat from not vacuuming or bloated indexes. So, before the start of the next semester, at least do vacuum. (maybe also reindex) Best wishes, Harald --=20 GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Stra=DFe 49 70435 Stuttgart 0173/9409607 no fx, no carrier pidgeon - EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned!