pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Inconsitancies in pg_stat_bgwriter and pg_stat_database returned values
5+ messages / 2 participants
[nested] [flat]

* Inconsitancies in pg_stat_bgwriter and pg_stat_database returned values
@ 2020-09-16 15:06 RECHTÉ Marc <marc.rechte@meteo.fr>
  2020-09-16 15:31 ` Re: Inconsitancies in pg_stat_bgwriter and pg_stat_database returned values Jehan-Guillaume de Rorthais <jgdr@dalibo.com>
  0 siblings, 1 reply; 5+ messages in thread

From: RECHTÉ Marc @ 2020-09-16 15:06 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org

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





^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Inconsitancies in pg_stat_bgwriter and pg_stat_database returned values
  2020-09-16 15:06 Inconsitancies in pg_stat_bgwriter and pg_stat_database returned values RECHTÉ Marc <marc.rechte@meteo.fr>
@ 2020-09-16 15:31 ` Jehan-Guillaume de Rorthais <jgdr@dalibo.com>
  2020-09-17 06:38   ` Re: Inconsitancies in pg_stat_bgwriter and pg_stat_database returned values RECHTÉ Marc <marc.rechte@meteo.fr>
  0 siblings, 1 reply; 5+ messages in thread

From: Jehan-Guillaume de Rorthais @ 2020-09-16 15:31 UTC (permalink / raw)
  To: RECHTÉ Marc <marc.rechte@meteo.fr>; +Cc: pgsql-bugs@lists.postgresql.org

On Wed, 16 Sep 2020 17:06:45 +0200 (CEST)
RECHTÉ Marc <marc.rechte@meteo.fr> wrote:

> 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:  [...]
>
> 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. 

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,





^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Inconsitancies in pg_stat_bgwriter and pg_stat_database returned values
  2020-09-16 15:06 Inconsitancies in pg_stat_bgwriter and pg_stat_database returned values RECHTÉ Marc <marc.rechte@meteo.fr>
  2020-09-16 15:31 ` Re: Inconsitancies in pg_stat_bgwriter and pg_stat_database returned values Jehan-Guillaume de Rorthais <jgdr@dalibo.com>
@ 2020-09-17 06:38   ` RECHTÉ Marc <marc.rechte@meteo.fr>
  2020-09-17 07:39     ` Re: Inconsitancies in pg_stat_bgwriter and pg_stat_database returned values Jehan-Guillaume de Rorthais <jgdr@dalibo.com>
  0 siblings, 1 reply; 5+ messages in thread

From: RECHTÉ Marc @ 2020-09-17 06:38 UTC (permalink / raw)
  To: Jehan-Guillaume de Rorthais <jgdr@dalibo.com>; +Cc: pgsql-bugs@lists.postgresql.org

On Wed, 16 Sep 2020 17:06:45 +0200 (CEST)
RECHTÉ Marc <marc.rechte@meteo.fr> wrote:

> 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:  [...]
>
> 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. 


----- Mail original -----
De: "Jehan-Guillaume de Rorthais" <jgdr@dalibo.com>
À: "RECHTÉ Marc" <marc.rechte@meteo.fr>
Cc: pgsql-bugs@lists.postgresql.org
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





^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Inconsitancies in pg_stat_bgwriter and pg_stat_database returned values
  2020-09-16 15:06 Inconsitancies in pg_stat_bgwriter and pg_stat_database returned values RECHTÉ Marc <marc.rechte@meteo.fr>
  2020-09-16 15:31 ` Re: Inconsitancies in pg_stat_bgwriter and pg_stat_database returned values Jehan-Guillaume de Rorthais <jgdr@dalibo.com>
  2020-09-17 06:38   ` Re: Inconsitancies in pg_stat_bgwriter and pg_stat_database returned values RECHTÉ Marc <marc.rechte@meteo.fr>
@ 2020-09-17 07:39     ` Jehan-Guillaume de Rorthais <jgdr@dalibo.com>
  2020-09-17 11:02       ` Re: Inconsitancies in pg_stat_bgwriter and pg_stat_database returned values RECHTÉ Marc <marc.rechte@meteo.fr>
  0 siblings, 1 reply; 5+ messages in thread

From: Jehan-Guillaume de Rorthais @ 2020-09-17 07:39 UTC (permalink / raw)
  To: RECHTÉ Marc <marc.rechte@meteo.fr>; +Cc: pgsql-bugs@lists.postgresql.org

On Thu, 17 Sep 2020 08:38:10 +0200 (CEST)
RECHTÉ Marc <marc.rechte@meteo.fr> 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,
> 
> 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.

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,





^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Inconsitancies in pg_stat_bgwriter and pg_stat_database returned values
  2020-09-16 15:06 Inconsitancies in pg_stat_bgwriter and pg_stat_database returned values RECHTÉ Marc <marc.rechte@meteo.fr>
  2020-09-16 15:31 ` Re: Inconsitancies in pg_stat_bgwriter and pg_stat_database returned values Jehan-Guillaume de Rorthais <jgdr@dalibo.com>
  2020-09-17 06:38   ` Re: Inconsitancies in pg_stat_bgwriter and pg_stat_database returned values RECHTÉ Marc <marc.rechte@meteo.fr>
  2020-09-17 07:39     ` Re: Inconsitancies in pg_stat_bgwriter and pg_stat_database returned values Jehan-Guillaume de Rorthais <jgdr@dalibo.com>
@ 2020-09-17 11:02       ` RECHTÉ Marc <marc.rechte@meteo.fr>
  0 siblings, 0 replies; 5+ messages in thread

From: RECHTÉ Marc @ 2020-09-17 11:02 UTC (permalink / raw)
  To: Jehan-Guillaume de Rorthais <jgdr@dalibo.com>; +Cc: pgsql-bugs@lists.postgresql.org

----- Mail original -----
De: "Jehan-Guillaume de Rorthais" <jgdr@dalibo.com>
À: "RECHTÉ Marc" <marc.rechte@meteo.fr>
Cc: pgsql-bugs@lists.postgresql.org
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 <marc.rechte@meteo.fr> 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,
> 
> 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.

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.





^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2020-09-17 11:02 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-09-16 15:06 Inconsitancies in pg_stat_bgwriter and pg_stat_database returned values RECHTÉ Marc <marc.rechte@meteo.fr>
2020-09-16 15:31 ` Jehan-Guillaume de Rorthais <jgdr@dalibo.com>
2020-09-17 06:38   ` RECHTÉ Marc <marc.rechte@meteo.fr>
2020-09-17 07:39     ` Jehan-Guillaume de Rorthais <jgdr@dalibo.com>
2020-09-17 11:02       ` RECHTÉ Marc <marc.rechte@meteo.fr>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox