Обсуждение: Postgresql OOM

Hello all,

I have a query that forces an out of memory error, where the OS will kill the postgresql process.
The query plan (run immediately after a vacuum analyze) is at https://explain.depesz.com/s/ITQI#html .

Any idea on how to identify the problem? I can reproduce it on demand. Should I report it pgsql-bugs?

Best regards,
Radu

  FROM inputrequest t  LEFT JOIN outputrequest rec_tro      ON rec_tro.input_sequence = t.input_sequence  LEFT JOIN inputrequest r      ON r.originalRequest_id = t.input_sequence  LEFT JOIN outputrequest rpl_rec_tro      ON rpl_rec_tro.input_sequence = r.input_sequence  LEFT JOIN outputrequest rpl_snd_tro      ON rpl_snd_tro.reply_input_sequence = r.input_sequence  LEFT JOIN outputrequest snd_tro      ON snd_tro.reply_input_sequence = t.input_sequence  

I am not qualified to answer on the OOM issue but why are you joining the same table (outputrequest) 4 times (using an identical join condition)?

This essentially does a cross join, if an input_sequence value has say, 1000 related rows in outputrequest, you will be getting 1000^4 rows in the result set.

The planner should recognize this situation and avoid use of hash
join in such cases, but maybe the statistics aren't reflecting the
problem, or maybe there's something wrong with the logic specific
to parallel hash join.  You've not really provided enough information
to diagnose why the poor choice of plan.

                        regards, tom lane

Thanks for looking into this. I'm not sure what information would be needed to look at the choice of plan.

The statistics for the join conditions in the query would be:

 join_condition | min_count | max_count |         avg_count          
----------------+-----------+-----------+----------------------------
 snd_tro        |         0 |         0 | 0.000000000000000000000000
 rpl_rec_tro    |         0 |         2 |     0.99869222814474470477
 rec_tro        |         0 |         2 |     0.99869222814474470477
 rpl_snd_tro    |         0 |         0 | 0.000000000000000000000000

 r              |         0 |         1 |     0.49850916663490161653 

The relevant columns for the tables are:

postgres=# \d inputrequest
                               Table "public.inputrequest"
          Column          |            Type             | Collation | Nullable | Default
--------------------------+-----------------------------+-----------+----------+---------
 input_sequence           | bigint                      |           | not null |
 msg_type                 | character varying(8)        |           | not null |
 msg_content              | text                        |           | not null |
 msg_reference            | character varying(35)       |           |          |
 originalrequest_id       | bigint                      |           |          |
 receive_time             | timestamp without time zone |           | not null |
 related_output_sequence  | bigint                      |           |          |
 msg_status               | character varying(15)       |           |          |
 

Indexes:
    "inputrequest_pkey" PRIMARY KEY, btree (input_sequence)
    "inputrequest_originalrequest_id_idx" btree (originalrequest_id)

postgres=# \d outputrequest
                             Table "public.outputrequest"
         Column         |            Type             | Collation | Nullable | Default
------------------------+-----------------------------+-----------+----------+---------
 output_sequence        | bigint                      |           | not null |
 input_sequence         | bigint                      |           |          |
 msg_type               | character varying(8)        |           |          |
 msg_content            | text                        |           | not null |
 msg_reference          | character varying(35)       |           |          |
 reply_input_sequence   | bigint                      |           |          |
 status                 | integer                     |           | not null |
 related_input_sequence | bigint                      |           |          |
Indexes:
    "outputrequest_pkey" PRIMARY KEY, btree (output_sequence)
    "outputrequest_input_sequence_idx" btree (input_sequence)
    "outputrequest_reply_input_sequence_idx" btree (reply_input_sequence)

Hi,

On 2024-06-06 15:25:25 +0300, Radu Radutiu wrote:
> I have a query that forces an out of memory error, where the OS will kill
> the postgresql process.

FWIW, it can be useful to configure the OS with strict memory overcommit. That
causes postgres to fail more gracefully, because the OOM killer won't be
invoked.


> The query plan (run immediately after a vacuum analyze) is at
> https://explain.depesz.com/s/ITQI#html .

Can you get EXPLAIN (ANALYZE, BUFFERS) to complete if you reduce the number of
workers? It'd be useful to get some of the information about the actual
numbers of tuples etc.

FWIW, it can be useful to configure the OS with strict memory overcommit. That
causes postgres to fail more gracefully, because the OOM killer won't be
invoked.

That's helpful, thanks!

One thing to note is that postgres' work_mem is confusing - it applies to
individual query execution nodes, not the whole query. Additionally, when you
use parallel workers, each of the parallel workers can use the "full"
work_mem, rather than work_mem being evenly distributed across workers.

Within that, the memory usage in the EXPLAIN ANALYZE isn't entirely unexpected
(I'd say it's unreasonable if you're not a postgres dev, but that's a
different issue).

We can see each of the Hash nodes use ~1GB, which is due to
(1 leader + 4 workers) * work_mem = 5 * 200MB = 1GB.

In this specific query we probably could free the memory in the "lower" hash
join nodes once the node directly above has finished building, but we don't
have the logic for that today.

Of course that doesn't explain why the memory usage / temp file creation is so
extreme with a lower limit / fewer workers.  There aren't any bad statistics
afaics, nor can I really see a potential for a significant skew, it looks to
me that the hashtables are all built on a single text field and that nearly
all the input rows are distinct.

Could you post the table definition (\d+) and the database definition
(\l). One random guess I have is that you ended up with a "non-deterministic"
collation for that column and that we end up with a bad hash due to that.