From: | AI Rumman <rummandba(at)gmail(dot)com> |
---|---|
To: | Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: pg_xlog is getting bigger |
Date: | 2012-12-20 00:54:56 |
Message-ID: | CAGoODpfuLcDF68RJEjndef3JgherpaZUgn2xusk7tSzjeYj-2g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Wed, Dec 19, 2012 at 7:52 PM, Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com>wrote:
> On 12/19/2012 04:12 PM, Tom Lane wrote:
>
>> Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com> writes:
>>
>>> Well the question is how long have those idle transactions been around?
>>>
>>
>> Idle transactions shouldn't have anything to do with pg_xlog bloat.
>> What causes xlog bloat is inability to release old WAL because either
>> (a) we're not able to complete checkpoints, or (b) WAL archiving is
>> enabled but malfunctioning, and the old WAL segments are being kept
>> pending successful archiving.
>>
>
> Its obvious I am missing something important about WAL.
> Scenario:
> 1) Transaction is opened and say many UPDATEs are done.
> 2) This means there is now an old tuple and a new tuple for the previous
> row.
> 3) The transaction is not committed.
>
> I assumed the WAL logs contained information necessary to either go
> forward to the new on commit or go back to the old on rollback. I further
> assumed the log segment(s) could not be released until either a
> commit/rollback was done.
>
> At this point I figure I the above assumption is wrong or my understanding
> of <IDLE in TRANSACTION> is wrong or both!
>
>
>
>
>> Either (a) or (b) should result in bleating in the postmaster log.
>>
>> regards, tom lane
>>
>>
>>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)gmail(dot)com
>
I modified checkpoint_segment to 100 form 300 and then forced some
CHECKPOINT and pg_switch_xlog() and now found that the pg_xlog file got
almost 1 gb of space back.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2012-12-20 00:55:18 | Re: pg_xlog is getting bigger |
Previous Message | Adrian Klaver | 2012-12-20 00:52:05 | Re: pg_xlog is getting bigger |