WAL usage calculation patch

Kirill Bychik <kirill.bychik@gmail.com>

From: Kirill Bychik <kirill.bychik@gmail.com>

To: pgsql-hackers@postgresql.org

Date: 2020-02-05T13:35:59Z

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 →

Change the display of WAL usage statistics in Explain.
- 69bfaf2e1de4 13.0 landed
Cosmetic fixups for WAL usage work.
- ef08ca113fe3 13.0 landed
Allow parallel create index to accumulate buffer usage stats.
- 5c71362174eb 13.0 landed
- f179e9f01b65 11.8 landed
- 13823ad3b012 12.3 landed
Allow autovacuum to log WAL usage statistics.
- b7ce6de93b59 13.0 landed
Add the option to report WAL usage in EXPLAIN and auto_explain.
- 33e05f89c53e 13.0 landed
Allow pg_stat_statements to track WAL usage statistics.
- 6b466bf5f2be 13.0 landed
Add infrastructure to track WAL usage.
- df3b181499b4 13.0 landed
Include information on buffer usage during planning phase, in EXPLAIN output, take two.
- ce77abe63cfc 13.0 cited
Include information on buffer usage during planning phase, in EXPLAIN output.
- ed7a5095716e 13.0 cited
Allow parallel vacuum to accumulate buffer usage.
- 3a5e22138a8d 13.0 landed
Allow pg_stat_statements to track planning statistics.
- 17e03282241c 13.0 cited

Attachments

wal_stats.ext.patch (application/octet-stream) patch
wal_stats.core.patch (application/octet-stream) patch

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.