Search using summary indexing is still really slow. What else can I do to speed it up?

[lyndac]

I am using a summary index to help speed up a search for my dashboard. Prior to using the summary index, the search would take almost 7 minutes to generate stats over 30 days. Using the summary index, it takes about 65 seconds. Which, according to the users, is still too long.

What else can I do to try to speed it up? The summary search is set up to run hourly with earliest set to -4h@h and latest to -3h@h to protect against late arriving files.
The summary search is : "index=foo | sistats count as count sum(filesize) as volume by location, hub, src, direction, priority"

The search that is called from the dashboard (created with sideviewutil) is "index=si-br-summary | stats count as count sum(filesize) as volume by location, hub, src, direction, priority | search location=$location$ | stats sum(eval(if(direction="IN",count,0))) as count_in sum(eval(if(direction="OUT",count,0))) as count_out sum(eval(if(direction="IN", volume, 0))) as volume_in sum(eval(if(direction="OUT", volume, 0))) as volume_out sum(eval(if(direction="IN" AND priority=1, count, 0))) as p1_in sum(eval(if(direction="IN" AND priority=2, count, 0))) as p2_in sum(eval(if(direction="IN" AND priority=3, count, 0))) as p3_in sum(eval(if(direction="OUT" AND priority=1, count, 0))) as p1_out sum(eval(if(direction="OUT" AND priority=2, count, 0))) as p2_out sum(eval(if(direction="OUT" AND priority=3, count, 0))) as p3_out by location,hub, src

The entire search takes 75 seconds to run. So 65 seconds for the initial stats from the summary index and then an additional 10 seconds for the follow on calculations.

The original data comes from a csv file with these fields: location, direction, hub, src, priority, filesize, timestamp. It basically is a listing of files delivered to an ftp server at a location or sent from the ftp server at a location.

I have created the dashboard using sideview utils. It will be used by many different users, each of whom can select the timeframe they want to look at. Most common timeframes will be last 24 hours, last 7 days, last 30 days, but they can choose whatever they want.

The goal is to show a table similar to this:

I thought about summarizing the summary index daily, but then I have to be able to smartly use the daily summary for some parts of the timeframe and the hourly summary for anything in the current day. Is that even possible to do?

[woodcock]

You can speed it up by rolling up daily and you can do it all in daily like this:
You run the daily summary against hour data every hour but first you delete the previous hour's data for "today". That way, "today's" daily summary gets regenerated every hour, 24 times each day and then it is static.

[martin_mueller]

Automated |delete calls sounds like you're looking for trouble.

[woodcock]

If you limit it to only "today" and it only runs "today", then you have a search that runs "just after midnight for "yesterday", then all you have liability for is "today's" summary data. It is pretty safe.

[martin_mueller]

By running |delete automatically, you're causing a hot bucket roll every time... setting yourself up for many tiny slow buckets.

[martin_mueller]

First of all, look at the job inspector of the dashboard's search to see what phase of the search is using up the most time, and focus on optimizing that first.

I'm guessing by location, hub, src, direction, priority has very high cardinality? Reducing that cardinality is going to significantly reduce the number of events in your summary index, and therefore speed up the searches using the summary index.
Is there a split-by field you don't actually need in the dashboard? Drop it.
Can you pre-compute some of the evals you run again and again in the dashboard? Do that.

Additionally, you can move the location filter to before the first stats to avoid loading events from locations not of interest to the user.

Beyond that there's report and datamodel acceleration as an alternative to summary indexing.

[lyndac]

I tried rolling the evaluations into the summary index search and I'm not getting any results. If i run the same command in the search bar (using just stats instead of si-stats), I get the results I expect. However, when I run it as a summary index using the sistats command, the only columns that have values are location,hub,src. All other column names are present, but the values are all 0 (or no value as for volume_in and volume_out). Am I allowed to do evals in the sistats command?

index=foo | sistats count(eval(direction="IN")) as count_in count(eval(direction="OUT")) as count_out sum(eval(if(direction="IN", volume, 0))) as volume_in sum(eval(if(direction="OUT", volume, 0))) as volume_out count(eval(direction="IN" AND priority=1)) as p1_in count(eval(direction="IN" AND priority=2)) as p2_in count(eval(direction="IN" AND priority=3)) as p3_in count(eval(direction="OUT" AND priority=1)) as p1_out count(eval(direction="OUT" AND priority=2)) as p2_out count(eval(direction="OUT" AND priority=3)) as p3_out by location,hub, src

[lyndac]

I tried moving the field evaluations out of the sistats command. This allowed me to take two fields out of the by clause, which should have reduced the cardinality, thus making the search faster. BUT...the search is now SLOWER! How is that possible? A 7-day search was taking 15 seconds. Now it takes 43.4 seconds. I haven't retried a 30 day search because I haven't taken the time to backfill the si completely.

The summary-index populating search is:
index=foo|eval count_in=if(direction="IN",1,0) | eval volume_in=if(direction="IN", filesize,0) | eval p1_in=if(direction="IN" AND priority=1,1,0) | eval p2_in=if(direction="IN" AND priority=2,1,0) | eval p3_in=if(direction="IN" AND priority=3,1,0) |eval count_out=if(direction="OUT",1,0) | eval volume_out=if(direction="OUT", filesize,0) | eval p1_out=if(direction="OUT" AND priority=1,1,0) | eval p2_out=if(direction="OUT" AND priority=2,1,0) | eval p3_out=if(direction="OUT" AND priority=3,1,0) | sistats sum(count_in) as count_in sum(volume_in) as volume_in sum(p1_in) as p1_in sum(p2_in) as p2_in sum(p3_in) as p3_in sum(count_out) as count_out sum(volume_out) as volume_out sum(p1_out) as p1_out sum(p2_out) as p2_out sum(p3_out) as p3_out by location, hub, src

And the search on the dashboard is :
index=si-br-summary location=$location$ | stats sum(count_in) as count_in sum(volume_in) as volume_in sum(p1_in) as p1_in sum(p2_in) as p2_in sum(p3_in) as p3_in sum(count_out) as count_out sum(volume_out) as volume_out sum(p1_out) as p1_out sum(p2_out) as p2_out sum(p3_out) as p3_out by location, hub, src

[martin_mueller]

Compare the number of events in your summary index for those seven days between the old search and the new search. The new search should have yielded several times fewer, if not then something's wrong.