Обсуждение: Question about buffers_alloc in pg_stat_bgwriter view for monitoring

Hi all,

Can someone tell me, what the value of buffers_alloc in the pg_stat_bgwriter view
(https://www.postgresql.org/docs/current/static/monitoring-stats.html#PG-STAT-BGWRITER-VIEW)is exactly? Is this the
amountof shared buffers used by the bgwriter? 

I’m working on a new PostgreSQL monitoring framework (https://github.com/alvar-freude/Posemo) and now look how to make
reasonablechecks (and as result graphs) for BGWriter activity. 

At the moment my plan is to make more then one check out of pg_stat_bgwriter:

 * BGWriterAmount
   A check which returns the amount of written data,
   taken from the following values multiplied by
   current_setting('block_size'):
   buffers_checkpoint, buffers_clean, buffers_backend

 * BGWriterCheckpoints:
   A check which returns the number of checkpoints:
   checkpoints_timed + checkpoints_req
   Or: checkpoints_timed, checkpoints_req

 * BGWriterTime:
   Time taken by the BGWriter
   checkpoint_write_time, checkpoint_sync_time

 * BGWriterAlloc:
   Amount of memory used by BGWriter
   buffers_alloc * current_setting('block_size‘)

 * BGWriterInfo:
   Extra values, which should go to an extra graph, because they
   are much smaller and usually only increase in special situations
   maxwritten_clean, buffers_backend_fsync


Is this a reasonable plan to divide pg_stat_bgwriter into this parts?



Thanks && bye
  Alvar



--
Alvar C.H. Freude | http://alvar.a-blast.org/
https://blog.alvar-freude.de/
https://www.wen-waehlen.de/

Am 28.03.2018 um 23:38 schrieb Alvar Freude:
> Hi all,
>
> Can someone tell me, what the value of buffers_alloc in the pg_stat_bgwriter view
(https://www.postgresql.org/docs/current/static/monitoring-stats.html#PG-STAT-BGWRITER-VIEW)is exactly? Is this the
amountof shared buffers used by the bgwriter? 

As I had to research that anyway, there's no reason not to write this
down here as well... (probably simplified, but I'm quite confident the
information is correct ;-):

Whenever a buffer is allocated, a global counter is incremented (see
"StrategyGetBuffer" in storage/buffer/freelist.c. That counter is used
by the BGWriter to determine its own wakeup/hibernate strategy, and
on-the-fly written to the global stats.

Thus, buffer_alloc is the global count of buffers allocated in the
cluster. That it appears in the bgwriter statistics is more or less
coincidental.

Best regards,
--
Gunnar "Nick" Bluth
RHCE/SCLA

Mobil +49 172 8853339
Email: gunnar.bluth@pro-open.de
_____________________________________________________________
In 1984 mainstream users were choosing VMS over UNIX.
Ten years later they are choosing Windows over UNIX.
What part of that message aren't you getting? - Tom Payne

Hi,

> Am 29.03.2018 um 10:30 schrieb Gunnar Nick Bluth <gunnar.bluth@pro-open.de>:
>
> Thus, buffer_alloc is the global count of buffers allocated in the
> cluster. That it appears in the bgwriter statistics is more or less
> coincidental.

So it is the number of shared_buffers used?

This isn’t possible:

postgres=# SELECT buffers_alloc*current_setting('block_size')::numeric/1024/1024/1024,
current_setting('shared_buffers')FROM pg_stat_bgwriter; 
      ?column?        | current_setting
-----------------------+-----------------
1219.7707748413085938 | 64450MB
(1 row)


About 64 GB shared buffers and 1219 used? ;-)


Or other machine:

      ?column?       | current_setting
----------------------+-----------------
126.4642944335937500 | 64450MB
(1 row)


My Private:

      ?column?      | current_setting
--------------------+-----------------
 3.3014221191406250 | 6GB
(1 Zeile)




Ciao
  Alvar

--
Alvar C.H. Freude | http://alvar.a-blast.org
https://blog.alvar-freude.de/
https://www.wen-waehlen.de/

Hi,

> Am 29.03.2018 um 10:30 schrieb Gunnar Nick Bluth <gunnar.bluth@pro-open.de>:
>
> Thus, buffer_alloc is the global count of buffers allocated in the
> cluster. That it appears in the bgwriter statistics is more or less
> coincidental.

But it seems not to be the total shared_buffers used, but the total number of allocated and re-allocated buffers. So it
incrementsevery time a buffer is allocated. Maybe I’m the only one who misunderstands it – or someone with better
englishthen me should update the docs. ;-) 


postgres=# SELECT buffers_alloc*current_setting('block_size')::numeric/1024/1024/1024,
current_setting('shared_buffers')FROM pg_stat_bgwriter; 
     ?column?        | current_setting
-----------------------+-----------------
1219.7707748413085938 | 64450MB
(1 row)


Or other machine:

     ?column?       | current_setting
----------------------+-----------------
126.4642944335937500 | 64450MB
(1 row)


Small one:

     ?column?      | current_setting
--------------------+-----------------
3.3014221191406250 | 6GB
(1 Zeile)


Ciao
 Alvar

--
Alvar C.H. Freude | http://alvar.a-blast.org
https://blog.alvar-freude.de/
https://www.wen-waehlen.de/

On 29/03/2018 20:24, Alvar Freude wrote:
> Hi,
>
>> Am 29.03.2018 um 10:30 schrieb Gunnar Nick Bluth <gunnar.bluth@pro-open.de>:
>>
>> Thus, buffer_alloc is the global count of buffers allocated in the
>> cluster. That it appears in the bgwriter statistics is more or less
>> coincidental.
> But it seems not to be the total shared_buffers used, but the total number of allocated and re-allocated buffers. So
itincrements every time a buffer is allocated. Maybe I’m the only one who misunderstands it – or someone with better
englishthen me should update the docs. ;-)
 
>
But shared_buffers represents the max no of postgresql buffers, per server, at any given time. It is a limit (constant)
nota metric. The count of all concurrent buffers at any given time must be less 
 
than shared_buffers.
So my question is : Does buffer_alloc represent the total read/write traffic of the database since the last reset?
>
> Ciao
>   Alvar
>
> --
> Alvar C.H. Freude | http://alvar.a-blast.org
> https://blog.alvar-freude.de/
> https://www.wen-waehlen.de/


-- 
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt