Best practices to deal with event – “GROUP BY key set did not fit in memory, using external sort gro

Hey Eli, Good question. Actually, the best practices are described by the "suggested_action" column. I would advise following either of its suggestions. If you have (and, for complex queries, if Vertica chooses to use) a projection that's sorted by the GROUP BY column, then the GROUP BY will use drastically less memory, and be much faster too, since in some sense the data is already pre-grouped. As I so like to say, please use the Vertica Database Designer to automatically figure that kind of projection-design stuff out for you, rather than doing it manually. It's really quite good at what it does; it gets lots of little details that people commonly miss. If you haven't run the DBD lately, give it a shot; you might be surprised at what you've been missing out on. Alternatively, if you give GROUP BY more memory, then it can group more (un-sorted) data before spilling to disk and explicitly sorting at query time. You could do this by running the query in question (which is probably a SELECT, not a COPY or INSERT from your data-load threads) on a separate resource pool. Adam

Adam Thanks for the replay ! But how to relate to data load ? why Copy command had to run any kind of GROUP BY ? I also see meny SIP_FALLBACK message on my QUERY_EVENTS table with description of "Sideways information passing filter disabled due to ineffectiveness." Any idea ?

Hm... I can't immediately think of why COPY would be doing a GROUP BY in this case. Unless you also have a pre-join projection using this table somewhere? I would expect it to come from running SELECT queries. You're certain that these are not being caused by some other query on the system? Regarding sideways information passing, this shouldn't be a problem: SIP is a speculative optimization; it helps with some queries and not with others, and there's no real way to tell for sure until query runtime. So we often try it, then disable it if it's not actually helping.

Hi Adam,

In our vertica database, for one of the join query fired between a 100M fact table and 12k dimesion table, i can see the following message "SPI is disabled due to ineffectiveness" in the query_events table. Does it interpret, that it is disabled for this query or is it the case "it is disabled for the Vertica Cluster". If it is disabled for the cluster, is there a way we can enable it in order to check join performance? Also, is there a way through which we can check that SIP is being applied apart from the explain plan?

Hi Adam,

If you're seeing that message, that just means that it's disabled for the query.  Other queries will continue to use SIP.

In general, Vertica will use SIP if it thinks it's useful, and it will disable SIP if it's just getting in the way.  The EXPLAIN plan is probably the best way to tell whether a given query would try to use SIP if it were executed.  For running queries, as you've noted, the query_events table also works.

Adam

Hi Adam,

For query:

select a.col1 from a,b where a.col2=b.col2; 
explain=> Uses:  Runtime Filter: (SIP4(HashJoin): a.col2),


So, the thing is my explain shows that it is using SIP:"Runtime Filters: (SIP1(HashJoin)SIP(" .But, in 
query_events it is saying "it is disabled due to ineffectiveness". So, can we somehow check, that if SIP is being actually used.
Also, can some changes be made at the projection specific to the fact table to enforce SIP and then check it?

In order to do check SIP as per documentation or available material on web, i tried the following query:

select a.col1 from a,b where a.col2=b.col2 where  b.col2=5; --passing additional predicate for less rows in hash table.  
explain=> Instead of SIP hash join it did SIP Merge join, Any reasons why?

Query events table showed-> The optimizer created a transitive predicate due to a join condition: Informational; No user action is necessary.

Also is there any useful link for SIP Merge join. Or, is it similarly implemented for other DB's like Teradata?

Hi Adam,

Hm...  I'm not familiar with SIP in Teradata.  (Vertica's SIP doesn't describe how a join works; it is a mechanism that joins and other operators can use.)  SIP is not something that you would turn on or not for a particular query; that doesn't even make sense for our implementation...  These messages are informational only (they are sometimes used by our Customer Support to investigate certain types of issues); they're not something that you need to act on.

Perhaps you could explain your interest in SIP in more detail?  Maybe in other systems it is important to tune their SIP mechanism manually?

Regarding checking whether SIP was actually used, you did check :-)  As the message indicates, SIP was turned off at runtime in this case.

Vertica doesn't stop optimizing once it has a query plan.  In this case, our optimizer decided that SIP might help, so it tried it initially; but the optimizer determined while running the query that SIP filtering was not accelerating the query in practice (given the actual data being fetched by the query), so it decided to stop using SIP.

Regarding the choice of hash vs merge join -- it sounds like this was a merge join, not a sort-merge join?  (If you are coming from another database system, what you think of as a "merge join" is what we call a "sort-merge join", or a merge join with a sort operation attached.)  Vertica stores its data pre-sorted.  A merge join that doesn't have to sort the data first (because it is already sorted on disk) is very fast, much faster than a hash join; it also uses dramatically less memory.  So we generally prefer it.  In some situations, we may prefer a sort-merge join to a hash join too; generally if it is advantageous for some reason to have the data in sorted order after the join.

Adam

Thanks Adam. Can u throw some light on "The optimizer created a transitive predicate due to a join condition" event as per Vertica functionality?

Hi ,

There is undocumented method to disable SIP ( use it only for testing )  on session level   , so you will be able to know how SIP impact on your query performance

Disable

SELECT add_vertica_options('BASIC', 'DISABLE_SIPS');

 

Enable

SELECT clr_vertica_options('BASIC', 'DISABLE_SIPS'); 

Hi Eli,

Is there a way to find such functions? I have seen many such non documented (in vertica documentation) functions on forums. Would be really helpful, if you could share some links?