Horton

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

Kyotaro Horiguchi <horikyota.ntt@gmail.com>

View thread View on postgr.es
From: Kyotaro Horiguchi <horikyota.ntt@gmail.com>
To: tgl@sss.pgh.pa.us
Cc: andres@anarazel.de, melanieplageman@gmail.com, vignesh21@gmail.com, pryzby@telsasoft.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-06T06:24:25Z
Lists: pgsql-hackers 
At Sat, 04 Mar 2023 18:21:09 -0500, Tom Lane <tgl@sss.pgh.pa.us> wrote in 
> Andres Freund <andres@anarazel.de> writes:
> > Just pushed the actual pg_stat_io view, the splitting of the tablespace test,
> > and the pg_stat_io tests.
> 
> One of the test cases is flapping a bit:
> 
> diff -U3 /home/pg/build-farm-15/buildroot/HEAD/pgsql.build/src/test/regress/expected/stats.out /home/pg/build-farm-15/buildroot/HEAD/pgsql.build/src/test/regress/results/stats.out
> --- /home/pg/build-farm-15/buildroot/HEAD/pgsql.build/src/test/regress/expected/stats.out	2023-03-04 21:30:05.891579466 +0100
> +++ /home/pg/build-farm-15/buildroot/HEAD/pgsql.build/src/test/regress/results/stats.out	2023-03-04 21:34:26.745552661 +0100
> @@ -1201,7 +1201,7 @@
>  SELECT :io_sum_shared_after_reads > :io_sum_shared_before_reads;
>   ?column? 
>  ----------
> - t
> + f
>  (1 row)
>  
>  DROP TABLE test_io_shared;
> 
> There are two instances of this today [1][2], and I've seen it before
> but failed to note down where.

The concurrent autoanalyze below is logged as performing at least one
page read from the table. It is unclear, however, how that analyze
operation resulted in 19 hits and 2 reads on the (I think) single-page
relation.

In any case, I think we need to avoid such concurrent autovacuum/analyze.


https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=grison&dt=2023-03-04%2021%3A19%3A39

2023-03-04 22:36:27.781 CET [4073:106] pg_regress/stats LOG:  statement: ALTER TABLE test_io_shared SET TABLESPACE regress_tblspace;
2023-03-04 22:36:27.838 CET [4073:107] pg_regress/stats LOG:  statement: SELECT COUNT(*) FROM test_io_shared;
2023-03-04 22:36:27.864 CET [4255:5] LOG:  automatic analyze of table "regression.public.test_io_shared"
	avg read rate: 5.208 MB/s, avg write rate: 5.208 MB/s
	buffer usage: 17 hits, 2 misses, 2 dirtied
2023-03-04 22:36:28.024 CET [4073:108] pg_regress/stats LOG:  statement: SELECT pg_stat_force_next_flush();
2023-03-04 22:36:28.024 CET [4073:108] pg_regress/stats LOG:  statement: SELECT pg_stat_force_next_flush();
2023-03-04 22:36:28.027 CET [4073:109] pg_regress/stats LOG:  statement: SELECT sum(reads) AS io_sum_shared_after_reads
	  FROM pg_stat_io WHERE io_context = 'normal' AND io_object = 'relation'  



> [1] https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=grison&dt=2023-03-04%2021%3A19%3A39
> [2] https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=mule&dt=2023-03-04%2020%3A30%3A05

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center

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.