SmartConnector for Oracle DB connection persistence / multiple idle connection issue
After we've upgraded the SmartConnector to version 7.1.2, Oracle DBAs reported that there are 5 concurrent connections per connector from which one is active and four seems to be idle. This is making performance impact on DB side.
Based on the information we received from support, from connector version 7.1.2 :
"Arcsight leaves the optimizations and the management of the connection pool/sessions mostly to Apache Tomcat and does not try to control it too much. There is no single parameter that can control closing inactive or sniped session. However they can be controlled programmatically (through api) by controlling multiple parameters/apis with questionable/little benefit."
Did anyone face this issue and is there any possibility for having only one active DB session instead of several ?
Thanks and regards,
I will check this.
However if there is a parameter for this, I don't understand the statement I copied before about ArcSight is not managing the connections..
More details about Database SmartConnector Parameters (so you can see what each setting does and test):
See below for details on the use of each parameter:
initretrysleeptime If/when database connection fails, this parameter controls the time the SmartConnector will wait before trying to reconnect. jdbcquerytimeout An internal parameter which controls the SQL query timeout. It is disabled (with a value of -1) by default. jdbctimeout Controls the timeout period of JDBC connection. loopingenabled Used as a performance testing utility, set to false by default.
It has no impact on the behavior of the agent.
persistenceinterval This is a Boolean flag, disabled by default, which tells the SmartConnector to store the last processed event or byte offset in the log files. If enabled, the SmartConnector will continue where it left off. If it is disabled, the SmartConnector starts with the first log. preservestate If set to true, The SmartConnector should keep track of the last event processed. preservedstatecount If preservestate is set to true, this is the count of events to process before writing the preserve state. preservedstateinterval If preservestate is set to true, time interval in ms before writing the preserve state. useconnectionpool If false, a connection will be opened every time a query is made on the database.
Use of this parameter makes the SmartConnector process more resource intensive.
Parameters below are used for JDBC Connection Pools setting:
dbcpcachestatements Option to enable/disable caching of statements. dbcpcheckouttimeout Maximum number of seconds a Thread can checkout a connection before it is closed and returned to the pool. dbcpidletimeout Maximum number of seconds a connection can remain idle before it is closed. dbcpmaxcheckout Number of times a connection may be checked out before it is closed. dbcpmaxconn Maximum number of connections to open. dbcpreap How many seconds to wait between reaping connections in the pool. dbcprowprefetch Use this value for row pre-fetch.
Thanks Marijo, very useful!
May I ask from which documentation have you copied this? I'd be curious about the same for all types of smartconnectors.
You are welcome.
It is not from classical documentation, like PDFs for each SmartConnector, but from one knowledge base article
Document ID : KM1272123
Hi Shaun and All,
Getting back to this. What if I set this parameter to 1 and it goes like that but the current connection cannot be reused for any reason ?
What is the recommendation here ? Should I go with 2 instead of 1 ?
Hello Marijo! Thanks for the info.
Could you plz share a link to this knowledge base article? Unfortunately I couldn't find it in the KB by the Document ID >.<
Hello , I did come across a similar scenario today. Referring to "What if I set this parameter to 1 and it goes like that but the current connection cannot be reused for any reason ?" - Can you let me know why do you want the current connection to be reused. Is this something related to the 'query frequency' that might occur before the previous query timesout?