Re: Fwd: Vacuum Full + Cluster + Vacuum full = non removable dead rows - Mailing list pgsql-performance
| From | Alexandre de Arruda Paes |
|---|---|
| Subject | Re: Fwd: Vacuum Full + Cluster + Vacuum full = non removable dead rows |
| Date | |
| Msg-id | AANLkTi=wP4pEnuFCx529qWCwPuSMpbKc1RUEh3Lhugwr@mail.gmail.com Whole thread Raw |
| In response to | Re: Fwd: Vacuum Full + Cluster + Vacuum full = non removable dead rows (Dimitri <dimitrik.fr@gmail.com>) |
| Responses |
Re: Fwd: Vacuum Full + Cluster + Vacuum full = non
removable dead rows
Re: Fwd: Vacuum Full + Cluster + Vacuum full = non removable dead rows |
| List | pgsql-performance |
2010/8/21 Dimitri <dimitrik.fr@gmail.com>
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
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.marlowe@gmail.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.fr@gmail.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.Grittner@wicourts.gov> wrote:
>>> Alexandre de Arruda Paes <adaldeia@gmail.com> wrote:
>>>> 2010/8/18 Tom Lane <tgl@sss.pgh.pa.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@postgresql.org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-performance
>>>
>>
>> --
>> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-performance
>>
>
>
>
> --
> To understand recursion, one must first understand recursion.
>
pgsql-performance by date: