UPDATE! The community will be go into read-only on April 19, 8am Pacific in preparation for migration on April 21. Read more.
UPDATE! The community will be go into read-only on April 19, 8am Pacific in preparation for migration on April 21.Read more.
Commodore
Commodore
2559 views

SmartConnector for Oracle DB connection persistence / multiple idle connection issue

Hi,

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,

Andras

Labels (2)
0 Likes
12 Replies
Fleet Admiral Fleet Admiral
Fleet Admiral

Check agent.properties for:

agents[0].dbcpmaxconn=5

Try modifying the value to have a single connection.

0 Likes
Commodore
Commodore

Thanks Shaun,

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

Thanks,

Andras

0 Likes
Fleet Admiral
Fleet Admiral

Hello Andras,

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:

initretrysleeptimeIf/when database connection fails, this parameter controls the time the SmartConnector will wait before trying to reconnect.
jdbcquerytimeoutAn internal parameter which controls the SQL query timeout. It is disabled (with a value of -1) by default.
jdbctimeoutControls the timeout period of JDBC connection.
loopingenabledUsed as a performance testing utility, set to false by default.
It has no impact on the behavior of the agent.
persistenceintervalThis 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.
preservestateIf set to true, The SmartConnector should keep track of the last event processed.
preservedstatecountIf preservestate is set to true, this is the count of events to process before writing the preserve state.
preservedstateintervalIf preservestate is set to true, time interval in ms before writing the preserve state.
useconnectionpoolIf 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:

dbcpcachestatementsOption to enable/disable caching of statements.
dbcpcheckouttimeoutMaximum number of seconds a Thread can checkout a connection before it is closed and returned to the pool.
dbcpidletimeoutMaximum number of seconds a connection can remain idle before it is closed.
dbcpmaxcheckoutNumber of times a connection may be checked out before it is closed.
dbcpmaxconnMaximum number of connections to open.
dbcpreapHow many seconds to wait between reaping connections in the pool.
dbcprowprefetchUse this value for row pre-fetch.

Best regards,

Marijo Mandic

0 Likes
Commodore
Commodore

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.

Thanks,

Andras

Fleet Admiral
Fleet Admiral

You are welcome.

It is not from classical documentation, like PDFs for each SmartConnector, but from one knowledge base article

Document ID : KM1272123

Regards,

Marijo

0 Likes
Commodore
Commodore

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 ?

Thanks,

Andras

0 Likes
Cadet 1st Class Cadet 1st Class
Cadet 1st Class

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

Best regards,

Sergey

0 Likes
Cadet 1st Class Cadet 1st Class
Cadet 1st Class

Hello Marijo!

Thank you for the direct link - the document was very helpful.

Best regards,

Sergey

0 Likes
Cadet 1st Class
Cadet 1st Class

Hi Adinnyes,

Were you able to resolve this problem?

0 Likes
Captain
Captain

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?

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.