First let me say thanks for any help which can be provided.
An example of the memory usage that concerns us is here:
Name # of Objects
Shallow Size
org.h2.result.simpleRowValue 140,379 4,492,128
org.h2.value.Value[] 62,522
2,006,784
org.h2.value.ValueLong 60,866
973,856
org.h2.result.row 59,461
4,281,192
It's mostly concerning because as more queries are called - these
number seem to continually grow.
Version
Stable release from 8/16.
Background:
We essentially are monitoring real time data coming in from multiple
sources.
An instantaneous "snapshot" is taken from this data and specific
information from each data point in the snapshot is inserted into a
two table database.
The data persistence is needed so that after the application a user
could chart/plot a data set without plotting it as the actual data was
coming in. It gives us a way to
look/graph past data.
We have a singleton which gets created via spring when the
application starts up which provides a single entry point to the H2
database.
Because the data points configuration can change(but not nearly as
often as the actual data does) we've separated config from data into
two tables:
This embedded db is initialized (tables/indexes setup) using the
following code:
public void initDB() throws SQLException {
String sql = "";
sql += "DROP INDEX IF EXISTS indxChannelConfigId;";
sql += "DROP INDEX IF EXISTS indxChannelConfigAgentId;";
sql += "DROP INDEX IF EXISTS indxChannelConfigChannelId;";
sql += "DROP TABLE IF EXISTS PAST_SCOREBOARD_CHANNEL_CONFIG;";
sql += "DROP TABLE IF EXISTS PAST_SCOREBOARD_CHANNEL_DATA;";
sql += "CREATE TABLE PAST_SCOREBOARD_CHANNEL_CONFIG";
sql += "(";
sql += " ID IDENTITY PRIMARY KEY, ";
sql += " AGENT_ID LONGVARCHAR, ";
sql += " CHANNEL_ID LONGVARCHAR, ";
sql += " LABEL VARCHAR , ";
sql += " TYPE VARCHAR , ";
sql += " UNITS VARCHAR ";
sql += ");";
sql += "CREATE INDEX indxChannelConfig_AgentId ON
PAST_SCOREBOARD_CHANNEL_CONFIG(AGENT_ID);";
sql += "CREATE INDEX indxChannelConfig_ChannelId ON
PAST_SCOREBOARD_CHANNEL_CONFIG(CHANNEL_ID);";
sql += "CREATE TABLE PAST_SCOREBOARD_CHANNEL_DATA";
sql += "(";
sql += " ID IDENTITY PRIMARY KEY, ";
sql += " CHANNEL_CONFIG_ID VARCHAR, ";
sql += " VALUE DOUBLE, ";
sql += " ELAPSED_TIME_IN_SECS FLOAT";
sql += ");";
sql += "CREATE INDEX indxChannelData_ChannelConfigId ON
PAST_SCOREBOARD_CHANNEL_DATA(CHANNEL_CONFIG_ID);";
sql += "CREATE INDEX indxChannelData_ELAPSED_TIME ON
PAST_SCOREBOARD_CHANNEL_DATA(ELAPSED_TIME_IN_SECS);";
synchronized (this.dbLock) {
if (canRunQuery()) {
conn.setAutoCommit(true);
Statement statement = conn.createStatement();
statement.execute(sql);
logger.debug("Init DB complete");
}
}
}
We are using three prepared statements
1) Saving config
String sqlSaveConfig = "insert into
PAST_SCOREBOARD_CHANNEL_CONFIG(AGENT_ID, CHANNEL_ID, LABEL, TYPE,
UNITS) values(?,?,?,?,?);";
this.saveConfigPrepStatement = conn.prepareStatement(sqlSaveConfig);
2) Saving Data
String sqlSaveData = "insert into
PAST_SCOREBOARD_CHANNEL_DATA(CHANNEL_CONFIG_ID, VALUE,
ELAPSED_TIME_IN_SECS)"
+ " (select max(id) as
configId, ? as value, ? as
ELAPSED_TIME_IN_SECS"
+ " FROM
PAST_SCOREBOARD_CHANNEL_CONFIG" + " WHERE
AGENT_ID=? AND CHANNEL_ID=?);";
this.saveDataPrepStatement = conn.prepareStatement(sqlSaveData);
3) Querying for all instances of a single data point and its
configuration for that snapshot instance.
String sqlGetChannelsPastData = " select TYPE, UNITS, VALUE,
ELAPSED_TIME_IN_SECS"//
+ " FROM
PAST_SCOREBOARD_CHANNEL_CONFIG tConfig "
+ " inner join
PAST_SCOREBOARD_CHANNEL_DATA tData "
+ " on
tConfig.id=tData.CHANNEL_CONFIG_ID"
+ " WHERE tConfig.AGENT_ID=?
AND tConfig.CHANNEL_ID=?"
+ " ORDER BY
tData.ELAPSED_TIME_IN_SECS ASC;";
this.getPastDataForChannelAddressPrepStatement =
conn.prepareStatement(sqlGetChannelsPastData);
This function actually makes the db call and builds a list of data
from the result set. The list is returned.
public List<PastData> getPastDataForChannelAddress(ChannelAddress
channelAddress) throws SQLException {
if (channelAddress == null) {
throw new IllegalStateException("Channel Address cannot
be null");
}
String agentId = channelAddress.getAgentId();
String channelId = channelAddress.getChannelId();
List<PastData> pastDataList = new ArrayList<PastData>();
ResultSet rs = null;
try {
synchronized (this.dbLock) {
if (canRunQuery()) {
getPastDataForChannelAddressPrepStatement.setString(1, agentId);
getPastDataForChannelAddressPrepStatement.setString(2,
channelId);
h2Connection.setAutoCommit(true);
logger.debug("\nDB Transaction
started");
rs =
getPastDataForChannelAddressPrepStatement.executeQuery();
logger.debug("DB Transaction complete");
}
}
String type;
String units;
double data;
long elapsedTimeInSecs;
PastData pastData;
while (rs != null && rs.next()) {
type = rs.getString(TYPE);
units = rs.getString(UNITS);
data = rs.getDouble(VALUE);
elapsedTimeInSecs =
rs.getLong(ELAPSED_TIME_IN_SECS);
pastData = new PastData(type, units, data,
elapsedTimeInSecs);
pastDataList.add(pastData);
}
logger.debug("Creation of past data-list complete: #of
datapoints:" + pastDataList.size());
rs.close();
rs = null;
return pastDataList;
} catch (SQLException e) {
throw new SQLException("SqlException thrown while
getting past data
for agentID:" + agentId + " ChannelID:"
+ channelId + " " + e.getMessage());
}
}
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en
-~----------~----~----~----~------~----~------~--~---
