Horton

Re: WAL usage calculation patch

Julien Rouhaud <rjuju123@gmail.com>

View thread
From: Julien Rouhaud <rjuju123@gmail.com>
To: Kirill Bychik <kirill.bychik@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@postgresql.org>, Thomas Munro <thomas.munro@gmail.com>
Date: 2020-03-06T19:19:17Z
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.

On Fri, Mar 6, 2020 at 6:59 PM Kirill Bychik <kirill.bychik@gmail.com> wrote:
>
> пт, 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:
> > > 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?

That's unlikely, since autovacuum won't trigger any hook.  I was
thinking on some new view for pgstats, similarly to the example I
showed previously. The implementation is straightforward, although
pg_stat_database is maybe not the best choice here.

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

Just in case, if the problem is a lack of time, I'd be happy to help
on that if needed.  Otherwise, I'll definitely not try to block any
progress for the feature as proposed.

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

Thanks!