Horton

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

Alvaro Herrera <alvherre@alvh.no-ip.org>

View thread View on postgr.es
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Melanie Plageman <melanieplageman@gmail.com>
Cc: Kyotaro Horiguchi <horikyota.ntt@gmail.com>, Andres Freund <andres@anarazel.de>, Magnus Hagander <magnus@hagander.net>, Pg Hackers <pgsql-hackers@postgresql.org>, lukas@fittl.com, Thomas Munro <thomas.munro@gmail.com>
Date: 2021-06-04T21:52:17Z
Lists: pgsql-hackers 
On 2021-Apr-12, Melanie Plageman wrote:

> As for the way I have recorded strategy writes -- it is quite inelegant,
> but, I wanted to make sure that I only counted a strategy write as one
> in which the backend wrote out the dirty buffer from its strategy ring
> but did not check if there was any clean buffer in shared buffers more
> generally (so, it is *potentially* an avoidable write). I'm not sure if
> this distinction is useful to anyone. I haven't done enough with
> BufferAccessStrategies to know what I'd want to know about them when
> developing or using Postgres. However, if I don't need to be so careful,
> it will make the code much simpler (though, I'm sure I can improve the
> code regardless).

I was bitten last year by REFRESH MATERIALIZED VIEW counting its writes
via buffers_backend, and I was very surprised/confused about it.  So it
seems definitely worthwhile to count writes via strategy separately.
For a DBA tuning the server configuration it is very useful.

The main thing is to *not* let these writes end up regular
buffers_backend (or whatever you call these now).  I didn't read your
patch, but the way you have described it seems okay to me.

-- 
Álvaro Herrera                            39°49'30"S 73°17'W

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.