Inconsitancies in pg_stat_bgwriter and pg_stat_database returned values

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Inconsitancies in pg_stat_bgwriter and pg_stat_database returned values

RECHTÉ Marc
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


Reply | Threaded
Open this post in threaded view
|

Re: Inconsitancies in pg_stat_bgwriter and pg_stat_database returned values

Jehan-Guillaume de Rorthais
On Wed, 16 Sep 2020 17:06:45 +0200 (CEST)
RECHTÉ Marc <[hidden email]> 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,


Reply | Threaded
Open this post in threaded view
|

Re: Inconsitancies in pg_stat_bgwriter and pg_stat_database returned values

RECHTÉ Marc
On Wed, 16 Sep 2020 17:06:45 +0200 (CEST)
RECHTÉ Marc <[hidden email]> 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" <[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


Reply | Threaded
Open this post in threaded view
|

Re: Inconsitancies in pg_stat_bgwriter and pg_stat_database returned values

Jehan-Guillaume de Rorthais
On Thu, 17 Sep 2020 08:38:10 +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,
>
> 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,


Reply | Threaded
Open this post in threaded view
|

Re: Inconsitancies in pg_stat_bgwriter and pg_stat_database returned values

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

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