Re: Fwd: Vacuum Full + Cluster + Vacuum full = non removable dead rows

2010/8/21 Dimitri <dimitrik(dot)fr(at)gmail(dot)com>

> Great! - it's what I expected until now :-)
> but discussion in this thread put my mind in trouble :-))
>
> So, the advice for Alexandre here is just to check the age of the
> oldest running transaction and the last time when the table in
> question was modified.. - if modification time is older than the
> oldest transaction = we have a problem in PG.. Otherwise it works as
> expected to match MVCC.
>
> Rgds,
> -Dimitri
>
>
Unfortunately, the customer can't wait for the solution and the programmer
eliminated the
use of this table by using a in-memory array.

I understood that all transactions, touching this table or not, can affect
the ability of the vacuum to recover the dead tuples.
In my scenario, it's too bad because I have long transactions and I really
not know when I will recover this tuples.
And, like I sad, the table will become more slow every time.

Only for discussion: the CLUSTER command, in my little knowledge, is a
intrusive command that's cannot recover the dead tuples too.

Only TRUNCATE can do this job, but obviously is not applicable all the time.

Best regards,

Alexandre

> On 8/21/10, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:
> > No, it means it can't clean rows that are younger than the oldest
> > transaction currently in progress. if you started a transaction 5
> > hours ago, then all the dead tuples created in the last 5 hours are
> > not recoverable. Dead tuples created before that transaction are
> > recoverable. If you run transactions for days or weeks, then you're
> > gonna have issues.
> >
> > On Sat, Aug 21, 2010 at 2:25 AM, Dimitri <dimitrik(dot)fr(at)gmail(dot)com> wrote:
> >> So, does it mean that VACUUM will never clean dead rows if you have a
> >> non-stop transactional activity in your PG database???... (24/7 OLTP
> >> for ex.)
> >>
> >> Rgds,
> >> -Dimitri
> >>
> >>
> >> On 8/19/10, Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> >>> Alexandre de Arruda Paes <adaldeia(at)gmail(dot)com> wrote:
> >>>> 2010/8/18 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> >>>
> >>>>> There's an open transaction somewhere that VACUUM is preserving
> >>>>> the tuples for. This transaction need not ever have touched the
> >>>>> table, or ever intend to touch the table --- but VACUUM cannot
> >>>>> know that, so it saves any tuples that the transaction might be
> >>>>> entitled to see if it looked.
> >>>>>
> >>>>> > carmen=# select * from vlocks where relname='tp93t'; select *
> >>>>> > from pg_stat_activity where usename='webpa';
> >>>>>
> >>>>> You keep on showing us only subsets of pg_stat_activity :-(
> >>>
> >>>> select * from pg_stat_activity where usename='webpa';
> >>>
> >>> You keep on showing us only subsets of pg_stat_activity :-(
> >>>
> >>> *ANY* open transaction, including "idle in transaction" including
> >>> transactions by other users in other databases will prevent vacuum
> >>> from cleaning up rows, for the reasons Tom already gave you.
> >>>
> >>> What do you get from?:
> >>>
> >>> select * from pg_stat_activity where current_query <> '<IDLE>'
> >>> order by xact_start limit 10;
> >>>
> >>> -Kevin
> >>>
> >>> --
> >>> Sent via pgsql-performance mailing list
> >>> (pgsql-performance(at)postgresql(dot)org)
> >>> To make changes to your subscription:
> >>> http://www.postgresql.org/mailpref/pgsql-performance
> >>>
> >>
> >> --
> >> Sent via pgsql-performance mailing list (
> pgsql-performance(at)postgresql(dot)org)
> >> To make changes to your subscription:
> >> http://www.postgresql.org/mailpref/pgsql-performance
> >>
> >
> >
> >
> > --
> > To understand recursion, one must first understand recursion.
> >
>