Performance Expectations

A search like that across that amount of data on that hardware should take something closer to 30 seconds on a single-instance Splunk system, even assuming your testId and region accounts for basically all 1.2 million records.

If you're way off from that, I would try a couple of things first:

• If you're running in the web UI/timeline, turn off "Field discovery"
• Better yet, try running in the "Advanced Charting" view (under the "Views" menu)
• Run in the "Advanced Charting" view, hiding the chart and turning off the "Preview" checkbox.
• Even better try running on the command line, and try adding the "-preview false" option.

Just for reference, when I do a slightly smaller (just over 1,000,000 events) and somewhat simpler search than yours on a three-plus year old laptop, I go from taking about 130 seconds to about 70 (doubling the speed) when I turn off field discovery, and then down to 30 seconds (another doubling) in the "Advanced Charting" view (with preview still on), and down to 25 seconds on the command line without preview.

I don't actually see any obvious improvements that can be made to your query while keeping the same results. However, I would be curious as to how it runs if you try each of the following:

• Omit the eventstatsand where clauses near the beginning
• Omit the sort at the end
• Omit the eventstats, where, and sort clauses
• And just for kicks, run it with only the base search plus the eventstats and where clauses.

It would also be helpful to know the final number of results returned as well as the scan count. The information in the "Inspect Search Job" page (under the "Actions" menu on the timeline search view) would be useful too, though maybe a bit obscure.

One thing to note is that a single-instance Splunk is not able to take advantage of your hardware when running a single search. I would say that if you're trying to get this to run faster, you could probably run three, four, or even more Splunk instances in a distributed config on that same machine to better utilize it, but that setup takes a bit of work and knowledge to get right.

Finally, seeing a few lines of your data might indicate something, though if it's CDN access logs, that's unlikely.

UPDATE: Try this and see how it compares. Be sure to turn preview off:

type="CDN" (testId="tl" OR testId="l") region="us" | eval median = if( median <= [ search type="CDN" (testId="tl" OR testId="l") region="us" | stats perc90(median) as search ], median,null()) | stats mean(median) as mean median(median) as median stdev(median) as stdev avg(stdDev) as avg_stdev count(median) as num_tests dc(ip) as num_ips by id | eval rel_stdev=100*(stdev/median) | table id, mean, median, avg_stdev, stdev, rel_stdev, num_tests, num_ips | sort median

Also, what I suspect is happening is that the eventstats is taking a long time to finalize, i.e., the actual computation is getting done pretty quick, but marking up the set of intermediate results is taking a long time. If you are okay with having only an approximate 90th percentile, rather than exact, try:

type="CDN" (testId="tl" OR testId="l") region="us" | eval median = if( median <= [ search type="CDN" (testId="tl" OR testId="l") region="us" | head 9999 | stats perc90(median) as search ], median,null()) | stats mean(median) as mean median(median) as median stdev(median) as stdev avg(stdDev) as avg_stdev count(median) as num_tests dc(ip) as num_ips by id | eval rel_stdev=100*(stdev/median) | table id, mean, median, avg_stdev, stdev, rel_stdev, num_tests, num_ips | sort median

which should only look at the most recent 9999 events to compute the 90th percentile, rather than scanning all 1.2 million events. This should be a lot faster than the previous, though different.