DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=aOoSZ8aifjqlulEt9R2+VarirZCRJxsyzMIyw5VToOdr50uGVn2i8g17n51ynzM1zamDNVd17ZmrcCLsv/lR+aBU7HNwYT1C12aJUuKA8dK/jthn2flRI3u1MM+E+ryoAXvdzoCxQKqUmwJSg5zXzD4g/W5dH82/z5/Xw7Ml25A=
Message-ID: <dcc563d10711120835q55fa3829s6d8932058ae6182e@mail.gmail.com>
Date: Mon, 12 Nov 2007 10:35:52 -0600
From: "Scott Marlowe" <scott.marlowe@gmail.com>
To: "Rafael Martinez" <r.m.guerrero@usit.uio.no>
Subject: Re: Need to run CLUSTER to keep performance
Cc: "Tom Lane" <tgl@sss.pgh.pa.us>,
	"Heikki Linnakangas" <heikki@enterprisedb.com>,
	pgsql-performance@postgresql.org
In-Reply-To: <47387B4F.7000608@usit.uio.no>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <4732E6B4.1030907@usit.uio.no> <4732EF07.3060807@enterprisedb.com>
	<47332200.9050705@usit.uio.no> <24775.1194535980@sss.pgh.pa.us>
	<4738735B.6030408@usit.uio.no> <47387B4F.7000608@usit.uio.no>

On Nov 12, 2007 10:11 AM, Rafael Martinez <r.m.guerrero@usit.uio.no> wrote:

> Sending this just in case it can help ....
>
> Checking all the log files from these vacuum jobs we have been running,
> we found one that looks difference from the rest, specially on the
> amount of removed pages.
>
> We are sending also the output before and after the one we are talking
> about:
>
> ###############################################
> 2007-11-11_0245.log
> ###############################################
> COMMAND: /local/opt/pgsql-8.1/bin/psql -h /tmp/pg_sockets/dbpg-meridien
> -p 5432 scanorama -c 'VACUUM VERBOSE ANALYZE hosts'
> CODE: 0
>
> OUTPUT:
> INFO:  vacuuming "public.hosts"
> INFO:  index "hosts_pkey" now contains 110886 row versions in 554 pages
> DETAIL:  0 index pages have been deleted, 0 are currently reusable.
> CPU 0.02s/0.00u sec elapsed 0.87 sec.
> INFO:  "hosts": found 0 removable, 110886 nonremovable row versions in
> 3848 pages
> DETAIL:  94563 dead row versions cannot be removed yet.
> There were 0 unused item pointers.

You see that right there?  You've got 94k dead rows that cannot be removed.

Then, later on, they can:

> CPU 0.04s/0.09u sec elapsed 590.48 sec.
> INFO:  "hosts": removed 94551 row versions in 3835 pages
> DETAIL:  CPU 0.00s/0.03u sec elapsed 0.10 sec.
> INFO:  "hosts": found 94551 removable, 16695 nonremovable row versions
> in 3865 pages

So, between the first and second vacuum you had a long running
transaction that finally ended and let you clean up the dead rows.

> After this last job the amount of dead rows just continued growing until
>  today.

I think you've got a long running transaction that's preventing you
from recovering dead rows.