Memory leak ??? org.h2.mvstore.cache.CacheLongKeyLIRS$Entry
88 views
Skip to first unread message
Mathieu Moloney
Nov 1, 2019, 1:16:22 PM11/1/19
to H2 Database
I am experiencing a deteriorating performance of the DB and my application is running into java.lang.OutOfMemoryError: Java heap space
Using JProfiler, I have identified that the application runs steadily for 16 hours or so and then we can see memory slowly increasing, as shown in the screenshot below.
Far and away the biggest consumer of memory in the JVM are references to org.h2.mvstore.cache.CacheLongKeyLIRS$Entry, approximately 50MB.
There is more memory dedicated to this cache than there is space used by the DB on the harddisk.
The DB is running on 28 different .mv.db files, using H2 v1.4.199 in embedded mode, for a total of 23 MB.
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<version>1.4.199</version>
</dependency>
The application is taking measurements of different inputs and inserting them into their respective DBs at a rate of about 2 per second (can be higher depending on the data).
Every minute, an SQL query samples the data and compresses this into a sample record comprising of the AVG, MIN, MAX and INST of each value measured on the device.
Data (raw measurements and samples) older than 2 minutes are deleted after the samples are written to CSV files.
I have included at the bottom of this message, an example of a DAO file which is managing the recording and sampling of data.
This DAO is an example of a DB with few columns, but all DAO follow the same logic and structure in terms of the queries being executed on the DB.
One difference being, in this example there is a single data table, wherein others there may be several data tables which are sampled usinga single INSERT INTO SELECT statement.
Can someone tell me if I can disable the cache of the KEYS ?
Is this behavior expected ?
Am I abusing the DB in some way ?
How can I debug and resolve this issue ?
EXAMPLE DAO :
public class JanitzaDao implements SampleDao {
@Override
public int sample(DataLogger logger, Instant from, Instant until) {
try (final Connection conn = bds.getConnection()) {
try (final PreparedStatement statement = conn.prepareStatement(
"INSERT INTO " + JANITZA_SAMPLE + " ("
+ "JANITZA_NO, STE_ID, STP, LOG_ID, "
+ "VOLTAGE_U13_MN, VOLTAGE_U13_MX, VOLTAGE_U13_AV, VOLTAGE_U13_IN, "
+ "VOLTAGE_U12_MN, VOLTAGE_U12_MX, VOLTAGE_U12_AV, VOLTAGE_U12_IN, "
+ "VOLTAGE_U23_MN, VOLTAGE_U23_MX, VOLTAGE_U23_AV, VOLTAGE_U23_IN, "
+ "CURRENT_L1_MN, CURRENT_L1_MX, CURRENT_L1_AV, CURRENT_L1_IN, "
+ "CURRENT_L2_MN, CURRENT_L2_MX, CURRENT_L2_AV, CURRENT_L2_IN, "
+ "CURRENT_L3_MN, CURRENT_L3_MX, CURRENT_L3_AV, CURRENT_L3_IN, "
+ "POWER_P_MN, POWER_P_MX, POWER_P_AV, POWER_P_IN, "
+ "POWER_Q_MN, POWER_Q_MX, POWER_Q_AV, POWER_Q_IN, "
+ "POWER_S_MN, POWER_S_MX, POWER_S_AV, POWER_S_IN, "
+ "FREQUENCY_MN, FREQUENCY_MX, FREQUENCY_AV, FREQUENCY_IN, "
+ "ENERGY_ACTIVE_CONSUMED_Ph1_IN, "
+ "ENERGY_ACTIVE_CONSUMED_Ph2_IN, "
+ "ENERGY_ACTIVE_CONSUMED_Ph3_IN, "
+ "ENERGY_ACTIVE_PRODUCED_Ph1_IN, "
+ "ENERGY_ACTIVE_PRODUCED_Ph2_IN, "
+ "ENERGY_ACTIVE_PRODUCED_Ph3_IN, "
+ "ENERGY_REACTIVE_INDUCTIVE_Ph1_IN, "
+ "ENERGY_REACTIVE_INDUCTIVE_Ph2_IN, "
+ "ENERGY_REACTIVE_INDUCTIVE_Ph3_IN, "
+ "ENERGY_REACTIVE_CAPACITIVE_Ph1_IN, "
+ "ENERGY_REACTIVE_CAPACITIVE_Ph2_IN, "
+ "ENERGY_REACTIVE_CAPACITIVE_Ph3_IN, "
+ "VOLTAGE_V1_MN, VOLTAGE_V1_MX, VOLTAGE_V1_AV, VOLTAGE_V1_IN, "
+ "VOLTAGE_V2_MN, VOLTAGE_V2_MX, VOLTAGE_V2_AV, VOLTAGE_V2_IN, "
+ "VOLTAGE_V3_MN, VOLTAGE_V3_MX, VOLTAGE_V3_AV, VOLTAGE_V3_IN, "
+ "POWER_P_L1_MN, POWER_P_L1_MX, POWER_P_L1_AV, POWER_P_L1_IN, "
+ "POWER_P_L2_MN, POWER_P_L2_MX, POWER_P_L2_AV, POWER_P_L2_IN, "
+ "POWER_P_L3_MN, POWER_P_L3_MX, POWER_P_L3_AV, POWER_P_L3_IN, "
+ "POWER_Q_L1_MN, POWER_Q_L1_MX, POWER_Q_L1_AV, POWER_Q_L1_IN, "
+ "POWER_Q_L2_MN, POWER_Q_L2_MX, POWER_Q_L2_AV, POWER_Q_L2_IN, "
+ "POWER_Q_L3_MN, POWER_Q_L3_MX, POWER_Q_L3_AV, POWER_Q_L3_IN, "
+ "COS_PHI_L1_MN, COS_PHI_L1_MX, COS_PHI_L1_AV, COS_PHI_L1_IN, "
+ "COS_PHI_L2_MN, COS_PHI_L2_MX, COS_PHI_L2_AV, COS_PHI_L2_IN, "
+ "COS_PHI_L3_MN, COS_PHI_L3_MX, COS_PHI_L3_AV, COS_PHI_L3_IN) "
+ "SELECT "
+ "aggMsr.JANITZA_NO, aggMsr.STE_ID, ?, ?, "
+ "aggMsr.VOLTAGE_U13_MN, aggMsr.VOLTAGE_U13_MX, aggMsr.VOLTAGE_U13_AV, curMsr.VOLTAGE_U13_IN, "
+ "aggMsr.VOLTAGE_U12_MN, aggMsr.VOLTAGE_U12_MX, aggMsr.VOLTAGE_U12_AV, curMsr.VOLTAGE_U12_IN, "
+ "aggMsr.VOLTAGE_U23_MN, aggMsr.VOLTAGE_U23_MX, aggMsr.VOLTAGE_U23_AV, curMsr.VOLTAGE_U23_IN, "
+ "aggMsr.CURRENT_L1_MN, aggMsr.CURRENT_L1_MX, aggMsr.CURRENT_L1_AV, curMsr.CURRENT_L1_IN, "
+ "aggMsr.CURRENT_L2_MN, aggMsr.CURRENT_L2_MX, aggMsr.CURRENT_L2_AV, curMsr.CURRENT_L2_IN, "
+ "aggMsr.CURRENT_L3_MN, aggMsr.CURRENT_L3_MX, aggMsr.CURRENT_L3_AV, curMsr.CURRENT_L3_IN, "
+ "aggMsr.POWER_P_MN, aggMsr.POWER_P_MX, aggMsr.POWER_P_AV, curMsr.POWER_P_IN, "
+ "aggMsr.POWER_Q_MN, aggMsr.POWER_Q_MX, aggMsr.POWER_Q_AV, curMsr.POWER_Q_IN, "
+ "aggMsr.POWER_S_MN, aggMsr.POWER_S_MX, aggMsr.POWER_S_AV, curMsr.POWER_S_IN, "
+ "aggMsr.FREQUENCY_MN, aggMsr.FREQUENCY_MX, aggMsr.FREQUENCY_AV, curMsr.FREQUENCY_IN, "
+ "curMsr.ENERGY_ACTIVE_CONSUMED_Ph1_IN, "
+ "curMsr.ENERGY_ACTIVE_CONSUMED_Ph2_IN, "
+ "curMsr.ENERGY_ACTIVE_CONSUMED_Ph3_IN, "
+ "curMsr.ENERGY_ACTIVE_PRODUCED_Ph1_IN, "
+ "curMsr.ENERGY_ACTIVE_PRODUCED_Ph2_IN, "
+ "curMsr.ENERGY_ACTIVE_PRODUCED_Ph3_IN, "
+ "curMsr.ENERGY_REACTIVE_INDUCTIVE_Ph1_IN, "
+ "curMsr.ENERGY_REACTIVE_INDUCTIVE_Ph2_IN, "
+ "curMsr.ENERGY_REACTIVE_INDUCTIVE_Ph3_IN, "
+ "curMsr.ENERGY_REACTIVE_CAPACITIVE_Ph1_IN, "
+ "curMsr.ENERGY_REACTIVE_CAPACITIVE_Ph2_IN, "
+ "curMsr.ENERGY_REACTIVE_CAPACITIVE_Ph3_IN, "
+ "aggMsr.VOLTAGE_V1_MN, aggMsr.VOLTAGE_V1_MX, aggMsr.VOLTAGE_V1_AV, curMsr.VOLTAGE_V1_IN, "
+ "aggMsr.VOLTAGE_V2_MN, aggMsr.VOLTAGE_V2_MX, aggMsr.VOLTAGE_V2_AV, curMsr.VOLTAGE_V2_IN, "
+ "aggMsr.VOLTAGE_V3_MN, aggMsr.VOLTAGE_V3_MX, aggMsr.VOLTAGE_V3_AV, curMsr.VOLTAGE_V3_IN, "
+ "aggMsr.POWER_P_L1_MN, aggMsr.POWER_P_L1_MX, aggMsr.POWER_P_L1_AV, curMsr.POWER_P_L1_IN, "
+ "aggMsr.POWER_P_L2_MN, aggMsr.POWER_P_L2_MX, aggMsr.POWER_P_L2_AV, curMsr.POWER_P_L2_IN, "
+ "aggMsr.POWER_P_L3_MN, aggMsr.POWER_P_L3_MX, aggMsr.POWER_P_L3_AV, curMsr.POWER_P_L3_IN, "
+ "aggMsr.POWER_Q_L1_MN, aggMsr.POWER_Q_L1_MX, aggMsr.POWER_Q_L1_AV, curMsr.POWER_Q_L1_IN, "
+ "aggMsr.POWER_Q_L2_MN, aggMsr.POWER_Q_L2_MX, aggMsr.POWER_Q_L2_AV, curMsr.POWER_Q_L2_IN, "
+ "aggMsr.POWER_Q_L3_MN, aggMsr.POWER_Q_L3_MX, aggMsr.POWER_Q_L3_AV, curMsr.POWER_Q_L3_IN, "
+ "aggMsr.COS_PHI_L1_MN, aggMsr.COS_PHI_L1_MX, aggMsr.COS_PHI_L1_AV, curMsr.COS_PHI_L1_IN, "
+ "aggMsr.COS_PHI_L2_MN, aggMsr.COS_PHI_L2_MX, aggMsr.COS_PHI_L2_AV, curMsr.COS_PHI_L2_IN, "
+ "aggMsr.COS_PHI_L3_MN, aggMsr.COS_PHI_L3_MX, aggMsr.COS_PHI_L3_AV, curMsr.COS_PHI_L3_IN "
+ "FROM (SELECT "
+ "JANITZA_NO, STE_ID, "
+ "MIN(VOLTAGE_U13) AS VOLTAGE_U13_MN, MAX(VOLTAGE_U13) AS VOLTAGE_U13_MX, AVG(VOLTAGE_U13) AS VOLTAGE_U13_AV, "
+ "MIN(VOLTAGE_U12) AS VOLTAGE_U12_MN, MAX(VOLTAGE_U12) AS VOLTAGE_U12_MX, AVG(VOLTAGE_U12) AS VOLTAGE_U12_AV, "
+ "MIN(VOLTAGE_U23) AS VOLTAGE_U23_MN, MAX(VOLTAGE_U23) AS VOLTAGE_U23_MX, AVG(VOLTAGE_U23) AS VOLTAGE_U23_AV, "
+ "MIN(CURRENT_L1) AS CURRENT_L1_MN, MAX(CURRENT_L1) AS CURRENT_L1_MX, AVG(CURRENT_L1) AS CURRENT_L1_AV, "
+ "MIN(CURRENT_L2) AS CURRENT_L2_MN, MAX(CURRENT_L2) AS CURRENT_L2_MX, AVG(CURRENT_L2) AS CURRENT_L2_AV, "
+ "MIN(CURRENT_L3) AS CURRENT_L3_MN, MAX(CURRENT_L3) AS CURRENT_L3_MX, AVG(CURRENT_L3) AS CURRENT_L3_AV, "
+ "MIN(POWER_P) AS POWER_P_MN, MAX(POWER_P) AS POWER_P_MX, AVG(POWER_P) AS POWER_P_AV, "
+ "MIN(POWER_Q) AS POWER_Q_MN, MAX(POWER_Q) AS POWER_Q_MX, AVG(POWER_Q) AS POWER_Q_AV, "
+ "MIN(POWER_S) AS POWER_S_MN, MAX(POWER_S) AS POWER_S_MX, AVG(POWER_S) AS POWER_S_AV, "
+ "MIN(FREQUENCY) AS FREQUENCY_MN, MAX(FREQUENCY) AS FREQUENCY_MX, AVG(FREQUENCY) AS FREQUENCY_AV, "
+ "MIN(VOLTAGE_V1) AS VOLTAGE_V1_MN, MAX(VOLTAGE_V1) AS VOLTAGE_V1_MX, AVG(VOLTAGE_V1) AS VOLTAGE_V1_AV, "
+ "MIN(VOLTAGE_V2) AS VOLTAGE_V2_MN, MAX(VOLTAGE_V2) AS VOLTAGE_V2_MX, AVG(VOLTAGE_V2) AS VOLTAGE_V2_AV, "
+ "MIN(VOLTAGE_V3) AS VOLTAGE_V3_MN, MAX(VOLTAGE_V3) AS VOLTAGE_V3_MX, AVG(VOLTAGE_V3) AS VOLTAGE_V3_AV, "
+ "MIN(POWER_P_L1) AS POWER_P_L1_MN, MAX(POWER_P_L1) AS POWER_P_L1_MX, AVG(POWER_P_L1) AS POWER_P_L1_AV, "
+ "MIN(POWER_P_L2) AS POWER_P_L2_MN, MAX(POWER_P_L2) AS POWER_P_L2_MX, AVG(POWER_P_L2) AS POWER_P_L2_AV, "
+ "MIN(POWER_P_L3) AS POWER_P_L3_MN, MAX(POWER_P_L3) AS POWER_P_L3_MX, AVG(POWER_P_L3) AS POWER_P_L3_AV, "
+ "MIN(POWER_Q_L1) AS POWER_Q_L1_MN, MAX(POWER_Q_L1) AS POWER_Q_L1_MX, AVG(POWER_Q_L1) AS POWER_Q_L1_AV, "
+ "MIN(POWER_Q_L2) AS POWER_Q_L2_MN, MAX(POWER_Q_L2) AS POWER_Q_L2_MX, AVG(POWER_Q_L2) AS POWER_Q_L2_AV, "
+ "MIN(POWER_Q_L3) AS POWER_Q_L3_MN, MAX(POWER_Q_L3) AS POWER_Q_L3_MX, AVG(POWER_Q_L3) AS POWER_Q_L3_AV, "
+ "MIN(COS_PHI_L1) AS COS_PHI_L1_MN, MAX(COS_PHI_L1) AS COS_PHI_L1_MX, AVG(COS_PHI_L1) AS COS_PHI_L1_AV, "
+ "MIN(COS_PHI_L2) AS COS_PHI_L2_MN, MAX(COS_PHI_L2) AS COS_PHI_L2_MX, AVG(COS_PHI_L2) AS COS_PHI_L2_AV, "
+ "MIN(COS_PHI_L3) AS COS_PHI_L3_MN, MAX(COS_PHI_L3) AS COS_PHI_L3_MX, AVG(COS_PHI_L3) AS COS_PHI_L3_AV "
+ "FROM " + JANITZA_MEASUREMENT + " "
+ "WHERE STP > ? AND STP <= ? "
+ "GROUP BY STE_ID, JANITZA_NO) AS aggMsr "
+ "LEFT JOIN ("
+ "SELECT "
+ "JANITZA_NO, "
+ "STE_ID, "
+ "VOLTAGE_U13 AS VOLTAGE_U13_IN, "
+ "VOLTAGE_U12 AS VOLTAGE_U12_IN, "
+ "VOLTAGE_U23 AS VOLTAGE_U23_IN, "
+ "CURRENT_L1 AS CURRENT_L1_IN, "
+ "CURRENT_L2 AS CURRENT_L2_IN, "
+ "CURRENT_L3 AS CURRENT_L3_IN, "
+ "POWER_P AS POWER_P_IN, "
+ "POWER_Q AS POWER_Q_IN, "
+ "POWER_S AS POWER_S_IN, "
+ "FREQUENCY AS FREQUENCY_IN, "
+ "ENERGY_ACTIVE_CONSUMED_Ph1 AS ENERGY_ACTIVE_CONSUMED_Ph1_IN, "
+ "ENERGY_ACTIVE_CONSUMED_Ph2 AS ENERGY_ACTIVE_CONSUMED_Ph2_IN, "
+ "ENERGY_ACTIVE_CONSUMED_Ph3 AS ENERGY_ACTIVE_CONSUMED_Ph3_IN, "
+ "ENERGY_ACTIVE_PRODUCED_Ph1 AS ENERGY_ACTIVE_PRODUCED_Ph1_IN, "
+ "ENERGY_ACTIVE_PRODUCED_Ph2 AS ENERGY_ACTIVE_PRODUCED_Ph2_IN, "
+ "ENERGY_ACTIVE_PRODUCED_Ph3 AS ENERGY_ACTIVE_PRODUCED_Ph3_IN, "
+ "ENERGY_REACTIVE_INDUCTIVE_Ph1 AS ENERGY_REACTIVE_INDUCTIVE_Ph1_IN, "
+ "ENERGY_REACTIVE_INDUCTIVE_Ph2 AS ENERGY_REACTIVE_INDUCTIVE_Ph2_IN, "
+ "ENERGY_REACTIVE_INDUCTIVE_Ph3 AS ENERGY_REACTIVE_INDUCTIVE_Ph3_IN, "
+ "ENERGY_REACTIVE_CAPACITIVE_Ph1 AS ENERGY_REACTIVE_CAPACITIVE_Ph1_IN, "
+ "ENERGY_REACTIVE_CAPACITIVE_Ph2 AS ENERGY_REACTIVE_CAPACITIVE_Ph2_IN, "
+ "ENERGY_REACTIVE_CAPACITIVE_Ph3 AS ENERGY_REACTIVE_CAPACITIVE_Ph3_IN, "
+ "VOLTAGE_V1 AS VOLTAGE_V1_IN, "
+ "VOLTAGE_V2 AS VOLTAGE_V2_IN, "
+ "VOLTAGE_V3 AS VOLTAGE_V3_IN, "
+ "POWER_P_L1 AS POWER_P_L1_IN, "
+ "POWER_P_L2 AS POWER_P_L2_IN, "
+ "POWER_P_L3 AS POWER_P_L3_IN, "
+ "POWER_Q_L1 AS POWER_Q_L1_IN, "
+ "POWER_Q_L2 AS POWER_Q_L2_IN, "
+ "POWER_Q_L3 AS POWER_Q_L3_IN, "
+ "COS_PHI_L1 AS COS_PHI_L1_IN, "
+ "COS_PHI_L2 AS COS_PHI_L2_IN, "
+ "COS_PHI_L3 AS COS_PHI_L3_IN "
+ "FROM " + JANITZA_MEASUREMENT + " i "
+ "WHERE STP = ("
+ "SELECT MAX(STP) "
+ "FROM " + JANITZA_MEASUREMENT + " "
+ "WHERE i.STE_ID = STE_ID AND i.JANITZA_NO = JANITZA_NO AND STP > ? AND STP <= ?)"
+ ") AS curMsr "
+ "ON (aggMsr.STE_ID = curMsr.STE_ID AND aggMsr.JANITZA_NO = curMsr.JANITZA_NO)")) {
int index = 1;
statement.setObject(index++, LocalDateTime.ofInstant(until, ZoneOffset.UTC));
statement.setLong(index++, logger.getId());
statement.setObject(index++, LocalDateTime.ofInstant(from, ZoneOffset.UTC));
statement.setObject(index++, LocalDateTime.ofInstant(until, ZoneOffset.UTC));
statement.setObject(index++, LocalDateTime.ofInstant(from, ZoneOffset.UTC));
statement.setObject(index++, LocalDateTime.ofInstant(until, ZoneOffset.UTC));
final int count = statement.executeUpdate();
conn.commit();
log.debug("Sampled {} janitza on LOG_ID '{}' between '{}' and '{}'", count, logger.getId(), from, until);
return count;
}
} catch (final SQLException ex) {
log.warn("janitza sample between {} and {} failed: {}", from, until, ex.getMessage(), ex);
return 0;
}
}
@Override
public Collection<JanitzaSample> getSamples(DataLogger logger, Instant from, Instant until) {
try (final Connection conn = bds.getConnection()) {
try (final PreparedStatement statement = conn.prepareStatement("SELECT * FROM " + JANITZA_SAMPLE + " WHERE LOG_ID = ? AND STP > ? AND STP <= ?")) {
statement.setLong(1, logger.getId());
statement.setObject(2, LocalDateTime.ofInstant(from, ZoneOffset.UTC));
statement.setObject(3, LocalDateTime.ofInstant(until, ZoneOffset.UTC));
try (final ResultSet rs = statement.executeQuery()) {
final Collection<JanitzaSample> samples = new ArrayList<>();
while (rs != null && rs.next()) {
final JanitzaSample sample = new JanitzaSample();
sample.setNumber(rs.getInt("JANITZA_NO"));
sample.setTimestamp(rs.getObject("STP", LocalDateTime.class).toInstant(ZoneOffset.UTC));
sample.getMinMeasurements().setVOLTAGE_L3_L1(rs.getDouble("VOLTAGE_U13_MN"));
sample.getMaxMeasurements().setVOLTAGE_L3_L1(rs.getDouble("VOLTAGE_U13_MX"));
sample.getAvgMeasurements().setVOLTAGE_L3_L1(rs.getDouble("VOLTAGE_U13_AV"));
sample.getInstMeasurements().setVOLTAGE_L3_L1(rs.getDouble("VOLTAGE_U13_IN"));
sample.getMinMeasurements().setVOLTAGE_L1_L2(rs.getDouble("VOLTAGE_U12_MN"));
sample.getMaxMeasurements().setVOLTAGE_L1_L2(rs.getDouble("VOLTAGE_U12_MX"));
sample.getAvgMeasurements().setVOLTAGE_L1_L2(rs.getDouble("VOLTAGE_U12_AV"));
sample.getInstMeasurements().setVOLTAGE_L1_L2(rs.getDouble("VOLTAGE_U12_IN"));
sample.getMinMeasurements().setVOLTAGE_L2_L3(rs.getDouble("VOLTAGE_U23_MN"));
sample.getMaxMeasurements().setVOLTAGE_L2_L3(rs.getDouble("VOLTAGE_U23_MX"));
sample.getAvgMeasurements().setVOLTAGE_L2_L3(rs.getDouble("VOLTAGE_U23_AV"));
sample.getInstMeasurements().setVOLTAGE_L2_L3(rs.getDouble("VOLTAGE_U23_IN"));
sample.getMinMeasurements().setCURRENT_L1_N(rs.getDouble("CURRENT_L1_MN"));
sample.getMaxMeasurements().setCURRENT_L1_N(rs.getDouble("CURRENT_L1_MX"));
sample.getAvgMeasurements().setCURRENT_L1_N(rs.getDouble("CURRENT_L1_AV"));
sample.getInstMeasurements().setCURRENT_L1_N(rs.getDouble("CURRENT_L1_IN"));
sample.getMinMeasurements().setCURRENT_L2_N(rs.getDouble("CURRENT_L2_MN"));
sample.getMaxMeasurements().setCURRENT_L2_N(rs.getDouble("CURRENT_L2_MX"));
sample.getAvgMeasurements().setCURRENT_L2_N(rs.getDouble("CURRENT_L2_AV"));
sample.getInstMeasurements().setCURRENT_L2_N(rs.getDouble("CURRENT_L2_IN"));
sample.getMinMeasurements().setCURRENT_L3_N(rs.getDouble("CURRENT_L3_MN"));
sample.getMaxMeasurements().setCURRENT_L3_N(rs.getDouble("CURRENT_L3_MX"));
sample.getAvgMeasurements().setCURRENT_L3_N(rs.getDouble("CURRENT_L3_AV"));
sample.getInstMeasurements().setCURRENT_L3_N(rs.getDouble("CURRENT_L3_IN"));
sample.getMinMeasurements().setP_SUM(rs.getDouble("POWER_P_MN"));
sample.getMaxMeasurements().setP_SUM(rs.getDouble("POWER_P_MX"));
sample.getAvgMeasurements().setP_SUM(rs.getDouble("POWER_P_AV"));
sample.getInstMeasurements().setP_SUM(rs.getDouble("POWER_P_IN"));
sample.getMinMeasurements().setQ_SUM(rs.getDouble("POWER_Q_MN"));
sample.getMaxMeasurements().setQ_SUM(rs.getDouble("POWER_Q_MX"));
sample.getAvgMeasurements().setQ_SUM(rs.getDouble("POWER_Q_AV"));
sample.getInstMeasurements().setQ_SUM(rs.getDouble("POWER_Q_IN"));
sample.getMinMeasurements().setS_SUM(rs.getDouble("POWER_S_MN"));
sample.getMaxMeasurements().setS_SUM(rs.getDouble("POWER_S_MX"));
sample.getAvgMeasurements().setS_SUM(rs.getDouble("POWER_S_AV"));
sample.getInstMeasurements().setS_SUM(rs.getDouble("POWER_S_IN"));
sample.getMinMeasurements().setFREQUENCY(rs.getDouble("FREQUENCY_MN"));
sample.getMaxMeasurements().setFREQUENCY(rs.getDouble("FREQUENCY_MX"));
sample.getAvgMeasurements().setFREQUENCY(rs.getDouble("FREQUENCY_AV"));
sample.getInstMeasurements().setFREQUENCY(rs.getDouble("FREQUENCY_IN"));
sample.getInstMeasurements().setREAL_E_L1_CONSUMED(rs.getDouble("ENERGY_ACTIVE_CONSUMED_Ph1_IN"));
sample.getInstMeasurements().setREAL_E_L2_CONSUMED(rs.getDouble("ENERGY_ACTIVE_CONSUMED_Ph2_IN"));
sample.getInstMeasurements().setREAL_E_L3_CONSUMED(rs.getDouble("ENERGY_ACTIVE_CONSUMED_Ph3_IN"));
sample.getInstMeasurements().setREAL_E_L1_DELIVERED(rs.getDouble("ENERGY_ACTIVE_PRODUCED_Ph1_IN"));
sample.getInstMeasurements().setREAL_E_L2_DELIVERED(rs.getDouble("ENERGY_ACTIVE_PRODUCED_Ph2_IN"));
sample.getInstMeasurements().setREAL_E_L3_DELIVERED(rs.getDouble("ENERGY_ACTIVE_PRODUCED_Ph3_IN"));
sample.getInstMeasurements().setREACTIVE_E_L1_INDUCTIVE(rs.getDouble("ENERGY_REACTIVE_INDUCTIVE_Ph1_IN"));
sample.getInstMeasurements().setREACTIVE_E_L2_INDUCTIVE(rs.getDouble("ENERGY_REACTIVE_INDUCTIVE_Ph2_IN"));
sample.getInstMeasurements().setREACTIVE_E_L3_INDUCTIVE(rs.getDouble("ENERGY_REACTIVE_INDUCTIVE_Ph3_IN"));
sample.getInstMeasurements().setREACTIVE_E_L1_CAPACITIVE(rs.getDouble("ENERGY_REACTIVE_CAPACITIVE_Ph1_IN"));
sample.getInstMeasurements().setREACTIVE_E_L2_CAPACITIVE(rs.getDouble("ENERGY_REACTIVE_CAPACITIVE_Ph2_IN"));
sample.getInstMeasurements().setREACTIVE_E_L3_CAPACITIVE(rs.getDouble("ENERGY_REACTIVE_CAPACITIVE_Ph3_IN"));
sample.getMinMeasurements().setVOLTAGE_L1_N(rs.getDouble("VOLTAGE_V1_MN"));
sample.getMaxMeasurements().setVOLTAGE_L1_N(rs.getDouble("VOLTAGE_V1_MX"));
sample.getAvgMeasurements().setVOLTAGE_L1_N(rs.getDouble("VOLTAGE_V1_AV"));
sample.getInstMeasurements().setVOLTAGE_L1_N(rs.getDouble("VOLTAGE_V1_IN"));
sample.getMinMeasurements().setVOLTAGE_L2_N(rs.getDouble("VOLTAGE_V2_MN"));
sample.getMaxMeasurements().setVOLTAGE_L2_N(rs.getDouble("VOLTAGE_V2_MX"));
sample.getAvgMeasurements().setVOLTAGE_L2_N(rs.getDouble("VOLTAGE_V2_AV"));
sample.getInstMeasurements().setVOLTAGE_L2_N(rs.getDouble("VOLTAGE_V2_IN"));
sample.getMinMeasurements().setVOLTAGE_L3_N(rs.getDouble("VOLTAGE_V3_MN"));
sample.getMaxMeasurements().setVOLTAGE_L3_N(rs.getDouble("VOLTAGE_V3_MX"));
sample.getAvgMeasurements().setVOLTAGE_L3_N(rs.getDouble("VOLTAGE_V3_AV"));
sample.getInstMeasurements().setVOLTAGE_L3_N(rs.getDouble("VOLTAGE_V3_IN"));
sample.getMinMeasurements().setREAL_P_L1_N(rs.getDouble("POWER_P_L1_MN"));
sample.getMaxMeasurements().setREAL_P_L1_N(rs.getDouble("POWER_P_L1_MX"));
sample.getAvgMeasurements().setREAL_P_L1_N(rs.getDouble("POWER_P_L1_AV"));
sample.getInstMeasurements().setREAL_P_L1_N(rs.getDouble("POWER_P_L1_IN"));
sample.getMinMeasurements().setREAL_P_L2_N(rs.getDouble("POWER_P_L2_MN"));
sample.getMaxMeasurements().setREAL_P_L2_N(rs.getDouble("POWER_P_L2_MX"));
sample.getAvgMeasurements().setREAL_P_L2_N(rs.getDouble("POWER_P_L2_AV"));
sample.getInstMeasurements().setREAL_P_L2_N(rs.getDouble("POWER_P_L2_IN"));
sample.getMinMeasurements().setREAL_P_L3_N(rs.getDouble("POWER_P_L3_MN"));
sample.getMaxMeasurements().setREAL_P_L3_N(rs.getDouble("POWER_P_L3_MX"));
sample.getAvgMeasurements().setREAL_P_L3_N(rs.getDouble("POWER_P_L3_AV"));
sample.getInstMeasurements().setREAL_P_L3_N(rs.getDouble("POWER_P_L3_IN"));
sample.getMinMeasurements().setQ_L1(rs.getDouble("POWER_Q_L1_MN"));
sample.getMaxMeasurements().setQ_L1(rs.getDouble("POWER_Q_L1_MX"));
sample.getAvgMeasurements().setQ_L1(rs.getDouble("POWER_Q_L1_AV"));
sample.getInstMeasurements().setQ_L1(rs.getDouble("POWER_Q_L1_IN"));
sample.getMinMeasurements().setQ_L2(rs.getDouble("POWER_Q_L2_MN"));
sample.getMaxMeasurements().setQ_L2(rs.getDouble("POWER_Q_L2_MX"));
sample.getAvgMeasurements().setQ_L2(rs.getDouble("POWER_Q_L2_AV"));
sample.getInstMeasurements().setQ_L2(rs.getDouble("POWER_Q_L2_IN"));
sample.getMinMeasurements().setQ_L3(rs.getDouble("POWER_Q_L3_MN"));
sample.getMaxMeasurements().setQ_L3(rs.getDouble("POWER_Q_L3_MX"));
sample.getAvgMeasurements().setQ_L3(rs.getDouble("POWER_Q_L3_AV"));
sample.getInstMeasurements().setQ_L3(rs.getDouble("POWER_Q_L3_IN"));
sample.getMinMeasurements().setCOS_PHI_UL1_IL1(rs.getDouble("COS_PHI_L1_MN"));
sample.getMaxMeasurements().setCOS_PHI_UL1_IL1(rs.getDouble("COS_PHI_L1_MX"));
sample.getAvgMeasurements().setCOS_PHI_UL1_IL1(rs.getDouble("COS_PHI_L1_AV"));
sample.getInstMeasurements().setCOS_PHI_UL1_IL1(rs.getDouble("COS_PHI_L1_IN"));
sample.getMinMeasurements().setCOS_PHI_UL2_IL2(rs.getDouble("COS_PHI_L2_MN"));
sample.getMaxMeasurements().setCOS_PHI_UL2_IL2(rs.getDouble("COS_PHI_L2_MX"));
sample.getAvgMeasurements().setCOS_PHI_UL2_IL2(rs.getDouble("COS_PHI_L2_AV"));
sample.getInstMeasurements().setCOS_PHI_UL2_IL2(rs.getDouble("COS_PHI_L2_IN"));
sample.getMinMeasurements().setCOS_PHI_UL3_IL3(rs.getDouble("COS_PHI_L3_MN"));
sample.getMaxMeasurements().setCOS_PHI_UL3_IL3(rs.getDouble("COS_PHI_L3_MX"));
sample.getAvgMeasurements().setCOS_PHI_UL3_IL3(rs.getDouble("COS_PHI_L3_AV"));
sample.getInstMeasurements().setCOS_PHI_UL3_IL3(rs.getDouble("COS_PHI_L3_IN"));
samples.add(sample);
}
log.debug("Retrieved {} janitza samples", samples.size());
return samples;
}
}
} catch (final SQLException ex) {
log.error(ex.getMessage(), ex);
return Arrays.asList();
}
}
@Override
public Collection getSamples(DataLogger logger, Instant until) throws SQLException {
throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
}
@Override
public int deleteMeasurements(Instant until) {
int count = 0;
try (final Connection conn = bds.getConnection()) {
try (final PreparedStatement statement = conn.prepareStatement("DELETE FROM " + JANITZA_MEASUREMENT + " WHERE STP < ?")) {
statement.setObject(1, LocalDateTime.ofInstant(until, ZoneOffset.UTC));
count += statement.executeUpdate();
conn.commit();
log.debug("Deleted {} janitza measurements", count);
}
} catch (final SQLException ex) {
log.warn("Failed to delete measurements: {}", ex.getMessage());
} finally {
return count;
}
}
@Override
public int deleteSamples(DataLogger logger, Instant until) {
try (final Connection conn = bds.getConnection()) {
try (final PreparedStatement statement = conn.prepareStatement("DELETE FROM " + JANITZA_SAMPLE + " WHERE LOG_ID = ? AND STP < ?")) {
statement.setLong(1, logger.getId());
statement.setObject(2, LocalDateTime.ofInstant(until, ZoneOffset.UTC));
final int count = statement.executeUpdate();
conn.commit();
log.debug("Deleted {} janitza samples", count);
return count;
}
} catch (final SQLException ex) {
log.warn("Failed to delete samples: {}", ex.getMessage());
return 0;
}
}
static final BasicDataSource bds = SampleDao.setup("janitza", db -> {
createJanitzaMeasurementTable(db);
createJanitzaSampleTable(db);
});
private static Logger log = LoggerFactory.getLogger(JanitzaDao.class);
static final String JANITZA_MEASUREMENT = "JANITZA_MSR";
static final String JANITZA_SAMPLE = "JANITZA_SPL";
static void createJanitzaMeasurementTable(BasicDataSource bds) throws SQLException {
try (final Connection conn = bds.getConnection()) {
try (final Statement statement = conn.createStatement()) {
statement.execute("CREATE TABLE IF NOT EXISTS " + JANITZA_MEASUREMENT + " ("
+ "JANITZA_NO INT NOT NULL, "
+ "STE_ID INT NOT NULL, "
+ "STP TIMESTAMP(6) NOT NULL, "
+ "VOLTAGE_U13 DECIMAL(4,1) NOT NULL, "
+ "VOLTAGE_U12 DECIMAL(4,1) NOT NULL, "
+ "VOLTAGE_U23 DECIMAL(4,1) NOT NULL, "
+ "CURRENT_L1 DECIMAL(5,1) NOT NULL, "
+ "CURRENT_L2 DECIMAL(5,1) NOT NULL, "
+ "CURRENT_L3 DECIMAL(5,1) NOT NULL, "
+ "POWER_P DECIMAL(8,1) NOT NULL, "
+ "POWER_Q DECIMAL(8,1) NOT NULL, "
+ "POWER_S DECIMAL(8,1) NOT NULL, "
+ "FREQUENCY DECIMAL(3,1) NOT NULL, "
+ "ENERGY_ACTIVE_CONSUMED_Ph1 INT NOT NULL, "
+ "ENERGY_ACTIVE_CONSUMED_Ph2 INT NOT NULL, "
+ "ENERGY_ACTIVE_CONSUMED_Ph3 INT NOT NULL, "
+ "ENERGY_ACTIVE_PRODUCED_Ph1 INT NOT NULL, "
+ "ENERGY_ACTIVE_PRODUCED_Ph2 INT NOT NULL, "
+ "ENERGY_ACTIVE_PRODUCED_Ph3 INT NOT NULL, "
+ "ENERGY_REACTIVE_INDUCTIVE_Ph1 INT NOT NULL, "
+ "ENERGY_REACTIVE_INDUCTIVE_Ph2 INT NOT NULL, "
+ "ENERGY_REACTIVE_INDUCTIVE_Ph3 INT NOT NULL, "
+ "ENERGY_REACTIVE_CAPACITIVE_Ph1 INT NOT NULL, "
+ "ENERGY_REACTIVE_CAPACITIVE_Ph2 INT NOT NULL, "
+ "ENERGY_REACTIVE_CAPACITIVE_Ph3 INT NOT NULL, "
+ "VOLTAGE_V1 DECIMAL(4,1) NOT NULL, "
+ "VOLTAGE_V2 DECIMAL(4,1) NOT NULL, "
+ "VOLTAGE_V3 DECIMAL(4,1) NOT NULL, "
+ "POWER_P_L1 DECIMAL(8,1) NOT NULL, "
+ "POWER_P_L2 DECIMAL(8,1) NOT NULL, "
+ "POWER_P_L3 DECIMAL(8,1) NOT NULL, "
+ "POWER_Q_L1 DECIMAL(8,1) NOT NULL, "
+ "POWER_Q_L2 DECIMAL(8,1) NOT NULL, "
+ "POWER_Q_L3 DECIMAL(8,1) NOT NULL, "
+ "COS_PHI_L1 DECIMAL(4,3) NOT NULL, "
+ "COS_PHI_L2 DECIMAL(4,3) NOT NULL, "
+ "COS_PHI_L3 DECIMAL(4,3) NOT NULL, "
+ "CONSTRAINT " + JANITZA_MEASUREMENT + "_PK PRIMARY KEY(STE_ID, JANITZA_NO, STP))");
}
try (final Statement statement = conn.createStatement()) {
statement.execute("CREATE INDEX IF NOT EXISTS " + JANITZA_MEASUREMENT + "_STP_IDX ON " + JANITZA_MEASUREMENT + " (STP)");
}
conn.commit();
}
}
static void createJanitzaSampleTable(BasicDataSource bds) throws SQLException {
try (final Connection conn = bds.getConnection()) {
try (final Statement statement = conn.createStatement()) {
statement.execute("CREATE TABLE IF NOT EXISTS " + JANITZA_SAMPLE + " ("
+ "JANITZA_NO INT NOT NULL, "
+ "STE_ID INT NOT NULL, "
+ "LOG_ID INT NOT NULL, "
+ "STP TIMESTAMP(6) NOT NULL, "
+ "VOLTAGE_U13_MN DECIMAL(4,1) NOT NULL, "
+ "VOLTAGE_U13_MX DECIMAL(4,1) NOT NULL, "
+ "VOLTAGE_U13_AV DECIMAL(4,1) NOT NULL, "
+ "VOLTAGE_U13_IN DECIMAL(4,1) NOT NULL, "
+ "VOLTAGE_U12_MN DECIMAL(4,1) NOT NULL, "
+ "VOLTAGE_U12_MX DECIMAL(4,1) NOT NULL, "
+ "VOLTAGE_U12_AV DECIMAL(4,1) NOT NULL, "
+ "VOLTAGE_U12_IN DECIMAL(4,1) NOT NULL, "
+ "VOLTAGE_U23_MN DECIMAL(4,1) NOT NULL, "
+ "VOLTAGE_U23_MX DECIMAL(4,1) NOT NULL, "
+ "VOLTAGE_U23_AV DECIMAL(4,1) NOT NULL, "
+ "VOLTAGE_U23_IN DECIMAL(4,1) NOT NULL, "
+ "CURRENT_L1_MN DECIMAL(5,1) NOT NULL, "
+ "CURRENT_L1_MX DECIMAL(5,1) NOT NULL, "
+ "CURRENT_L1_AV DECIMAL(5,1) NOT NULL, "
+ "CURRENT_L1_IN DECIMAL(5,1) NOT NULL, "
+ "CURRENT_L2_MN DECIMAL(5,1) NOT NULL, "
+ "CURRENT_L2_MX DECIMAL(5,1) NOT NULL, "
+ "CURRENT_L2_AV DECIMAL(5,1) NOT NULL, "
+ "CURRENT_L2_IN DECIMAL(5,1) NOT NULL, "
+ "CURRENT_L3_MN DECIMAL(5,1) NOT NULL, "
+ "CURRENT_L3_MX DECIMAL(5,1) NOT NULL, "
+ "CURRENT_L3_AV DECIMAL(5,1) NOT NULL, "
+ "CURRENT_L3_IN DECIMAL(5,1) NOT NULL, "
+ "POWER_P_MN DECIMAL(8,1) NOT NULL, "
+ "POWER_P_MX DECIMAL(8,1) NOT NULL, "
+ "POWER_P_AV DECIMAL(8,1) NOT NULL, "
+ "POWER_P_IN DECIMAL(8,1) NOT NULL, "
+ "POWER_Q_MN DECIMAL(8,1) NOT NULL, "
+ "POWER_Q_MX DECIMAL(8,1) NOT NULL, "
+ "POWER_Q_AV DECIMAL(8,1) NOT NULL, "
+ "POWER_Q_IN DECIMAL(8,1) NOT NULL, "
+ "POWER_S_MN DECIMAL(8,1) NOT NULL, "
+ "POWER_S_MX DECIMAL(8,1) NOT NULL, "
+ "POWER_S_AV DECIMAL(8,1) NOT NULL, "
+ "POWER_S_IN DECIMAL(8,1) NOT NULL, "
+ "FREQUENCY_MN DECIMAL(3,1) NOT NULL, "
+ "FREQUENCY_MX DECIMAL(3,1) NOT NULL, "
+ "FREQUENCY_AV DECIMAL(3,1) NOT NULL, "
+ "FREQUENCY_IN DECIMAL(3,1) NOT NULL, "
+ "ENERGY_ACTIVE_CONSUMED_Ph1_IN INT NOT NULL, "
+ "ENERGY_ACTIVE_CONSUMED_Ph2_IN INT NOT NULL, "
+ "ENERGY_ACTIVE_CONSUMED_Ph3_IN INT NOT NULL, "
+ "ENERGY_ACTIVE_PRODUCED_Ph1_IN INT NOT NULL, "
+ "ENERGY_ACTIVE_PRODUCED_Ph2_IN INT NOT NULL, "
+ "ENERGY_ACTIVE_PRODUCED_Ph3_IN INT NOT NULL, "
+ "ENERGY_REACTIVE_INDUCTIVE_Ph1_IN INT NOT NULL, "
+ "ENERGY_REACTIVE_INDUCTIVE_Ph2_IN INT NOT NULL, "
+ "ENERGY_REACTIVE_INDUCTIVE_Ph3_IN INT NOT NULL, "
+ "ENERGY_REACTIVE_CAPACITIVE_Ph1_IN INT NOT NULL, "
+ "ENERGY_REACTIVE_CAPACITIVE_Ph2_IN INT NOT NULL, "
+ "ENERGY_REACTIVE_CAPACITIVE_Ph3_IN INT NOT NULL, "
+ "VOLTAGE_V1_MN DECIMAL(4,1) NOT NULL, "
+ "VOLTAGE_V1_MX DECIMAL(4,1) NOT NULL, "
+ "VOLTAGE_V1_AV DECIMAL(4,1) NOT NULL, "
+ "VOLTAGE_V1_IN DECIMAL(4,1) NOT NULL, "
+ "VOLTAGE_V2_MN DECIMAL(4,1) NOT NULL, "
+ "VOLTAGE_V2_MX DECIMAL(4,1) NOT NULL, "
+ "VOLTAGE_V2_AV DECIMAL(4,1) NOT NULL, "
+ "VOLTAGE_V2_IN DECIMAL(4,1) NOT NULL, "
+ "VOLTAGE_V3_MN DECIMAL(4,1) NOT NULL, "
+ "VOLTAGE_V3_MX DECIMAL(4,1) NOT NULL, "
+ "VOLTAGE_V3_AV DECIMAL(4,1) NOT NULL, "
+ "VOLTAGE_V3_IN DECIMAL(4,1) NOT NULL, "
+ "POWER_P_L1_MN DECIMAL(8,1) NOT NULL, "
+ "POWER_P_L1_MX DECIMAL(8,1) NOT NULL, "
+ "POWER_P_L1_AV DECIMAL(8,1) NOT NULL, "
+ "POWER_P_L1_IN DECIMAL(8,1) NOT NULL, "
+ "POWER_P_L2_MN DECIMAL(8,1) NOT NULL, "
+ "POWER_P_L2_MX DECIMAL(8,1) NOT NULL, "
+ "POWER_P_L2_AV DECIMAL(8,1) NOT NULL, "
+ "POWER_P_L2_IN DECIMAL(8,1) NOT NULL, "
+ "POWER_P_L3_MN DECIMAL(8,1) NOT NULL, "
+ "POWER_P_L3_MX DECIMAL(8,1) NOT NULL, "
+ "POWER_P_L3_AV DECIMAL(8,1) NOT NULL, "
+ "POWER_P_L3_IN DECIMAL(8,1) NOT NULL, "
+ "POWER_Q_L1_MN DECIMAL(8,1) NOT NULL, "
+ "POWER_Q_L1_MX DECIMAL(8,1) NOT NULL, "
+ "POWER_Q_L1_AV DECIMAL(8,1) NOT NULL, "
+ "POWER_Q_L1_IN DECIMAL(8,1) NOT NULL, "
+ "POWER_Q_L2_MN DECIMAL(8,1) NOT NULL, "
+ "POWER_Q_L2_MX DECIMAL(8,1) NOT NULL, "
+ "POWER_Q_L2_AV DECIMAL(8,1) NOT NULL, "
+ "POWER_Q_L2_IN DECIMAL(8,1) NOT NULL, "
+ "POWER_Q_L3_MN DECIMAL(8,1) NOT NULL, "
+ "POWER_Q_L3_MX DECIMAL(8,1) NOT NULL, "
+ "POWER_Q_L3_AV DECIMAL(8,1) NOT NULL, "
+ "POWER_Q_L3_IN DECIMAL(8,1) NOT NULL, "
+ "COS_PHI_L1_MN DECIMAL(4,3) NOT NULL, "
+ "COS_PHI_L1_MX DECIMAL(4,3) NOT NULL, "
+ "COS_PHI_L1_AV DECIMAL(4,3) NOT NULL, "
+ "COS_PHI_L1_IN DECIMAL(4,3) NOT NULL, "
+ "COS_PHI_L2_MN DECIMAL(4,3) NOT NULL, "
+ "COS_PHI_L2_MX DECIMAL(4,3) NOT NULL, "
+ "COS_PHI_L2_AV DECIMAL(4,3) NOT NULL, "
+ "COS_PHI_L2_IN DECIMAL(4,3) NOT NULL, "
+ "COS_PHI_L3_MN DECIMAL(4,3) NOT NULL, "
+ "COS_PHI_L3_MX DECIMAL(4,3) NOT NULL, "
+ "COS_PHI_L3_AV DECIMAL(4,3) NOT NULL, "
+ "COS_PHI_L3_IN DECIMAL(4,3) NOT NULL, "
+ "CONSTRAINT " + JANITZA_SAMPLE + "_PK PRIMARY KEY(LOG_ID, STP, STE_ID, JANITZA_NO))");
}
conn.commit();
}
}
int insert(final Janitza janitza, final JanitzaBlock m) {
try (final Connection conn = bds.getConnection()) {
try (final PreparedStatement statement = conn.prepareStatement("INSERT INTO " + JANITZA_MEASUREMENT + " ("
+ "JANITZA_NO, "
+ "STE_ID, "
+ "STP, "
+ "VOLTAGE_U13, "
+ "VOLTAGE_U12, "
+ "VOLTAGE_U23, "
+ "CURRENT_L1, "
+ "CURRENT_L2, "
+ "CURRENT_L3, "
+ "POWER_P, "
+ "POWER_Q, "
+ "POWER_S, "
+ "FREQUENCY, "
+ "ENERGY_ACTIVE_CONSUMED_Ph1, "
+ "ENERGY_ACTIVE_CONSUMED_Ph2, "
+ "ENERGY_ACTIVE_CONSUMED_Ph3, "
+ "ENERGY_ACTIVE_PRODUCED_Ph1, "
+ "ENERGY_ACTIVE_PRODUCED_Ph2, "
+ "ENERGY_ACTIVE_PRODUCED_Ph3, "
+ "ENERGY_REACTIVE_INDUCTIVE_Ph1, "
+ "ENERGY_REACTIVE_INDUCTIVE_Ph2, "
+ "ENERGY_REACTIVE_INDUCTIVE_Ph3, "
+ "ENERGY_REACTIVE_CAPACITIVE_Ph1, "
+ "ENERGY_REACTIVE_CAPACITIVE_Ph2, "
+ "ENERGY_REACTIVE_CAPACITIVE_Ph3, "
+ "VOLTAGE_V1, "
+ "VOLTAGE_V2, "
+ "VOLTAGE_V3, "
+ "POWER_P_L1, "
+ "POWER_P_L2, "
+ "POWER_P_L3, "
+ "POWER_Q_L1, "
+ "POWER_Q_L2, "
+ "POWER_Q_L3, "
+ "COS_PHI_L1, "
+ "COS_PHI_L2, "
+ "COS_PHI_L3) "
+ "VALUES ("
+ "?, ?, ?, ?, ?, "
+ "?, ?, ?, ?, ?, "
+ "?, ?, ?, ?, ?, "
+ "?, ?, ?, ?, ?, "
+ "?, ?, ?, ?, ?, "
+ "?, ?, ?, ?, ?, "
+ "?, ?, ?, ?, ?, ?, ?)")) {
statement.setInt(1, janitza.getNumber());
statement.setInt(2, janitza.getSite().getNumber());
statement.setObject(3, LocalDateTime.ofInstant(m.getTimestamp(), ZoneOffset.UTC));
statement.setDouble(4, m.getVOLTAGE_L3_L1());
statement.setDouble(5, m.getVOLTAGE_L1_L2());
statement.setDouble(6, m.getVOLTAGE_L2_L3());
statement.setDouble(7, m.getCURRENT_L1_N());
statement.setDouble(8, m.getCURRENT_L2_N());
statement.setDouble(9, m.getCURRENT_L3_N());
statement.setDouble(10, m.getP_SUM());
statement.setDouble(11, m.getQ_SUM());
statement.setDouble(12, m.getS_SUM());
statement.setDouble(13, m.getFREQUENCY());
statement.setDouble(14, m.getREAL_E_L1_CONSUMED());
statement.setDouble(15, m.getREAL_E_L2_CONSUMED());
statement.setDouble(16, m.getREAL_E_L3_CONSUMED());
statement.setDouble(17, m.getREAL_E_L1_DELIVERED());
statement.setDouble(18, m.getREAL_E_L2_DELIVERED());
statement.setDouble(19, m.getREAL_E_L3_DELIVERED());
statement.setDouble(20, m.getREACTIVE_E_L1_INDUCTIVE());
statement.setDouble(21, m.getREACTIVE_E_L2_INDUCTIVE());
statement.setDouble(22, m.getREACTIVE_E_L3_INDUCTIVE());
statement.setDouble(23, m.getREACTIVE_E_L1_CAPACITIVE());
statement.setDouble(24, m.getREACTIVE_E_L2_CAPACITIVE());
statement.setDouble(25, m.getREACTIVE_E_L3_CAPACITIVE());
statement.setDouble(26, m.getVOLTAGE_L1_N());
statement.setDouble(27, m.getVOLTAGE_L2_N());
statement.setDouble(28, m.getVOLTAGE_L3_N());
statement.setDouble(29, m.getREAL_P_L1_N());
statement.setDouble(30, m.getREAL_P_L2_N());
statement.setDouble(31, m.getREAL_P_L3_N());
statement.setDouble(32, m.getQ_L1());
statement.setDouble(33, m.getQ_L2());
statement.setDouble(34, m.getQ_L3());
statement.setDouble(35, m.getCOS_PHI_UL1_IL1());
statement.setDouble(36, m.getCOS_PHI_UL2_IL2());
statement.setDouble(37, m.getCOS_PHI_UL3_IL3());
final int count = statement.executeUpdate();
conn.commit();
log.debug("Inserted {} Janitza measurements", count);
return count;
}
} catch (final SQLException ex) {
log.warn("Failed to insert Janitza: {}", ex.getMessage());
return 0;
}
}
}
Evgenij Ryazanov
Nov 2, 2019, 1:10:00 AM11/2/19
to H2 Database
Hello.
When you open many databases at once you need to decrease the cache size in all of them or at least in the most of them to a some reasonable size for your use case:
Mathieu Moloney
Nov 2, 2019, 6:22:05 PM11/2/19
to h2-da...@googlegroups.com
Hello,
After about 50 hours, I get the first java.lang.OutOfMemoryError: Java heap space
The GC is really going crazy.
About 80MB of org.h2.mvstore.cache.CacheLongKeyLIRS$Entry on the heap.
Total database size is 13MB
--
You received this message because you are subscribed to a topic in the Google Groups "H2 Database" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/h2-database/wL-Qo_OYCZY/unsubscribe.
To unsubscribe from this group and all its topics, send an email to h2-database...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/f9ba0544-4dd8-4cb6-b2d9-02ff1c9a52f8%40googlegroups.com.
Evgenij Ryazanov
Nov 2, 2019, 10:14:05 PM11/2/19
to H2 Database
You really need to decrease cache sizes of your databases. You have too many of them for the default setting.
And if you want to see the real memory use, you need to compute the retained sizes, but on large dumps they will be computed very slowly.
Reply all
Reply to author
Forward
0 new messages