Re: New statistics for tuning WAL buffer size

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. But
since it cannot be reset, the value needs to be saved at reset time
like LSN. I don't mind either way we take from performance
perspective.

> > (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.

I don't mind that we have such small views as far as it is promised to
grow up:p

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center