topic Re: Transaction very slow in Splunk Search

Transaction very slow

iberecamara — Tue, 29 Sep 2020 20:33:46 GMT

I have to calculate the response time from an application that depends on the response of another application. For that, I need to get the response times from both and calculate. The response time from Application1 and Application2 are in different places, but they share a couple of unique fields. I'm using a transaction for this right now, but its being very very slow, can someone help me refactor this? The search string is below, thanks!

index=myindex source=mysource sourcetype=mysourcetype host=myhost CLASS=PERFORMANCE_LOG (PERF_TYPE=App1PerformanceMetrics OR PERF_TYPE=App2PerformanceMetrics)

| transaction TXN_ID COR_ID connected=true
| search eventcount=2 status=SUCCESS
| eval responseTime = app1ResponseTime - app2ResponseTime

| timechart avg(responseTime)

Re: Transaction very slow

auraria1 — Fri, 20 Jul 2018 12:36:33 GMT

Can you move the search eventcount=2 status=SUCCESS into the main query or is that info dictated by the transaction?

That should help speed due to the fact you'll be transacting more logs, then performing a search on that.

Searching is a lot less resource intensive than transaction, so if you limit the amount of results prior it should help the transaction speed as well.

Does that make sense?

Re: Transaction very slow

iberecamara — Fri, 20 Jul 2018 12:59:48 GMT

I know that I should narrow as much as possible before doing the transaction, but unfortunately no. The eventcount is a direct effect of the transaction, and the status=SUCCESS is only present in App1PerformanceMetrics.

Re: Transaction very slow

auraria1 — Fri, 20 Jul 2018 13:05:11 GMT

gotcha, is there anything else that you can filter on that you're specifically looking for in the main search?

Also you can remove the parens around the PERF_TYPE OR search. Don't think that'll make a difference though.

Are these going to a specific index you can filter on? Generally doing index and sourcetype/host is faster.

Re: Transaction very slow

iberecamara — Fri, 20 Jul 2018 13:12:36 GMT

I'm looking for response times from App1PerformanceMetrics and App2PerformanceMetrics, so I can eval them later for the real response time I want (app1ResponseTime - app2ResponseTime). I know the parens don't help at all, I like to use them as a visual aid.
I'm already using an index, I updated the search here.

Re: Transaction very slow

renjith_nair — Fri, 20 Jul 2018 13:43:29 GMT

@iberecamara,

Please try this and lets know if it gives same result and where's the difference.

index=myindex source=mysource sourcetype=mysourcetype host=myhost CLASS=PERFORMANCE_LOG (PERF_TYPE=App1PerformanceMetrics OR PERF_TYPE=App2PerformanceMetrics) 
|stats values(app1ResponseTime) as app1ResponseTime,dc(PERF_TYPE) no_of_perf_type,latest(status) as status by TXN_ID,COR_ID
|where no_of_perf_type>1 AND status="SUCCESS"
|eval app1ResponseTime=mvindex(app1ResponseTime,0), app2ResponseTime=mvindex(app2ResponseTime,1)|eval responseTime = app1ResponseTime - app2ResponseTime 
|timechart avg(responseTime)

Re: Transaction very slow

iberecamara — Tue, 29 Sep 2020 20:33:51 GMT

@renjith.nair
It errors trying to run the search.

Error in 'stats' command: The aggregation specifier 'dc(PERF_TYPE) no_of_perf_type' is invalid. The aggregation specifier must be in func_name format.

Re: Transaction very slow

renjith_nair — Fri, 20 Jul 2018 14:23:33 GMT

sorry, missed as between them . Replace it by dc(PERF_TYPE) as no_of_perf_type

Re: Transaction very slow

iberecamara — Fri, 20 Jul 2018 14:31:47 GMT

The results are different from mine. My search is returning a couple matches and generating a line chart as intended, but this search returns "No results found."

Re: Transaction very slow

renjith_nair — Fri, 20 Jul 2018 14:35:48 GMT

do you get some results for this?

 index=myindex source=mysource sourcetype=mysourcetype host=myhost CLASS=PERFORMANCE_LOG (PERF_TYPE=App1PerformanceMetrics OR PERF_TYPE=App2PerformanceMetrics) 
 |stats values(app1ResponseTime) as app1ResponseTime,dc(PERF_TYPE) no_of_perf_type,latest(status) as status by TXN_ID,COR_ID

Re: Transaction very slow

iberecamara — Tue, 29 Sep 2020 20:33:57 GMT

Yes, I do. It gives me the app1ResponseTime, no_of_perf_type and status for the matches in TXN_ID and COR_ID

Re: Transaction very slow

somesoni2 — Tue, 29 Sep 2020 20:31:56 GMT

Is combination of TXN_ID COR_ID unique for all transactions or they may overlap (there multiple records with combination of TXN_ID COR_ID that may exist at different time)?

Re: Transaction very slow

iberecamara — Fri, 20 Jul 2018 15:39:10 GMT

They are unique, or at least should be

Re: Transaction very slow

somesoni2 — Fri, 20 Jul 2018 16:01:57 GMT

Give thisa try

index=myindex source=mysource sourcetype=mysourcetype host=myhost CLASS=PERFORMANCE_LOG (PERF_TYPE=App1PerformanceMetrics OR PERF_TYPE=App2PerformanceMetrics) 
| stats min(_time) as _time count as eventcount values(status) as status values(app1ResponseTime) as app1ResponseTime values(app2ResponseTime) as app2ResponseTime by TXN_ID COR_ID connected=true 
| search eventcount=2 status=SUCCESS 
| eval responseTime = app1ResponseTime - app2ResponseTime 
| timechart avg(responseTime)

Re: Transaction very slow

iberecamara — Fri, 20 Jul 2018 16:47:00 GMT

I just had to remove the connected=true, but it seems that it works. I'm in the middle of a meeting right now, but I'll make some tests when I can, and if it doesn't break for different time ranges, I'll accept this as the answer, thank you very much!

Re: Transaction very slow

renjith_nair — Tue, 29 Sep 2020 20:34:11 GMT

sorry missed one field,
index=myindex source=mysource sourcetype=mysourcetype host=myhost CLASS=PERFORMANCE_LOG (PERF_TYPE=App1PerformanceMetrics OR PERF_TYPE=App2PerformanceMetrics)
|stats values(app1ResponseTime) as app1ResponseTime,values(app2ResponseTime) as app2ResponseTime,dc(PERF_TYPE) no_of_perf_type,latest(status) as status by TXN_ID,COR_ID
|where no_of_perf_type>1 AND status="SUCCESS"
|eval responseTime = app1ResponseTime - app2ResponseTime
|timechart avg(responseTime)