Background writer not active

From: Motog Plus <mplus7535(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Background writer not active
Date: 2025-03-18 04:04:30
Message-ID: CAL5Gnite-=yP+AaotgyO4n+Z5ZwvSu=D_d8QE_sigmTaL82p9A@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hi Team,

Hope you are doing well!
We are doing performance testing of our applications. During testing our
postgres db got crashed with the error checkpointer was killed by signal 9.
When checked system logs we found it was OOM issue.
We have postgreSQL on independent node with 32GB RAM, multiple DBs are
there witl multiple schemas.
Our current configuration is as follows:
Shared buffers 12 GB(increased from 10)
Checkpoint timeout 15 mins
Checkpoint completion target 0.9
Work_mem 6 MB
Maintenance work mem 1 gb
Effective cache size 20 GB.
Active connections around 1500-2000.
While analysing issue we made below changes:
Increased shared buffers from. 10 to 12 gb as buffers_alloc was getting
increased
Bgwriter_delay was 200 ms, reduced to 100ms
Bgwriter multiplier increased from 2 to 4 and then reduced to 3
Bgwriter max pages increased from 100 to 1000
We changed above bgwriter parameters to make bgwriter more aggressive but
still we see most of the writes are being done by checkpointer as indicated
by buffers_checkpoint and then by backends indicated by buffers_backend and
then by bgwriter indicated by buffers_clean. On an average 79% done by
checkpointer, 16.5% by backends and 4.5% by bgwriter. Also buffers_alloc
show huge number. All the stats taken from bg_writer_stats. I observed for
1 hour the stats form bgwriter after reducing bgwriter multiplier from 4 to
3 but buffers_clean value remained constant.

Below are the stats for last 8 hours:
Buffers cleaned/written during checkpoints: 3243044. 83%
Buffers cleaned/written by bgwriter: 55430. 1%
cleaned/written by backends:
616659. 16%
Buffers_alloc difference: 2980619

Can you please advise on how to make bgwriter more active or am I missing
to validate anything.
Also how to keep balance between bgwriter parameters - delay, multiplier
and maxpages

Also please advise can we tune any parameters to fix the OOM error that I
mentioned in the starting, apart from looking at the queries.
Also if I want to check what queries might have caused the memory issue,
that would be queries just above the checkpointer killed error message in
the postgres logs?

Thanks in advance!
Regards,
Ramzy

Browse pgsql-performance by date

  From Date Subject
Next Message Pavel Stehule 2025-03-18 05:46:09 Re: Re: proposal: schema variables
Previous Message Marcos Pegoraro 2025-03-17 20:52:38 Re: Re: proposal: schema variables