Horton

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

Andres Freund <andres@anarazel.de>

View thread View on postgr.es
From: Andres Freund <andres@anarazel.de>
To: Melanie Plageman <melanieplageman@gmail.com>
Cc: 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>, Lukas Fittl <lukas@fittl.com>, Thomas Munro <thomas.munro@gmail.com>
Date: 2022-10-02T17:24:04Z
Lists: pgsql-hackers 
Hi,

On 2022-09-27 14:20:44 -0400, Melanie Plageman wrote:
> v30 attached
> rebased and pgstat_io_ops.c builds with meson now
> also, I tested with pgstat_report_stat() only flushing when forced and
> tests still pass

Unfortunately tests fail in CI / cfbot. E.g.,
https://cirrus-ci.com/task/5816109319323648

https://api.cirrus-ci.com/v1/artifact/task/5816109319323648/testrun/build/testrun/main/regress/regression.diffs
diff -U3 /tmp/cirrus-ci-build/src/test/regress/expected/stats.out /tmp/cirrus-ci-build/build/testrun/main/regress/results/stats.out
--- /tmp/cirrus-ci-build/src/test/regress/expected/stats.out	2022-10-01 12:07:47.779183501 +0000
+++ /tmp/cirrus-ci-build/build/testrun/main/regress/results/stats.out	2022-10-01 12:11:38.686433303 +0000
@@ -997,6 +997,8 @@
 -- Set temp_buffers to a low value so that we can trigger writes with fewer
 -- inserted tuples.
 SET temp_buffers TO '1MB';
+ERROR:  invalid value for parameter "temp_buffers": 128
+DETAIL:  "temp_buffers" cannot be changed after any temporary tables have been accessed in the session.
 CREATE TEMPORARY TABLE test_io_local(a int, b TEXT);
 SELECT sum(alloc) AS io_sum_local_allocs_before FROM pg_stat_io WHERE io_context = 'local' \gset
 SELECT sum(read) AS io_sum_local_reads_before FROM pg_stat_io WHERE io_context = 'local' \gset
@@ -1037,7 +1039,7 @@
 SELECT :io_sum_local_writes_after > :io_sum_local_writes_before;
  ?column? 
 ----------
- t
+ f
 (1 row)
 
 SELECT :io_sum_local_extends_after > :io_sum_local_extends_before;


So the problem is just that something else accesses temp buffers earlier in
the same test.

That's likely because since you sent your email

commit d7e39d72ca1c6f188b400d7d58813ff5b5b79064
Author: Tom Lane <tgl@sss.pgh.pa.us>
Date:   2022-09-29 12:14:39 -0400
 
    Use actual backend IDs in pg_stat_get_backend_idset() and friends.

was applied, which adds a temp table earlier in the same session.


I think the easiest way to make this robust would be to just add a reconnect
before the place you need to set temp_buffers, that way additional temp tables
won't cause a problem.

Setting the patch to waiting-for-author for now.

Greetings,

Andres Freund

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.