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
