Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
From: =?utf-8?B?0JXQstCz0LXQvdC40Lkg0KfQtdC60LDQvQ==?= <hom3chuk@gmail.com>
Mime-Version: 1.0 (1.0)
Subject: =?utf-8?Q?Re:_Memory_Not_Released_After_Batch_Completion_?=
 =?utf-8?Q?=E2=80=93_Checkpointer/Background_Writer_Behavior_,_po?=
 =?utf-8?Q?stgres_15?=
Date: Sun, 6 Apr 2025 16:05:18 +0200
Message-Id: <0A3E5AC0-218C-4808-932F-6D227C48AF7E@gmail.com>
References: 
 <CAL5Gnit1cSHf+ux_RTvVw_2PmhHyxAk1dOQkep_um_Sc=H+18w@mail.gmail.com>
Cc: pgsql-performance@postgresql.org
In-Reply-To: 
 <CAL5Gnit1cSHf+ux_RTvVw_2PmhHyxAk1dOQkep_um_Sc=H+18w@mail.gmail.com>
To: Motog Plus <mplus7535@gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/0A3E5AC0-218C-4808-932F-6D227C48AF7E%40gmail.com>
Precedence: bulk

Checkpointer process holds the shared_buffers memory and it is the expected b=
ehaviour. Even during idle, shared buffers are there for reads and future mo=
difications. You have SB configured to be 10GB and it will stay allocated th=
roughout the whole uptime of the server. This is totally OK and should help (=
generally) with the performance.=20
The =E2=80=9Cidle=E2=80=9D thing is slightly less straightforward, as checkp=
ointer and writer try to spread the load in time.=20
As for memory climbing to 90%, you might want to reduce shared buffers to so=
mething less than 1/3 of your RAM and see if it helps.=20
But again, if there=E2=80=99s no issues with system availability and health t=
hen you should not be bothered imo

> 6 =D0=B0=D0=BF=D1=80. 2025=E2=80=AF=D0=B3., =D0=B2 14:37, Motog Plus <mplu=
s7535@gmail.com> =D0=BD=D0=B0=D0=BF=D0=B8=D1=81=D0=B0=D0=BB(=D0=B0):
>=20
> =EF=BB=BF
>=20
> Hi All,
>=20
> I=E2=80=99m running PostgreSQL on an EC2 c5.4xlarge Ubuntu instance with t=
he following specs:
>=20
> 32 GB RAM
>=20
> 1.2 TB disk
>=20
> 16 vCPUs
>=20
>=20
> Pgpool-II Configuration:
>=20
> max_pool =3D 2
>=20
> num_init_children =3D 1000
>=20
> client_idle_limit =3D 300 seconds
>=20
> connection_life_time =3D 300 seconds
>=20
> load_balance_mode =3D on
>=20
>=20
> PostgreSQL Configuration:
>=20
> max_connections =3D 3000
>=20
> checkpoint_timeout =3D 15min
>=20
> checkpoint_completion_target =3D 0.9
>=20
> shared_buffers =3D 10GB
>=20
> wal_buffers =3D 64MB
>=20
> min_wal_size =3D 80MB
>=20
> max_wal_size =3D 10GB
>=20
> effective_cache_size =3D 20GB
>=20
> work_mem =3D 4MB
>=20
> maintenance_work_mem =3D 1GB
>=20
> bgwriter_delay =3D 200ms
>=20
> bgwriter_lru_multiplier =3D 2
>=20
> bgwriter_lru_maxpages =3D 100
>=20
> max_standby_streaming_delay =3D 5s (on standby)
>=20
>=20
> I have a primary-standby streaming replication setup, and application modu=
les (running in Kubernetes pods) connect to the database through Pgpool-II u=
sing HikariCP.
>=20
>=20
> ---
>=20
> Issue Observed:
>=20
> Even when no queries are running and application batch jobs have completed=
, memory usage remains high(40%)=E2=80=94 particularly attributable to the c=
heckpointer and background writer processes. According to Grafana, memory co=
ntinues to remain cached.
>=20
> When I manually kill the checkpointer process on the node, memory usage dr=
ops immediately(9%). If I don't kill it and start another batch, memory usag=
e increases further =E2=80=94 reaching up to 90%, though under normal condit=
ions it stays around 65=E2=80=9370%.
>=20
>=20
> ---
>=20
> System Memory Snapshot (when idle):
>=20
> free -h :
> total 30 Gi
> used 12 Gi
> free 2.2 Gi
> shared 10Gi
> buff/cache 27Gi
> available 18Gi
> PostgreSQL memory usage from ps:
>=20
> ps -eo pid,rss,cmd | grep postgres | awk '{sum+=3D$2} END {print sum/1024 "=
 MB"}'
>=20
> Output:
> 25682.4 MB
>=20
> Note: Even though the system is idle for over 50 hours, connections connec=
t, become idle and drop.
>=20
> ---
>=20
> Question:
>=20
> Is it normal for the checkpointer and background writer to hold onto memor=
y like this after workloads finish? Is this related to how PostgreSQL manage=
s shared buffers or dirty pages in a streaming replication setup?
>=20
> Are there any parameters in postgresql.conf or Pgpool that can help ensure=
 memory is better reclaimed when the system is idle?
>=20
> Any guidance would be much appreciated.
>=20
> Best regards,
> Ramzy