DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type:content-transfer-encoding;
	b=QvFUgdgTP9AWIVku/4IGyC7QPP3hLK/s7st8vALh/evNZiN2v2hfxUxAfsOpOZNIZI
	Yzevxg8KcDYoAKZk3GiBiIvoGS2gmOscFv4An4uX13Yq3ZC1FukT71aytA+J+W5grzSV
	OUmBCsAEh99/23zJUbXbo631hhE/4LEqHzm04=
MIME-Version: 1.0
In-Reply-To: <4995ABC0.4090401@musicreports.com>
References: <4995ABC0.4090401@musicreports.com>
Date: Fri, 13 Feb 2009 14:21:39 -0700
Message-ID: <dcc563d10902131321h548104a0vdf3d16f0dc27f015@mail.gmail.com>
Subject: Re: Vacuum wait time problem
From: Scott Marlowe <scott.marlowe@gmail.com>
To: Roger Ging <rging@musicreports.com>
Cc: pgsql-admin@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

On Fri, Feb 13, 2009 at 10:20 AM, Roger Ging <rging@musicreports.com> wrote:
> Hi,
>
> I'm running vacuum full analyze verbose on a table with 20million rows and
> 11 indexes.  In top, I'm seeing [pdflush] and postgres: writer process each
> using diferent cpu cores, with wait time well above 90% on each of them.
>  The vacuum has been running for several hours, and the last thing to show
> on screen, over an hour ago, was :
>
> DETAIL:  8577281 index row versions were removed.
> 736 index pages have been deleted, 736 are currently reusable.
> CPU 7.57s/52.52u sec elapsed 381.70 sec.
>
> That's the last index
>
> The vacuum process itself is using less than 2% of a core.
> The pg version is 8.3.1 running on Suse.  Hardware is 2X dual core Opterons,
> 16 GB RAM, 24 drives in RAID 50
>
> It would seem to me that the system is extremely IO bound, but I don't know
> how to find out what specifically is wrong here.  Any advice greatly
> appreciated.

A couple of questions.
Why Vacuum full as opposed to vacuum (regular)?
Why 8.3.1 which has known bugs, instead of 8.3.latest?
What do "vmstat 10" and iostat -x 10 have to say about your drive
arrays while this vacuum is running?