From: Heikki Linnakangas <heikki@enterprisedb.com>
To: Ruben Rubio <ruben@rentalia.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Vacuum full is slow
Date: Mon, 19 Mar 2007 11:23:15 +0000
Message-ID: <45FE72A3.5020709@enterprisedb.com> (raw)
In-Reply-To: <45FE6DD4.7080304@rentalia.com>
References: <45FE6DD4.7080304@rentalia.com>
Ruben Rubio wrote:
> Vacuum full is very slow for me . I dont know how to speed it up. It
> takes between 60 and 90 minutes.
>
> I have set up autovacuum but I also run vacuum full once per week.
Do you really need to run vacuum full? I don't know you're workload, but
usually you're better off just not running it.
One alternative is to run CLUSTER instead of VACUUM FULL. It's usually
faster, but beware that it's not safe if you're concurrently running
serializable transactions that access the table. pg_dump in particular
is a problem. In a maintenance window with no other activity, however,
it's ok.
> The slowest parts in the vacuum full output are :
>
> INFO: "a": moved 14076 row versions, truncated 6013 to 1005 pages
> DETAIL: CPU 3.51s/2.16u sec elapsed 1156.00 sec.
>
> INFO: "b": moved 22174 row versions, truncated 1285 to 933 pages
> DETAIL: CPU 3.77s/1.52u sec elapsed 443.79 sec.
>
> INFO: "c": moved 36897 row versions, truncated 2824 to 1988 pages
> DETAIL: CPU 3.26s/1.45u sec elapsed 676.18 sec.
>
> How can I speed it up?
You don't have vacuum_cost_delay set, do you? How long does normal
vacuum run?
The manual suggests dropping all indexes before running vacuum full, and
recreating them afterwards. That's worth trying.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
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: heikki@enterprisedb.com, ruben@rentalia.com
Subject: Re: Vacuum full is slow
In-Reply-To: <45FE72A3.5020709@enterprisedb.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