From: Joshua D. Drake <jd@commandprompt.com>
To: Eamonn Kent <ekent@xsigo.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Vacuum not identifying rows for removal..
Date: Mon, 21 Aug 2006 18:55:50 -0700
Message-ID: <44EA6426.4020901@commandprompt.com> (raw)
In-Reply-To: <9146E3EBBFBCC94D95F95A1C4065348A837AB4@exch01.xsigo.com>
References: <9146E3EBBFBCC94D95F95A1C4065348A837AB4@exch01.xsigo.com>
> 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@sss.pgh.pa.us]
> Sent: Monday, August 21, 2006 2:06 PM
> To: Eamonn Kent
> Cc: pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] Vacuum not identifying rows for removal..
>
> "Eamonn Kent" <ekent@xsigo.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/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: jd@commandprompt.com, ekent@xsigo.com
Subject: Re: Vacuum not identifying rows for removal..
In-Reply-To: <44EA6426.4020901@commandprompt.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox