How do I globally enable JFMT?

Deb_Chatigny

Is it possible to enable JFMT(v) for all VARCHAR columns by default (rather than do it in each query) - and if so, how?

Question from Optimizing Query Performance and Resource Pool Tuning @rbankula @bat

rroelke

You can - there is a parameter JoinDefaultTupleFormat which will deploy jfmt(v) whenever possible. You can set this parameter on the session or database level.

Sudhakar_B

@rroelke and @Deb_Chatigny ,
Just ran into this thread...
I ran into some challenges using the parameter at DB level. At session level it worked GREAT!
https://forum.vertica.com/discussion/242036/vertica-9-3-introduced-joindefaulttupleformat-parameter-at-db-level-a-big-problem#latest
Have you or anyone else experienced this?
We could really use the DB level parameter! Any follow up and/or help is greatly appreciated.
Thanks.
@rbankula for awareness.

Jim_Knicely

There are reasons why JoinDefaultTupleFormat defaults to "fixed" i.e. Variable-length optimized joins are limited to 2GiB of join data. If a join attempts to go above the 2GiB limit, it throws the "join inner did not fit in memory" error; Wide VARCHAR columns on inner side of a join degrades performance of hash joins; Etc...

There are a few Jiras out there to address the known issues with the VLTS (Variable Length Tuple Store). I will keep this thread updated as to when they are resolved and are avaiable in Vertica as hot fixes.

That being said, your workload might benefit immensely by changing JoinDefaultTupleFormat to "variable"!

Sudhakar_B

Thanks @Jim_Knicely ,
It has been a long time! Hope all is well.
Thanks for some details on this parameter (including JIRA). Will keep a watch on the thread.

With current status (version 9.2.x +), do you think I can advise my customers to use this at "session level"?
With all the extensive tests I have done "at session level settings" this JoinDefaultTupleFormat parameter ROCKS!

Of course "JFMT" hint could be used as well, but that is more laborious and too "tied" to code.
Your thoughts, please?

Jim_Knicely

@Sudhakar_B - Yeah, I think it's okay to recommend setting JoinDefaultTupleFormat to "variable" at the session level! This will isolate the change from anything that the database itself is up to