VACUUM FULL performance issues with pg_largeobject table

From: PG User 2010 <pguser2010(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: VACUUM FULL performance issues with pg_largeobject table
Date: 2010-01-22 23:27:57
Message-ID: 1e937d501001221527g3826dc18g1d721e06510844f2@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi there,

I originally posted these questions to the pgsql-performance mailing list,
but due to lack of response, I think that these may be more general in
nature--so I'm re-posting them here. Apologies for the cross-posting ahead
of time.

We are having real issues trying to reclaim dead blob space via VACUUM FULL
in Postgres 8.4.2 (Linux). We have generated this large amount of blob
garbage through application code and lack of running vacuumlo often enough.
In any case, we have done a vacuumlo, and "vacuum analyze
pg_largeobject"--but when we go to run "vacuum full pg_largeobject" [to
reclaim the many gigabytes of dead disk space now in there] the vacuum
process runs without end for days, becoming CPU bound to 1 CPU, barely doing
any I/O, and of course locking the pg_largeobject table exclusively.

Therefore, we have the following questions:

1) is there any easy way to fiddle with the vacuum process so that it is not
CPU bound and doing very little I/O? Why would vacuum full be CPU bound
anyway???

2) is it possible to interrupt VACUUM FULL, then re-start it later on and
have it pick up where it was working before? This way we could do the
cleanup in pieces.

3) are there any alternatives, such as CLUSTER (which doesn't seem to be
allowed since pg_largeobject is a system table) that would work? Trying
CLUSTER on the pg_largeobject table yields this: ERROR: "pg_largeobject" is
a system catalog

If anybody could help out with these questions, it would be GREATLY
appreciated....

Sam

Responses

Browse pgsql-general by date

  From Date Subject
Next Message DM 2010-01-22 23:35:08 Re: When is the release date for Postgres 8.5?
Previous Message Bob Pawley 2010-01-22 23:25:34 Re: Old/New