From: | Rene Romero Benavides <rene(dot)romero(dot)b(at)gmail(dot)com> |
---|---|
To: | Postgres General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: PostgreSQL 10.12. background writer not cleaning buffers, small working set, big shared_buffers |
Date: | 2020-08-14 07:11:46 |
Message-ID: | CANaGW08j72iLzyMy-5Vru8bp78L5OwEgUVY08LFWZRE30mMhMg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Btw, these are its wal / checkpoint related settings:
activemq1=# select name,setting,unit from pg_settings where category =
'Write-Ahead Log / Checkpoints';
name | setting | unit
------------------------------+---------+------
checkpoint_completion_target | 0.9 |
checkpoint_flush_after | 32 | 8kB
checkpoint_timeout | 300 | s
checkpoint_warning | 30 | s
max_wal_size | 4096 | MB
min_wal_size | 2048 | MB
Thank you.
On Fri, Aug 14, 2020 at 1:39 AM Rene Romero Benavides <
rene(dot)romero(dot)b(at)gmail(dot)com> wrote:
> Hello Postgres community.
> In a few words I'm figuring out this stats from the background writer
> where you can see buffers_clean is 0 and buffers_backend = 44849371, I
> would like it to be the other way around, that's more efficient, right?
>
> -[ RECORD 1 ]---------+------------------------------
> checkpoints_timed | 97504
> checkpoints_req | 4
> checkpoint_write_time | 21171407908
> checkpoint_sync_time | 684641
> buffers_checkpoint | 273934008
> buffers_clean | 0
> maxwritten_clean | 0
> buffers_backend | 44849371
> buffers_backend_fsync | 0
> buffers_alloc | 44744713
> stats_reset | 2019-09-10 08:42:34.490899-07
>
> Using default settings
> postgres=# select name,setting,unit from pg_settings where name like
> '%bgwriter%'
> ;
> name | setting | unit
> -------------------------+---------+------
> bgwriter_delay | 200 | ms
> bgwriter_flush_after | 64 | 8kB
> bgwriter_lru_maxpages | 100 |
> bgwriter_lru_multiplier | 2 |
>
> shared_buffers = '6GB'
> and the VM has a RAM of 24GB
>
> The working set is about 160MB, I know, shared buffers are oversized for
> this DB, and I know that the bgwriter is supposed to do work when the
> working set doesn't fit into shared_buffers, but backends cleaning their
> buffers produces undesirable sync waits, right ?
> Any advice ?
>
> Thank you.
>
>
> --
> El genio es 1% inspiración y 99% transpiración.
> Thomas Alva Edison
>
>
>
--
El genio es 1% inspiración y 99% transpiración.
Thomas Alva Edison
http://pglearn.blogspot.mx/
From | Date | Subject | |
---|---|---|---|
Next Message | Brajendra Pratap Singh | 2020-08-14 11:59:39 | Block Corruption Resolution |
Previous Message | Rene Romero Benavides | 2020-08-14 06:39:20 | PostgreSQL 10.12. background writer not cleaning buffers, small working set, big shared_buffers |