Re: Increasing WAL usage followed by sudden drop

On Fri, Aug 17, 2012 at 10:53 AM, delongboy <sdelong(at)saucontech(dot)com> wrote:
>
> Josh Berkus wrote
>>
>>> We are not doing anything to postgres that would cause the rise and
>>> drop.
>>> Data base activity is pretty consistent. nor are we doing any kind
>>> of
>>> purge. This week the drop occurred after 6 days. We are thinking it
>>> must
>>> be some kind of internal postgres activity but we can't track it
>>> down.
>>
>> Well, we certainly can't figure it out on this list by blind guessing ...
>>
>
> Have to agree with you there. Unfortunately this is where we've ended up.
> What can we look at and/or show that would help us to narrow it down? Is
> there anyway we can look into the wal file and see exactly what is being
> sent? I think this might actually give us the most insight.

Maybe there is an easier way, but one thing would be to compile a test
server (of the same version as the production) with WAL_DEBUG defined
in src/include/pg_config_manual.h, turn on the wal_debug guc, and
crank up trace_recovery_messages. Then replay the WAL log files from
production through this test server and see what it logs. That
requires that you have

Easier would to be turn on wal_debug and watch the server log as the
WAL logs are generated, instead of recovered, but you probably don't
want to do that on production. So you would need a workload generator
that also exhibits the phenomenon of interest.

Cheers,

Jeff