Horton

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

Justin Pryzby <pryzby@telsasoft.com>

View thread View on postgr.es
From: Justin Pryzby <pryzby@telsasoft.com>
To: Andres Freund <andres@anarazel.de>
Cc: Melanie Plageman <melanieplageman@gmail.com>, Kyotaro Horiguchi <horikyota.ntt@gmail.com>, tgl@sss.pgh.pa.us, vignesh21@gmail.com, lukas@fittl.com, alvherre@alvh.no-ip.org, magnus@hagander.net, pgsql-hackers@postgresql.org, thomas.munro@gmail.com, m.sakrejda@gmail.com
Date: 2023-03-10T20:19:04Z
Lists: pgsql-hackers 
On Thu, Mar 09, 2023 at 11:43:01AM -0800, Andres Freund wrote:
> On 2023-03-09 06:51:31 -0600, Justin Pryzby wrote:
> > On Tue, Mar 07, 2023 at 10:18:44AM -0800, Andres Freund wrote:
> > > Hi,
> > > 
> > > On 2023-03-06 15:21:14 -0500, Melanie Plageman wrote:
> > > > Good point. Attached is what you suggested. I committed the transaction
> > > > before the drop table so that the statistics would be visible when we
> > > > queried pg_stat_io.
> > > 
> > > Pushed, thanks for the report, analysis and fix, Tom, Horiguchi-san, Melanie.
> > 
> > There's a 2nd portion of the test that's still flapping, at least on
> > cirrusci.
> > 
> > The issue that Tom mentioned is at:
> >  SELECT :io_sum_shared_after_writes > :io_sum_shared_before_writes;
> > 
> > But what I've seen on cirrusci is at:
> >  SELECT :io_sum_shared_after_writes > :io_sum_shared_before_writes;
> 
> Seems you meant to copy a different line for Tom's (s/writes/redas/)?

Seems so

> > https://api.cirrus-ci.com/v1/artifact/task/6701069548388352/log/src/test/recovery/tmp_check/regression.diffs
> 
> Hm. I guess the explanation here is that the buffers were already all written
> out by another backend. Which is made more likely by your patch.

FYI: that patch would've made it more likely for each backend to write
out its *own* dirty pages of TOAST ... but the two other failures that I
mentioned were for patches which wouldn't have affected this at all.

-- 
Justin

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.