topic Re: Why is using base searches causing major performance issues on my dashboard? in Splunk Search

Why is using base searches causing major performance issues on my dashboard?

klinek — Thu, 01 Sep 2016 21:01:42 GMT

Working on making dashboards to help report on activity.

To make the dashboards as performant as possible, I'm using base searches.

However, it appears that using base searches throughout a dashboard (1 search, but used in all the panels) almost causes an exponential increase in the size of the search - which causes the search to prematurely terminate.

Doing a straight search for one of the panels got: 98,803 results, and took up 0.29 MB.

Just turning that 1 search into a 1 panel dashboard using a base search got same number of results, but took up 83.66 MB.

Which seems completely ridiculous.
If I trim down results to just the last 2 weeks (instead of the last month), I get around 45,000 results, and the dashboard load only takes up 7MB.

I'm trying to figure out if I'm doing something wrong? Or should I not use base queries at all - as they seem to constantly cause performance issues??

The dashboard:

<form>
  <label>Usage</label>
  <fieldset autoRun="false" submitButton="true">
    <input type="dropdown" token="tenantid_tok">
      <label>Domain</label>
      <default>All</default>
      <choice value="">All</choice> 
    </input>
    <input type="time" token="date_tok" searchWhenChanged="true">
      <label>Time Range</label>
      <default>
        <earliest>-1mon@d</earliest>
        <latest>now</latest>
      </default>
    </input>
  </fieldset>
  <search id="login_checkpoints">
    <query>index=myIndex sourcetype=mySource $tenantid_tok$ NOT CrashReport NOT ErrorReport ("TYPE1" OR "TYPE2")
            | `expand_checkpoints`
            | fields - _raw
            | spath input=checkPoint output=eventName path=EventName
            | where match(eventName,"TYPE1") or match(eventName,"TYPE2") 
            | spath input=checkPoint output=userId path=UserId
            | spath input=checkPoint output=tenantId path=TenantId
            | spath input=checkPoint output=eventTime path=EventTime
            | spath input=checkPoint output=metaData path=MetaData
            | spath input=metaData output=userType path=USER_TYPE
            | spath input=checkPoint output=deviceId path=DeviceId
            | spath input=checkPoint output=deviceModel path=DeviceModel
            | spath input=checkPoint output=deviceOS path=DeviceOS
            | spath input=checkPoint output=appVersion path=Version
            | eval deviceType=if(like(deviceOS,"Android%"),"Android","iOS")
            | eval fullUserName=userId+"@"+tenantId
            | fields - checkPoint
            | fields eventName,userId,tenantId,eventTime,metaData,userType,deviceId,deviceModel,deviceOS,appVersion,deviceType,fullUserName</query>
    <earliest>$date_tok.earliest$</earliest>
    <latest>$date_tok.latest$</latest>
  </search>
  <row>
    <panel>
      <title>Users</title>
      <single>
        <title>Total</title>
        <search base="login_checkpoints">
          <query>chart dc(fullUserName)</query>
        </search>
        <option name="drilldown">none</option>
        <option name="colorBy">value</option>
        <option name="colorMode">none</option>
        <option name="numberPrecision">0</option>
        <option name="showSparkline">1</option>
        <option name="showTrendIndicator">1</option>
        <option name="trendColorInterpretation">standard</option>
        <option name="trendDisplayMode">absolute</option>
        <option name="useColors">0</option>
        <option name="useThousandSeparators">1</option>
        <option name="linkView">search</option>
        <option name="rangeColors">["0x65a637","0x6db7c6","0xf7bc38","0xf58f39","0xd93f3c"]</option>
        <option name="rangeValues">[0,30,70,100]</option>
        <option name="trendInterval">auto</option>
      </single>
    </panel>
    <panel>
      <title>Devices</title>
      <single>
        <title>Total</title>
        <search base="login_checkpoints">
          <query>dedup deviceId
            | chart count(deviceId)</query>
        </search>
        <option name="drilldown">none</option>
        <option name="colorBy">value</option>
        <option name="colorMode">none</option>
        <option name="numberPrecision">0</option>
        <option name="showSparkline">1</option>
        <option name="showTrendIndicator">1</option>
        <option name="trendColorInterpretation">standard</option>
        <option name="trendDisplayMode">absolute</option>
        <option name="useColors">0</option>
        <option name="useThousandSeparators">1</option>
        <option name="linkView">search</option>
      </single>
    </panel>
  </row>
</form>

-Thanks!

Re: Why is using base searches causing major performance issues on my dashboard?

sundareshr — Thu, 01 Sep 2016 21:22:13 GMT

All queries are executed on the server (index/search) and only final results are sent to the client. In most cases, the final results is only a small subset of the number of events. However, in your case, since you do not have a tranforming command in your base search, the query returns all the events (there a limit of 500K), increasing the size. Try adding the chart command to your base search and eliminate fields in your subsearch. One way could be

*Base Search*

... | chart dc(deviceId) as device_count dc(fullUserName) user_count

*Sub Search*

| fields user_count

Re: Why is using base searches causing major performance issues on my dashboard?

klinek — Thu, 01 Sep 2016 21:31:04 GMT

@sundareshr - Thanks for the quick response!

The problem is, the end result for the dashboard is going to have lots of other panels on it, too. So, I wouldn't be able to use a single 'transforming command'. (ex. I want to show a pie chart of what different OS's are being used, and what different device types are being used, and show a line chart of number of users per day).

Now, all of this derives from the same data, but, do I need to still break it into separate base queries - that have a transform command at the end?

Or, does the 'table' command count as a transforming command? I would have thought the 'fields' command at the end would limit the end data to just those final results...

-Thanks!

Re: Why is using base searches causing major performance issues on my dashboard?

sundareshr — Thu, 01 Sep 2016 23:11:31 GMT

Unfortunately, the table command does not reduce the number of rows. If you share all the queries (just the queries) we can come up with a way to optimize the base search. Or the other option is you can create 2-3 base searches, doesn't have to be only one. You will still see the benefits of reducing the number of concurrent searches.

One option could be to do a stats count by deviceId fullUserName os etc etc etc in the base search and then do a stats dc(deviceId) OR stats count by os in the sub-search.

Hope this gives you some ideas. If not, do share the queries and we can come up with a optimized solution

Re: Why is using base searches causing major performance issues on my dashboard?

klinek — Tue, 06 Sep 2016 13:36:45 GMT

@sundareshr - Sure, here is the whole dashboard (slightly modified, to protect the innocent! 😉 ).

I really appreciate the quick responses, and I hope you can help us get to a good pattern we can follow with other dashboards we are working to create.

-Thanks!

<form>
  <label>Usage</label>
  <fieldset autoRun="false" submitButton="true">
    <input type="dropdown" token="tenantid_tok">
      <label>Domain</label>
      <default>All</default>
      <choice value="">All</choice> 
    </input>
    <input type="time" token="date_tok" searchWhenChanged="true">
      <label>Time Range</label>
      <default>
        <earliest>-1mon@d</earliest>
        <latest>now</latest>
      </default>
    </input>
  </fieldset>
  <search id="login_checkpoints">
    <query>index=myIndex sourcetype=mySource $tenantid_tok$ NOT CrashReport NOT ErrorReport ("TYPE1" OR "TYPE2")
            | `expand_checkpoints`
            | fields - _raw
            | spath input=checkPoint output=eventName path=EventName
            | where match(eventName,"TYPE1") or match(eventName,"TYPE2") 
            | spath input=checkPoint output=userId path=UserId
            | spath input=checkPoint output=tenantId path=TenantId
            | spath input=checkPoint output=eventTime path=EventTime
            | spath input=checkPoint output=metaData path=MetaData
            | spath input=metaData output=userType path=USER_TYPE
            | spath input=checkPoint output=deviceId path=DeviceId
            | spath input=checkPoint output=deviceModel path=DeviceModel
            | spath input=checkPoint output=deviceOS path=DeviceOS
            | spath input=checkPoint output=appVersion path=Version
            | eval deviceType=if(like(deviceOS,"Android%"),"Android","iOS")
            | eval fullUserName=userId+"@"+tenantId
            | fields - checkPoint
            | fields    eventName,userId,tenantId,eventTime,metaData,userType,deviceId,deviceModel,deviceOS,appVersion,deviceType,fullUserName</query>
    <earliest>$date_tok.earliest$</earliest>
    <latest>$date_tok.latest$</latest>
  </search>
  <row>
    <panel>
      <title>Users</title>
      <single>
        <title>Total</title>
        <search base="login_checkpoints">
          <query>chart dc(fullUserName)</query>
        </search>
        <option name="drilldown">none</option>
        <option name="colorBy">value</option>
        <option name="colorMode">none</option>
        <option name="numberPrecision">0</option>
        <option name="showSparkline">1</option>
        <option name="showTrendIndicator">1</option>
        <option name="trendColorInterpretation">standard</option>
        <option name="trendDisplayMode">absolute</option>
        <option name="useColors">0</option>
        <option name="useThousandSeparators">1</option>
        <option name="linkView">search</option>
        <option name="rangeColors">["0x65a637","0x6db7c6","0xf7bc38","0xf58f39","0xd93f3c"]</option>
        <option name="rangeValues">[0,30,70,100]</option>
        <option name="trendInterval">auto</option>
      </single>
    </panel>
    <panel>
      <title>Devices</title>
      <single>
        <title>Total</title>
        <search base="login_checkpoints">
          <query>dedup deviceId
            | chart count(deviceId)</query>
        </search>
        <option name="drilldown">none</option>
        <option name="colorBy">value</option>
        <option name="colorMode">none</option>
        <option name="numberPrecision">0</option>
        <option name="showSparkline">1</option>
        <option name="showTrendIndicator">1</option>
        <option name="trendColorInterpretation">standard</option>
        <option name="trendDisplayMode">absolute</option>
        <option name="useColors">0</option>
        <option name="useThousandSeparators">1</option>
        <option name="linkView">search</option>
      </single>
    </panel>
  </row>
  <row>
    <panel>
      <single>
        <search base="login_checkpoints">
          <query>dedup fullUserName
            | where match(userType,"INTERNAL")
            | chart count(fullUserName)</query>
        </search>
        <option name="drilldown">none</option>
        <option name="colorBy">value</option>
        <option name="colorMode">none</option>
        <option name="numberPrecision">0</option>
        <option name="showSparkline">1</option>
        <option name="showTrendIndicator">1</option>
        <option name="trendColorInterpretation">standard</option>
        <option name="trendDisplayMode">absolute</option>
        <option name="useColors">0</option>
        <option name="useThousandSeparators">1</option>
        <option name="linkView">search</option>
        <option name="rangeColors">["0x65a637","0x6db7c6","0xf7bc38","0xf58f39","0xd93f3c"]</option>
        <option name="rangeValues">[0,30,70,100]</option>
        <option name="trendInterval">auto</option>
        <option name="afterLabel">Internal</option>
      </single>
      <single>
        <search base="login_checkpoints">
          <query>dedup fullUserName
            | where match(userType,"EXTERNAL")
            | chart count(fullUserName)</query>
        </search>
        <option name="drilldown">none</option>
        <option name="colorBy">value</option>
        <option name="colorMode">none</option>
        <option name="numberPrecision">0</option>
        <option name="showSparkline">1</option>
        <option name="showTrendIndicator">1</option>
        <option name="trendColorInterpretation">standard</option>
        <option name="trendDisplayMode">absolute</option>
        <option name="useColors">0</option>
        <option name="useThousandSeparators">1</option>
        <option name="linkView">search</option>
        <option name="rangeColors">["0x65a637","0x6db7c6","0xf7bc38","0xf58f39","0xd93f3c"]</option>
        <option name="rangeValues">[0,30,70,100]</option>
        <option name="trendInterval">auto</option>
        <option name="beforeLabel">External</option>
      </single>
    </panel>
    <panel>
      <single>
        <search base="login_checkpoints">
          <query>dedup deviceId
            | where match(deviceType,"iOS")
            | chart count(deviceId)</query>
        </search>
        <option name="drilldown">none</option>
        <option name="colorBy">value</option>
        <option name="colorMode">none</option>
        <option name="numberPrecision">0</option>
        <option name="showSparkline">1</option>
        <option name="showTrendIndicator">1</option>
        <option name="trendColorInterpretation">standard</option>
        <option name="trendDisplayMode">absolute</option>
        <option name="useColors">0</option>
        <option name="useThousandSeparators">1</option>
        <option name="linkView">search</option>
        <option name="rangeColors">["0x65a637","0x6db7c6","0xf7bc38","0xf58f39","0xd93f3c"]</option>
        <option name="rangeValues">[0,30,70,100]</option>
        <option name="trendInterval">auto</option>
        <option name="afterLabel">iOS</option>
      </single>
      <single>
        <search base="login_checkpoints">
          <query>dedup deviceId
            | where match(deviceType,"Android")
            | chart count(deviceId)</query>
        </search>
        <option name="drilldown">none</option>
        <option name="colorBy">value</option>
        <option name="colorMode">none</option>
        <option name="numberPrecision">0</option>
        <option name="showSparkline">1</option>
        <option name="showTrendIndicator">1</option>
        <option name="trendColorInterpretation">standard</option>
        <option name="trendDisplayMode">absolute</option>
        <option name="useColors">0</option>
        <option name="useThousandSeparators">1</option>
        <option name="linkView">search</option>
        <option name="rangeColors">["0x65a637","0x6db7c6","0xf7bc38","0xf58f39","0xd93f3c"]</option>
        <option name="rangeValues">[0,30,70,100]</option>
        <option name="trendInterval">auto</option>
        <option name="beforeLabel">Android</option>
      </single>
    </panel>
  </row>
  <row>
    <panel>
      <title>Unique User Logins</title>
      <chart>
        <search base="login_checkpoints">
          <query>timechart dc(fullUserName) as "Unique Users"</query>
        </search>
        <option name="charting.chart">line</option>
        <option name="charting.axisLabelsX.majorLabelStyle.overflowMode">ellipsisNone</option>
        <option name="charting.axisLabelsX.majorLabelStyle.rotation">0</option>
        <option name="charting.axisTitleX.visibility">collapsed</option>
        <option name="charting.axisTitleY.visibility">collapsed</option>
        <option name="charting.axisTitleY2.visibility">visible</option>
        <option name="charting.axisX.scale">linear</option>
        <option name="charting.axisY.scale">linear</option>
        <option name="charting.axisY2.enabled">0</option>
        <option name="charting.axisY2.scale">inherit</option>
        <option name="charting.chart.bubbleMaximumSize">50</option>
        <option name="charting.chart.bubbleMinimumSize">10</option>
        <option name="charting.chart.bubbleSizeBy">area</option>
        <option name="charting.chart.nullValueMode">gaps</option>
        <option name="charting.chart.showDataLabels">none</option>
        <option name="charting.chart.sliceCollapsingThreshold">0.01</option>
        <option name="charting.chart.stackMode">default</option>
        <option name="charting.chart.style">shiny</option>
        <option name="charting.drilldown">all</option>
        <option name="charting.layout.splitSeries">0</option>
        <option name="charting.layout.splitSeries.allowIndependentYRanges">0</option>
        <option name="charting.legend.labelStyle.overflowMode">ellipsisMiddle</option>
        <option name="charting.legend.placement">none</option>
        <option name="height">325</option>
      </chart>
    </panel>
  </row>
  <row>
    <panel>
      <title>Application Versions</title>
      <chart>
        <search base="login_checkpoints">
          <query>dedup deviceId
            | eval versionAndDevice=appVersion+" ("+deviceType+")"
            | chart count by versionAndDevice useother=false</query>
        </search>
        <option name="charting.chart">pie</option>
        <option name="charting.axisLabelsX.majorLabelStyle.overflowMode">ellipsisNone</option>
        <option name="charting.axisLabelsX.majorLabelStyle.rotation">0</option>
        <option name="charting.axisTitleX.visibility">visible</option>
        <option name="charting.axisTitleY.visibility">visible</option>
        <option name="charting.axisTitleY2.visibility">visible</option>
        <option name="charting.axisX.scale">linear</option>
        <option name="charting.axisY.scale">linear</option>
        <option name="charting.axisY2.enabled">0</option>
        <option name="charting.axisY2.scale">inherit</option>
        <option name="charting.chart.bubbleMaximumSize">50</option>
        <option name="charting.chart.bubbleMinimumSize">10</option>
        <option name="charting.chart.bubbleSizeBy">area</option>
        <option name="charting.chart.nullValueMode">gaps</option>
        <option name="charting.chart.showDataLabels">none</option>
        <option name="charting.chart.sliceCollapsingThreshold">0.001</option>
        <option name="charting.chart.stackMode">default</option>
        <option name="charting.chart.style">shiny</option>
        <option name="charting.drilldown">all</option>
        <option name="charting.layout.splitSeries">0</option>
        <option name="charting.layout.splitSeries.allowIndependentYRanges">0</option>
        <option name="charting.legend.labelStyle.overflowMode">ellipsisMiddle</option>
        <option name="charting.legend.placement">right</option>
        <option name="height">375</option>
      </chart>
    </panel>
    <panel>
      <title>Device OS</title>
      <chart>
        <search base="login_checkpoints">
          <query>dedup deviceId
            | chart count by deviceOS useother=false</query>
        </search>
        <option name="charting.chart">pie</option>
        <option name="charting.axisLabelsX.majorLabelStyle.overflowMode">ellipsisNone</option>
        <option name="charting.axisLabelsX.majorLabelStyle.rotation">0</option>
        <option name="charting.axisTitleX.visibility">visible</option>
        <option name="charting.axisTitleY.visibility">visible</option>
        <option name="charting.axisTitleY2.visibility">visible</option>
        <option name="charting.axisX.scale">linear</option>
        <option name="charting.axisY.scale">linear</option>
        <option name="charting.axisY2.enabled">0</option>
        <option name="charting.axisY2.scale">inherit</option>
        <option name="charting.chart.bubbleMaximumSize">50</option>
        <option name="charting.chart.bubbleMinimumSize">10</option>
        <option name="charting.chart.bubbleSizeBy">area</option>
        <option name="charting.chart.nullValueMode">gaps</option>
        <option name="charting.chart.showDataLabels">none</option>
        <option name="charting.chart.sliceCollapsingThreshold">0.001</option>
        <option name="charting.chart.stackMode">default</option>
        <option name="charting.chart.style">shiny</option>
        <option name="charting.drilldown">all</option>
        <option name="charting.layout.splitSeries">0</option>
        <option name="charting.layout.splitSeries.allowIndependentYRanges">0</option>
        <option name="charting.legend.labelStyle.overflowMode">ellipsisMiddle</option>
        <option name="charting.legend.placement">right</option>
        <option name="height">375</option>
      </chart>
    </panel>
  </row>
  <row>
    <panel>
      <title>Internal Devices</title>
      <table>
        <search base="login_checkpoints">
          <query>where match(userType,"INTERNAL")
            | dedup deviceId
            | chart count by deviceModel useother=false
            | sort - count</query>
        </search>
        <option name="wrap">true</option>
        <option name="rowNumbers">false</option>
        <option name="drilldown">row</option>
        <option name="dataOverlayMode">none</option>
        <option name="count">15</option>
      </table>
    </panel>
    <panel>
      <title>External Devices</title>
      <table>
        <search base="login_checkpoints">
          <query>where match(userType,"EXTERNAL")
            | dedup deviceId
            | chart count by deviceModel useother=false
            | sort - count</query>
        </search>
        <option name="wrap">true</option>
        <option name="rowNumbers">false</option>
        <option name="drilldown">row</option>
        <option name="dataOverlayMode">none</option>
        <option name="count">15</option>
      </table>
    </panel>
  </row>
</form>

Re: Why is using base searches causing major performance issues on my dashboard?

ppablo — Tue, 06 Sep 2016 16:38:00 GMT

Hi @sundareshr

Just fyi, I tried converting the "answer" by @klinek to a comment under your answer, but it has too many characters to convert unfortunately. When you do get a chance to respond, just continue your comments under your answer here. Thanks!

Patrick

Re: Why is using base searches causing major performance issues on my dashboard?

the_wolverine — Tue, 06 Sep 2016 17:04:02 GMT

What version of Splunk are you seeing this in? There may be a bug you are encountering depending on the version -- exactly what I don't know without more details but there's a one that will crash your browser if you have multiple panels and one or more is rendering a chart. Bug in 6.3, fixed in 6.4.

Re: Why is using base searches causing major performance issues on my dashboard?

sundareshr — Tue, 06 Sep 2016 18:04:34 GMT

I would suggest creating a base search for all panels, except the timechart panel. And repeat your search for timechart panel.

   <search id="login_checkpoints">
     <query>    everything from your base search minus the fields command in the end | eval versionAndDevice=appVersion+" ("+deviceType+")" | stats count by 
    userId, userType, deviceId, deviceModel, deviceOS, appVersion, deviceType, fullUserName versionAndDevice</query>
   </search>
   <row>
     <panel>
       <title>Users</title>
       <single>
         <title>Total</title>
         <search base="login_checkpoints">
           <query>stats dc(fullUserName)</query>
         </search>
       </single>
     </panel>
     <panel>
       <title>Devices</title>
       <single>
         <title>Total</title>
         <search base="login_checkpoints">
           <query>| stats dc(deviceId)</query>
         </search>
       </single>
     </panel>
   </row>
   <row>
     <panel>
       <single>
         <search base="login_checkpoints">
           <query>dedup fullUserName
             | where match(userType,"INTERNAL")
             | stats count(fullUserName)</query>
         </search>
       </single>
       <single>
         <search base="login_checkpoints">
           <query>dedup fullUserName
             | where match(userType,"EXTERNAL")
             | stats count(fullUserName)</query>
         </search>
       </single>
     </panel>
     <panel>
       <single>
         <search base="login_checkpoints">
           <query>dedup deviceId
             | where match(deviceType,"iOS")
             | chart count(deviceId)</query>
         </search>
       </single>
       <single>
         <search base="login_checkpoints">
           <query>dedup deviceId
             | where match(deviceType,"Android")
             | chart count(deviceId)</query>
         </search>
       </single>
     </panel>
   </row>
   <row>
     <panel>
       <title>Unique User Logins</title>
       <chart>
         <search>
           <query>REPEAT YOUR BASE SEARCH | timechart dc(fullUserName) as "Unique Users"</query>
         </search>
       </chart>
     </panel>
   </row>
   <row>
     <panel>
       <title>Application Versions</title>
       <chart>
         <search base="login_checkpoints">
           <query>dedup deviceId
             | chart count by versionAndDevice useother=false</query>
         </search>
       </chart>
     </panel>
     <panel>
       <title>Device OS</title>
       <chart>
         <search base="login_checkpoints">
           <query>dedup deviceId
             | chart count by deviceOS useother=false</query>
         </search>
       </chart>
     </panel>
   </row>
   <row>
     <panel>
       <title>Internal Devices</title>
       <table>
         <search base="login_checkpoints">
           <query>where match(userType,"INTERNAL")
             | dedup deviceId
             | chart count by deviceModel useother=false
             | sort - count</query>
         </search>
       </table>
     </panel>
     <panel>
       <title>External Devices</title>
       <table>
         <search base="login_checkpoints">
           <query>where match(userType,"EXTERNAL")
             | dedup deviceId
             | chart count by deviceModel useother=false
             | sort - count</query>
         </search>
       </table>
     </panel>
   </row

Re: Why is using base searches causing major performance issues on my dashboard?

klinek — Tue, 06 Sep 2016 20:23:00 GMT

@sundareshr - Thanks again for all the help.

Your plan of attack worked!

By using the 'chart' command - that got those results down to 10.2 MB, and then separating out the timechart results, only take 0.29MB. For the same number of results as before.

So, looks like a decent set of 'design' rules would be:

1) Ensure your base query ends with a 'transforming command' - chart, stats, etc.
2) Use different queries for timecharts & individual results

Is there a good way to re-use a base query for multiple timecharts?

-Thanks!

Re: Why is using base searches causing major performance issues on my dashboard?

sundareshr — Tue, 06 Sep 2016 20:37:52 GMT

For timechart based panels, you can generate data using bin & stats. Something like this. This may work in your current dashboard as well.

... | bin span=15m (or whatever span you choose) _time | stats count avg max etc by _time

This will give you the following columns

_time count avg max etc

Then in your sub-search, you can use timechart

Re: Why is using base searches causing major performance issues on my dashboard?

klinek — Tue, 06 Sep 2016 22:26:06 GMT

@sundareshr - I don't seem to be able get get bin, stats, and timechart to play nice together...

Re: Why is using base searches causing major performance issues on my dashboard?

soumyasaha2506 — Mon, 08 Jan 2018 09:34:25 GMT

@klinek a bit off topic, how did you get the size of the search results, i could not find anything related in thew job inspector.