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

llecocq · February 2017

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,

emoreno · February 2017

Hi,
The error happens when the INNER part of the join did not fit in memory.

To answer your questions :

1) Why would a join where one of the table is empty cause a memory problem?
Is the empty table in the INNER part ( it should be ) if not maybe you don't have the right statistics so Vertica choses the wrong table in the INNER.

2) Any reason why this is always the same node that is reporting the problem, and not the others ?
Is this the query initiator?

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?

The result should be fine, but the query performance probably is slower , in the way that works is, vertica run the query, inner do not fit in memory, Vertica prints the error and retry the querying spilling on disk the data that does not fit in MEM.

4) What can we change to prevent this error?

To avoid this problem you could,

changing projections to make a MergeJoin,
change the query so the inner is not that big ( check statistics, without statistics sometimes Vertica could chose as Inner the big table, if the statistics are Ok and vertica still uses the big table as Inner open a support ticket),
or give more memory to the query so it does not spill ( You can see how much memory uses in the execution engine profiles table)

I recommend you to read this docs to understand better what the query is doing

https://my.vertica.com/kb/Reading-Query-Plans/Content/BestPractices/Reading-Query-Plans.htm?Highlight=explain
https://my.vertica.com/docs/SolutionsArchitects/systemtables.pdf
https://my.vertica.com/kb/Redesigning-Projections-for-Query-Optimization/Content/BestPractices/Redesigning-Projections-for-Query-Optimization.htm?Highlight=projection

Hope this helps.
Eugenia

llecocq · February 2017

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

llecocq · February 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

emoreno · February 2017

can you do an EXPLAIN the query? if Table_b is empty it should not be join spill if table_b is the inner.

llecocq · February 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 ...?

emoreno · February 2017

Does the table has statistics? Because if so, Optimizer should do the change, otherwise it will follow the way that the query is written. If you do EXPLAIN it will tell you if the table has statistics or not.

llecocq · February 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 ?

emoreno · February 2017

Hi,

the empty table won't have statistics because it is empty. But Vertica should had got statistics in the other tables and that should had helped the query.

the export statistics create a file 'my_stat' on the vertica node, you need to look for it in the folders.

if you want it in your screen do select export_statistics('');. or if you are connecting from a client computer you can do

vsql -c "select export_statistics('');" > my_state.xml

Hope this helps
Eugenia

llecocq · February 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 · February 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)]