Re: New statistics for tuning WAL buffer size

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.

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.

> (Another reason to propose this is that a substantially one-column
> table may look not-great..)

I'm ok with such "small" view. But if this is really problem, I'm ok to expose only functions pg_stat_get_wal_buffers_full() and pg_stat_get_wal_stat_reset_time(), without the view, at first.

Regards,

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