Skip to main content

Question about error "join inner did not fit in memory"

  • February 13, 2017
  • 8 replies
  • 17 views
llecocq

Hello,

We are using Vertica 7.2 on a 4 nodes cluster, in a lab environment.
On the Vertica MC, we are observing this error quite frequently, in the context of the qualification of an SQL package.
We are more particularly concerned by one type of occurrence of this message, mentioning a join between 2 tables, one of them being always strictly empty.
In addition to that, we see that the initiator node of this error is always the same.
Finally, when we look at the results of all our queries, everything looks OK.

So I have several questions here:
1) Why would a join where one of the table is empty cause a memory problem?
2) Any reason why this is always the same node that is reporting the problem, and not the others ?
3) Should we really worry about the results of our query execution, given that we do not observe any symptoms other than this message in the console and in vertica logfile?
4) What can we change to prevent this error?

Thanks for helping,

    8 replies

    llecocq
    • llecocq
    • Author
    • Participating Frequently
    • February 13, 2017

    Thanks a lot Eugenia, I sent this to our R&D, will get back as soon as I have answers

    llecocq
    • llecocq
    • Author
    • Participating Frequently
    • February 13, 2017

    Hi again,

    1) Our query looks like:
    Select A,B,C,D FROM TABLE_A AS A INNER JOIN TABLE_B AS B ON A.F1 = B.F2 INNER JOIN TABLE_C AS C ON A.F3 = C.F4 WHERE ...
    The table that is empty is TABLE_B, so my understanding is that we are in the good case, right ?
    2) Yes, this is always the query initiator.

    If you have any suggestion to improve the query, please tell me

    llecocq
    • llecocq
    • Author
    • Participating Frequently
    • February 13, 2017

    My mistake, you were right, this is TABLE_A which is empty, not table_B.
    If I understand you, we should put it in 2nd position ...?

    llecocq
    • llecocq
    • Author
    • Participating Frequently
    • February 14, 2017

    Hi,

    The table had no statistics,so I did an ANALYZE_STATISTICS (''), on the entire database, but at the end, the table still did not have statistics, at least this is what the explain says.
    Then I did an export_statistics('my_stat');, it says successful, but did not produce any files.
    Any idea why ?

    llecocq
    • llecocq
    • Author
    • Participating Frequently
    • February 15, 2017

    Hi Eugenia,
    I was finally able to generate statistics, for all tables of my data model.
    The EXPLAIN looks different now, the INNER table is now the one that is empty.
    Unfortunately, I am still getting the same errors, but not on the same node as previously.
    I have attached the EXPLAIN results, before and after the statistics generation.
    If you have time to look at it, that would be great.

    llecocq
    • llecocq
    • Author
    • Participating Frequently
    • February 15, 2017

    I attached a new version of the explain output, the previous was garbaged, sorry.
    The exact error I get in vertica.log is:
    2017-02-15 12:43:17.384 Init Session:0x7f3e3c015c00-c0000000498577 [EE] Join inner did not fit in memory [(SCHEMA_2.TABLE_EMPTY x SCHEMA_2.TABLE_BIG) using previous join and TABLE_BIG_b0 (PATH ID: 1)]

    qinchaofeng
    Forum|alt.badge.img
    • qinchaofeng
    • Participating Frequently
    • March 14, 2019

    Please change the explain to Merge Join, have a try!

    Jim_Knicely
    Forum|alt.badge.img+2
    • Jim_Knicely
    • Participating Frequently
    • March 14, 2019

    @llecocq -

    Check out "Optimizing for Merge Join":
    https://www.vertica.com/blog/optimizing-for-merge-joinba-p223279/

    And "Choosing Sort Order: Best Practices":
    https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AdministratorsGuide/ConfiguringTheDB/PhysicalSchema/ChoosingSortOrderBestPractices.htm

    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement