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: Justin Pryzby <pryzby@telsasoft.com>
Cc: Andres Freund <andres@anarazel.de>, 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:33:44Z
Lists: pgsql-hackers 
On Fri, Mar 10, 2023 at 3:19 PM Justin Pryzby <pryzby@telsasoft.com> wrote:
>
> On Thu, Mar 09, 2023 at 11:43:01AM -0800, Andres Freund wrote:
> > > 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.

I think your patch made it more likely that a backend needing to flush a
buffer in order to fit its own data would be doing so in a buffer access
strategy IO context.

Your patch makes it so those toast table writes are using a
BAS_BULKWRITE (see GetBulkInsertState()) and when they are looking for
buffers to put their data in, they have to evict other data (theirs and
others) but all of this is tracked in io_context = 'bulkwrite' -- and
the test only counted writes done in io_context 'normal'. But it is good
that your patch did that! It helped us to see that this test is not
reliable.

The other times this test failed in cfbot were for a patch that had many
failures and might have something wrong with its code, IIRC.

Thanks again for the report!

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