Re: New statistics for tuning WAL buffer size

On 2020/09/11 16:54, Kyotaro Horiguchi wrote:
> At Fri, 11 Sep 2020 13:48:49 +0900, Fujii Masao <masao(dot)fujii(at)oss(dot)nttdata(dot)com> wrote in
>>
>>
>> On 2020/09/11 12:17, Kyotaro Horiguchi wrote:
>>> Hello.
>>> At Wed, 09 Sep 2020 13:57:37 +0900, Masahiro Ikeda
>>> <ikedamsh(at)oss(dot)nttdata(dot)com> wrote in
>>>> I checked what function calls XLogBackgroundFlush() which calls
>>>> AdvanceXLInsertBuffer() to increment m_wal_buffers_full.
>>>>
>>>> I found that WalSndWaitForWal() calls it, so I added it.
>>>> Is it better to move it in WalSndLoop() like the attached patch?
>>> By the way, we are counting some wal-related numbers in
>>> pgWalUsage.(bytes, records, fpi). Since now that we are going to have
>>> a new view related to WAL statistics, wouln't it be more useful to
>>> show them together in the view?
>>
>> Probably yes. But IMO it's better to commit the current patch first,
>> and then add those stats into the view after confirming exposing them
>> is useful.
>
> I'm fine with that.
>
>> BTW, to expose the total WAL bytes, I think it's better to just save
>> the LSN at when pg_stat_wal is reset rather than counting
>> pgWalUsage.bytes. If we do that, we can easily total WAL bytes by
>> subtracting that LSN from the latest LSN. Also saving the LSN at the
>> reset timing causes obviously less overhead than counting
>> pgWalUsage.bytes.
>
> pgWalUsage is always counting so it doesn't add any overhead.

Yes. And I'm a bit concerned about the overhead by frequent message sent for WAL bytes to the stats collector.

Regards,

--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION