topic Re: Queries are timing out in Splunk Search

Queries are timing out

sranga — Wed, 04 Aug 2010 21:49:46 GMT

We have a few charts that display summary-indexed data. The charts take a couple of form inputs including _time and an application specific parameter (attr1). The charts display data for a time range of a week. But when "All Time" or "Year to date" is chosen, the queries time out.

The query is of the form:

index=app_summary_index report=app_report | stats count as Count by attr1, attr2, _time  
  | search attr1="<user-selected-value>"  | timechart span=1h sum(Count) by attr2

The above query had close to 26K events for the past week.

Could you let me know where/how to start researching on what should be optimized? We are using Splunk version 4.0.9.

Thanks.

Re: Queries are timing out

Lowell — Thu, 05 Aug 2010 01:14:22 GMT

Please add additional information to you question. Examples include: how many events you are looking at for a week. Do you have any search time restrictions setup on your environment? What kind of data is being represented? Have you attempted to use the "inspect search" feature in the "Actions" menu. (You may need to manually run the search.) ... Please use the "edit" link below you question to add more details. The more details you can provide the more likely a helpful answer can be found.

Re: Queries are timing out

sranga — Thu, 05 Aug 2010 05:38:14 GMT

Thanks. I don't know what "search time restrictions" mean. I don't see a "inspect search" feature in the "Actions" menu. We are using version 4.0.9.

Re: Queries are timing out

Lowell — Fri, 06 Aug 2010 02:02:34 GMT

Inspect search was added around 4.1.1, if I remember correctly. It's very helpful in tracking down issues. By search restrictions, I'm talking about stuff in limits.conf or role-based search limitations; If you don't know what I'm taking about, then you probably haven't changed them; but you could easily test by running a search as an admin user vs a regular user and see if it makes a difference.

Re: Queries are timing out

Lowell — Fri, 06 Aug 2010 02:09:10 GMT

I'm not sure if your data will allow for this, but this would certainly be faster if it doesn't cause your search to break:

Option 1:

index=app_summary_index report=app_report attr1="<user-selected-value>" | stats count as Count by attr1, attr2, _time | timechart span=1h sum(Count) by attr2

If that works, then this should be even faster:

Option 2:

index=app_summary_index report=app_report attr1="<user-selected-value>" | timechart span=1h count as Count by attr2

The purpose of this is simply to reduce the number of events in the main search; reducing the number of events that splunk has to process is the single biggest way to improve search performance.

If neither of the above, work, then this could be slightly faster. But it's hard to know for sure, and I doubt it would be enough to get past your issue, but here goes...

Option 3:

index=app_summary_index report=app_report | bucket _time span=1h | stats count as Count by attr1, attr2, _time | search attr1="<user-selected-value>" | timechart span=1h count by attr2

Update:

Option 4:

index=app_summary_index report=app_report | search attr1="<user-selected-value>" | timechart span=1h count as Count by attr2

Re: Queries are timing out

sranga — Sat, 07 Aug 2010 00:45:50 GMT

Thanks Lowell. The first two options wouldn't work for us since we have to filter based on a user-input value for "attr1". The third option is what we have already. The summary indexed events are bucketed over the hour.

Re: Queries are timing out

Lowell — Sat, 07 Aug 2010 01:40:14 GMT

I'm confused on why you are ruling options 1 and 2. It's perfectly acceptable for a form search to take a form variable and use it as part of the base search; in fact it is much faster. Unless there is some complexity that you haven't posted? As long as the "attr1" field exists in the events you are searching on, then you should be able to simply use: index=app_summary_index report=app_report attr1="$token$" | ... in your searchTemplate.

Re: Queries are timing out

Lowell — Sat, 07 Aug 2010 06:40:42 GMT

So, why do you have both a stats and then timechart, is there some reason why you can't combine these like I've done in Option 2 and 4.