On Sat, Oct 03, 2020 at 04:01:49PM -0700, Andres Freund wrote:
> The below turned out to be somewhat obsoleted by what you wrote below,
> which I unfortunately hadn't yet read - but I think it's still good
> information, so I'm not gonna delete it:
Right, I understand that RES RAM might includes shared buffers and demand
loaded file-backed and mmap stuff. But on our servers, I tentatively wouldn't
expect it to be more than a handful of GB. It might be true that most of our
report processes run in a forked children, specifically to avoid leaks, and the
forked children would have their own, shortlived backend, which would hide any
postgres leak (in addition to having tried to use jit since v11).
> > I was able to make a small, apparent leak like so. This applies to
> > postgis3.0/postgres12/LLVM5/centos7, and
> > postgis3.1alpha/postgres13/LLVM9/centos8.
> >
> > Inlining is essential to see the leak, optimization is not. Showing every 9th
> > query:
> >
> > $ for a in `seq 1 99`; do echo "SET jit_inline_above_cost=1; SET jit_optimize_above_cost=-1; SET jit_above_cost=0;
SETjit_expressions=on; SET log_executor_stats=on; SET client_min_messages=debug; SET jit=on; explain analyze SELECT
st_x(site_geo)FROM sites WHERE site_geo IS NOT NULL;"; done |psql ts 2>&1 |grep 'resident' |sed -n '1~9p'
> > ! 46024 kB max resident size
> > ! 46492 kB max resident size
>
> Huh, that's certainly not good. Looking.
Reproduce like:
postgres=# CREATE EXTENSION postgis;
postgres=# CREATE TABLE geo(g geometry(point));
postgres=# INSERT INTO geo SELECT st_GeometryFromText('POINT(11 12)',0) FROM generate_series(1,99999);
postgres=# SET jit_above_cost=0; SET jit_inline_above_cost=0; SET client_min_messages=debug; SET log_executor_stats=on;
explainanalyze SELECT st_x(g) FROM geo;