Inconsitancies in pg_stat_bgwriter and pg_stat_database returned values

Hello,

On one particular PG12.3 instance (same behaviour with PG12.4) we are experimenting occasional strange values in the above tables.

For instance looping (10s. period) over this request:

SELECT current_timestamp, checkpoints_timed,
checkpoints_req,
checkpoint_write_time,
checkpoint_sync_time,
buffers_checkpoint,
buffers_clean,
maxwritten_clean,
buffers_backend,
buffers_backend_fsync,
buffers_alloc
FROM pg_stat_bgwriter

Gives:

020-09-16 13:41:39.781609+00,438,2,10095976,0,611207,870625,8695,612255,0,39028850
2020-09-16 13:41:49.815612+00,438,2,10095976,0,611207,870625,8695,612255,0,39028850
2020-09-16 13:41:59.851812+00,438,2,10095976,0,611207,870625,8695,612255,0,39028850
2020-09-16 13:42:09.89053+00,438,2,10095976,0,611207,870625,8695,612255,0,39028850
2020-09-16 13:42:19.923291+00,438,2,10095976,0,611207,870625,8695,612255,0,39028850
2020-09-16 13:42:29.959282+00,3291,227,1278516493,26327,66708986,1743379,16800,253585756,0,59080296
2020-09-16 13:42:39.98198+00,438,2,10095976,0,611207,870625,8695,612255,0,39028850
2020-09-16 13:42:50.028727+00,438,2,10095976,0,611207,870625,8695,612255,0,39028850
2020-09-16 13:43:00.067928+00,3291,227,1278516493,26327,66709285,1743379,16800,253585756,0,59080296
2020-09-16 13:43:10.090266+00,438,2,10095976,0,611207,870625,8695,612255,0,39028850
2020-09-16 13:43:20.140272+00,438,2,10095976,0,611207,870625,8695,612255,0,39028850
2020-09-16 13:43:30.170372+00,3291,227,1278516493,26327,66709585,1743379,16800,253585756,0,59080296
2020-09-16 13:43:40.204747+00,438,2,10095976,0,611207,870625,8695,612255,0,39028850
2020-09-16 13:43:50.239374+00,438,2,10095976,0,611207,870625,8695,612255,0,39028850
2020-09-16 13:44:00.26905+00,3291,227,1278516493,26327,66709884,1743379,16800,253585756,0,59080296
2020-09-16 13:44:10.314927+00,438,2,10095976,0,611207,870625,8695,612255,0,39028850
2020-09-16 13:44:20.351091+00,438,2,10095976,0,611207,870625,8695,612255,0,39028850
2020-09-16 13:44:30.408797+00,3291,227,1278516493,26327,66710184,1743379,16800,253585756,0,59080296
2020-09-16 13:44:40.44833+00,438,2,10095976,0,611207,870625,8695,612255,0,39028850
2020-09-16 13:44:50.484298+00,438,2,10095976,0,611207,870625,8695,612255,0,39028850

One can see that some columns that are supposed to increase only are not always. For instance checkpoints_timed suddenly jumps from 438 to 3291, then back to 438.

We experiment the same issue in pg_stat_database.

Thanks

On Wed, 16 Sep 2020 17:06:45 +0200 (CEST)
RECHTÉ Marc <[hidden email]> wrote:

The full row are different in your example, not just a few fields. It looks
like mixed values from different instance.

Could you share some more informations about the context and how to reproduce
it?

Regards,

On Wed, 16 Sep 2020 17:06:45 +0200 (CEST)
RECHTÉ Marc <[hidden email]> wrote:


----- Mail original -----
De: "Jehan-Guillaume de Rorthais" <[hidden email]>
À: "RECHTÉ Marc" <[hidden email]>
Cc: [hidden email]
Envoyé: Mercredi 16 Septembre 2020 17:31:24
Objet: Re: Inconsitancies in pg_stat_bgwriter and pg_stat_database returned values

The full row are different in your example, not just a few fields. It looks
like mixed values from different instance.

Could you share some more informations about the context and how to reproduce
it?

Regards,

Hello

Thanks a lot, you got it right: there are 2 instances on this machine and they are both configured with:

stats_temp_directory = '/dev/shm'

We some time ago tried, stats_temp_directory = '/dev/shm/instance', but PostgreSQL ignored the subdirectory part and kept creating the temp file at the FS root.
As this is a temporary FS, one cannot in advance create the instance subdirectory as it is wiped out between reboots.

Best regards

On Thu, 17 Sep 2020 08:38:10 +0200 (CEST)
RECHTÉ Marc <[hidden email]> wrote:
[...]
Use "/var/run/postgresql/instance_name" and setup systemd tmpfiles to create
the folder automatically. Eg.:

  cat <<EOF > /etc/tmpfiles.d/postgresql-stats.conf
  # Directory for PostgreSQL temp stat files
  d /var/run/postgresql/stats-instance_name 0700 postgres postgres - -
  EOF

You can add as many folders as you need.

To take this file in consideration immediately without rebooting the server,
run the following command:

 systemd-tmpfiles --create /etc/tmpfiles.d/postgresql-stats.conf

Regards,

----- Mail original -----
De: "Jehan-Guillaume de Rorthais" <[hidden email]>
À: "RECHTÉ Marc" <[hidden email]>
Cc: [hidden email]
Envoyé: Jeudi 17 Septembre 2020 09:39:44
Objet: Re: Inconsitancies in pg_stat_bgwriter and pg_stat_database returned values

On Thu, 17 Sep 2020 08:38:10 +0200 (CEST)
RECHTÉ Marc <[hidden email]> wrote:
[...]
Use "/var/run/postgresql/instance_name" and setup systemd tmpfiles to create
the folder automatically. Eg.:

  cat <<EOF > /etc/tmpfiles.d/postgresql-stats.conf
  # Directory for PostgreSQL temp stat files
  d /var/run/postgresql/stats-instance_name 0700 postgres postgres - -
  EOF

You can add as many folders as you need.

To take this file in consideration immediately without rebooting the server,
run the following command:

 systemd-tmpfiles --create /etc/tmpfiles.d/postgresql-stats.conf

Regards,


I applied your suggestions. Thanks again.

Marc.