topic Re: Should search performance improve using saved search or summary index? in Knowledge Management

Should search performance improve using saved search or summary index?

pgadhari — Wed, 30 Sep 2020 00:14:34 GMT

Hi All,

I am running a search which shows the total_used_space (storage used) of an application for last 30 days. Below is the query for the same, but it takes some 40 to 45 seconds to load the panel. I want to improve the performance of this search, so that the panel loads faster. I tried creating a saved search and use that search in the panel, but still it is running very slow. Below is the search:

index=app sourcetype="app:users" | dedup user| stats sum(space_used) as total_space | eval total_space=round(total_space/1024/1024/1024/1024,2)."TB"

Please suggest on how can I create a summary index for this, as I think summary index would improve the performance for the same. Please help resolve this issue ?

Thanks
PG

Re: Should search performance improve using saved search or summary index?

lakshman239 — Thu, 25 Apr 2019 08:30:03 GMT

You could perhaps see if the below search improves the time taken? I have assumed you may want to know usage by user and only want to track where there are valid values. If you don't need user , you can take that off.

index=app sourcetype="app:users" user=* space_used=* | fields space_used, user | stats sum(space_used) as total_space  by user| eval total_space=round(total_space/1024/1024/1024/1024,2)."TB"

Depending on your number of events per day in your index, search against 30day could take a long time. If the search is still taking long, you can create a savedsearch that runs each day or a few times in a day, but only looks like last 1 day or a few hours [ you would need to adjust the earliest and latest to avoid overlap]. Pls refer to https://docs.splunk.com/Documentation/Splunk/7.2.4/Knowledge/Usesummaryindexing

Re: Should search performance improve using saved search or summary index?

vishaltaneja070 — Thu, 25 Apr 2019 08:32:53 GMT

Hello @pgadhari

If you don't want to run the query again and again, then you can create a scheduled search for this and using loadjob command you can load the result of last job, which can make it faster.
Otherwise go for acceleration of report which can also be helpful. you can create acceleration for 30days.
For summary indexing, you can create a summary index and using collect command send the results to that index.

Re: Should search performance improve using saved search or summary index?

pgadhari — Thu, 25 Apr 2019 08:46:32 GMT

No. I dont want to know usage by user. I am finding out total used space by all users in last 30 days. The query you have written will not give me my result, as it is returning used_space by each user and that too multiple entries for each user. That is why I am using dedup user so that i get latest utilization for users. My query is returning the proper value, but as that has to be run for 30 days, which is taking time.

Hence, I need suggestion that how can I make use of summary index in this and this output value should be close to real-time. How do I configure summary index for a panel that aggregates the data for last 30 days and shows the real-time value ? Hope you got my question ? Please advise ?

Re: Should search performance improve using saved search or summary index?

pgadhari — Thu, 25 Apr 2019 08:47:21 GMT

Can you provide an example of how can I implement point no. 1 ?

Out of 3 points you have specified ? which is the best solution, please advise ?

Re: Should search performance improve using saved search or summary index?

vishaltaneja070 — Thu, 25 Apr 2019 10:26:39 GMT

Hello @pgadhari

If you want to have the results showed for last 30days, then it is better to go with load job one, you can schedule job to run once a day, at the starting of day and load the results to the panel for the full day.
like

 | loadjob savedsearch="admin:search:MySavedSearch"

I will always to try to find out other way then summary indexing and also with summary indexing if you are changing the sourcetype then the usage will count as license usage.

Re: Should search performance improve using saved search or summary index?

pgadhari — Thu, 25 Apr 2019 10:31:05 GMT

The problem with the loadjob is - if the user wants to change the "time range" from 30 days to last 2 months or 3 months, then it will still show the value for 30 days only, which will be wrong, as the saved search will be configured to run for 30 days. How can I resolve that issue ? any solution on that problem ?

Re: Should search performance improve using saved search or summary index?

vishaltaneja070 — Thu, 25 Apr 2019 10:37:50 GMT

Hello @pgadhari

Check this out, you can do it like this:
https://answers.splunk.com/answers/188469/how-to-get-results-to-load-with-a-time-picker-sett.html#answer-432226.

Re: Should search performance improve using saved search or summary index?

lakshman239 — Thu, 25 Apr 2019 10:38:49 GMT

when you use stats by user, it shouldn't return multiple entries for same user. So, if you change your search to something like below, how long does it take to run? index=app sourcetype="app:users" user=* space_used=* | fields space_used | stats sum(space_used) as total_space| eval total_space=round(total_space/1024/1024/1024/1024,2)."TB"

If you are going down the summary index approach, you can setup a scheduled search (as per the link sent earlier) and write the results to 'summary' index. Your dashboard then can have another search that pulls the results off the summary index. [ the eval total_space can be moved to the search in the dashboard as well]

Re: Should search performance improve using saved search or summary index?

pgadhari — Thu, 25 Apr 2019 10:48:33 GMT

Ok. Will check that and update shortly. Thanks.

Re: Should search performance improve using saved search or summary index?

vishaltaneja070 — Fri, 26 Apr 2019 06:36:19 GMT

@pgadgari

Any update?

Re: Should search performance improve using saved search or summary index?

pgadhari — Sat, 27 Apr 2019 11:26:27 GMT

@vishaltaneja07011993 .. I tried putting the time picker options using above link, but that is not working. It takes the time from "time picker" properly in the search, but no results are found in that timeframe selected. Please advise ?

Re: Should search performance improve using saved search or summary index?

pgadhari — Sat, 27 Apr 2019 11:46:24 GMT

the query still takes 112 seconds to execute. If I use stats by user, it calculates all the event values of last 30 days which is not correct, hence I have to use "dedup user" which will take latest used_space field value and add for all users. But still it takes more than 100 seconds which is very slow.

I have already configured summary index which is populated by scheduled search, but still that takes time. Hence, I think feasible approach is "loadjob", but the loadjob time range is the issue, as it does not load data based on timepicker option.

Re: Should search performance improve using saved search or summary index?

ddrillic — Sat, 27 Apr 2019 14:03:25 GMT

It's a very good use case for a summary index. The major draw back of summary index is the fact that it's very common for the Splunk platform to skip searches and the summary index integrity can be compromised when searches are skipped. But here in your use case (if I understand it correctly), some skipped searches won't impact the value of the generated summary index.

Re: Should search performance improve using saved search or summary index?

woodcock — Sun, 28 Apr 2019 03:38:45 GMT

The Scheduled Saved Search option should work just fine for your use-case. You are probably using the | savesearch in your panel but try switching to | loadjob and make sure that your saved search is scheduled to run periodically and your panel will be instantaneous.

Re: Should search performance improve using saved search or summary index?

pgadhari — Sun, 28 Apr 2019 08:38:26 GMT

I am trying to use the loadjob for some other query also wherein I am facing the performance issues. But somehow I think it is not getting the _time after the loadjob output.

Re: Should search performance improve using saved search or summary index?

pgadhari — Wed, 30 Sep 2020 00:17:41 GMT

@vishaltaneja07011993 - I was able to fix the total_used_space issue using the loadjob command wherein it shows for last 30 days. That is working fine now.

I have another query which is powered by summary index and datamodel. But that query is also taking more time to execute and I am thinking of using loadjob command there, but I think the problem is - after the loadjob command, _time is not returned and thats why the start_time and end_time link which you shared earlier does not seems to be working. I will share the query details shortly.

Re: Should search performance improve using saved search or summary index?

pgadhari — Sun, 28 Apr 2019 09:51:12 GMT

I will close this question, as loadjob for my this query is working very good. I have another query for which I am opening a new question. Thanks.

Re: Should search performance improve using saved search or summary index?

woodcock — Sun, 28 Apr 2019 19:02:37 GMT

That makes no sense. You just need to pass it the events=true argument.

Re: Should search performance improve using saved search or summary index?

pgadhari — Mon, 29 Apr 2019 05:02:17 GMT

where I have to pass the argument events=true ? can you share more details please ?