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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


On 8/21/10, Scott Marlowe <scott.marlowe@xxxxxxxxx> 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.fr@xxxxxxxxx> 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.Grittner@xxxxxxxxxxxx> wrote:
>>> Alexandre de Arruda Paes <adaldeia@xxxxxxxxx> wrote:
>>>> 2010/8/18 Tom Lane <tgl@xxxxxxxxxxxxx>
>>>
>>>>> 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@xxxxxxxxxxxxxx)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-performance
>>>
>>
>> --
>> Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-performance
>>
>
>
>
> --
> To understand recursion, one must first understand recursion.
>

-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance



[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux