Re: vacuum output question

On Thu, Nov 13, 2008 at 4:08 PM, Dan Armbrust
<daniel(dot)armbrust(dot)list(at)gmail(dot)com> wrote:
> I have a system backed by a PostgreSQL DB at a customer site that
> mysteriously slowed way down - and couldn't keep up with the load for
> no apparent reason.
>
> I had them run a vacuum analyze verbose on my database, and had these
> lines come back which made me suspicious:
>
> INFO: index "ix_cpe_ispid" now contains 41626 row versions in 13727 pages
> DETAIL: 5224 index row versions were removed.
> 1543 index pages have been deleted, 1373 are currently reusable.
> CPU 13.09s/3.51u sec elapsed 157.85 sec.
>
> INFO: index "ix_cpe_enable" now contains 41628 row versions in 29417 pages
> DETAIL: 5224 index row versions were removed.
> 3706 index pages have been deleted, 3291 are currently reusable.
> CPU 31.27s/8.22u sec elapsed 687.60 sec.
>
> INFO: "cpe": found 5224 removable, 41626 nonremovable row versions in
> 1303 pages
> DETAIL: 0 dead row versions cannot be removed yet.
> There were 22416 unused item pointers.
> 0 pages are entirely empty.
> CPU 44.46s/11.82u sec elapsed 852.85 sec.

That's a fair bit of dead space, but 60k rows isn't really that many.

> Why did those particular tables and indexes take _so_ long to vacuum?
> Perhaps we have a disk level IO problem on this system?

Assuming pagesize is 8k, then we're talking about scanning 1303*8192
bytes or 10 Megabytes. My laptop can scan that in less than a second.

So, either the hard drive is incredibly fragmented, or there's
something wrong with that machine.