Date: Mon, 14 Oct 2019 20:18:47 +0200
From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: dangal <danielito.gallo@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: pg_stat_bgwriter
Message-ID: <20191014181847.6lf5s3fwuh7ui25r@development>
References: <1571016455698-0.post@n3.nabble.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <1571016455698-0.post@n3.nabble.com>
User-Agent: NeoMutt/20180716
Precedence: bulk

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