Horton

Re: pg_stat_bgwriter.buffers_backend is pretty meaningless (and more?)

Melanie Plageman <melanieplageman@gmail.com>

View thread View on postgr.es
From: Melanie Plageman <melanieplageman@gmail.com>
To: Lukas Fittl <lukas@fittl.com>
Cc: Andres Freund <andres@anarazel.de>, Justin Pryzby <pryzby@telsasoft.com>, Alvaro Herrera <alvherre@alvh.no-ip.org>, Kyotaro Horiguchi <horikyota.ntt@gmail.com>, Magnus Hagander <magnus@hagander.net>, Pg Hackers <pgsql-hackers@postgresql.org>, Thomas Munro <thomas.munro@gmail.com>, Maciek Sakrejda <m.sakrejda@gmail.com>
Date: 2022-10-06T22:23:53Z
Lists: pgsql-hackers

Attachments

v31 failed in CI, so
I've attached v32 which has a few issues fixed:
- addressed some compiler warnings I hadn't noticed locally
- autovac launcher and worker do indeed use bulkread strategy if they
  end up starting before critical indexes have loaded and end up doing a
  sequential scan of some catalog tables, so I have changed the
  restrictions on BackendTypes allowed to track IO Operations in
  IOCONTEXT_BULKREAD
- changed the name of the column "fsynced" to "files_synced" to make it
  more clear what unit it is in (and that the unit differs from that of
  the "unit" column)

In an off-list discussion with Andres, he mentioned that he thought
buffers reused by a BufferAccessStrategy should be split from buffers
"acquired" and that "acquired" should be renamed "clocksweeps".

I have started doing this, but for BufferAccessStrategy IO there are a
few choices about how we want to count the clocksweeps:

Currently the following situations are counted under the following
IOContexts and IOOps:

IOCONTEXT_[VACUUM,BULKREAD,BULKWRITE], IOOP_ACQUIRE
- reuse a buffer from the ring

IOCONTEXT_SHARED, IOOP_ACQUIRE
- add a buffer to the strategy ring initially
- add a new shared buffer to the ring when all the existing buffers in
  the ring are pinned

And in the new paradigm, I think these are two good options:

1)
IOCONTEXT_[VACUUM,BULKREAD,BULKWRITE], IOOP_CLOCKSWEEP
- add a buffer to the strategy ring initially
- add a new shared buffer to the ring when all the existing buffers in
  the ring are pinned

IOCONTEXT_[VACUUM,BULKREAD,BULKWRITE], IOOP_REUSE
- reuse a buffer from the ring

2)
IOCONTEXT_[VACUUM,BULKREAD,BULKWRITE], IOOP_CLOCKSWEEP
- add a buffer to the strategy ring initially

IOCONTEXT_[VACUUM,BULKREAD,BULKWRITE], IOOP_REUSE
- reuse a buffer from the ring

IOCONTEXT SHARED, IOOP_CLOCKSWEEP
- add a new shared buffer to the ring when all the existing buffers in
  the ring are pinned

However, if we want to differentiate between buffers initially added to
the ring and buffers taken from shared buffers and added to the ring
because all strategy ring buffers are pinned or have a usage count above
one, then we would need to either do so inside of GetBufferFromRing() or
propagate this distinction out somehow (easy enough if we care to do
it).

There are other combinations that I could come up with a justification
for as well, but I wanted to know what other people thought made sense
(and would make sense to users).

- Melanie

Commits

  1. Stabilize pg_stat_io writes test

  2. Fix flakey pg_stat_io test

  3. Suppress more compiler warnings in new pgstats code.

  4. Suppress compiler warnings in new pgstats code.

  5. Add tests for pg_stat_io

  6. Create regress_tblspc in test_setup

  7. Add pg_stat_io view, providing more detailed IO statistics

  8. pgstat: Track more detailed relation IO statistics

  9. pgstat: Infrastructure for more detailed IO statistics

  10. doc: Fix some issues in logical replication section

  11. Manual cleanup and pgindent of pgstat and bufmgr related code

  12. Have the planner consider Incremental Sort for DISTINCT

  13. Use actual backend IDs in pg_stat_get_backend_idset() and friends.

  14. Remove redundant call to pgstat_report_wal()

  15. Add BackendType for standalone backends

  16. Initialize backend status reporting during bootstrap.