MIME-Version: 1.0
References: <CAGbX52Fm=k8hHJKEzo6-mnh7gn91s=Lz_t6B5uF1SotpXH3UeA@mail.gmail.com>
In-Reply-To: <CAGbX52Fm=k8hHJKEzo6-mnh7gn91s=Lz_t6B5uF1SotpXH3UeA@mail.gmail.com>
From: David Mullineux <dmullx@gmail.com>
Date: Wed, 20 Nov 2024 22:10:40 +0000
Message-ID: <CAGsyd8Xf=o=0tL7ttqEWg7mPehyL2e=7Nc+OpoNcpSny-JVB9g@mail.gmail.com>
Subject: Re: Memory settings when running postgres in a docker container
To: Koen De Groote <kdg.dev@gmail.com>
Cc: PostgreSQL General <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000467e7206275f6c04"
Archived-At: <https://www.postgresql.org/message-id/CAGsyd8Xf%3Do%3D0tL7ttqEWg7mPehyL2e%3D7Nc%2BOpoNcpSny-JVB9g%40mail.gmail.com>
Precedence: bulk

--000000000000467e7206275f6c04
Content-Type: text/plain; charset="UTF-8"

i dont get why you think all memroy will be used.
 When you say
shared_buffers = 16GB
effective_cache_size = 48GB

...then this is using only 16GB for shared buffers.

The effective _cache_size doesn't cause any memory to.be allocated. It's
just a hint to optomizer ....

On Wed, 20 Nov 2024, 11:16 Koen De Groote, <kdg.dev@gmail.com> wrote:

> Assuming a machine with:
>
> * 16 CPU cores
> * 64GB RAM
>
> Set to 500 max connections
>
> A tool like this: https://pgtune.leopard.in.ua/
>
> Will output recommended settings:
>
> max_connections = 500
> shared_buffers = 16GB
> effective_cache_size = 48GB
> maintenance_work_mem = 2GB
> checkpoint_completion_target = 0.9
> wal_buffers = 16MB
> default_statistics_target = 100
> random_page_cost = 1.1
> effective_io_concurrency = 200
> work_mem = 8388kB
> huge_pages = try
> min_wal_size = 1GB
> max_wal_size = 4GB
> max_worker_processes = 16
> max_parallel_workers_per_gather = 4
> max_parallel_workers = 16
> max_parallel_maintenance_workers = 4
>
> And they basically use up all the memory of the machine.
>
> 16GB shared buffers, 48GB effective cache size, 8MB of work_mem for some
> reason...
>
> This seems rather extreme. I feel there should be free memory for
> emergencies and monitoring solutions.
>
> And then there's the fact that postgres on this machine will be run in a
> docker container. Which, on Linux, receives 64MB of /dev/shm shared memory
> by default, but can be increased.
>
> I feel like I should probably actually lower my upper limit for memory,
> regardless of what the machine actually has, so I can have free memory, and
> also not bring the container process itself into danger.
>
> Is it as straightforward as putting my limit on, say 20GB, and then giving
> more /dev/shm to the container? Or is there more to consider?
>
> Regards,
> Koen De Groote
>
>
>
>
>
>
>

--000000000000467e7206275f6c04
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"auto">i dont get why you think all memroy will be used.<div dir=
=3D"auto">=C2=A0When you say</div><div dir=3D"auto"><span style=3D"font-siz=
e:12.8px">shared_buffers =3D 16GB</span><br style=3D"font-size:12.8px"><spa=
n style=3D"font-size:12.8px">effective_cache_size =3D 48GB</span></div><div=
 dir=3D"auto"><span style=3D"font-size:12.8px"><br></span></div><div dir=3D=
"auto"><span style=3D"font-size:12.8px">...then this is using only 16GB for=
 shared buffers.</span></div><div dir=3D"auto"><span style=3D"font-size:12.=
8px"><br></span></div><div dir=3D"auto"><span style=3D"font-size:12.8px">Th=
e effective _cache_size doesn&#39;t cause any memory <a href=3D"http://to.b=
e">to.be</a> allocated. It&#39;s just a hint to optomizer ....</span></div>=
</div><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">=
On Wed, 20 Nov 2024, 11:16 Koen De Groote, &lt;<a href=3D"mailto:kdg.dev@gm=
ail.com">kdg.dev@gmail.com</a>&gt; wrote:<br></div><blockquote class=3D"gma=
il_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-lef=
t:1ex"><div dir=3D"ltr">Assuming a machine with:<div><br></div><div>* 16 CP=
U cores</div><div>* 64GB RAM</div><div><br></div><div>Set to 500 max connec=
tions</div><div><br></div><div>A tool like this:=C2=A0<a href=3D"https://pg=
tune.leopard.in.ua/" target=3D"_blank" rel=3D"noreferrer">https://pgtune.le=
opard.in.ua/</a></div><div><br></div><div>Will output recommended settings:=
</div><div><br></div>max_connections =3D 500<br>shared_buffers =3D 16GB<br>=
effective_cache_size =3D 48GB<br>maintenance_work_mem =3D 2GB<br>checkpoint=
_completion_target =3D 0.9<br>wal_buffers =3D 16MB<br>default_statistics_ta=
rget =3D 100<br>random_page_cost =3D 1.1<br>effective_io_concurrency =3D 20=
0<br>work_mem =3D 8388kB<br>huge_pages =3D try<br>min_wal_size =3D 1GB<br>m=
ax_wal_size =3D 4GB<br>max_worker_processes =3D 16<br>max_parallel_workers_=
per_gather =3D 4<br>max_parallel_workers =3D 16<br>max_parallel_maintenance=
_workers =3D 4<div><br></div><div>And they basically use up all the memory =
of the machine.</div><div><br></div><div>16GB shared buffers, 48GB effectiv=
e cache size, 8MB of work_mem for some reason...</div><div><br></div><div>T=
his seems rather extreme. I feel there should be free memory for emergencie=
s and monitoring solutions.</div><div><br></div><div>And then there&#39;s t=
he fact that postgres on this machine will be run in a docker container. Wh=
ich, on Linux, receives 64MB of /dev/shm shared memory by default, but can =
be increased.</div><div><br></div><div>I feel like I should probably actual=
ly lower my upper limit for memory, regardless of what the machine actually=
 has, so I can have free memory, and also not bring the container process i=
tself into danger.</div><div><br></div><div>Is it as straightforward as put=
ting my limit on, say 20GB, and then giving more /dev/shm to the container?=
 Or is there more to consider?</div><div><br></div><div>Regards,</div><div>=
Koen De Groote</div><div><br></div><div><br></div><div><br></div><div><br><=
/div><div><br></div><div><br></div></div>
</blockquote></div>

--000000000000467e7206275f6c04--