Re: Slow Vacuum was: vacuum output question

On Tue, Dec 30, 2008 at 9:10 AM, Dan Armbrust
<daniel(dot)armbrust(dot)list(at)gmail(dot)com> wrote:
> To follow up on an old thread that I started - I had a customer who
> had a system where manual vacuum runs were taking a very long time to
> run. I was seeing output like this:
>
> INFO: "cpe": found 95498 removable, 18757 nonremovable row versions
> in 11117 pages
> DETAIL: 0 dead row versions cannot be removed yet.
> There were 280173 unused item pointers.
> 0 pages are entirely empty.
> CPU 5.35s/0.99u sec elapsed 724.38 sec.
>
> Then, running vacuum again immediately afterword, on a system that was
> basically idle, would result in nearly the same amount of time to
> vacuum the table.

You do realize that except for the end of a table, vacuum recovers no
actual space, just makes it available for new tuples to move in there.
So it makes sense that the second vacuum would take just as long, or
nearly so.

cluster or vacuum full followed by reindex will regain space lost.

> Getting a copy of the database from the customer, and loading it onto
> my Postgres System and running the vacuum would result in runs that
> took less than a second (as expected).
>
> General opinion was that it was a disk-io problem. We rebooted the
> system, and magically, the problem went away.

Hard to say. Have them run

vmstat 1

while vacuuming and see what bi/bo look like.