On Sun, Oct 13, 2019 at 06:27:35PM -0700, dangal wrote:
Dear I would like to share with you to see what you think about the
statistics of pg_stat_bgwriter
postgres = # select * from pg_stat_bgwriter;
checkpoints_timed | checkpoints_req | checkpoint_write_time |
checkpoint_sync_time | buffers_checkpoint | buffers_clean | maxwritten_clean
| buffers_backend | buffers_
backend_fsync | buffers_alloc | stats_reset
------------------- + ----------------- + ------------ ----------- +
---------------------- + --------------- ----- + --------------- +
------------------ + --------- -------- + ---------
-------------- + --------------- + ------------------- ------------
338 | 6 | 247061792 | 89418 | 2939561 | 19872289 | 54876 |
6015787 |
0 | 710682240 | 2019-10-06 19: 25: 30.688186-03
(1 row)
postgres = # show bgwriter_delay;
bgwriter_delay
----------------
200ms
(1 row)
postgres = # show bgwriter_lru_maxpages;
bgwriter_lru_maxpages
-----------------------
100
(1 row)
postgres = # show bgwriter_lru_multiplier;
bgwriter_lru_multiplier
-------------------------
2
(1 row)
Do you think it should increase bgwriter_lru_maxpages due to the value of
maxwritten_clean?
Do you think it should increase bgwriter_lru_maxpages,
bgwriter_lru_multiplier, and decrease bgwriter_delay due to the value of
buffers_backend compared to buffers_alloc?
Do you think a modification is necessary?
What values would you recommend?
buffers_alloc does not really matter, here, IMO. You need to compare
buffers_checkpoint, buffers_backend and buffers_clean, and ideally you'd
have (checkpoints > clean > backend). In your case it's already
buffers_checkpoint | buffers_clean | buffers_backend
2939561 | 19872289 | 6015787
You could make bgwriter even more aggressive, but that's unlikely to be
a huge improvement. You should investigate why buffers_checkpoint is so
low. This is usually a sign of shared_buffers being too small for the
active set, so perhaps you need to increase shared_buffers, or see which
queries are causing this and optimize them.
Note: FWIW, a single snapshot of pg_stats* may be misleading, because
it's cumulative, so it's not clear how accurately it reflects current
state. Next time take two snapshots and subtract them.
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
