Why is my search slow when performed in a dashboard and fast when entered directly into the search box?

[altink]

Dear All

A search command that performed directly under "Search" takes 10-12 sec, takes 2 Minutes and 30 sec to complete when the search is performed inside a dashboard loading a single statistical table.

Can someone please explain why this performance degrade in the dashboard?

form code:

<form>
  <label>test dashboard form</label>
  <search id="mc_unf_base">
    <query>`mc_unf_base_4`</query>
    <earliest>$field_time.earliest$</earliest>
    <latest>$field_time.latest$</latest>
  </search>
  <fieldset submitButton="false">
    <input type="time" token="field_time">
      <label>Time</label>
      <default>
        <earliest>-24h@h</earliest>
        <latest>now</latest>
      </default>
    </input>
  </fieldset>
  <row>
    <panel>
      <title>Unified Audit Trail - Summary</title>
      <table>
        <search base="mc_unf_base">
          <query>stats count as "Events" by LMS_DB_NAME, LMS_DB_HOST, USERNAME, ACTION_NAME, 
          OBJECT_OWNER, OBJECT_NAME, RETURNCODE, OS_USER, USERHOST 
          | rename LMS_DB_HOST as "DB Host", LMS_DB_NAME as Database, USERNAME as Username, 
          ACTION_NAME as Action, OBJECT_OWNER as "Obj. Owner", 
          OBJECT_NAME as "Obj. Name", RETURNCODE as ReturnCode, OS_USER as "OS User", 
          USERHOST as "User Host", CURRENT_USER as "Current User"
          </query>
        </search>
        <option name="count">16</option>
        <option name="rowNumbers">true</option>
        <option name="wrap">false</option>
      </table>
    </panel>
  </row>
</form>

best regards
Altin

[dbroggy]

try saving as a report, enabling report acceleration, and then apply the report to your dashboard.

[martin_mueller]

Put your stats count into the base search, extend with other calculations needed by other panels - build a data cube, so to speak.

[martin_mueller]

The base search plus that stats search together is postprocessing. Your base search needs to return fewer rows, ideally by running a stats that computes a datacube.

Is that stats the only panel powered from the base search? To build a datacube you need to know everything powered from that cube.

[altink]

I am trying to keep the number of columns minimal in the macro - but there is a limit to this.
The stats table is not the only one, but enough to describe the problem I encountered

[martin_mueller]

I was asking for the postprocessing searches, not the macro. The problem is bad use of postprocessing.

[altink]

Correct me if wrong - but the post-processing isn't the query put above:

<query>stats count as "Events" by LMS_DB_NAME, LMS_DB_HOST, USERNAME, ACTION_NAME, 
           OBJECT_OWNER, OBJECT_NAME, RETURNCODE, OS_USER, USERHOST 
           | rename LMS_DB_HOST as "DB Host", LMS_DB_NAME as Database, USERNAME as Username, 
           ACTION_NAME as Action, OBJECT_OWNER as "Obj. Owner", 
           OBJECT_NAME as "Obj. Name", RETURNCODE as ReturnCode, OS_USER as "OS User", 
           USERHOST as "User Host", CURRENT_USER as "Current User"
           </query>

?
Am I missing something - or something is wrong in the code above?

thank you very much for helping,
best regards
Altin

[martin_mueller]

That's not a real issue. You can almost always define a datacube-style initial stats, and then slice-and-dice your other panels from that datacube, including other stats or chart commands.

I'd help you if you posted the other postprocessing searches.

[altink]

the macro contain no initial "stats", but records, X field out Y all.
may be it is the fault of my small RAM on my Linux-Splunk - 4Gb only.
If asked for the macro code, I can past that, but I see no point as the problem is form vs free search, the later being the same in both

[altink]

Thank you very much for the answer.

The "base search" is a common part to all dashboard, some of the later do "| stats", some do "| chart".
Cannot put the stats in the common search - as it is specific to only one dashboard - not all.

bet regards
Altin

[VatsalJagani]

@altink , I would suggest not to use base-search if you are having only one search in the dashboard because your base-search is not having any transforming command that will degrade performance of your dashboard.
http://docs.splunk.com/Documentation/Splunk/7.2.0/Viz/Savedsearches#Post-process_searches

[altink]

I have more then one, some 7-8 I guess. Posted only one in this example for the sake of simplicity.

[Jeremiah]

Usually it will be faster to eliminate the base search/post process searches and have 7-8 individual searches, especially if you are running in a distributed environment.

[altink]

Thank you very much for the reply @Jeremiah .
But this Question precisely I did asked for the Direct Search and single-tab Dashboard speed only, without going into the case of multiple dashboards in the form, which indeed is the real case. The later, justly proposed by you, will probably be the case for a new question.

thank you very much
Altin

[niketn]

@altink, I believe you are returning raw fields in your base search. See if you can form the base search with stats pipe in the base search rather than the post process search. Refer to Post Processing Best Practice.

[altink]

Thank you very much @niketn

Let me put here the code of the macro used, mc_unf_base_4 :

index="omega_ca" 
| eval FIELD1 = if(isnull(FIELD1),"",FIELD1) 
| eval FIELD2 = if(isnull(FIELD2),"",FIELD2) 
.......................... more eval-s..............................
| fields _time, FIELD1, FIELD2, ........., FIELDx

I have used the | fields commands which is supposed to determine explicitly the fields retrieved and avoid any _raw field pop out. Am I correct on this?

Please advise

best regards
Altin

[altink]

I guess that using "| fields" implicitly includes _raw field. Although the later I cannot see, at least in my Splunk 6.5.2. But I have seen it in some 6.4.x version - the _raw coming out.

I did add the "| table" clause after the "| field". The new code:

 index="omega_ca" 
 | eval FIELD1 = if(isnull(FIELD1),"",FIELD1) 
 | eval FIELD2 = if(isnull(FIELD2),"",FIELD2) 
 .......................... more eval-s..............................
 | fields _time, FIELD1, FIELD2, ........., FIELDx
 | table _time, FIELD1, FIELD2, ........., FIELDx

performs much better - approximately 30 sec.
But the time to run in direct search increased from 10 to 20 seconds.

My two questions at this point are:
1. why the Direct Search time increased from 10s to 20s
2. why the Dashboard time is 30 while the Direct Search 20 - meaning 10 seconds more?
shouldn't these two be the same - since it is a single statistical table form?

best regards
Altin

[niketn]

If you would see the post processing documentation your base search should not have raw results. Ideally it should be stats. Based on number of fields to be displayed in your dashboard, you should try moving stats command to your base search. Later use sum() of count based on fields you want to retain in each subsequent query.

PS: For removing internal fields like _time and _raw from search results, you need to explicitly perform fields minus command...i.e. fields - _raw.

However as listed in Post processing best practices and link provided before, try having transforming command like stats in base search for query performance improvement with post processing, including recursive post processing.

[altink]

It seemed I did have _raw results, as I did modify the code - added _raw exclusion, as below:

index="omega_ca" 
  | eval FIELD1 = if(isnull(FIELD1),"",FIELD1) 
  | eval FIELD2 = if(isnull(FIELD2),"",FIELD2) 
  .......................... more eval-s..............................
  | fields - _raw
  | fields _time, FIELD1, FIELD2, ........., FIELDx
  | table _time, FIELD1, FIELD2, ........., FIELDx

which gave me in direct search - tested many times - some 20-50% gain in time.
But if this gain is not coming from _raw exclusion:
Q1: what can it be?

I mean - I am happy with this gain - but I need to know what is about, especially if I am removing the _raw field for real, cause if not - it will surely create problems in live systems.

Clarification:
I am afraid i cannot put the stats anything higher, especially in the macro (saved-search), as the later is intended to be called from several dashboards other then the statistical table I have used for demo reasons. Thus this macro is needed as a "base dataset", which is later stat-ed or timechart-ed, or... by each dashboard.

The gain in the direct search was matched to the dashboard search also.
However in each case (fields - _raw vs not), the ratio of search speed between Direct Search and Dashboard Search remains the same in both cases, Dashboard (single stats tab) being slower then the Direct Search.
Q2: Why is the Dashboard slower - double time ! - then the direct search?
is this just the normal Splunk construction and should I try no more on this, or can something
be advised at this point?

best regards
Altin

[altink]

Furthermore:
trying to improve speed - and reduce code that I think is not needed, I did removed the "table" clause at the end of the above. Dropped line is:

| table _time, FIELD1, FIELD2, ........., FIELDx

I noticed another gain in the speed of the Direct Search (20-30%) - as I expected.

What I didn't expected - and cannot get why - was a 4-6 time more slowness in the Dashboard - which renders it practically unusable.

Q3:
How is possible that while improving speed of the direct search 20-30%, that same single run inside a form in a single dashboard type statistical table - slows with some 400-600%?
Why should the stats command of the dashboard above be impacted much negatively from the removal of the "table" clause, instead of improving?

please advise
thanks and regards
Altin

[altink]

Keeping both Fields and Table clauses - best results so far, I have however noticed that the same search run in a dashboard and populating a statistical table takes 3-4 times more to complete then the same search - got with the small bottom Loop button - executed in Search and Reports

Is this a normal Splunk behavior?

best regards
Altin