LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: Travis Brundige

My question concerns complicated group by expressions. Yesterday I ran some Agg Ex nodes, each on ~13M records with group by expressions similar to 'FIELD1.date().year(),FIELD2.date().month().subtra ct(1).div(3)'. There were 20 such nodes operating at once. One of the nodes finished in ~4.5 hours. I suspect that using these 'methods' inside a group by expression is a very bad idea. Can someone confirm this? I tested a similar set of nodes by creating new fields containing the results of the initial group by expressions in a filter node ahead of the Agg Ex and grouping by the newly created fields. These node groups finished in much less time.

Is it better to create the values you need to group by in a field ahead of any nodes you are grouping in rather than using group by statements similar to the above?

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ryeh

Hi, Travis! Good to see you on the forums.

My guess is that most of the time is spent on sorting. Can you confirm? If so, I would look at parallelizing the sort. Take a look at example #5 here: https://infogix.zendesk.com/hc/en-us/community/posts/360051778173

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: stonysmith

In the case, as you point out, it (obviously) is faster to not use the complicated expressions inside the AggEx.
The reason for this is actually rather simple.. the SORT node (inside the AggEx) can perform better with simpler expressions.

The sort node makes multiple passes of the data. This is an over-generalization of the sort node, but it'll explain why:
The sort node makes an initial pass of the data to determine which of several sort algorithms will work best for the data. During this first pass, the sort node breaks the data up into "sort segments" - smaller groups of records. The individual sort segments are then sorted, sometimes requiring more "sort segments" to be created, and finally the node outputs the final sorted sequence.

If you have complicated expressions for the sort keys, then the sort node has to re-evaluate the key(s) each time it writes records for the various segments.. if an initial segment has to be broken again up into 2 or 3 smaller segments, then it has to re-evaluate the expressions.

You've hit upon one of the optimizations possible... First, if the data is already sorted, then in the AggEx, turn off sorting, and you'll save that whole step. Second, as you found, if the Sort key is based on expressions, you may well find that it's faster to build a temporary column for the key.

FYI.. when building that temporary field, you might find another small speed enhancement if you'd make it a single column...
FIELD1.date().year() * 100 + FIELD2.date().month().subtract(1).div(3)

=======
A small side note:
The "Sort Segments" are individual files that are held open by the operating system while Sort is running. Sometimes, for huge datasets, you end up opening more files than the system can handle. On some servers, the setting is at only 1024 files, and you might see an error message "Too many files open".

There are two parameters you can tweak to work with this situation.
1) In Linux, there is an "open files limit" per user. You can see the settings by using the "ulimit -a" command. You can change the OpenFilesLimit by using the command "ulimit -n nnnnnn"
2) There is a LAE Server Property named ls.brain.node.sort.maxbuffersize which you can work with. If you make the number larger, the node will create larger segments, requiring fewer open files. Unfortunately, there is no "rule" for what to set it to.. it purely is a function of how much data you have to sort (both number of rows and number of columns), how many keys are in use, and what your system's cpu and memory configuration is. You'll have to just experiment with it a bit.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: Travis Brundige

Thank you both for your help. The parameter tweaks are definitely outside my sphere of influence. I am probably unable to determine what the settings are and certainly cannot change them, but the information and examples provided will help me optimize my graphs in the areas that I can affect, particularly sorting, grouping and parallelizing.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: stonysmith

The attached node will enumerate all the properties for you.
It doesn't allow you to change any of them, but you can at least see what they are.
Attachments:
LAE_Properties.brn