Horton

Re: WAL usage calculation patch

Craig Ringer <craig@2ndquadrant.com>

View thread
From: Craig Ringer <craig@2ndquadrant.com>
To: Kirill Bychik <kirill.bychik@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Date: 2020-02-10T07:20:32Z
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 Wed, 5 Feb 2020 at 21:36, Kirill Bychik <kirill.bychik@gmail.com> wrote:
>
> Hello pgsql-hackers,
>
> Submitting a patch that would enable gathering of per-statement WAL
> generation statistics, similar to how it is done for buffer usage.
> Collected is the number of records added to WAL and number of WAL
> bytes written.
>
> The data collected was found valuable to analyze update-heavy load,
> with WAL generation being the bottleneck.
>
> The usage data is collected at low level, after compression is done on
> WAL record. Data is then exposed via pg_stat_statements, could also be
> used in EXPLAIN ANALYZE if needed. Instrumentation is alike to the one
> used for buffer stats. I didn't dare to unify both usage metric sets
> into single struct, nor rework the way both are passed to parallel
> workers.
>
> Performance impact is (supposed to be) very low, essentially adding
> two int operations and memory access on WAL record insert. Additional
> efforts to allocate shmem chunk for parallel workers. Parallel workers
> shmem usage is increased to fir in a struct of two longs.
>
> Patch is separated in two parts: core changes and pg_stat_statements
> additions. Essentially the extension has its schema updated to allow
> two more fields, docs updated to reflect the change. Patch is prepared
> against master branch.
>
> Please provide your comments and/or code findings.

I like the concept, I'm a big fan of anything that affordably improves
visibility into Pg's I/O and activity.

To date I've been relying on tools like systemtap to do this sort of
thing. But that's a bit specialised, and Pg currently lacks useful
instrumentation for it so it can be a pain to match up activity by
parallel workers and that sort of thing. (I aim to find time to submit
a patch for that.)

I haven't yet reviewed the patch.

-- 
 Craig Ringer                   http://www.2ndQuadrant.com/
 2ndQuadrant - PostgreSQL Solutions for the Enterprise