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.
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 :-).
