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-10T18:48:49Z
Lists: pgsql-hackers

Attachments

I've gone ahead and implemented option 1 (commented below).

On Thu, Oct 6, 2022 at 6:23 PM Melanie Plageman
<melanieplageman@gmail.com> wrote:
>
> 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
>

I've implemented this option in attached v33.

> 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


- 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.