Le vendredi 23 juin 2023, 22:55:51 CEST Peter Eisentraut a écrit :
> On 22.06.23 15:35, Ronan Dunklau wrote:
> > The thing is, by default, those parameters are adjusted dynamically by the
> > glibc itself. It starts with quite small thresholds, and raises them when
> > the program frees some memory, up to a certain limit. This patch proposes
> > a new GUC allowing the user to adjust those settings according to their
> > workload.
> >
> > This can cause problems. Let's take for example a table with 10k rows, and
> > 32 columns (as defined by a bench script David Rowley shared last year
> > when discussing the GenerationContext for tuplesort), and execute the
> > following
> > query, with 32MB of work_mem:
> I don't follow what you are trying to achieve with this. The examples
> you show appear to work sensibly in my mind. Using this setting, you
> can save some of the adjustments that glibc does after the first query.
> But that seems only useful if your session only does one query. Is that
> what you are doing?
No, not at all: glibc does not do the right thing, we don't "save" it.
I will try to rephrase that.
In the first test case I showed, we see that glibc adjusts its threshold, but
to a suboptimal value since repeated executions of a query needing the same
amount of memory will release it back to the kernel, and move the brk pointer
again, and will not adjust it again. On the other hand, by manually adjusting
the thresholds, we can set them to a higher value which means that the memory
will be kept in malloc's freelist for reuse for the next queries. As shown in
the benchmark results I posted, this can have quite a dramatic effect, going
from 396 tps to 894. For ease of benchmarking, it is a single query being
executed over and over again, but the same thing would be true if different
queries allocating memories were executed by a single backend.
The worst part of this means it is unpredictable: depending on past memory
allocation patterns, glibc will end up in different states, and exhibit
completely different performance for all subsequent queries. In fact, this is
what Tomas noticed last year, (see [0]), which led to investigation into
this.
I also tried to show that for certain cases glibcs behaviour can be on the
contrary to greedy, and hold on too much memory if we just need the memory
once and never allocate it again.
I hope what I'm trying to achieve is clearer that way. Maybe this patch is not
the best way to go about this, but since the memory allocator behaviour can
have such an impact it's a bit sad we have to leave half the performance on
the table because of it when there are easily accessible knobs to avoid it.
[0] https://www.postgresql.org/message-id/bcdd4e3e-c12d-cd2b-7ead-a91ad416100a%40enterprisedb.com
