Re: vacuum, dead rows, usual solutions didn't help

From: Gábor Farkas <gabor(at)nekomancer(dot)net>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: vacuum, dead rows, usual solutions didn't help
Date: 2008-01-10 06:46:50
Message-ID: 4785BF5A.1060803@nekomancer.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Tom Lane wrote:
> =?iso-8859-1?Q?G=E1bor?= Farkas <gabor(at)nekomancer(dot)net> writes:
>> basically, the problem is this part of the vacuum-output:
>
>> INFO: "sessions": found 2 removable, 6157654 nonremovable row versions
>> in 478069 pages
>> DETAIL: 6155746 dead row versions cannot be removed yet.
>
> The problem is that you've got some old open transactions that could
> potentially see those recently-dead rows, so VACUUM can't remove the
> rows without breaking MVCC rules for those transactions.
>
> Find the clients that are holding open transactions, and zap 'em.

well, that's the problem :-)

if i do a "ps aux | grep postgres" on the db-server, then

(ignoring the bin/postgres, writer-process and stats-collector-process)

then the oldest process is 3 days old.

but this dead-row-issue we have since weeks.

is it possible that a recently opened connection is blocking the
vacuuming of older-dead-rows?

>
>> previously we were running this application with postgresql-7.4, and
>> there the vacuuming worked fine. now we migrated this to
>> postgresql-8.2.4, and it does not want to vacuum it properly.
>
> I wonder whether you updated the client-side support libraries?

yes, they were updated too.

a related question:

on the db-server, let's say i have 3 databases: A, B, C.

if i have problems vacuuming B, then i only have to look for
processes/transactions touching B, correct? in other words, if i do a
"ps aux | grep postgres", then i see also the username and the
database-name in the process-list. and if a postgres-process is not
touching B, then i do not have to check it. is this correct?

gabor

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Gábor Farkas 2008-01-10 06:52:08 Re: vacuum, dead rows, usual solutions didn't help
Previous Message Joshua D. Drake 2008-01-10 06:44:01 Re: Query to get column-names in table via PG tables?