Author: [log in to unmask]
Date: Tue Mar 29 20:47:07 2016
New Revision: 4336
Log:
Magic incantations that will actually close idle connections when using DataSource (on a branch for now pending more testing).
Modified:
java/branches/jeremy-dev2/conditions/src/main/java/org/hps/conditions/database/DatabaseConditionsManager.java
java/branches/jeremy-dev2/conditions/src/main/java/org/hps/conditions/database/DatabaseUtilities.java
java/branches/jeremy-dev2/run-database/src/main/java/org/hps/run/database/RunManager.java
Modified: java/branches/jeremy-dev2/conditions/src/main/java/org/hps/conditions/database/DatabaseConditionsManager.java
=============================================================================
--- java/branches/jeremy-dev2/conditions/src/main/java/org/hps/conditions/database/DatabaseConditionsManager.java (original)
+++ java/branches/jeremy-dev2/conditions/src/main/java/org/hps/conditions/database/DatabaseConditionsManager.java Tue Mar 29 20:47:07 2016
@@ -116,6 +116,11 @@
* Data source initialized with default settings.
*/
private DataSource dataSource;
+
+ /**
+ * Currently active database connection.
+ */
+ private Connection connection;
/**
* True to cache all known conditions sets (from keys) during initialization.
@@ -202,6 +207,7 @@
* @return the <code>DataSource</code> for connecting to the conditions database
*/
private static DataSource createDefaultDataSource() {
+ LOGGER.info("creating default data source");
return createDataSource(DEFAULT_CONNECTION_URI);
}
@@ -227,25 +233,34 @@
}
if (properties.containsKey("user")) {
user = properties.getProperty("user");
+ LOGGER.info("user prop = " + user);
}
if (properties.containsKey("password")) {
password = properties.getProperty("password");
+ LOGGER.info("password prop = " + password);
}
- if (properties.containsKey("host")) {
- host = properties.getProperty("host");
+ if (properties.containsKey("hostname")) {
+ host = properties.getProperty("hostname");
+ LOGGER.info("host prop = " + host);
}
if (properties.containsKey("port")) {
port = Integer.parseInt(properties.getProperty("port"));
+ LOGGER.info("port prop = " + port);
}
if (properties.containsKey("database")) {
database = properties.getProperty("database");
+ LOGGER.info("database prop = " + database);
}
-
- return "jdbc:mysql://" + host + ":" + port + "/"
+
+ String connectionUri = "jdbc:mysql://" + host + ":" + port + "/"
+ database + "?"
- + "max-connections=" + DEFAULT_MAX_CONNECTIONS + "&"
+ "user=" + user + "&"
+ "password=" + password;
+
+ LOGGER.info("returning db connection URL " + connectionUri);
+
+ // + "max-connections=" + DEFAULT_MAX_CONNECTIONS + "&"
+ return connectionUri;
}
/**
@@ -397,7 +412,7 @@
int rowCount = 0;
Statement statement = null;
ResultSet resultSet = null;
- try (Connection connection = dataSource.getConnection()) {
+ try (Connection connection = getConnection()) {
statement = connection.createStatement();
resultSet = statement.executeQuery(sql);
resultSet.last();
@@ -490,7 +505,7 @@
final Set<String> tags = new LinkedHashSet<String>();
Statement statement = null;
ResultSet resultSet = null;
- try (Connection connection = dataSource.getConnection()) {
+ try (Connection connection = getConnection()) {
statement = connection.createStatement();
resultSet = statement.executeQuery("select distinct(tag) from conditions_tags where tag is not null order by tag");
while (resultSet.next()) {
@@ -530,7 +545,7 @@
PreparedStatement statement = null;
ResultSet resultSet = null;
int collectionId = -1;
- try (Connection connection = dataSource.getConnection()) {
+ try (Connection connection = getConnection()) {
statement = connection.prepareStatement(
"INSERT INTO collections (table_name, log, description, created) VALUES (?, ?, ?, NOW())",
Statement.RETURN_GENERATED_KEYS);
@@ -565,7 +580,7 @@
*/
public ConditionsRecordCollection getConditionsRecords() {
- try (Connection connection = dataSource.getConnection()) {
+ try (Connection connection = getConnection()) {
if (this.run == -1 || this.detectorName == null) {
throw new IllegalStateException("Conditions system is not initialized.");
@@ -622,11 +637,18 @@
* @return the JDBC connection
*/
public Connection getConnection() {
+ LOGGER.info("getting db connection");
try {
- return dataSource.getConnection();
+ if (this.connection == null || this.connection.isClosed()) {
+ LOGGER.info("creating new db connection");
+ connection = dataSource.getConnection();
+ } else {
+ LOGGER.info("using existing connection");
+ }
} catch (SQLException e) {
throw new RuntimeException(e);
}
+ return connection;
}
/**
Modified: java/branches/jeremy-dev2/conditions/src/main/java/org/hps/conditions/database/DatabaseUtilities.java
=============================================================================
--- java/branches/jeremy-dev2/conditions/src/main/java/org/hps/conditions/database/DatabaseUtilities.java (original)
+++ java/branches/jeremy-dev2/conditions/src/main/java/org/hps/conditions/database/DatabaseUtilities.java Tue Mar 29 20:47:07 2016
@@ -5,8 +5,6 @@
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Logger;
-
-import javax.sql.DataSource;
import org.apache.commons.dbcp2.BasicDataSource;
@@ -50,16 +48,22 @@
}
/**
- * Create a pooled data source from a connection URI.
+ * Create a data source from a connection URI and apply properties so idle connections are eventually closed.
+ *
* @param uri the connection URI
* @return the pooled data source
*/
- static DataSource createDataSource(String uri) {
+ public static BasicDataSource createDataSource(String uri) {
BasicDataSource dataSource = new BasicDataSource();
dataSource.setUrl(uri);
- LOGGER.config("created new data source from URI: " + uri);
- //dataSource.setInitialSize(1);
- //dataSource.setMaxTotal(1);
+ dataSource.setMaxTotal(1); /* allow only 1 connection at a time */
+ dataSource.setMaxConnLifetimeMillis(120000); /* 2 minute connection liftime */
+ dataSource.setMinEvictableIdleTimeMillis(10000); /* evict after 10 seconds in idle state */
+ dataSource.setMinIdle(0); /* zero min connections so no idle connections will hang around */
+ dataSource.setRemoveAbandonedOnBorrow(true); /* remove abandoned when getting a connection */
+ dataSource.setRemoveAbandonedOnMaintenance(true); /* remove abandoned when running maintenance thread */
+ dataSource.setRemoveAbandonedTimeout(5); /* remove abandoned connections after 5 seconds in idle state */
+ dataSource.setTimeBetweenEvictionRunsMillis(10000); /* run eviction thread every 10 seconds */
return dataSource;
}
@@ -67,6 +71,6 @@
* Do not allow instantiation.
*/
private DatabaseUtilities() {
+ throw new RuntimeException("This class should not be instantiated.");
}
-
}
Modified: java/branches/jeremy-dev2/run-database/src/main/java/org/hps/run/database/RunManager.java
=============================================================================
--- java/branches/jeremy-dev2/run-database/src/main/java/org/hps/run/database/RunManager.java (original)
+++ java/branches/jeremy-dev2/run-database/src/main/java/org/hps/run/database/RunManager.java Tue Mar 29 20:47:07 2016
@@ -7,7 +7,6 @@
import javax.sql.DataSource;
-import org.apache.commons.dbcp2.BasicDataSource;
import org.hps.record.daqconfig.DAQConfig;
import org.hps.record.epics.EpicsData;
import org.hps.record.scalers.ScalerData;
@@ -16,6 +15,8 @@
import org.lcsim.conditions.ConditionsEvent;
import org.lcsim.conditions.ConditionsListener;
+import org.hps.conditions.database.DatabaseUtilities;
+
/**
* Manages access to the run database.
*
@@ -27,7 +28,7 @@
private static final String DATABASE_PROPERTY = "org.hps.rundb.connection.database";
private static final String USER_PROPERTY = "org.hps.rundb.connection.user";
private static final String PASSWORD_PROPERTY = "org.hps.rundb.connection.password";
- private static final String HOST_PROPERTY = "org.hps.rundb.connection.host";
+ private static final String HOST_PROPERTY = "org.hps.rundb.connection.hostname";
private static final String PORT_PROPERTY = "org.hps.rundb.connection.port";
/**
@@ -44,7 +45,12 @@
* Initialize the logger.
*/
private static final Logger LOGGER = Logger.getLogger(RunManager.class.getPackage().getName());
-
+
+ /**
+ * Currently active database connection.
+ */
+ private Connection connection = null;
+
/**
* Get the global instance of the {@link RunManager}.
* @return the global instance of the {@link RunManager}
@@ -55,14 +61,27 @@
}
return INSTANCE;
}
+
+ /**
+ * Get a database connection.
+ * @return the database connection
+ */
+ private Connection getConnection() {
+ try {
+ if (connection == null || connection.isClosed()) {
+ connection = dataSource.getConnection();
+ }
+ } catch (SQLException e) {
+ throw new RuntimeException(e);
+ }
+ return connection;
+ }
/**
* Create the <code>DataSource</code> to connect to the run database.
* @return the <code>DataSource</code> for connecting to the run database
*/
private static DataSource createDataSource() {
-
- BasicDataSource dataSource = new BasicDataSource();
// Default settings.
String user = "hpsuser";
@@ -88,29 +107,9 @@
database = System.getProperty(DATABASE_PROPERTY);
}
- String uri = "jdbc:mysql://" + host + ":" + port + "/" + database;
- dataSource.setUsername(user);
- dataSource.setPassword(password);
- dataSource.setUrl(uri);
-
- //dataSource.setAutoReconnect(true);
- //try {
- // dataSource.setConnectTimeout(120000);
- // dataSource.setLoginTimeout(120);
- //} catch (SQLException e) {
- // throw new RuntimeException(e);
- //}
-
- //StringBuffer sb = new StringBuffer();
- //sb.append("user = " + user + '\n');
- //sb.append("password = " + password + '\n');
- //sb.append("host = " + host + '\n');
- //sb.append("port = " + port + '\n');
- //sb.append("database = " + database + '\n');
-
- LOGGER.config("Created new data source with URI: " + uri);
-
- return dataSource;
+ String uri = "jdbc:mysql://" + host + ":" + port + "/" + database + "?" + "user=" + user + "&" + "password=" + password;
+ LOGGER.config("run db URI " + uri);
+ return DatabaseUtilities.createDataSource(uri);
}
/**
|
