Re: Out of Memory errors are frustrating as heck! - Mailing list pgsql-performance

Also, I don’t know if perf probes & perf record/script could be useful for creating a log of all the calls to do memory allocation along with the unwound call stacks? Then analyzing that file? At least this can be done for a single process, and just while the problematic sql is running.

-Jeremy

On 4/19/2019 17:01, Justin Pryzby wrote:

Were you able to reproduce the issue in some minimized way ?  Like after
joining fewer tables or changing to join with fewer join conditions ?

On Thu, Apr 18, 2019 at 05:21:28PM +0200, Tomas Vondra wrote:

It would be possible to do at least one of these two things:

Thanks, and sorry for my pushyness. Yes, I have pin pointed the HashJoin, and I have created the two tables involved.

The data distribution of the join keys, they are all essentially UUIDs and essentially random.

I am sharing this data with you. However, only someone who can actually control the planner can use it to reproduce the problem. I have tried but not succeeded. But I am sure the problem is reproduced by this material.

Here is the part of the plan that generates this massive number of calls to

        ->  Hash Right Join  (cost=4255031.53..5530808.71 rows=34619 width=1197)             Hash Cond: (((q.documentinternalid)::text = (documentinformationsubject.documentinternalid)::text) AND ((r.targetinternalid)::text = (documentinformationsubject.actinternalid)::text))             ->  Hash Right Join  (cost=1341541.37..2612134.36 rows=13 width=341)                   Hash Cond: (((documentinformationsubject_2.documentinternalid)::text = (q.documentinternalid)::text) AND ((documentinformationsubject_2.actinternalid)::text = (q.actinternalid)::text))                 ... let's call this tmp_q ...             ->  Hash  (cost=2908913.87..2908913.87 rows=34619 width=930)                   ->  Gather Merge  (cost=2892141.40..2908913.87 rows=34619 width=930)                       ... let's call this tmp_r ...                

This can be logically reduced to the following query

SELECT * FROM tmp_q q RIGHT OUTER JOIN tmp_r r   USING(documentInternalId, actInternalId);

with the following two tables

CREATE TABLE xtmp_q (documentinternalid        character varying(255),operationqualifiercode    character varying(512),operationqualifiername    character varying(512),actinternalid             character varying(255),approvalinternalid        character varying(255),approvalnumber            character varying(555),approvalnumbersystem      character varying(555),approvalstatecode         character varying(512),approvalstatecodesystem   character varying(512),approvaleffectivetimelow  character varying(512),approvaleffectivetimehigh character varying(512),approvalstatuscode        character varying(32),licensecode               character varying(512),agencyid                  character varying(555),agencyname                text
);

CREATE TABLE tmp_r (documentinternalid      character varying(255),is_current              character(1),documentid              character varying(555),documenttypecode        character varying(512),subjectroleinternalid   character varying(255),subjectentityinternalid character varying(255),subjectentityid         character varying(555),subjectentityidroot     character varying(555),subjectentityname       character varying,subjectentitytel        text,subjectentityemail      text,otherentityinternalid   character varying(255),confidentialitycode     character varying(512),actinternalid           character varying(255),operationcode           character varying(512),operationname           text,productitemcode         character varying(512),productinternalid       character varying(255)..
);

you can download the data here (URLs just a tiny bit obfuscated):

The small table http:// gusw dot net/tmp_q.gz

The big table is in the form of 9 parts of 20 MB each, http:// gusw dot net/tmp_r.gz.00, .01, .02, ..., .09, maybe you need only the first part.

Download as many as you have patience to grab, and then import the data like this:

\copy tmp_q from program 'zcat tmp_q.gz'
\copt tmp_r from program 'cat tmp_r.gz.* |zcat'

The only problem is that I can't test that this actually would trigger the memory problem, because I can't force the plan to use the right join, it always reverts to the left join hashing the tmp_q:

        ->  Hash Left Join  (cost=10.25..5601401.19 rows=5505039 width=12118)             Hash Cond: (((r.documentinternalid)::text = (q.documentinternalid)::text) AND ((r.actinternalid)::text = (q.actinternalid)::text))             ->  Seq Scan on tmp_r r  (cost=0.00..5560089.39 rows=5505039 width=6844)             ->  Hash  (cost=10.10..10.10 rows=10 width=6306)                   ->  Seq Scan on tmp_q q  (cost=0.00..10.10 rows=10 width=6306)

which is of course much better, but when tmp_q and tmp_r are the results of complex stuff that the planner can't estimate, then it gets it wrong, and then the issue gets triggered because we are hashing on the big tmp_r, not tmp_q.

It would be so nice if there was a way to force a specific plan for purposes of the testing.  I tried giving false data in pg_class reltuples and relpages:

foo=# analyze tmp_q;
ANALYZE
foo=# analyze tmp_r;
ANALYZE
foo=# select relname, relpages, reltuples from pg_class where relname in ('tmp_q', 'tmp_r');relname | relpages |  reltuples
---------+----------+-------------tmp_r   |  5505039 | 1.13467e+08tmp_q   |        7 |         236
(2 rows)

foo=# update pg_class set (relpages, reltuples) = (5505039, 1.13467e+08) where relname = 'tmp_q';
UPDATE 1
foo=# update pg_class set (relpages, reltuples) = (7, 236) where relname = 'tmp_r';
UPDATE 1

but that didn't help. Somehow the planner outsmarts every such trick, so I can't get it to follow my right outer join plan where the big table is hashed.  I am sure y'all know some way to force it.

regards,
-Gunther