From: Ronan Dunklau <ronan.dunklau@aiven.io>
To: Tomas Vondra <tomas.vondra@enterprisedb.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: scalability bottlenecks with (many) partitions (and more)
Date: Mon, 29 Jan 2024 15:15:25 +0100
Message-ID: <13440175.uLZWGnKmhe@aivenlaptop>
In-Reply-To: <c3cddb9d-283e-4caf-b558-5c9196320650@enterprisedb.com>
References: 
 <f261vl5c0IuAF4XdXZDQ_KiQhUbxaaUWbNBbHoU9uawJsIGP5Fq3HbXSNAMEAySS0OPdyvULiNItmR7TcRSm_g==@protonmail.internalid>
 <4541483.LvFx2qVVIh@aivenlaptop>
 <c3cddb9d-283e-4caf-b558-5c9196320650@enterprisedb.com>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain; charset="iso-8859-1"
Archived-At: 
 <https://www.postgresql.org/message-id/13440175.uLZWGnKmhe%40aivenlaptop>
Precedence: bulk

Le lundi 29 janvier 2024, 13:17:07 CET Tomas Vondra a =E9crit :
> > Did you try running an strace on the process ? That may give you some
> > hindsights into what malloc is doing. A more sophisticated approach wou=
ld
> > be using stap and plugging it into the malloc probes, for example
> > memory_sbrk_more and memory_sbrk_less.
>=20
> No, I haven't tried that. In my experience strace is pretty expensive,
> and if the issue is in glibc itself (before it does the syscalls),
> strace won't really tell us much. Not sure, ofc.

It would tell you how malloc actually performs your allocations, and how of=
ten=20
they end up translated into syscalls. The main issue with glibc would be th=
at=20
it releases the memory too agressively to the OS, IMO.

>=20
> > An important part of glibc's malloc behaviour in that regard comes from
> > the
> > adjustment of the mmap and free threshold. By default, mmap adjusts them
> > dynamically and you can poke into that using the
> > memory_mallopt_free_dyn_thresholds probe.
>=20
> Thanks, I'll take a look at that.
>=20
> >> FWIW I was wondering if this is a glibc-specific malloc bottleneck, so=
 I
> >> tried running the benchmarks with LD_PRELOAD=3Djemalloc, and that impr=
oves
> >> the behavior a lot - it gets us maybe ~80% of the mempool benefits.
> >> Which is nice, it confirms it's glibc-specific (I wonder if there's a
> >> way to tweak glibc to address this), and it also means systems using
> >> jemalloc (e.g. FreeBSD, right?) don't have this problem. But it also
> >> says the mempool has ~20% benefit on top of jemalloc.
> >=20
> > GLIBC's malloc offers some tuning for this. In particular, setting eith=
er
> > M_MMAP_THRESHOLD or M_TRIM_THRESHOLD will disable the unpredictable "au=
to
> > adjustment" beheviour and allow you to control what it's doing.
> >=20
> > By setting a bigger M_TRIM_THRESHOLD, one can make sure memory allocated
> > using sbrk isn't freed as easily, and you don't run into a pattern of
> > moving the sbrk pointer up and down repeatedly. The automatic trade off
> > between the mmap and trim thresholds is supposed to prevent that, but t=
he
> > way it is incremented means you can end in a bad place depending on your
> > particular allocation patttern.
>=20
> So, what values would you recommend for these parameters?
>=20
> My concern is increasing those value would lead to (much) higher memory
> usage, with little control over it. With the mempool we keep more
> blocks, ofc, but we have control over freeing the memory.

Right now depending on your workload (especially if you use connection=20
pooling) you can end up with something like 32 or 64MB of dynamically adjus=
ted=20
trim-threshold which will never be released back.=20

The first heurstic I had in mind was to set it to work_mem, up to a=20
"reasonable" limit I guess. One can argue that it is expected for a backend=
 to=20
use work_mem frequently, and as such it shouldn't be released back. By sett=
ing=20
work_mem to a lower value, we could ask glibc at the same time to trim the=
=20
excess kept memory. That could be useful when a long-lived connection is=20
pooled, and sees a spike in memory usage only once. Currently that could we=
ll=20
end up with 32MB "wasted" permanently but tuning it ourselves could allow u=
s=20
to releaase it back.=20

Since it was last year I worked on this, I'm a bit fuzzy on the details but=
 I=20
hope this helps.