Horton

Re: pg_stat_statements issue with parallel maintenance (Was Re: WAL usage calculation patch)

Masahiko Sawada <masahiko.sawada@2ndquadrant.com>

View thread
From: Masahiko Sawada <masahiko.sawada@2ndquadrant.com>
To: Peter Geoghegan <pg@bowt.ie>
Cc: Amit Kapila <amit.kapila16@gmail.com>, Julien Rouhaud <rjuju123@gmail.com>, Dilip Kumar <dilipbalaut@gmail.com>, Robert Haas <robertmhaas@gmail.com>, PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Date: 2020-04-07T07:59:39Z
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.

Attachments

On Tue, 7 Apr 2020 at 02:40, Peter Geoghegan <pg@bowt.ie> wrote:
>
> On Mon, Apr 6, 2020 at 2:21 AM Amit Kapila <amit.kapila16@gmail.com> wrote:
> > AFAIU, it uses heapam_index_build_range_scan but for writing to index,
> > it doesn't use buffer manager.
>
> Right. It doesn't need to use the buffer manager to write to the
> index, unlike (say) GIN's CREATE INDEX.

Hmm, after more thoughts and testing, it seems to me that parallel
btree index creation uses buffer manager while scanning the table in
parallel, i.e in heapam_index_build_range_scan, which affects
shared_blks_xxx in pg_stat_statements. I've some parallel create index
tests with the current HEAD and with the attached patch. The table has
44248 blocks.

HEAD, no workers:

-[ RECORD 1 ]-------+----------
total_plan_time     | 0
total_plan_time     | 0
shared_blks_hit     | 148
shared_blks_read    | 44281
total_read_blks     | 44429
shared_blks_dirtied | 44261
shared_blks_written | 24644
wal_records         | 71693
wal_num_fpw         | 71682
wal_bytes           | 566815038

HEAD, 4 workers:

-[ RECORD 1 ]-------+----------
total_plan_time     | 0
total_plan_time     | 0
shared_blks_hit     | 160
shared_blks_read    | 8892
total_read_blks     | 9052
shared_blks_dirtied | 8871
shared_blks_written | 5342
wal_records         | 71693
wal_num_fpw         | 71682
wal_bytes           | 566815038

The WAL usage statistics are good but the buffer usage statistics seem
not correct.

Patched, no workers:

-[ RECORD 1 ]-------+----------
total_plan_time     | 0
total_plan_time     | 0
shared_blks_hit     | 148
shared_blks_read    | 44281
total_read_blks     | 44429
shared_blks_dirtied | 44261
shared_blks_written | 24843
wal_records         | 71693
wal_num_fpw         | 71682
wal_bytes           | 566815038

Patched, 4 workers:

-[ RECORD 1 ]-------+----------
total_plan_time     | 0
total_plan_time     | 0
shared_blks_hit     | 172
shared_blks_read    | 44282
total_read_blks     | 44454
shared_blks_dirtied | 44261
shared_blks_written | 26968
wal_records         | 71693
wal_num_fpw         | 71682
wal_bytes           | 566815038

Buffer usage statistics seem correct. The small differences would be
catalog lookups Peter mentioned.

Regards,

-- 
Masahiko Sawada            http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services