topic Re: How to improve the performance of a search with multiple tstats command? in Splunk Search

How to improve the performance of a search with multiple tstats command?

AKG1_old1 — Wed, 29 May 2019 12:13:49 GMT

Hi,

My search query is having mutliple tstats commands. Also there are two independent search query seprated by appencols. Looking for suggestion to improve performance.

I tried using multisearch but its not working saying subsearch containing non-streaming command.

Query:

| tstats summariesonly=false max(LIVEBOOK.Elapsed) AS Elapsed FROM datamodel=MXTIMING_LIVEBOOK_V2 WHERE host=QCST_RSAT_V42 LIVEBOOK.Elapsed > 0 AND LIVEBOOK.Context+Command="*" AND 
    [| tstats count FROM datamodel=MXTIMING_LIVEBOOK_V2 WHERE host=QCST_RSAT_V42 LIVEBOOK.NPID="727614" GROUPBY source 
    | table source ] 
    GROUPBY LIVEBOOK.Context+Command LIVEBOOK.Time _time span=1s 
| rename LIVEBOOK.Context+Command as Context+Command 
| rename LIVEBOOK.Time as Time 
| timechart bins=2000 max(Elapsed) by Context+Command 
| appendcols 
    [| tstats summariesonly=false max(LIVEBOOK.Memory) AS VmPeak max(LIVEBOOK.VmHWM_V2) AS VmHWM max(LIVEBOOK.Malloc_V2) AS Malloc FROM datamodel=MXTIMING_LIVEBOOK_V2 WHERE host=QCST_RSAT_V42 AND LIVEBOOK.Elapsed > 0 AND 
        [| tstats count FROM datamodel=MXTIMING_LIVEBOOK_V2 WHERE host=QCST_RSAT_V42 LIVEBOOK.NPID="727614" GROUPBY source 
        | table source ]
        AND LIVEBOOK.Context+Command="*" GROUPBY LIVEBOOK.Context+Command LIVEBOOK.Time _time span=1s 
    | timechart bins=2000 max(VmPeak) as Mem_VmPeak ] 
| fields - OTHER 
| eval Mem_Malloc = if(Mem_Malloc==0,"",Mem_Malloc) 
| eval Mem_VmHWM = if(Mem_VmHWM==0,"",Mem_VmHWM) 
| eval Mem_VmPeak = if(Mem_VmPeak==0,"",Mem_VmPeak)

Re: How to improve the performance of a search with multiple tstats command?

richgalloway — Wed, 29 May 2019 13:06:03 GMT

Have you checked the job inspector to see where the query is spending its time?

Re: How to improve the performance of a search with multiple tstats command?

AKG1_old1 — Wed, 29 May 2019 13:16:44 GMT

attached screenshot of job inspector. most of the time is spent on appendcols, thatswhy I am looking option of running these two queries parallel as these are independent. mutlisearch is not working either.

Re: How to improve the performance of a search with multiple tstats command?

richgalloway — Wed, 29 May 2019 15:27:57 GMT

I think this can be done without appendcols. Try this query to see if it helps.

| tstats summariesonly=false max(LIVEBOOK.Elapsed) AS Elapsed max(LIVEBOOK.Memory) AS VmPeak max(LIVEBOOK.VmHWM_V2) AS VmHWM max(LIVEBOOK.Malloc_V2) AS Malloc FROM datamodel=MXTIMING_LIVEBOOK_V2 WHERE host=QCST_RSAT_V42 LIVEBOOK.Elapsed > 0 AND LIVEBOOK.Context+Command="*" AND 
     [| tstats count FROM datamodel=MXTIMING_LIVEBOOK_V2 WHERE host=QCST_RSAT_V42 LIVEBOOK.NPID="727614" GROUPBY source 
     | fields source
     | format ] 
     GROUPBY LIVEBOOK.Context+Command LIVEBOOK.Time _time span=1s 
| rename LIVEBOOK.* as * 
| bin bins=2000 _time 
| stats max(Elapsed) max(VmPeak) as Mem_VmPeak by Context+Command 
| fields - OTHER 
| eval Mem_VmPeak = if(Mem_VmPeak==0,"",Mem_VmPeak)

Re: How to improve the performance of a search with multiple tstats command?

AKG1_old1 — Wed, 29 May 2019 16:25:14 GMT

Thanks but this query producing different results.

Re: How to improve the performance of a search with multiple tstats command?

richgalloway — Wed, 29 May 2019 19:18:16 GMT

How is it different?

Re: How to improve the performance of a search with multiple tstats command?

AKG1_old1 — Thu, 30 May 2019 08:17:40 GMT

output produce only two column. oringinal query have mutiple column(for each context+command and one for memory).

Re: How to improve the performance of a search with multiple tstats command?

richgalloway — Thu, 30 May 2019 13:00:33 GMT

Modify the stats command to add the columns to want.

Re: How to improve the performance of a search with multiple tstats command?

AKG1_old1 — Fri, 31 May 2019 08:17:56 GMT

there is no fixed / same occurance of Context+Command. My reason for posting this question is if there is anyway to run muliple query in parallel using tstats.