Re: WAL usage calculation patch

пт, 6 мар. 2020 г. в 20:14, Julien Rouhaud <rjuju123(at)gmail(dot)com>:
>
> On Thu, Mar 5, 2020 at 8:55 PM Kirill Bychik <kirill(dot)bychik(at)gmail(dot)com> wrote:
> >
> > > While at it, did you consider adding a full-page image counter in the WalUsage?
> > > That's something I'd really like to have and it doesn't seem hard to integrate.
> >
> > Well, not sure I understand you 100%, being new to Postgres dev. Do
> > you want a separate counter for pages written whenever doPageWrites is
> > true? I can do that, if needed. Please confirm.
>
> Yes, I meant a separate 3rd counter for the number of full page images
> written. However after a quick look I think that a FPI should be
> detected with (doPageWrites && fpw_lsn != InvalidXLogRecPtr && fpw_lsn
> <= RedoRecPtr).

This seems easy, will implement once I get some spare time.

> > > Another point is that this patch won't help to see autovacuum activity.
> > > As an example, I did a quick te.....
> > > ...LONG QUOTE...
> > > but that may seem strange to only account for (auto)vacuum activity, rather
> > > than globally, grouping per RmgrId or CommandTag for instance. We could then
> > > see the complete WAL usage per-database. What do you think?
> >
> > I wanted to keep the patch small and simple, and fit to practical
> > needs. This patch is supposed to provide tuning assistance, catching
> > an io heavy query in commit-bound situation.
> > Total WAL usage per DB can be assessed rather easily using other means.
> > Let's get this change into the codebase and then work on connecting
> > WAL usage to (auto)vacuum stats.
>
> I agree that having a view of the full activity is a way bigger scope,
> so it could be done later (and at this point in pg14), but I'm still
> hoping that we can get insight of other backend WAL activity, such as
> autovacuum, in pg13.

How do you think this information should be exposed? Via the pg_stat_statement?

Anyways, I believe this change could be bigger than FPI. I propose to
plan a separate patch for it, or even add it to the TODO after the
core patch of wal usage is merged.

Please expect a new patch version next week, with FPI counters added.