Re: Vacuum full is slow

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