From: Craig Ringer <craig@postnewspapers.com.au>
To: Adrian Moisey <adrian@careerjunction.co.za>
Cc: PFC <lists@peufeu.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: db size
Date: Mon, 14 Apr 2008 18:40:39 +0800
Message-ID: <480334A7.1070102@postnewspapers.com.au> (raw)
In-Reply-To: <48032237.4040604@careerjunction.co.za>
References: <4802F9E4.3000704@careerjunction.co.za>
<op.t9lf4tvrcigqcu@apollo13.peufeu.com>
<48032237.4040604@careerjunction.co.za>
Adrian Moisey wrote:
> Hi
>
>> If you suspect your tables or indexes are bloated, restore your
>> dump to a test box.
>> Use fsync=off during restore, you don't care about integrity on
>> the test box.
>> This will avoid slowing down your production database.
>> Then look at the size of the restored database.
>> If it is much smaller than your production database, then you have
>> bloat.
>
> I have done that, and I get the following:
>
> the live one is 113G
> the restored one is 78G
>
> How should I get rid of the bloat?
> VACUUM FULL?
And/or REINDEX if you're not satisfied with the results of a VACUUM FULL.
http://www.postgresql.org/docs/8.3/interactive/vacuum.htmlhttp://www.postgresql.org/docs/8.3/interactive/sql-reindex.html
Of course, all of these will have performance consequences while they're
running, and take out locks that prevent certain other operatons as
shown in table 13-2:
http://www.postgresql.org/docs/8.3/static/explicit-locking.html
and the explanation following it.
Note in particular:
----
ACCESS EXCLUSIVE
Conflicts with locks of all modes (ACCESS SHARE, ROW SHARE, ROW
EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE,
EXCLUSIVE, and ACCESS EXCLUSIVE). This mode guarantees that the holder
is the only transaction accessing the table in any way.
Acquired by the ALTER TABLE, DROP TABLE, TRUNCATE, REINDEX, CLUSTER,
and VACUUM FULL commands. This is also the default lock mode for LOCK
TABLE statements that do not specify a mode explicitly.
Tip: Only an ACCESS EXCLUSIVE lock blocks a SELECT (without FOR
UPDATE/SHARE) statement.
----
In other words, you won't be doing much with a table/index while a
VACUUM FULL or a REINDEX is in progress on it.
Given that, you probably want to check your table/index sizes and see if
there are particular problem tables or indexes, rather than just using a
sledgehammer approach.
--
Craig Ringer
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: craig@postnewspapers.com.au, adrian@careerjunction.co.za, lists@peufeu.com
Subject: Re: db size
In-Reply-To: <480334A7.1070102@postnewspapers.com.au>
* 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