Horton

Re: WAL usage calculation patch

Kirill Bychik <kirill.bychik@gmail.com>

View thread
From: Kirill Bychik <kirill.bychik@gmail.com>
To: PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Cc: Thomas Munro <thomas.munro@gmail.com>, Julien Rouhaud <rjuju123@gmail.com>
Date: 2020-03-06T17:59:31Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Change the display of WAL usage statistics in Explain.

  2. Cosmetic fixups for WAL usage work.

  3. Allow parallel create index to accumulate buffer usage stats.

  4. Allow autovacuum to log WAL usage statistics.

  5. Add the option to report WAL usage in EXPLAIN and auto_explain.

  6. Allow pg_stat_statements to track WAL usage statistics.

  7. Add infrastructure to track WAL usage.

  8. Include information on buffer usage during planning phase, in EXPLAIN output, take two.

  9. Include information on buffer usage during planning phase, in EXPLAIN output.

  10. Allow parallel vacuum to accumulate buffer usage.

  11. Allow pg_stat_statements to track planning statistics.

пт, 6 мар. 2020 г. в 20:14, Julien Rouhaud <rjuju123@gmail.com>:
>
> On Thu, Mar 5, 2020 at 8:55 PM Kirill Bychik <kirill.bychik@gmail.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.