All Apps and Add-ons

dbxlookup with MySQL (DB Connect app 3.8) huge delay / bad performance

flle
Path Finder

Hi,

we have a severe performance issue with dbxlookup in DB Connect App 3.8 for a MySQL DB.
dbxlookups in a Splunk Query take several minutes to return results. 
The strange thing is, that it only happens when using dbxlookup.

Using dbxquery is blazing fast (1.4 seconds to return 100K results) and also when configuring the lookup in the DB Connect app (where you run the actual SQL query that is to be used for the lookup) it is extremely fast.

Example of using dbxlookup in a search:

 

| makeresults
| eval page_id=510376245
| dbxlookup connection="myDB"
query="SELECT C.CONTENTID as content_id,C.TITLE as page_title, S.SPACEKEY as space_key,S.SPACENAME as space_name FROM CONTENT AS C LEFT JOIN SPACES AS S ON (C.SPACEID=S.SPACEID) ORDER BY C.CONTENTID DESC" "content_id" AS "page_id"

 

The search job inspector shows the long time the dbxlookup took.

dbxlookup_issue.jpg

The search log does not yield any helpful information:

 

03-31-2022 16:27:11.988 INFO  SearchParser [110743 StatusEnforcerThread] - PARSING: table  _time, page_id 
03-31-2022 16:27:11.988 INFO  SearchParser [110743 StatusEnforcerThread] - PARSING: head  20  
03-31-2022 16:27:11.988 INFO  SearchParser [110743 StatusEnforcerThread] - PARSING: dbxlookup  lookup="scwiki_db"
03-31-2022 16:27:11.988 INFO  ChunkedExternProcessor [110743 StatusEnforcerThread] - Running process: /opt/splunk/jdk1.8.0_111/bin/java -Dlogback.configurationFile\=../config/command_logback.xml -DDBX_COMMAND_LOG_LEVEL\=DEBUG -cp ../jars/dbxquery.jar com.splunk.dbx.command.DbxLookupCommand
03-31-2022 16:27:12.585 INFO  DispatchExecutor [110743 StatusEnforcerThread] - BEGIN OPEN: Processor=dbxlookup
03-31-2022 16:27:12.605 INFO  DispatchExecutor [110743 StatusEnforcerThread] - END OPEN: Processor=dbxlookup
03-31-2022 16:27:12.605 INFO  ChunkedExternProcessor [110743 StatusEnforcerThread] - Skipping custom search command since we are in preview mode: dbxlookup
03-31-2022 16:27:12.620 INFO  PreviewExecutor [110743 StatusEnforcerThread] - Finished preview generation in 0.663433841 seconds.
03-31-2022 16:27:13.143 INFO  DispatchExecutor [110818 phase_1] - END OPEN: Processor=table
03-31-2022 16:27:13.143 INFO  DispatchExecutor [110818 phase_1] - BEGIN OPEN: Processor=dbxlookup
03-31-2022 16:27:13.364 INFO  DispatchExecutor [110818 phase_1] - END OPEN: Processor=dbxlookup
03-31-2022 16:27:14.627 INFO  ReducePhaseExecutor [110743 StatusEnforcerThread] - ReducePhaseExecutor=1 action=PREVIEW

-> here the delay happens

03-31-2022 16:29:44.577 INFO  PreviewExecutor [110743 StatusEnforcerThread] - Stopping preview triggers since search almost finished
03-31-2022 16:29:44.580 INFO  DownloadRemoteDataTransaction [110818 phase_1] - Downloading logs from all remote event providers
03-31-2022 16:29:44.849 INFO  ReducePhaseExecutor [110818 phase_1] - Downloading all remote search.log files took 0.270 seconds
03-31-2022 16:29:44.850 INFO  DownloadRemoteDataTransaction [110818 phase_1] - Downloading logs from all remote event providers

 

 
So it must have something to do with the way dbxlookup works. Could be an java issue or an mysql driver issue, a combination of both or something completely different :-).
We are using the latest DB Connect MySQL Add-On.

I am grateful for any hints or tipps on how to troubleshoot this furter. Or an actual solution :-).

Labels (1)
Get Updates on the Splunk Community!

Splunk Forwarders and Forced Time Based Load Balancing

Splunk customers use universal forwarders to collect and send data to Splunk. A universal forwarder can send ...

NEW! Log Views in Splunk Observability Dashboards Gives Context From a Single Page

Today, Splunk Observability releases log views, a new feature for users to add their logs data from Splunk Log ...

Last Chance to Submit Your Paper For BSides Splunk - Deadline is August 12th!

Hello everyone! Don't wait to submit - The deadline is August 12th! We have truly missed the community so ...