Re: Vacuum not identifying rows for removal..

Hi,

I am using PostgreSQL 8.1.4 for an embedded application. For some
reason, vacuum is not able to identify rows that are candidates for
removal (i.e., mark space as available).

Background Info:

I observed some performance problems - our database seemed to be using
an unusually high amount of cpu. Further investigation of the problem
revealed a very bloated database; the database was around 300M when it
should have been about 150M. A number of the database files were quite
large, however, the tables that they stored information for were very
small. For example, we had one table that had only 46 rows, but was
using up more than 17M of disk space. We had a number of other tables
that were similarly large.

We run auto vacuum and I can see from the logs that it is running quite
frequently. When I run vacuum full from the psql, I can see that space
is not being recovered. I have run vacuum full with the verbose flag
set, I can see that messages that indicate the existence of "dead row
versions that cannot be removed yet.

<--- CUT FROM VACUUM OUTPUT --->

CPU 0.00s/0.00u sec elapsed 0.18 sec.

INFO: "ibportreceivestatsca": found 0 removable, 88017 nonremovable row
versions in 4001 pages

DETAIL: 87957 dead row versions cannot be removed yet.

There were 1 unused item pointers.

<--- CUT FROM VACUUM OUTPUT --->

If I shutdown our application and run a vacuum full, the space is
recovered and the database size goes down to 150M.

So, my best guess is that something in our application is preventing
vacuum from removing dead rows. What could cause this? Would it be
caused by a long-living transaction? What is the best way to track the
problem down...right now, I am looking through pg_stat_activity and
pg_locks to find processes that are "in transaction" and what locks they
are holding.

Has anyone had a similar problem? If so, how did you resolve it?

Thanks

Ike

On Mon, 2006-08-21 at 11:50 -0700, Eamonn Kent wrote:

> So, my best guess is that something in our application is preventing
> vacuum from removing dead rows. What could cause this? Would it be
> caused by a long-living transaction? What is the best way to track
> the problem down...right now, I am looking through pg_stat_activity
> and pg_locks to find processes that are “in transaction” and what
> locks they are holding.

If you have any long running transactions - idle or active, that's your
problem. Vacuum can only clear out dead tuples older than that oldest
transaction. Deal with those. Make sure every single transaction your
app initiates commits or rolls back every single time.

You'll generally find them in pg_stat_activity, but not always. ps may
show you idle transactions not showing as idle in pg_stat_activity

--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.

"Eamonn Kent" <ekent(at)xsigo(dot)com> writes:
> I am using PostgreSQL 8.1.4 for an embedded application. For some
> reason, vacuum is not able to identify rows that are candidates for
> removal (i.e., mark space as available).
> ...
> We run auto vacuum and I can see from the logs that it is running quite
> frequently. When I run vacuum full from the psql, I can see that space
> is not being recovered. I have run vacuum full with the verbose flag
> set, I can see that messages that indicate the existence of "dead row
> versions that cannot be removed yet.

This means you've got an open transaction somewhere that could
potentially still be able to see those rows. Look around for
applications sitting "idle in transaction".

regards, tom lane

Hello,

Thanks for the help...It appears that a transaction is indeed being
opened and remains idle. I am able to identify the postgreSQL backend
process that is associated with the transaction, however, I need to
further localize the issue. We have around 22 (postgres) backend
processes associated with various application processes. I would like
to identify our application process.

I have tried using netstat -ap and looking through the logs..but, to no
avail. (Both the database and the server processes are running on the
same server...connected via unix sockets I believe, perhaps this is
making the association difficult to determine).

Any ideas of how to identify the application process that is the
postgres process (whose id I know). Perhaps I need to turn on a
different log flag?

Thanks

Ike

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Monday, August 21, 2006 2:06 PM
To: Eamonn Kent
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] Vacuum not identifying rows for removal..

"Eamonn Kent" <ekent(at)xsigo(dot)com> writes:
> I am using PostgreSQL 8.1.4 for an embedded application. For some
> reason, vacuum is not able to identify rows that are candidates for
> removal (i.e., mark space as available).
> ...
> We run auto vacuum and I can see from the logs that it is running
quite
> frequently. When I run vacuum full from the psql, I can see that space
> is not being recovered. I have run vacuum full with the verbose flag
> set, I can see that messages that indicate the existence of "dead row
> versions that cannot be removed yet.

This means you've got an open transaction somewhere that could
potentially still be able to see those rows. Look around for
applications sitting "idle in transaction".

regards, tom lane

> Any ideas of how to identify the application process that is the
> postgres process (whose id I know). Perhaps I need to turn on a
> different log flag?

select * from pg_stat_activity will give you the pid :)

Joshua D. Drake

>
>
> Thanks
>
> Ike
>
>
>
>
> -----Original Message-----
> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> Sent: Monday, August 21, 2006 2:06 PM
> To: Eamonn Kent
> Cc: pgsql-performance(at)postgresql(dot)org
> Subject: Re: [PERFORM] Vacuum not identifying rows for removal..
>
> "Eamonn Kent" <ekent(at)xsigo(dot)com> writes:
>> I am using PostgreSQL 8.1.4 for an embedded application. For some
>> reason, vacuum is not able to identify rows that are candidates for
>> removal (i.e., mark space as available).
>> ...
>> We run auto vacuum and I can see from the logs that it is running
> quite
>> frequently. When I run vacuum full from the psql, I can see that space
>> is not being recovered. I have run vacuum full with the verbose flag
>> set, I can see that messages that indicate the existence of "dead row
>> versions that cannot be removed yet.
>
> This means you've got an open transaction somewhere that could
> potentially still be able to see those rows. Look around for
> applications sitting "idle in transaction".
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Hi Joshua,

Thanks for the info...but, what I already have the backend id. I was
trying to get the process id of the client application. The client is
using libpq and running on the same workstation. We have approximately
22 different clients running and it would help to isolate the client
program that is causing the problem.

I was unable to locate the client using the backend server's process id
with lsof and netstat. Really the information should be there...since,
each (I believe) each backend postgreSQL server will service a single
client via a unix socket (in the case where they are collocated on a
unix workstation).

Thanks

Ike

> Any ideas of how to identify the application process that is the
> postgres process (whose id I know). Perhaps I need to turn on a
> different log flag?

select * from pg_stat_activity will give you the pid :)

Joshua D. Drake

>
>
> Thanks
>
> Ike
>
>
>
>
> -----Original Message-----
> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> Sent: Monday, August 21, 2006 2:06 PM
> To: Eamonn Kent
> Cc: pgsql-performance(at)postgresql(dot)org
> Subject: Re: [PERFORM] Vacuum not identifying rows for removal..
>
> "Eamonn Kent" <ekent(at)xsigo(dot)com> writes:
>> I am using PostgreSQL 8.1.4 for an embedded application. For some
>> reason, vacuum is not able to identify rows that are candidates for
>> removal (i.e., mark space as available).
>> ...
>> We run auto vacuum and I can see from the logs that it is running
> quite
>> frequently. When I run vacuum full from the psql, I can see that
space
>> is not being recovered. I have run vacuum full with the verbose flag
>> set, I can see that messages that indicate the existence of "dead row
>> versions that cannot be removed yet.
>
> This means you've got an open transaction somewhere that could
> potentially still be able to see those rows. Look around for
> applications sitting "idle in transaction".
>
> regards, tom lane
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/