JdbcCollector freeze Collectd when using Data Source Factories defined on opennms-datasources.xml instead of using their own connections.
Description
Acceptance / Success Criteria
Lucidchart Diagrams
Activity
Sandy Skipper June 3, 2020 at 2:40 PMEdited
No longer using C3P0.
Alejandro Galue September 19, 2011 at 1:59 PM
I guess the problem is something weird between C3P0 and MySQL.
What I saw on my tests is that the factory will apply the default settings for every data source defined on opennms-datasources.xml
So if for example the connection factory has maxPool=50, and I have 3 data sources, one for OpenNMS (the default database) and the other two for two different MySQL servers, all of those 3 data sources will have their own pool with 50 connections each. That means C3P0 will handle a 100 connection between the MySQL servers (aproximately).
The solution provided to the customer, i.e. avoid the usage of opennms-datasources.xml, is better because I'll have only a few connections at the same time against the MySQL servers. But if I'm using the collection pool, I'll always have a lots of connections even if the JdbcCollector is using only a very few of them.
I'm not an expert neither in C3P0 nor in MySQL, but my suggestion for this problem is to define custom properties for the pool on each data source and use the main properties as defaults if the custom properties don't exist.
Por ejemplo:
<datasource-configuration>
<connection-pool factory="org.opennms.netmgt.config.C3P0ConnectionFactory"
idleTimeout="600"
loginTimeout="3"
minPool="10"
maxPool="50"
maxSize="500" />
<jdbc-data-source name="opennms"
database-name="opennms_1991"
class-name="org.postgresql.Driver"
url="jdbc:postgresql://localhost:5432/opennms_1991"
user-name="opennms"
password="opennms" />
<jdbc-data-source name="mysql-db1"
database-name="test_database"
class-name="com.mysql.jdbc.Driver"
url="jdbc:mysql://192.168.0.8:3306/test_database"
user-name="opennms" password="secret" >
<property name="minPool" value="2" />
<property name="maxPool" value="5" />
<property name="maxSize" value="10" />
</jdbc-data-source>
<jdbc-data-source name="mysql-db2"
database-name="test_database"
class-name="com.mysql.jdbc.Driver"
url="jdbc:mysql://192.168.0.9:3306/test_database"
user-name="opennms" password="secret">
<property name="minPool" value="4" />
<property name="maxPool" value="4" />
<property name="maxSize" value="10" />
</jdbc-data-source>
</datasource-configuration>The above sample suggest that only the opennms data source will use the default settings (i.e., minPool=10, maxPool=50, maxSize=500), but mysqn-db1 and mysql-db2 will replace some pool parameters with their own settings.
Make sense?
Details
Assignee
UnassignedUnassignedReporter
Alejandro GalueAlejandro GalueLabels
Components
Priority
Critical
Details
Details
Assignee
Reporter
Labels
Components
Priority
CriticalPagerDuty
PagerDuty Incident
PagerDuty
PagerDuty Incident
PagerDuty
This issue is related with the support Ticket 616.
Currently there are two ways to configure the JDBC Collector:
1) Declare the external data-sources inside opennms-datasources.xml and let the DB Pool Manager manages the connections.
2) Declare the database connection directly on the service related with the JDBC Collector on collectd-configuration.xml
After a few hours, Collectd stop working if you use the first method.
I could reproduce the problem locally.
Here is the current state of the collector thread after collectd is frozen:
"CollectdScheduler-50 Pool-fiber0" prio=5 tid=101fe9000 nid=0x10d627000 in Object.wait() [10d626000] java.lang.Thread.State: WAITING (on object monitor) at java.lang.Object.wait(Native Method) at com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable(BasicResourcePool.java:1315) at com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(BasicResourcePool.java:557) - locked <7efe922b8> (a com.mchange.v2.resourcepool.BasicResourcePool) at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:477) at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:525) at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:128) at org.opennms.netmgt.config.C3P0ConnectionFactory.getConnection(C3P0ConnectionFactory.java:230) at org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy$LazyConnectionInvocationHandler.getTargetConnection(LazyConnectionDataSourceProxy.java:393) at org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy$LazyConnectionInvocationHandler.invoke(LazyConnectionDataSourceProxy.java:366) at $Proxy38.getMetaData(Unknown Source) at org.opennms.netmgt.collectd.JdbcCollector.isGroupAvailable(JdbcCollector.java:316) at org.opennms.netmgt.collectd.JdbcCollector.collect(JdbcCollector.java:216) at org.opennms.netmgt.collectd.CollectionSpecification.collect(CollectionSpecification.java:277) at org.opennms.netmgt.collectd.CollectableService.doCollection(CollectableService.java:382) at org.opennms.netmgt.collectd.CollectableService.run(CollectableService.java:316) at org.opennms.netmgt.scheduler.LegacyScheduler$1.run(LegacyScheduler.java:295) at org.opennms.core.concurrent.RunnableConsumerThreadPool$FiberThreadImpl.run(RunnableConsumerThreadPool.java:427) at java.lang.Thread.run(Thread.java:680)Here's the details about the configuration which does not work:
a) Declare a data-source on opennms-datasources.xml:
<jdbc-data-source name="mysql-test" database-name="test_database" class-name="com.mysql.jdbc.Driver" url="jdbc:mysql://192.168.0.8:3306/test_database" user-name="opennms" password="secret" />The MySQL Server is running on 192.168.0.8 and OpenNMS is running on other server (192.168.0.5)
b) Configure the JDBC Collection using the above data-source:
<package name="test-mysql-databases"> <filter>IPADDR IPLIKE 192.168.0.8</filter> <service name="MySQL_test interval="30000" user-defined="false" status="on"> <parameter key="collection" value="mysql-test-collection"/> <parameter key="thresholding-enabled" value="true"/> <parameter key="data-source" value="mysql-test"/> </service> </package>c) Declare some queries inside jdbc-datacollection-config.xml (the details of this are not relevant)
d) In order to reproduce the problem quickly, I've configured the poll interval to 30 seconds (remember to update jdbc-datacollection-config.xml)
e) Provision a node with the service MySQL_test.
After a few minutes (or hours if you let the default collection interval to 5 minutes), Collectd will freeze, and the JRBs will not be updated.
The workaround:
a) Remove or comment the data-sources to external databases from opennms-datasources.xml
b) Reconfigure the package from collectd-configuration.xml to use their own connection (remember to remove the parameter data-source):
<package name="test-mysql-databases"> <filter>IPADDR IPLIKE 192.168.0.8</filter> <service name="MySQL_test interval="30000" user-defined="false" status="on"> <parameter key="collection" value="mysql-test-collection"/> <parameter key="thresholding-enabled" value="true"/> <parameter key="driver" value="com.mysql.jdbc.Driver"/> <parameter key="user" value="opennms"/> <parameter key="password" value="secret"/> <parameter key="url" value="jdbc:mysql://OPENNMS_JDBC_HOSTNAME:3306/test_database"/> </service> </package>c) Restart OpenNMS.