Hi, Sorry for the delay.
>From what you wrote it looks like this is the database cache. You can set the cache size yourself, see also http://www.h2database.com/html/grammar.html#setcachesize If you think there is a memory leak, could you post a simple test case that actually runs out of memory? It would be great if the test case would be a standalone Java application with a simple main method (including opening the connection). Regards, Thomas On Wed, Sep 24, 2008 at 9:33 PM, edwardsk <[EMAIL PROTECTED]> wrote: > > 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 -~----------~----~----~----~------~----~------~--~---
