Обсуждение: VACUUM FULL achieves nothing / Postgres 7.3.2 + RedHat 7.3

Ladies and gentlemen,

I am running a Postgres 7.3.2 on a Red Hat Linux release 7.3
(Valhalla)
in a multiprocessor (4) environment.

There is a db table in one of the databases containing some 20.000
records updated on a daily basis. When attempting to delete about 4000
deprecated records from it the query ran for almost half an hour and
postmaster occupied 100% CPU, so i decided to cancel it. (Note: select
querys run fine and finish within milliseconds).

In order to reproduce the problem, i copied the above database to my
staging system (same linux, same postgres), and ran a vacuum full
analyze. It finished after 33 minutes. After the vacuum full the
deletion from the 20.000 records table succeeded within 23 ms.

Now, here's the problem:
When running the VACUUM FULL on the live system, it finished after 20
Seconds (without error messages) - and did not change anything.
Performance is still bad, and the delete from xxx where ... still
takes forever.

Additional information:
The applications using the database are java - based and create
persistent connections (from a connection pool) to the postgres
database. These connections  are on autocommit, thus, there should not
be any opened transactions preventing the vacuum full from write -
locking the tables.

Any idea what i am doing wrong? Any help appreciated.

Kind regards,

R. Willmington

On 15 Sep 2004, R. Willmington wrote:
> In order to reproduce the problem, i copied the above database to my
> staging system (same linux, same postgres), and ran a vacuum full
> analyze. It finished after 33 minutes. After the vacuum full the
> deletion from the 20.000 records table succeeded within 23 ms.

you should have given the verbose output from the vacuum full. you
could have seen how many dead row versions it had removed. perhaps
there were enough versions to justify 33 minutes of whatever it was
doing.

> Now, here's the problem:
> When running the VACUUM FULL on the live system, it finished after 20
> Seconds (without error messages) - and did not change anything.

it is likely other connections are preventing the row versions from
being reclaimed. again, you should have included verbose output.

> Additional information:
> The applications using the database are java - based and create
> persistent connections (from a connection pool) to the postgres
> database. These connections  are on autocommit, thus, there should not
> be any opened transactions preventing the vacuum full from write -
> locking the tables.

well, to prove it disconnect all the other connections and then try the
delete. you are not really eliminating the unknowns by assuming they
don't exist.