From: Harald Armin Massa <haraldarminmassa@gmail.com>
To: Andreas Hartmann <andreas@apache.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: Less rows -> better performance?
Date: Mon, 21 Jul 2008 16:56:27 +0200
Message-ID: <7be3f35d0807210756t6accf928qf7aeca4d7a4fe222@mail.gmail.com> (raw)
In-Reply-To: <4884A0F6.2000501@apache.org>
References: <48846A02.7090702@apache.org>
<48846DA3.2090308@archonet.com>
<4884722A.1000405@apache.org>
<1d4e0c10807210432v45c6272awb1ea13974747de54@mail.gmail.com>
<4884A0F6.2000501@apache.org>
Andreas,
> I just verified that the autovacuum property is enabled. I did the following
> 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 on
> both of them now. db_all didn't shrink significantly (only 1 MB), db_current
> 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
--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607
no fx, no carrier pidgeon
-
EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned!
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: haraldarminmassa@gmail.com, andreas@apache.org
Subject: Re: Less rows -> better performance?
In-Reply-To: <7be3f35d0807210756t6accf928qf7aeca4d7a4fe222@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox