Re: Out of Memory errors are frustrating as heck!

On Tue, Apr 23, 2019 at 04:37:50PM -0400, Gunther wrote:
> On 4/21/2019 23:09, Tomas Vondra wrote:
>
> What I think might work better is the attached v2 of the patch, with a
> single top-level condition, comparing the combined memory usage
> (spaceUsed + BufFile) against spaceAllowed. But it also tweaks
> spaceAllowed once the size needed for BufFile gets over work_mem/3.
>
> Thanks for this, and I am trying this now.
>
> So far it is promising.
>
> I see the memory footprint contained under 1 GB. I see it go up, but also
> down again. CPU, IO, all being live.
>
> foo=# set enable_nestloop to off;
> SET
> foo=# explain analyze select * from reports.v_BusinessOperation;
> WARNING: ExecHashIncreaseNumBatches: nbatch=32 spaceAllowed=4194304
> WARNING: ExecHashIncreaseNumBatches: nbatch=64 spaceAllowed=4194304
> WARNING: ExecHashIncreaseNumBatches: nbatch=128 spaceAllowed=4194304
> WARNING: ExecHashIncreaseNumBatches: nbatch=256 spaceAllowed=6291456
> WARNING: ExecHashIncreaseNumBatches: nbatch=512 spaceAllowed=12582912
> WARNING: ExecHashIncreaseNumBatches: nbatch=1024 spaceAllowed=25165824
> WARNING: ExecHashIncreaseNumBatches: nbatch=2048 spaceAllowed=50331648
> WARNING: ExecHashIncreaseNumBatches: nbatch=4096 spaceAllowed=100663296
> WARNING: ExecHashIncreaseNumBatches: nbatch=8192 spaceAllowed=201326592
> WARNING: ExecHashIncreaseNumBatches: nbatch=16384 spaceAllowed=402653184
> WARNING: ExecHashIncreaseNumBatches: nbatch=32768 spaceAllowed=805306368
> WARNING: ExecHashIncreaseNumBatches: nbatch=65536 spaceAllowed=1610612736
>
> Aaaaaand, it's a winner!
>

Good ;-)

> Unique (cost=5551524.36..5554207.33 rows=34619 width=1197) (actual time=6150303.060..6895451.210 rows=435274 loops=1)
> -> Sort (cost=5551524.36..5551610.91 rows=34619 width=1197) (actual time=6150303.058..6801372.192 rows=113478386 loops=1)
> Sort Key: ...
> Sort Method: external merge Disk: 40726720kB
> -> Hash Right Join (cost=4255031.53..5530808.71 rows=34619 width=1197) (actual time=325240.679..1044194.775 rows=113478386 loops=1)
> Hash Cond: ...
> ...
> Planning Time: 40.559 ms
> Execution Time: 6896581.566 ms
> (70 rows)
>
>
> For the first time this query has succeeded now. Memory was bounded. The
> time of nearly hours is crazy, but things sometimes take that long. The
> important thing was not to get an out of memory error.
>

TBH I don't think there's much we can do to improve this further - it's
a rather desperate effort to keep the memory usage as low as possible,
without any real guarantees.

Also, the hash join only takes about 1000 seconds out of the 6900 total.
So even if we got it much faster, the query would still take almost two
hours, give or take.

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services