Have you noticed any search performance issue when running dbxquery query? When I run a simple DB query like this
| dbxquery query="SELECT%201%20FROM%20DUAL" connection="AAA"
It takes 25 seconds to return the result. It is pointing to Oracle database and when I use any other client and run the same query it takes less than a second.
When I check the "Health" page and transaction, the DURATION is reported as 0.1 second which looks correct and the real time spent on database. But still it takes 25 seconds to get the query result.
Even enter a query with wrong SQL syntax and it takes 25 seconds to return the error:
| dbxquery query="YYYYYY" connection="AAA"
I want to post some of my findings which could cut your dbxquery time in half.
1. Keep an eye out for the number columns you are returning
I tested this against a table with 227 columns returning 5001 rows
all 227 rows took 32-34 seconds
1 column took 13- seconds
2. @health.logger attributes slow response times
Have a look at def execute_query in dbquery.py. I commented out the attribute #@health.Logger("dbquery:execute_query", health.DB)
This took the same query as above down from 13 to 6-8 seconds
If you insist on downvoting could you then provide an actual solution to the problem than telling everyone that it must be their environment that is causing the issue. That is a very anti-customer response to a very serious issue. I can tell you that I'm seeing similar issues as these other users and that all I have to do is use DB Connect v1 to get a significantly better response time. In DB Connect v2/3 (dbxquery command) the report takes 600+ seconds but if I run the same exact SQL in DB Connect v1 (dbquery command) it takes 10 to 30 seconds. These are running on the same 6.5.3 instance of Splunk Enterprise.
There isn't. Per the comments on your question, this sounds like an environmental issue. We'll be happy to take a bug ticket if there is something that we should alter to make it work better.
One thing to note is that when you want to just get a table into Splunk, which is what it looks like you're doing with that SQL, indexing it with a rising column to prevent dupes is probably the fastest and easiest solution.
Has there been any progress made here? We're having the same issues on our system, and would really like to finish our upgrade from db connect 1 as soon as possible, as many of our tasks rely on database queries.
We're facing this same situation. A simple query basically doing just a "select 1" is taking 8 seconds (on rare occasions it will return in 4). Our more complex query will also take 8 seconds. When running the real query via MS Sql Server Studio, the query itself returns in sub-second time. The query is pretty basic --- selecting maybe a half dozen or so fields from the join of a few tables across user ids. The tables themselves are not very large --- and as evidenced by the sql server took, the query itself runs pretty quickly. I've inspected the splunk job, and dbquery is listed as taking ... you guessed it ... roughly 8 seconds. No further detail to indicate where the holdup is.
I, too, doubt this is some environmental factor. We looked into connection pooling, and it does look like a pool is configured and is being used, so it doesn't seem to be from connecting/disconnecting from the database.
Thoughts? Ideas? Currently our simple dashboard with 3 of these queries takes what seems to a user to be a very long time.
My guess is that your search jobs are taking several seconds to start up. This might be because of concurrency issues, i/o issues, bundle replication issues, and so on. It is very tough to say based on the information provided.
It's not a search job issue. I can confirm in testing today that dbxquery is MANY times slower than the old dbquery from DBConnect1. Not sure what the cause of this huge discrepancy is, but it's going to make migrating to DBConnect2 from 1 basically impossible for us unless performance can get up to par.
People are used to the methodology and manner in which DBConnect1 operated (allowing you to display results without indexing the data... and thus counting against your license). It appears that, at least in order to display results in a expedient manner, Splunk is now making database inputs/indexing a requirement with DBConnect2. Really hope this is going to change as development continues, at least getting it back to a performance equal (or only SLIGHTLY slower) than DBConnect1. Asking people to use more of their incredibly valuable license volume for something that previously had no license impact it an unfair trade-off.
Hey there @tmeader, hope you are well.
I haven't been able to reproduce the slow query issue. In our test environments, simple dbxquery searches take 2-3 seconds. I do know that search/search commands on Windows is slightly slower, but this is out of our control. We'll certainly take another look at performance to identify any bottlenecks we can find.
Have you opened a support case? Have you tried running with debug logging enabled? Which driver are you using? What version of Oracle JRE? Are you using connection pooling with your driver?
In DB Connect 2, you can preview data at rest in a relational database using search without indexing using the Preview user interface. You can also run dbxquery as a stand-alone search command. Can you please let me know what requirements you have that we are not fulfilling with the UI and the search command?
@araitz, Thanks for the reply.
Since we're currently integrating the panels into a custom app, the Preview interface in the DBConnect2 app unfortunately wouldn't serve our purposes, so to replicate the functionality of "dbquery" directly via DBConnect2 "dbxquery" is the closest analogue.
We aren't actually using Oracle in this case: our queries are being run against an MSSQL DB (2005 at the moment, to be upgraded to 2008 relatively soon), using Oracle JRE 8u60 and the latest version of the MS JDBC driver as recommended in the DBConnect2 setup docs. In fact, we're using the same JRE and JDBC driver with DBConnect1 on the same system (which is performing drastically faster on queries).
The one modification we've had to make (due to the way that we built our tables/charts) is that the max_rows limit has been tweaked in DBConnect2 as described here: https://answers.splunk.com/answers/233222/splunk-db-connect-2-dbxquery-only-returns-1001-row.html to allow us to return the number of rows we needed. The 5,000 row limit was causing our charts to render improperly (and no such limit existed on "dbquery").
However, the speed difference exists regardless of the modification. The same chart, when rendered from 50,000+ rows returned via DBConnect1, is still rendering faster than it would with a max_rows limit of 5,000 rows in DBConnect2. Bumping the limit up so that all the rows are returned via DBConnect2 only exacerbates the situation. The query just takes much longer to return data... there's no way around it.
@tmeader - as I mentioned we'll look in to any bottlenecks that we can find in the search command and get back with you.
With regard to your use case, thanks for sharing the details. To date, we have been focused on making the connection configuration, db input, output, and lookup use cases easier for Splunk administrators. That is where we received most of our feedback when talking to customers., and we hope that we have been somewhat successful in improving the overall ease of use of the current product versus the previous one.
When we plan future versions of the product, we will be sure to keep your use case and feedback in mind.
I can also confirm that using database search and dbxquery without indexing doesn't count against license.
But we still encounter slowness no matter if query is simple or complicated, both on Linux and Windows environments.
Good to know it doesn't happen only with Oracle and Oracle JDBC. As previously mentioned it is not OS related neither. I experienced slowness both on Windows and Linux.
I got a little bit performance improvement by disabling and commenting out the
@ health.Logger("dbquery:execute_query", health.DB) decorator
Thanks for looking into this. I doubt if is an environment issue. I also tried it on Linux. It was a bit faster but still took 13 seconds for SELECT 1 FROM DUAL. I tried different versions of Oracle JDBC drivers and all showed same result.
It has nothing to do with SQL, indexing, etc... As I mentioned the simplest query (SELECT 1 FROM DUAL) and relatively more complicated queries all have almost same elapsed time. Even a query with wrong syntax. I suspect it is related to connection management. It is easy to reproduce it with any Oracle query.
I was planing to use dbxquery, run a database query and show it real time as a report in my dashboard. It works, but very slow.
Seeing the same issues with dbx2 and a Postgres database. With dbx2 and RPC debug on and looking at the network traffic it appears the RPC server isn't even sending the query to the database server until 10s+ after the dbxquery command has been issued; once the query is sent the search returns very quickly. I've just opened a ticket with support so I'll see what they say.
Thanks for the tip.
Out of 23.526 seconds, 22.51 seconds is spent by "command.dbxquery". The other components are not significant.
Another evidence in logs that dbxquery itself takes long time:
06-12-2015 09:41:49.065 INFO DispatchThread - Disk quota = 0
06-12-2015 09:42:11.593 INFO script - Invoked script dbxquery with 629 input bytes (0 events). Returned 96 output bytes in 22514 ms.
06-12-2015 09:42:11.781 INFO UserManager - Unwound user context: NULL -> NULL
Sounds like the bottleneck is probably on the Splunk server, not in the database. What is the resource usage (CPU, memory, etc.) like on the Splunk server while the query is running? Is there anything else running on the Splunk server (like McAfee) that might interfere with DBX's Java processing?