Re: 8.2 Autovacuum BUG ?

On Tue, Jan 23, 2018 at 1:16 PM, Pavan Teja <pavan(dot)postgresdba(at)gmail(dot)com>
wrote:

> On Jan 23, 2018 9:37 PM, "Claudio Freire" <klaussfreire(at)gmail(dot)com> wrote:
>
>
>
> On Tue, Jan 23, 2018 at 11:39 AM, Pavan Teja <pavan(dot)postgresdba(at)gmail(dot)com>
> wrote:
>
>> Yes so many wals are continuing to be produced.
>>
>> Deleting the wals after a backup of the database.
>>
>> Yes archiving mode is on. And the warning message in log file is
>>
>> " checkpoints are frequently occurring (1second apart). Consider
>> increasing checkpoint_segements parameter".
>>
>> My doubt is previously the same are the parameters which are reflected as
>> of now. Then what is the point in considering altering those values.
>> Correct me if I am wrong.
>>
>
> You can use pg_xlogdump to inspect those logs and see which
> relations/transactions are generating so much WAL.
>
> Then you can hunt within your apps which code is responsible for that
> traffic, or whether it in fact is autovacuum.
>
>
>
> Hi Claudio,
>
> Is pg_xlogdump available for postgres 9.1, as my current production is
> postgres 9.1.
>

Right, it was added in 9.3

I'm unsure whether it can parse pre-9.3 WAL. I know technically speaking,
WAL doesn't have to stay compatible across versions, but it might be for
the limited purposes of xlogdump.

Yes investigated in that area, found DML's and also autovacuum statements
> for some relations. And the DML's are the same before this huge WAL traffic
> and normal WAL traffic.
>
> Anyways, thanks for your timely response 😊
>

While looking at current query activity makes sense, if you can't identify
a culprit doing that, inspecting the WAL directly will let you know with
precision what is causing all that WAL. Hence the suggestion.

If xlogdump doesn't work in 9.1, I'm not sure what you can do.

One idea that pops to mind, though there's probably a better one, you may
want to consider attaching an strace to a recovery process on a replica.
Preferrably one you're not worried about slowing down. Analyzing output
from that is much harder, but it may give you some insight. You'll have to
correlate file handles to file names to relations manually, which can be
quite a chore.