You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Bergquist, Brett" <BB...@canoga.com> on 2012/11/21 01:09:53 UTC
Have Derby Network Server having an out of memory (PermGen)
I have a customer that is periodically having a problem and unknown to me, they have been accessing the Derby database using a query such as in the following and have been repeatedly experienced a server issue. I finally figured out it was an OutOfMemory error (PermGen). So I just wrote a little application that performs the same query over and over against a copy of the database and the Derby Network Server. In just a few hours, the Derby Network Server gave up the ghost with a OutOfMemory(PermGen) error.
This is running against Derby 10.8.2.2. There is no other access to the database from any other process during this test.
Note that the query returns no data as there are no records in the database that satisfy the query. Also, note that the table NPAResults is actually a View that looks like:
CREATE VIEW NPARESULTS
(
ID,
REPORTKEY,
MASTERIP,
BOOTCOUNT,
TESTRESULTID,
PROFILEREFID,
ADDRESSREFID,
STARTDATETIME,
ACCURACYLEVEL,
RESULTFLAG,
PACKETSSENT,
ROUNDTRIPPACKETS,
DROPPEDPACKETS,
OUTOFORDERPACKETS,
MINROUNDTRIPLATENCY,
MAXROUNDTRIPLATENCY,
TOTALROUNDTRIPLATENCY,
AVGROUNDTRIPLATENCY,
LATENCYBUCKETVALUE1,
LATENCYBUCKETVALUE2,
LATENCYBUCKETVALUE3,
LATENCYBUCKETVALUE4,
LATENCYBUCKETVALUE5,
LATENCYBUCKETVALUE6,
LATENCYBUCKETVALUE7,
LATENCYBUCKETVALUE8,
LATENCYBUCKETVALUE9,
LATENCYBUCKETVALUE10,
JITTERMEASUREMENT,
MINLOCALREMOTEJITTER,
MAXLOCALREMOTEJITTER,
TOTALLOCALREMOTEJITTER,
AVGLOCALREMOTEJITTER,
LOCALREMOTEJITTERBUCKETVALUE1,
LOCALREMOTEJITTERBUCKETVALUE2,
LOCALREMOTEJITTERBUCKETVALUE3,
LOCALREMOTEJITTERBUCKETVALUE4,
LOCALREMOTEJITTERBUCKETVALUE5,
LOCALREMOTEJITTERBUCKETVALUE6,
LOCALREMOTEJITTERBUCKETVALUE7,
LOCALREMOTEJITTERBUCKETVALUE8,
LOCALREMOTEJITTERBUCKETVALUE9,
MINREMOTELOCALJITTER,
MAXREMOTELOCALJITTER,
TOTALREMOTELOCALJITTER,
AVGREMOTELOCALJITTER,
REMOTELOCALJITTERBUCKETVALUE1,
REMOTELOCALJITTERBUCKETVALUE2,
REMOTELOCALJITTERBUCKETVALUE3,
REMOTELOCALJITTERBUCKETVALUE4,
REMOTELOCALJITTERBUCKETVALUE5,
REMOTELOCALJITTERBUCKETVALUE6,
REMOTELOCALJITTERBUCKETVALUE7,
REMOTELOCALJITTERBUCKETVALUE8,
REMOTELOCALJITTERBUCKETVALUE9,
CIRCUIT1REFID,
CIRCUIT2REFID,
UNAVAILABLEEXCLUDED
) AS
SELECT
ID,
REPORTKEY,
MASTERIP,
BOOTCOUNT,
TESTRESULTID,
PROFILEREFID,
ADDRESSREFID,
STARTDATETIME,
ACCURACYLEVEL,
RESULTFLAG,
PACKETSSENT,
ROUNDTRIPPACKETS,
DROPPEDPACKETS,
OUTOFORDERPACKETS,
MINROUNDTRIPLATENCY,
MAXROUNDTRIPLATENCY,
TOTALROUNDTRIPLATENCY,
AVGROUNDTRIPLATENCY,
LATENCYBUCKETVALUE1,
LATENCYBUCKETVALUE2,
LATENCYBUCKETVALUE3,
LATENCYBUCKETVALUE4,
LATENCYBUCKETVALUE5,
LATENCYBUCKETVALUE6,
LATENCYBUCKETVALUE7,
LATENCYBUCKETVALUE8,
LATENCYBUCKETVALUE9,
LATENCYBUCKETVALUE10,
JITTERMEASUREMENT,
MINLOCALREMOTEJITTER,
MAXLOCALREMOTEJITTER,
TOTALLOCALREMOTEJITTER,
AVGLOCALREMOTEJITTER,
LOCALREMOTEJITTERBUCKETVALUE1,
LOCALREMOTEJITTERBUCKETVALUE2,
LOCALREMOTEJITTERBUCKETVALUE3,
LOCALREMOTEJITTERBUCKETVALUE4,
LOCALREMOTEJITTERBUCKETVALUE5,
LOCALREMOTEJITTERBUCKETVALUE6,
LOCALREMOTEJITTERBUCKETVALUE7,
LOCALREMOTEJITTERBUCKETVALUE8,
LOCALREMOTEJITTERBUCKETVALUE9,
MINREMOTELOCALJITTER,
MAXREMOTELOCALJITTER,
TOTALREMOTELOCALJITTER,
AVGREMOTELOCALJITTER,
REMOTELOCALJITTERBUCKETVALUE1,
REMOTELOCALJITTERBUCKETVALUE2,
REMOTELOCALJITTERBUCKETVALUE3,
REMOTELOCALJITTERBUCKETVALUE4,
REMOTELOCALJITTERBUCKETVALUE5,
REMOTELOCALJITTERBUCKETVALUE6,
REMOTELOCALJITTERBUCKETVALUE7,
REMOTELOCALJITTERBUCKETVALUE8,
REMOTELOCALJITTERBUCKETVALUE9,
CIRCUIT1REFID,
CIRCUIT2REFID,
UNAVAILABLEEXCLUDED
FROM TABLE (PCS_V1.NPARESULTS_TABLE()) S;
The NPARESULTS_TABLE() is a table function that does a UNION of 5 tables. Just some background.
Since this is a PermGen error, it seem to be related to class generation, which I gather Derby does for queries. Using JVisualVM, I do see the loaded classes go up a good clip and the PermGen used heap go up (it also came down a little and then backup again) and then hit the max. The system is an Oracle M3000 (64Gb of memory) running Java 1.6.0_27 and has the options "-d64 -Xms8192m -Xmx8192m -XX:MaxPermSize=1024m"
Each time I run this sample, after a few hours, the OutOfMemory occurs. Any ideas on this will be greatly appreciated.
Here is the little sample code that I used to cause the condition.
/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
package pmresultsretriever;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
*
* @author brett
*/
public class PMResultsRetriever {
/**
* @param args the command line arguments
*/
public static void main(String[] args) {
new PMResultsRetriever().run();
}
private static final String SQLgetCVReportResults = "SELECT CSEM.ip_to_string(MasterIp) as MASTERIP, "
+ "CSEM.ip_to_string(TestResultIpAddress) as REMOTEIP, "
+ "TestResultVlanId as VLANID, "
+ "ProfileName, "
+ "PCS_V1.NPAResultsProfile.ProfileRefId as PROFILEREFID, "
+ "StartDateTime, "
+ "PCS_V1.NPAResults.ReportKey as ReportKey, "
+ "PacketsSent, "
+ "RoundTripPackets, "
+ "DroppedPackets, "
+ "OutOfOrderPackets, "
+ "case when PacketsSent = 0 then 100 else ((RoundTripPackets * 100) / PacketsSent) end as DATADELIVERY_RATIO, "
+ "LATENCYBUCKETSINUSE, "
+ "JITTERBUCKETSINUSE, "
+ "LatencyBucketValue1, "
+ "LatencyBucketValue2, "
+ "LatencyBucketValue3, "
+ "LatencyBucketValue4, "
+ "LatencyBucketValue5, "
+ "LatencyBucketValue6, "
+ "LatencyBucketValue7, "
+ "LatencyBucketValue8, "
+ "LatencyBucketValue9, "
+ "LatencyBucketValue10, "
+ "MinRoundTripLatency, "
+ "MaxRoundTripLatency, "
+ "TotalRoundTripLatency, "
+ "AvgRoundTripLatency, "
+ "LocalRemoteJitterBucketValue1, "
+ "LocalRemoteJitterBucketValue2, "
+ "LocalRemoteJitterBucketValue3, "
+ "LocalRemoteJitterBucketValue4, "
+ "LocalRemoteJitterBucketValue5, "
+ "LocalRemoteJitterBucketValue6, "
+ "LocalRemoteJitterBucketValue7, "
+ "LocalRemoteJitterBucketValue8, "
+ "LocalRemoteJitterBucketValue9, "
+ "MinLocalRemoteJitter, "
+ "MaxLocalRemoteJitter, "
+ "TotalLocalRemoteJitter, "
+ "AvgLocalRemoteJitter, "
+ "RemoteLocalJitterBucketValue1, "
+ "RemoteLocalJitterBucketValue2, "
+ "RemoteLocalJitterBucketValue3, "
+ "RemoteLocalJitterBucketValue4, "
+ "RemoteLocalJitterBucketValue5, "
+ "RemoteLocalJitterBucketValue6, "
+ "RemoteLocalJitterBucketValue7, "
+ "RemoteLocalJitterBucketValue8, "
+ "RemoteLocalJitterBucketValue9, "
+ "MinRemoteLocalJitter, "
+ "MaxRemoteLocalJitter, "
+ "TotalRemoteLocalJitter, "
+ "AvgRemoteLocalJitter, "
+ "c1.Circuit as Circuit1, "
+ "c2.Circuit as Circuit2, "
+ "TestResultDescription, "
+ "TestResultDescription2, "
+ "ResultFlag, "
+ "ID "
+ " FROM "
+ "PCS_V1.NPAResults_WEEK_1, "
+ "PCS_V1.NPAResultsProfile, "
+ "PCS_V1.NPAResultsAddress, "
+ "PCS_V1.NPAResultsCircuit as c1, "
+ "PCS_V1.NPAResultsCircuit as c2 "
+ " WHERE "
+ "PCS_V1.NPAResults.ProfileRefId = PCS_V1.NPAResultsProfile.ProfileRefId "
+ "AND PCS_V1.NPAResults.AddressRefId = PCS_V1.NPAResultsAddress.AddressRefId "
+ "AND PCS_V1.NPAResults.Circuit1RefId = c1.CircuitRefId "
+ "AND PCS_V1.NPAResults.Circuit2RefId = c2.CircuitRefId "
+ "AND PCS_V1.NPAResults.ID > ? "
+ "AND PCS_V1.NPAResults.ID <= ? "
+ "AND PCS_V1.NPAResults.ProfileRefId in (select ProfileRefId from PCS_V1.NPAResultsProfile where (ProfileName = 'EVPL-RT') or (ProfileName = 'ERS-RT')) ";
private Connection connection;
/**
* Get the value of connection
*
* @return the value of connection
*/
public Connection getConnection() {
return connection;
}
/**
* Set the value of connection
*
* @param connection new value of connection
*/
public void setConnection(Connection connection) {
this.connection = connection;
}
private PreparedStatement stmt;
/**
* Get the value of stmt
*
* @return the value of stmt
*/
public PreparedStatement getStmt() {
return stmt;
}
/**
* Set the value of stmt
*
* @param stmt new value of stmt
*/
public void setStmt(PreparedStatement stmt) {
this.stmt = stmt;
}
public void run() {
loadDbDriver();
createConnection();
createPreparedStatement();
for (int i = 1; i < Integer.MAX_VALUE; i += 5000) {
performQuery(i, i + 4999);
}
}
public void loadDbDriver() {
try {
Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance();
} catch (Exception exception) {
System.err.println("Failed to load database driver:" + exception.toString());
System.exit(1);
}
}
public void createConnection() {
try {
connection = DriverManager.getConnection("jdbc:derby://192.169.1.75:1527/csemdb", "CSEM", "CSEM");
} catch (SQLException ex) {
System.err.println("Failed to create Connection " + ex.toString());
System.exit(1);
}
}
public void createPreparedStatement() {
try {
stmt = connection.prepareStatement(SQLgetCVReportResults);
} catch (SQLException ex) {
System.err.println("Failed to create PreparedStatement " + ex.toString());
}
}
public void performQuery(int from, int to) {
System.out.println("Executing query for results from " + from + " to " + to);
try {
stmt.setInt(1, from);
stmt.setInt(2, to);
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
long id = rs.getLong("ID");
System.out.println("Got result: " + id);
}
rs.close();
} catch (SQLException ex) {
System.err.println("Failed to execute PreparedStatement " + ex.toString());
}
}
}
Re: Have Derby Network Server having an out of memory (PermGen)
Posted by Rick Hillegas <ri...@oracle.com>.
Thanks for starting this interesting thread, Brett. One comment inline...
On 11/23/12 4:39 AM, Bergquist, Brett wrote:
> ...
>
> I know I have not take into consideration the table functions column name restriction in the initScan which I will eventually get to. What would be useful I think would be to also possibly pass in the ORDER BY restriction and have the table function to be able to signal that it can return an ordered result set. This might make it possible to optimize any sorting that might be required if the returned result set were known to be ordered.
I have logged an enhancement request to track this useful suggestion:
https://issues.apache.org/jira/browse/DERBY-6004#comment-13503794.
Thanks,
-Rick
RE: Have Derby Network Server having an out of memory (PermGen)
Posted by "Bergquist, Brett" <BB...@canoga.com>.
Here is the table function that is being used in case it might be obvious that I am doing something wrong. Basically there are 53 tables named NPA_RESULTS_WEEK_1, NPA_RESULTS_WEEK_2, ..., NPA_RESULTS_WEEK_53. There is a customer defined setting of the number of weeks of data to retain (disk space is an issue as 2 weeks of data at one customer is about 28G). As data is to be inserted, the week ordinal number is computed and the correct week table is inserted into. At a scheduled time (usually Sunday at 2AM), a purge process is performed by truncating the proper week table which quickly removes all of the data and reclaims the disk space.
As data is extracted for analysis, a view is used which is based on this table function. The table function dynamically creates a query of the correct week tables by determining the current week ordinal and using the customer setting of the number of weeks and a UNION is created of just these tables and a result set is returned.
I know I have not take into consideration the table functions column name restriction in the initScan which I will eventually get to. What would be useful I think would be to also possibly pass in the ORDER BY restriction and have the table function to be able to signal that it can return an ordered result set. This might make it possible to optimize any sorting that might be required if the returned result set were known to be ordered.
Using the View based on the table function also affords me the ability to place a shared lock on a table to act as a semaphore. The purge process waits to exclusively lock this same table before performing it TRUNCATE TABLE. So the truncate will not happen while there is an open result set looking at the unioned data and the table function will wait while the truncate is being performed. Using just a straight View does not allow me to such.
I do have a question, however, and it is "is it more performant to have a View that is a union of 53 tables or have a View based on a table function that dynamically creates a query?" I was wondering if there is some internal knowledge that might sway one way or other?
Aslo it is interesting that modifying the query to not use the View but rather just use the table function directly, the class loaded count does not increased. So one might ask, "why not use the table function in the query instead of the View". Well this is part of a larger Java EE application that is designed in a component fashion and built and released and installed on the customers system. The part that has the query was built and released years ago, so by using the View, I was able to maintain the data structure appearance as it was at that time which was a single NPA_RESULTS table. So the code generating the query did not have to be rev'ed, Q/A'ed, scheduled for an install into a system that is up 24/7, etc. On the next release, it will be changed to used the table function directly.
Anyways, here is the table function details.
/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
package com.canoga.derby.fcn;
import java.io.InputStream;
import java.io.Reader;
import java.math.BigDecimal;
import java.net.URL;
import java.sql.Array;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.NClob;
import java.sql.Ref;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.RowId;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.SQLXML;
import java.sql.Statement;
import java.sql.Time;
import java.sql.Timestamp;
import java.util.Calendar;
import java.util.GregorianCalendar;
import java.util.Map;
import org.apache.derby.vti.Restriction;
import org.apache.derby.vti.RestrictedVTI;
import org.apache.derby.vti.VTIEnvironment;
/**
*
* @author root
*/
public class NpaResultsTableFunction implements ResultSet, /*VTICosting,*/ RestrictedVTI {
/**
* The internal connection that we will use
*/
private Connection conn;
/**
* The template of the statement that will be created
*/
private static String template = "SELECT " +
"ID, " +
"REPORTKEY, " +
"MASTERIP, " +
"BOOTCOUNT, " +
"TESTRESULTID, " +
"PROFILEREFID, " +
"ADDRESSREFID, " +
"STARTDATETIME, " +
"ACCURACYLEVEL, " +
"RESULTFLAG, " +
"PACKETSSENT, " +
"ROUNDTRIPPACKETS, " +
"DROPPEDPACKETS, " +
"OUTOFORDERPACKETS, " +
"MINROUNDTRIPLATENCY, " +
"MAXROUNDTRIPLATENCY, " +
"TOTALROUNDTRIPLATENCY, " +
"AVGROUNDTRIPLATENCY, " +
"LATENCYBUCKETVALUE1, " +
"LATENCYBUCKETVALUE2, " +
"LATENCYBUCKETVALUE3, " +
"LATENCYBUCKETVALUE4, " +
"LATENCYBUCKETVALUE5, " +
"LATENCYBUCKETVALUE6, " +
"LATENCYBUCKETVALUE7, " +
"LATENCYBUCKETVALUE8, " +
"LATENCYBUCKETVALUE9, " +
"LATENCYBUCKETVALUE10, " +
"JITTERMEASUREMENT, " +
"MINLOCALREMOTEJITTER, " +
"MAXLOCALREMOTEJITTER, " +
"TOTALLOCALREMOTEJITTER, " +
"AVGLOCALREMOTEJITTER, " +
"LOCALREMOTEJITTERBUCKETVALUE1, " +
"LOCALREMOTEJITTERBUCKETVALUE2, " +
"LOCALREMOTEJITTERBUCKETVALUE3, " +
"LOCALREMOTEJITTERBUCKETVALUE4, " +
"LOCALREMOTEJITTERBUCKETVALUE5, " +
"LOCALREMOTEJITTERBUCKETVALUE6, " +
"LOCALREMOTEJITTERBUCKETVALUE7, " +
"LOCALREMOTEJITTERBUCKETVALUE8, " +
"LOCALREMOTEJITTERBUCKETVALUE9, " +
"MINREMOTELOCALJITTER, " +
"MAXREMOTELOCALJITTER, " +
"TOTALREMOTELOCALJITTER, " +
"AVGREMOTELOCALJITTER, " +
"REMOTELOCALJITTERBUCKETVALUE1, " +
"REMOTELOCALJITTERBUCKETVALUE2, " +
"REMOTELOCALJITTERBUCKETVALUE3, " +
"REMOTELOCALJITTERBUCKETVALUE4, " +
"REMOTELOCALJITTERBUCKETVALUE5, " +
"REMOTELOCALJITTERBUCKETVALUE6, " +
"REMOTELOCALJITTERBUCKETVALUE7, " +
"REMOTELOCALJITTERBUCKETVALUE8, " +
"REMOTELOCALJITTERBUCKETVALUE9, " +
"CIRCUIT1REFID, " +
"CIRCUIT2REFID, " +
"UNAVAILABLEEXCLUDED " +
"FROM PCS_V1.NPARESULTS_WEEK_";
/**
* The statement that will be executed
*/
private Statement stmt = null;
/**
* The underlying result set
*/
private ResultSet resultSet;
/**
* The constraint clause
*/
private String whereConstraint;
/**
* Invoked by the databae engine to read the table
* @return An instance of this class used to read the table
* @throws java.sql.SQLException
*/
public static NpaResultsTableFunction instance() throws SQLException {
return new NpaResultsTableFunction();
}
/**
* Creates a new instance of this class.
* @throws java.sql.SQLException
*/
public NpaResultsTableFunction() throws SQLException {
conn = DriverManager.getConnection("jdbc:default:connection");
stmt = conn.createStatement();
}
private ResultSet getResultSet() throws SQLException {
if (!isClosed()) {
if (null == this.resultSet) {
pcsPmLock();
this.resultSet = stmt.executeQuery(prepareStatement());
}
return this.resultSet;
} else {
throw new SQLException("Already closed");
}
}
private void pcsPmLock() throws SQLException {
stmt.execute("LOCK TABLE PCS_V1.PCS_PM_LOCK IN SHARE MODE");
}
private String prepareStatement() throws SQLException {
int keepWeeks = getKeepWeeks();
int weekOfYear = getThisWeek();
StringBuilder sb = new StringBuilder();
String unionAll = "";
while (keepWeeks > 0) {
sb.append(unionAll);
sb.append(template);
sb.append(weekOfYear);
if (null != whereConstraint) {
sb.append(" WHERE ");
sb.append(whereConstraint);
}
keepWeeks -= 1;
if (--weekOfYear == 0) {
weekOfYear = 53;
}
unionAll = " UNION ALL ";
}
String s = sb.toString();
return s;
}
private int getKeepWeeks() throws SQLException {
int keepWeeks = 1;
ResultSet weeks = stmt.executeQuery("SELECT PM_PURGE_KEEP_WEEKS FROM PCS_V1.PCS_PROPERTIES");
if (weeks.next()) {
keepWeeks = weeks.getInt(1);
}
// See if we need to keep one more week. This will be the case for example
// where we are keeping 4 weeks: the current week and the previous 4 weeks
// and if the current week is the first week of the year and the previous
// year had 52 weeks, then we need to keep weeks 1, 53, 52, 51, and 50
// since week 53 of the previous year never had any data
if (52 == getLastWeekOfYear()) {
keepWeeks += 1;
}
return keepWeeks;
}
private int getThisWeek() {
java.util.Date date = new java.util.Date();
Calendar cal = GregorianCalendar.getInstance();
cal.setMinimalDaysInFirstWeek(7);
cal.setTime(date);
int weekOfYear = cal.get(Calendar.WEEK_OF_YEAR);
return weekOfYear;
}
private int getLastWeekOfYear() {
java.util.Date date = new java.util.Date();
Calendar cal = GregorianCalendar.getInstance();
cal.setMinimalDaysInFirstWeek(7);
cal.setTime(date);
cal.roll(Calendar.YEAR, -1);
cal.set(Calendar.MONTH, Calendar.DECEMBER);
cal.set(Calendar.DAY_OF_MONTH, 31);
int weekOfYear = cal.get(Calendar.WEEK_OF_YEAR);
return weekOfYear;
}
public void initScan(String[] columNames, Restriction restriction) throws SQLException {
if (null != restriction) {
String s = restriction.toSQL();
if (null != s && 0 != s.length()) {
whereConstraint = s;
}
}
}
public double getEstimatedRowCount(VTIEnvironment arg0) throws SQLException {
return 1000.0;
}
public double getEstimatedCostPerInstantiation(VTIEnvironment arg0) throws SQLException {
return 1.0;
}
public boolean supportsMultipleInstantiations(VTIEnvironment arg0) throws SQLException {
return false;
}
@Override
public void close() throws SQLException {
if (!isClosed()) {
if (null != this.resultSet) {
this.resultSet.close();
this.resultSet = null;
}
if (null != this.stmt) {
this.stmt.close();
this.stmt = null;
}
this.conn = null;
}
}
@Override
public ResultSetMetaData getMetaData() throws SQLException {
return getResultSet().getMetaData();
}
@Override
public boolean next() throws SQLException {
return getResultSet().next();
}
public RowId getRowId(int columnIndex) throws SQLException {
return getResultSet().getRowId(columnIndex);
}
public RowId getRowId(String columnLabel) throws SQLException {
return getResultSet().getRowId(columnLabel);
}
public void updateRowId(int columnIndex, RowId x) throws SQLException {
getResultSet().updateRowId(columnIndex, x);
}
public void updateRowId(String columnLabel, RowId x) throws SQLException {
getResultSet().updateRowId(columnLabel, x);
}
public int getHoldability() throws SQLException {
return getResultSet().getHoldability();
}
public boolean isClosed() throws SQLException {
return null == this.conn ? true : false;
}
public void updateNString(int columnIndex, String nString) throws SQLException {
getResultSet().updateNString(columnIndex, nString);
}
public void updateNString(String columnLabel, String nString) throws SQLException {
getResultSet().updateNString(columnLabel, nString);
}
public void updateNClob(int columnIndex, NClob nClob) throws SQLException {
getResultSet().updateNClob(columnIndex, nClob);
}
public void updateNClob(String columnLabel, NClob nClob) throws SQLException {
getResultSet().updateNClob(columnLabel, nClob);
}
public NClob getNClob(int columnIndex) throws SQLException {
return getResultSet().getNClob(columnIndex);
}
public NClob getNClob(String columnLabel) throws SQLException {
return getResultSet().getNClob(columnLabel);
}
public SQLXML getSQLXML(int columnIndex) throws SQLException {
return getResultSet().getSQLXML(columnIndex);
}
public SQLXML getSQLXML(String columnLabel) throws SQLException {
return getResultSet().getSQLXML(columnLabel);
}
public void updateSQLXML(int columnIndex, SQLXML xmlObject) throws SQLException {
getResultSet().updateSQLXML(columnIndex, xmlObject);
}
public void updateSQLXML(String columnLabel, SQLXML xmlObject) throws SQLException {
getResultSet().updateSQLXML(columnLabel, xmlObject);
}
public String getNString(int columnIndex) throws SQLException {
return getResultSet().getNString(columnIndex);
}
public String getNString(String columnLabel) throws SQLException {
return getResultSet().getNString(columnLabel);
}
public Reader getNCharacterStream(int columnIndex) throws SQLException {
return getResultSet().getNCharacterStream(columnIndex);
}
public Reader getNCharacterStream(String columnLabel) throws SQLException {
return getResultSet().getNCharacterStream(columnLabel);
}
public void updateNCharacterStream(int columnIndex, Reader x, long length) throws SQLException {
getResultSet().updateNCharacterStream(template, x, length);
}
public void updateNCharacterStream(String columnLabel, Reader reader, long length) throws SQLException {
getResultSet().updateNCharacterStream(columnLabel, reader, length);
}
public void updateAsciiStream(int columnIndex, InputStream x, long length) throws SQLException {
getResultSet().updateAsciiStream(columnIndex, x, length);
}
public void updateBinaryStream(int columnIndex, InputStream x, long length) throws SQLException {
getResultSet().updateBinaryStream(columnIndex, x, length);
}
public void updateCharacterStream(int columnIndex, Reader x, long length) throws SQLException {
getResultSet().updateCharacterStream(columnIndex, x, length);
}
public void updateAsciiStream(String columnLabel, InputStream x, long length) throws SQLException {
getResultSet().updateAsciiStream(columnLabel, x, length);
}
public void updateBinaryStream(String columnLabel, InputStream x, long length) throws SQLException {
getResultSet().updateBinaryStream(columnLabel, x, length);
}
public void updateCharacterStream(String columnLabel, Reader reader, long length) throws SQLException {
getResultSet().updateCharacterStream(columnLabel, reader, length);
}
public void updateBlob(int columnIndex, InputStream inputStream, long length) throws SQLException {
getResultSet().updateBlob(columnIndex, inputStream, length);
}
public void updateBlob(String columnLabel, InputStream inputStream, long length) throws SQLException {
getResultSet().updateBlob(columnLabel, inputStream, length);
}
public void updateClob(int columnIndex, Reader reader, long length) throws SQLException {
getResultSet().updateClob(columnIndex, reader, length);
}
public void updateClob(String columnLabel, Reader reader, long length) throws SQLException {
getResultSet().updateClob(columnLabel, reader, length);
}
public void updateNClob(int columnIndex, Reader reader, long length) throws SQLException {
getResultSet().updateNClob(columnIndex, reader, length);
}
public void updateNClob(String columnLabel, Reader reader, long length) throws SQLException {
getResultSet().updateNClob(columnLabel, reader, length);
}
public void updateNCharacterStream(int columnIndex, Reader x) throws SQLException {
getResultSet().updateNCharacterStream(columnIndex, x, columnIndex);
}
public void updateNCharacterStream(String columnLabel, Reader reader) throws SQLException {
getResultSet().updateNCharacterStream(columnLabel, reader, FETCH_FORWARD);
}
public void updateAsciiStream(int columnIndex, InputStream x) throws SQLException {
getResultSet().updateAsciiStream(columnIndex, x);
}
public void updateBinaryStream(int columnIndex, InputStream x) throws SQLException {
getResultSet().updateBinaryStream(columnIndex, x);
}
public void updateCharacterStream(int columnIndex, Reader x) throws SQLException {
getResultSet().updateCharacterStream(columnIndex, x);
}
public void updateAsciiStream(String columnLabel, InputStream x) throws SQLException {
getResultSet().updateAsciiStream(columnLabel, x);
}
public void updateBinaryStream(String columnLabel, InputStream x) throws SQLException {
getResultSet().updateBinaryStream(columnLabel, x);
}
public void updateCharacterStream(String columnLabel, Reader reader) throws SQLException {
getResultSet().updateCharacterStream(columnLabel, reader);
}
public void updateBlob(int columnIndex, InputStream inputStream) throws SQLException {
getResultSet().updateBlob(columnIndex, inputStream);
}
public void updateBlob(String columnLabel, InputStream inputStream) throws SQLException {
getResultSet().updateBlob(columnLabel, inputStream);
}
public void updateClob(int columnIndex, Reader reader) throws SQLException {
getResultSet().updateClob(columnIndex, reader);
}
public void updateClob(String columnLabel, Reader reader) throws SQLException {
getResultSet().updateClob(columnLabel, reader);
}
public void updateNClob(int columnIndex, Reader reader) throws SQLException {
getResultSet().updateNClob(columnIndex, reader);
}
public void updateNClob(String columnLabel, Reader reader) throws SQLException {
getResultSet().updateNClob(columnLabel, reader);
}
public <T> T unwrap(Class<T> iface) throws SQLException {
return getResultSet().unwrap(iface);
}
public boolean isWrapperFor(Class<?> iface) throws SQLException {
return getResultSet().isWrapperFor(iface);
}
public boolean wasNull() throws SQLException {
return getResultSet().wasNull();
}
public String getString(int columnIndex) throws SQLException {
return getResultSet().getString(columnIndex);
}
public boolean getBoolean(int columnIndex) throws SQLException {
return getResultSet().getBoolean(columnIndex);
}
public byte getByte(int columnIndex) throws SQLException {
return getResultSet().getByte(columnIndex);
}
public short getShort(int columnIndex) throws SQLException {
return getResultSet().getShort(columnIndex);
}
public int getInt(int columnIndex) throws SQLException {
return getResultSet().getInt(columnIndex);
}
public long getLong(int columnIndex) throws SQLException {
return getResultSet().getLong(columnIndex);
}
public float getFloat(int columnIndex) throws SQLException {
return getResultSet().getFloat(columnIndex);
}
public double getDouble(int columnIndex) throws SQLException {
return getResultSet().getDouble(columnIndex);
}
public BigDecimal getBigDecimal(int columnIndex, int scale) throws SQLException {
return getResultSet().getBigDecimal(columnIndex);
}
public byte[] getBytes(int columnIndex) throws SQLException {
return getResultSet().getBytes(columnIndex);
}
public Date getDate(int columnIndex) throws SQLException {
return getResultSet().getDate(columnIndex);
}
public Time getTime(int columnIndex) throws SQLException {
return getResultSet().getTime(columnIndex);
}
public Timestamp getTimestamp(int columnIndex) throws SQLException {
return getResultSet().getTimestamp(columnIndex);
}
public InputStream getAsciiStream(int columnIndex) throws SQLException {
return getResultSet().getAsciiStream(columnIndex);
}
public InputStream getUnicodeStream(int columnIndex) throws SQLException {
return getResultSet().getUnicodeStream(columnIndex);
}
public InputStream getBinaryStream(int columnIndex) throws SQLException {
return getResultSet().getBinaryStream(columnIndex);
}
public String getString(String columnLabel) throws SQLException {
return getResultSet().getString(columnLabel);
}
public boolean getBoolean(String columnLabel) throws SQLException {
return getResultSet().getBoolean(columnLabel);
}
public byte getByte(String columnLabel) throws SQLException {
return getResultSet().getByte(columnLabel);
}
public short getShort(String columnLabel) throws SQLException {
return getResultSet().getShort(columnLabel);
}
public int getInt(String columnLabel) throws SQLException {
return getResultSet().getInt(columnLabel);
}
public long getLong(String columnLabel) throws SQLException {
return getResultSet().getLong(columnLabel);
}
public float getFloat(String columnLabel) throws SQLException {
return getResultSet().getFloat(columnLabel);
}
public double getDouble(String columnLabel) throws SQLException {
return getResultSet().getDouble(columnLabel);
}
public BigDecimal getBigDecimal(String columnLabel, int scale) throws SQLException {
return getResultSet().getBigDecimal(columnLabel);
}
public byte[] getBytes(String columnLabel) throws SQLException {
return getResultSet().getBytes(columnLabel);
}
public Date getDate(String columnLabel) throws SQLException {
return getResultSet().getDate(columnLabel);
}
public Time getTime(String columnLabel) throws SQLException {
return getResultSet().getTime(columnLabel);
}
public Timestamp getTimestamp(String columnLabel) throws SQLException {
return getResultSet().getTimestamp(columnLabel);
}
public InputStream getAsciiStream(String columnLabel) throws SQLException {
return getResultSet().getAsciiStream(columnLabel);
}
public InputStream getUnicodeStream(String columnLabel) throws SQLException {
return getResultSet().getUnicodeStream(columnLabel);
}
public InputStream getBinaryStream(String columnLabel) throws SQLException {
return getResultSet().getBinaryStream(columnLabel);
}
public SQLWarning getWarnings() throws SQLException {
return getResultSet().getWarnings();
}
public void clearWarnings() throws SQLException {
getResultSet().clearWarnings();
}
public String getCursorName() throws SQLException {
return getResultSet().getCursorName();
}
public Object getObject(int columnIndex) throws SQLException {
return getResultSet().getObject(columnIndex);
}
public Object getObject(String columnLabel) throws SQLException {
return getResultSet().getObject(columnLabel);
}
public int findColumn(String columnLabel) throws SQLException {
return getResultSet().findColumn(columnLabel);
}
public Reader getCharacterStream(int columnIndex) throws SQLException {
;
return getResultSet().getCharacterStream(columnIndex);
}
public Reader getCharacterStream(String columnLabel) throws SQLException {
return getResultSet().getCharacterStream(columnLabel);
}
public BigDecimal getBigDecimal(int columnIndex) throws SQLException {
return getResultSet().getBigDecimal(columnIndex);
}
public BigDecimal getBigDecimal(String columnLabel) throws SQLException {
return getResultSet().getBigDecimal(columnLabel);
}
public boolean isBeforeFirst() throws SQLException {
return getResultSet().isBeforeFirst();
}
public boolean isAfterLast() throws SQLException {
return getResultSet().isAfterLast();
}
public boolean isFirst() throws SQLException {
return getResultSet().isFirst();
}
public boolean isLast() throws SQLException {
return getResultSet().isLast();
}
public void beforeFirst() throws SQLException {
getResultSet().beforeFirst();
}
public void afterLast() throws SQLException {
getResultSet().afterLast();
}
public boolean first() throws SQLException {
return getResultSet().first();
}
public boolean last() throws SQLException {
return getResultSet().last();
}
public int getRow() throws SQLException {
return getResultSet().getRow();
}
public boolean absolute(int row) throws SQLException {
return getResultSet().absolute(row);
}
public boolean relative(int rows) throws SQLException {
return getResultSet().relative(rows);
}
public boolean previous() throws SQLException {
return getResultSet().previous();
}
public void setFetchDirection(int direction) throws SQLException {
getResultSet().setFetchDirection(direction);
}
public int getFetchDirection() throws SQLException {
return getResultSet().getFetchDirection();
}
public void setFetchSize(int rows) throws SQLException {
getResultSet().setFetchSize(rows);
}
public int getFetchSize() throws SQLException {
return getResultSet().getFetchSize();
}
public int getType() throws SQLException {
return getResultSet().getType();
}
public int getConcurrency() throws SQLException {
return getResultSet().getConcurrency();
}
public boolean rowUpdated() throws SQLException {
return getResultSet().rowUpdated();
}
public boolean rowInserted() throws SQLException {
return getResultSet().rowInserted();
}
public boolean rowDeleted() throws SQLException {
return getResultSet().rowDeleted();
}
public void updateNull(int columnIndex) throws SQLException {
getResultSet().updateNull(columnIndex);
}
public void updateBoolean(int columnIndex, boolean x) throws SQLException {
getResultSet().updateBoolean(columnIndex, x);
}
public void updateByte(int columnIndex, byte x) throws SQLException {
getResultSet().updateByte(columnIndex, x);
}
public void updateShort(int columnIndex, short x) throws SQLException {
getResultSet().updateShort(columnIndex, x);
}
public void updateInt(int columnIndex, int x) throws SQLException {
getResultSet().updateInt(columnIndex, x);
}
public void updateLong(int columnIndex, long x) throws SQLException {
getResultSet().updateLong(columnIndex, x);
}
public void updateFloat(int columnIndex, float x) throws SQLException {
getResultSet().updateFloat(columnIndex, x);
}
public void updateDouble(int columnIndex, double x) throws SQLException {
getResultSet().updateDouble(columnIndex, x);
}
public void updateBigDecimal(int columnIndex, BigDecimal x) throws SQLException {
getResultSet().updateBigDecimal(columnIndex, x);
}
public void updateString(int columnIndex, String x) throws SQLException {
getResultSet().updateString(columnIndex, x);
}
public void updateBytes(int columnIndex, byte[] x) throws SQLException {
getResultSet().updateBytes(columnIndex, x);
}
public void updateDate(int columnIndex, Date x) throws SQLException {
getResultSet().updateDate(columnIndex, x);
}
public void updateTime(int columnIndex, Time x) throws SQLException {
getResultSet().updateTime(columnIndex, x);
}
public void updateTimestamp(int columnIndex, Timestamp x) throws SQLException {
getResultSet().updateTimestamp(columnIndex, x);
}
public void updateAsciiStream(int columnIndex, InputStream x, int length) throws SQLException {
getResultSet().updateAsciiStream(columnIndex, x);
}
public void updateBinaryStream(int columnIndex, InputStream x, int length) throws SQLException {
getResultSet().updateBinaryStream(columnIndex, x);
}
public void updateCharacterStream(int columnIndex, Reader x, int length) throws SQLException {
getResultSet().updateCharacterStream(columnIndex, x);
}
public void updateObject(int columnIndex, Object x, int scaleOrLength) throws SQLException {
getResultSet().updateObject(columnIndex, x);
}
public void updateObject(int columnIndex, Object x) throws SQLException {
getResultSet().updateObject(columnIndex, x);
}
public void updateNull(String columnLabel) throws SQLException {
getResultSet().updateNull(columnLabel);
}
public void updateBoolean(String columnLabel, boolean x) throws SQLException {
getResultSet().updateBoolean(columnLabel, x);
}
public void updateByte(String columnLabel, byte x) throws SQLException {
getResultSet().updateByte(columnLabel, x);
}
public void updateShort(String columnLabel, short x) throws SQLException {
getResultSet().updateShort(columnLabel, x);
}
public void updateInt(String columnLabel, int x) throws SQLException {
getResultSet().updateInt(columnLabel, x);
}
public void updateLong(String columnLabel, long x) throws SQLException {
getResultSet().updateLong(columnLabel, x);
}
public void updateFloat(String columnLabel, float x) throws SQLException {
getResultSet().updateFloat(columnLabel, x);
}
public void updateDouble(String columnLabel, double x) throws SQLException {
getResultSet().updateDouble(columnLabel, x);
}
public void updateBigDecimal(String columnLabel, BigDecimal x) throws SQLException {
getResultSet().updateBigDecimal(columnLabel, x);
}
public void updateString(String columnLabel, String x) throws SQLException {
getResultSet().updateString(columnLabel, x);
}
public void updateBytes(String columnLabel, byte[] x) throws SQLException {
getResultSet().updateBytes(columnLabel, x);
}
public void updateDate(String columnLabel, Date x) throws SQLException {
getResultSet().updateDate(columnLabel, x);
}
public void updateTime(String columnLabel, Time x) throws SQLException {
getResultSet().updateTime(columnLabel, x);
}
public void updateTimestamp(String columnLabel, Timestamp x) throws SQLException {
getResultSet().updateTimestamp(columnLabel, x);
}
public void updateAsciiStream(String columnLabel, InputStream x, int length) throws SQLException {
getResultSet().updateAsciiStream(columnLabel, x);
}
public void updateBinaryStream(String columnLabel, InputStream x, int length) throws SQLException {
getResultSet().updateBinaryStream(columnLabel, x);
}
public void updateCharacterStream(String columnLabel, Reader reader, int length) throws SQLException {
getResultSet().updateCharacterStream(columnLabel, reader, length);
}
public void updateObject(String columnLabel, Object x, int scaleOrLength) throws SQLException {
getResultSet().updateObject(columnLabel, x, scaleOrLength);
}
public void updateObject(String columnLabel, Object x) throws SQLException {
getResultSet().updateObject(columnLabel, x);
}
public void insertRow() throws SQLException {
getResultSet().insertRow();
}
public void updateRow() throws SQLException {
getResultSet().updateRow();
}
public void deleteRow() throws SQLException {
getResultSet().deleteRow();
}
public void refreshRow() throws SQLException {
getResultSet().refreshRow();
}
public void cancelRowUpdates() throws SQLException {
getResultSet().cancelRowUpdates();
}
public void moveToInsertRow() throws SQLException {
getResultSet().moveToInsertRow();
}
public void moveToCurrentRow() throws SQLException {
getResultSet().moveToCurrentRow();
}
public Statement getStatement() throws SQLException {
return getResultSet().getStatement();
}
public Object getObject(int columnIndex, Map<String, Class<?>> map) throws SQLException {
return getResultSet().getObject(columnIndex, map);
}
public Ref getRef(int columnIndex) throws SQLException {
return getResultSet().getRef(columnIndex);
}
public Blob getBlob(int columnIndex) throws SQLException {
return getResultSet().getBlob(columnIndex);
}
public Clob getClob(int columnIndex) throws SQLException {
return getResultSet().getClob(columnIndex);
}
public Array getArray(int columnIndex) throws SQLException {
return getResultSet().getArray(columnIndex);
}
public Object getObject(String columnLabel, Map<String, Class<?>> map) throws SQLException {
return getResultSet().getObject(columnLabel, map);
}
public Ref getRef(String columnLabel) throws SQLException {
return getResultSet().getRef(columnLabel);
}
public Blob getBlob(String columnLabel) throws SQLException {
return getResultSet().getBlob(columnLabel);
}
public Clob getClob(String columnLabel) throws SQLException {
return getResultSet().getClob(columnLabel);
}
public Array getArray(String columnLabel) throws SQLException {
return getResultSet().getArray(columnLabel);
}
public Date getDate(int columnIndex, Calendar cal) throws SQLException {
return getResultSet().getDate(columnIndex, cal);
}
public Date getDate(String columnLabel, Calendar cal) throws SQLException {
return getResultSet().getDate(columnLabel, cal);
}
public Time getTime(int columnIndex, Calendar cal) throws SQLException {
return getResultSet().getTime(columnIndex, cal);
}
public Time getTime(String columnLabel, Calendar cal) throws SQLException {
return getResultSet().getTime(columnLabel, cal);
}
public Timestamp getTimestamp(int columnIndex, Calendar cal) throws SQLException {
return getResultSet().getTimestamp(columnIndex, cal);
}
public Timestamp getTimestamp(String columnLabel, Calendar cal) throws SQLException {
return getResultSet().getTimestamp(columnLabel, cal);
}
public URL getURL(int columnIndex) throws SQLException {
return getResultSet().getURL(columnIndex);
}
public URL getURL(String columnLabel) throws SQLException {
return getResultSet().getURL(columnLabel);
}
public void updateRef(int columnIndex, Ref x) throws SQLException {
getResultSet().updateRef(columnIndex, x);
}
public void updateRef(String columnLabel, Ref x) throws SQLException {
getResultSet().updateRef(columnLabel, x);
}
public void updateBlob(int columnIndex, Blob x) throws SQLException {
getResultSet().updateBlob(columnIndex, x);
}
public void updateBlob(String columnLabel, Blob x) throws SQLException {
getResultSet().updateBlob(columnLabel, x);
}
public void updateClob(int columnIndex, Clob x) throws SQLException {
getResultSet().updateClob(columnIndex, x);
}
public void updateClob(String columnLabel, Clob x) throws SQLException {
getResultSet().updateClob(columnLabel, x);
}
public void updateArray(int columnIndex, Array x) throws SQLException {
getResultSet().updateArray(columnIndex, x);
}
public void updateArray(String columnLabel, Array x) throws SQLException {
getResultSet().updateArray(columnLabel, x);
}
}
________________________________________
From: Knut Anders Hatlen [knut.hatlen@oracle.com]
Sent: Thursday, November 22, 2012 4:57 AM
To: derby-dev@db.apache.org
Subject: Re: Have Derby Network Server having an out of memory (PermGen)
Mike Matrigali <mi...@sbcglobal.net> writes:
> On 11/21/2012 6:58 AM, Knut Anders Hatlen wrote:
>> "Bergquist, Brett" <BB...@canoga.com> writes:
>>
>>> Yes, the statement cache size has been increased to 50K statements so
>>> that might be an issue. Maybe the PermGen space will need to be
>>> increased because of that. The documentation is not clear which type
> I am not an expert in this area, is there any case where we expect the
> re-execution of the same query to need to generate a different entry
> in the statement cache?
I think what's flooding the statement cache here is whatever gets
executed by the table function, which I understand is some dynamically
generated SQL statements.
This is also why I don't understand how changing from a view to a direct
table function call should change anything, as the top-level statement
should only have one entry in the cache, and the statements executed
inside the table function should be the same.
Two possible explanations:
1) Changing between view and direct call changes the plan picked by the
optimizer, so that the table function call one time ends up as the inner
table in a join, and another time as the outer table. This could change
the number of times the table function is called per query. If each call
to the table function generates truly unique SQL statements, calling it
more often will fill the cache quicker.
2) If it is a restricted table function, the actual
restriction/projection pushed down to the table function may vary
depending on which plan the optimizer picks. And this could affect what
kind of SQL is generated by the table function. Perhaps sometimes it
generates statements that are likely to be identical across invocations,
needing fewer entries in the cache, and other times it generates
statements that are less likely to be identical.
Following up on that last thought, if the queries generated by the table
function would be something like
select * from t where x < N
where N varies between invocations, it's better for the statement cache
if a parameter marker is used, like
select * from t where x < ?
rather than inlining the actual constant
select * from t where x < 5
select * from t where x < 42
...
Even though the table function itself doesn't execute the query more
than once, using parameter markers increases the likelihood of finding a
match in the statement cache.
Not sure if this affects Brett's table function. Just throwing out
ideas...
--
Knut Anders
Re: Have Derby Network Server having an out of memory (PermGen)
Posted by Knut Anders Hatlen <kn...@oracle.com>.
Mike Matrigali <mi...@sbcglobal.net> writes:
> On 11/21/2012 6:58 AM, Knut Anders Hatlen wrote:
>> "Bergquist, Brett" <BB...@canoga.com> writes:
>>
>>> Yes, the statement cache size has been increased to 50K statements so
>>> that might be an issue. Maybe the PermGen space will need to be
>>> increased because of that. The documentation is not clear which type
> I am not an expert in this area, is there any case where we expect the
> re-execution of the same query to need to generate a different entry
> in the statement cache?
I think what's flooding the statement cache here is whatever gets
executed by the table function, which I understand is some dynamically
generated SQL statements.
This is also why I don't understand how changing from a view to a direct
table function call should change anything, as the top-level statement
should only have one entry in the cache, and the statements executed
inside the table function should be the same.
Two possible explanations:
1) Changing between view and direct call changes the plan picked by the
optimizer, so that the table function call one time ends up as the inner
table in a join, and another time as the outer table. This could change
the number of times the table function is called per query. If each call
to the table function generates truly unique SQL statements, calling it
more often will fill the cache quicker.
2) If it is a restricted table function, the actual
restriction/projection pushed down to the table function may vary
depending on which plan the optimizer picks. And this could affect what
kind of SQL is generated by the table function. Perhaps sometimes it
generates statements that are likely to be identical across invocations,
needing fewer entries in the cache, and other times it generates
statements that are less likely to be identical.
Following up on that last thought, if the queries generated by the table
function would be something like
select * from t where x < N
where N varies between invocations, it's better for the statement cache
if a parameter marker is used, like
select * from t where x < ?
rather than inlining the actual constant
select * from t where x < 5
select * from t where x < 42
...
Even though the table function itself doesn't execute the query more
than once, using parameter markers increases the likelihood of finding a
match in the statement cache.
Not sure if this affects Brett's table function. Just throwing out
ideas...
--
Knut Anders
Re: Have Derby Network Server having an out of memory (PermGen)
Posted by Mike Matrigali <mi...@sbcglobal.net>.
On 11/21/2012 6:58 AM, Knut Anders Hatlen wrote:
> "Bergquist, Brett" <BB...@canoga.com> writes:
>
>> Yes, the statement cache size has been increased to 50K statements so
>> that might be an issue. Maybe the PermGen space will need to be
>> increased because of that. The documentation is not clear which type
I am not an expert in this area, is there any case where we expect the
re-execution of the same query to need to generate a different entry
in the statement cache? If so, then any such query is likely to flood
the statement cache and make it useless. If you have a complete test
case showing this it would be worth filing a jira.
I have to say I was surprised at the 50k setting for statement cache,
definitely do not think that kind of size was in mind when it was
developed. If the cache was working correctly would your application
really generate 50k different queries which you expected to be executed
more than once?
Re: Have Derby Network Server having an out of memory (PermGen)
Posted by Knut Anders Hatlen <kn...@oracle.com>.
"Bergquist, Brett" <BB...@canoga.com> writes:
> Yes, the statement cache size has been increased to 50K statements so
> that might be an issue. Maybe the PermGen space will need to be
> increased because of that. The documentation is not clear which type
> of heap that the statement cache would affect, however. As a test, I
> am going to lower my statement cache size to 100 statements and see
> what happens. Thanks for the idea!
On the bright side, the current early-access builds of JDK 8 don't have
a separate PermGen area. So in the not too distant future one doesn't
have to spend time figuring out how to partition the heap space.
> Some more info however. It is definitely related to the View/table
> function mechanism (explained in my second email). I just did a little
> more testing and found that if I change the query to use the table
> function directly instead of using the View that is created that uses
> the table function, then the loaded classes reported by VisualVM stays
> stable. Any thoughts on why querying on a View that is created that
> uses the table function might be generating and holding onto classes
> whereas using the same query with the View name replaced by the table
> function name does not have this problem?
Nothing comes to mind. I think there are some transformations the query
optimizer isn't able to do on views, that it could do when working
directly on the tables, so the compiled plans may differ. But it
shouldn't affect how soon the generated classes can be reclaimed.
--
Knut Anders
RE: Have Derby Network Server having an out of memory (PermGen)
Posted by "Bergquist, Brett" <BB...@canoga.com>.
I reset the statement cache size to 100 statements and set the MaxPermGen size to 256m and reran the test querying the View which uses the table function. Now when I run it, the loaded classes increase along with the PermGen, and then the loaded classes are unloaded and PermGen comes back down when it collects! This is good!
So now I need to figure out what the MaxPermGen size needs to be for a larger statement cache.
-----Original Message-----
From: Bergquist, Brett [mailto:BBergquist@canoga.com]
Sent: Wednesday, November 21, 2012 8:40 AM
To: derby-dev@db.apache.org
Subject: RE: Have Derby Network Server having an out of memory (PermGen)
Yes, the statement cache size has been increased to 50K statements so that might be an issue. Maybe the PermGen space will need to be increased because of that. The documentation is not clear which type of heap that the statement cache would affect, however. As a test, I am going to lower my statement cache size to 100 statements and see what happens. Thanks for the idea!
Some more info however. It is definitely related to the View/table function mechanism (explained in my second email). I just did a little more testing and found that if I change the query to use the table function directly instead of using the View that is created that uses the table function, then the loaded classes reported by VisualVM stays stable. Any thoughts on why querying on a View that is created that uses the table function might be generating and holding onto classes whereas using the same query with the View name replaced by the table function name does not have this problem?
I much appreciate the feedback and thoughts.
Brett
-----Original Message-----
From: Knut Anders Hatlen [mailto:knut.hatlen@oracle.com]
Sent: Wednesday, November 21, 2012 8:28 AM
To: derby-dev@db.apache.org
Subject: Re: Have Derby Network Server having an out of memory (PermGen)
"Bergquist, Brett" <BB...@canoga.com> writes:
> I have a customer that is periodically having a problem and unknown to
> me, they have been accessing the Derby database using a query such as
> in the following and have been repeatedly experienced a server issue.
> I finally figured out it was an OutOfMemory error (PermGen). So I just
> wrote a little application that performs the same query over and over
> against a copy of the database and the Derby Network Server. In just a
> few hours, the Derby Network Server gave up the ghost with a
> OutOfMemory(PermGen) error.
>
> This is running against Derby 10.8.2.2. There is no other access to
> the database from any other process during this test.
>
> Note that the query returns no data as there are no records in the
> database that satisfy the query. Also, note that the table NPAResults
> is actually a View that looks like:
>
[...]
>
> Each time I run this sample, after a few hours, the OutOfMemory
> occurs. Any ideas on this will be greatly appreciated.
Hi Brett,
I don't see anything obvious that should cause problems in the code you posted. I tried to run it myself, but it didn't seem to cause any leak in my environment. Of course, I didn't run the exact same code, since I don't know exactly what your table function does. (And I ran it with -XX:MaxPermSize=16M since I didn't want to wait for hours to see the
result...)
If you manage to come up with a full repro that others could run, it might be easier to see what's going on.
By the way, have you changed the statement cache size, or are you running with default size? I'm asking because the size of the statement cache will affect how soon generated classes can be garbage collected.
--
Knut Anders
RE: Have Derby Network Server having an out of memory (PermGen)
Posted by "Bergquist, Brett" <BB...@canoga.com>.
Yes, the statement cache size has been increased to 50K statements so that might be an issue. Maybe the PermGen space will need to be increased because of that. The documentation is not clear which type of heap that the statement cache would affect, however. As a test, I am going to lower my statement cache size to 100 statements and see what happens. Thanks for the idea!
Some more info however. It is definitely related to the View/table function mechanism (explained in my second email). I just did a little more testing and found that if I change the query to use the table function directly instead of using the View that is created that uses the table function, then the loaded classes reported by VisualVM stays stable. Any thoughts on why querying on a View that is created that uses the table function might be generating and holding onto classes whereas using the same query with the View name replaced by the table function name does not have this problem?
I much appreciate the feedback and thoughts.
Brett
-----Original Message-----
From: Knut Anders Hatlen [mailto:knut.hatlen@oracle.com]
Sent: Wednesday, November 21, 2012 8:28 AM
To: derby-dev@db.apache.org
Subject: Re: Have Derby Network Server having an out of memory (PermGen)
"Bergquist, Brett" <BB...@canoga.com> writes:
> I have a customer that is periodically having a problem and unknown to
> me, they have been accessing the Derby database using a query such as
> in the following and have been repeatedly experienced a server issue.
> I finally figured out it was an OutOfMemory error (PermGen). So I just
> wrote a little application that performs the same query over and over
> against a copy of the database and the Derby Network Server. In just a
> few hours, the Derby Network Server gave up the ghost with a
> OutOfMemory(PermGen) error.
>
> This is running against Derby 10.8.2.2. There is no other access to
> the database from any other process during this test.
>
> Note that the query returns no data as there are no records in the
> database that satisfy the query. Also, note that the table NPAResults
> is actually a View that looks like:
>
[...]
>
> Each time I run this sample, after a few hours, the OutOfMemory
> occurs. Any ideas on this will be greatly appreciated.
Hi Brett,
I don't see anything obvious that should cause problems in the code you posted. I tried to run it myself, but it didn't seem to cause any leak in my environment. Of course, I didn't run the exact same code, since I don't know exactly what your table function does. (And I ran it with -XX:MaxPermSize=16M since I didn't want to wait for hours to see the
result...)
If you manage to come up with a full repro that others could run, it might be easier to see what's going on.
By the way, have you changed the statement cache size, or are you running with default size? I'm asking because the size of the statement cache will affect how soon generated classes can be garbage collected.
--
Knut Anders
Re: Have Derby Network Server having an out of memory (PermGen)
Posted by Knut Anders Hatlen <kn...@oracle.com>.
"Bergquist, Brett" <BB...@canoga.com> writes:
> I have a customer that is periodically having a problem and unknown to
> me, they have been accessing the Derby database using a query such as
> in the following and have been repeatedly experienced a server issue.
> I finally figured out it was an OutOfMemory error (PermGen). So I just
> wrote a little application that performs the same query over and over
> against a copy of the database and the Derby Network Server. In just a
> few hours, the Derby Network Server gave up the ghost with a
> OutOfMemory(PermGen) error.
>
> This is running against Derby 10.8.2.2. There is no other access to
> the database from any other process during this test.
>
> Note that the query returns no data as there are no records in the
> database that satisfy the query. Also, note that the table NPAResults
> is actually a View that looks like:
>
[...]
>
> Each time I run this sample, after a few hours, the OutOfMemory
> occurs. Any ideas on this will be greatly appreciated.
Hi Brett,
I don't see anything obvious that should cause problems in the code you
posted. I tried to run it myself, but it didn't seem to cause any leak
in my environment. Of course, I didn't run the exact same code, since I
don't know exactly what your table function does. (And I ran it with
-XX:MaxPermSize=16M since I didn't want to wait for hours to see the
result...)
If you manage to come up with a full repro that others could run, it
might be easier to see what's going on.
By the way, have you changed the statement cache size, or are you
running with default size? I'm asking because the size of the statement
cache will affect how soon generated classes can be garbage collected.
--
Knut Anders
(Updated) RE: Have Derby Network Server having an out of memory
(PermGen)
Posted by "Bergquist, Brett" <BB...@canoga.com>.
So more testing and have found out that this relates to the View/table function. I changed the query to not use the view but rather query one of the underlying tables that the table function is using and there is no class "leak". Memory stays stable.
Here is what is being done via the View/table function:
- There are 53 underlying tables, one for each week of the year of the form "NPARESULTS_WEEK_X" where "X" is the week of the year number.
- A configuration parameter maintains how many weeks are "active" and contain data.
- The table function "PCS_V1.NPARESULTS_TABLE()" uses this configuration parameter and dynamically creates a query that perform a UNION of the "active" week tables.
- The View is built using the "PCS_V1.NPARESULTS_TABLE() " function to hide the underlying dynamics of the query changing based on the current week of the year
This implements a poor man's portioning of the data by week (data is inserted into the correct week table based on the current week of the year), while allowing the rest of the applications accessing the database to be ignorant of this portioning. This all came about because of problems of poor deleting and space reclamation when having a data table inserted 24x7 with many millions of records per day. Using separate week tables allows the system to purge out a week of data by simply using the TRUNCATE TABLE which takes just a couple of seconds to perform and release about 50 million records and be able to reuse the space. The table function came about to dynamically alter the query based on the current week of the year and the configuration parameter of the number of active weeks. It also affords a good place to perform a table lock used as a semaphore while the truncate table is being performed.
So there is something wrong with using the View/table function combination. The table function is generating queries that are changing and for some reason the underlying generated class is not being freed. So if someone can point me in the correct direction in the Derby code to try to find this, it will be most helpful.
From: Bergquist, Brett [mailto:BBergquist@canoga.com]
Sent: Tuesday, November 20, 2012 7:10 PM
To: derby-dev@db.apache.org
Subject: Have Derby Network Server having an out of memory (PermGen)
I have a customer that is periodically having a problem and unknown to me, they have been accessing the Derby database using a query such as in the following and have been repeatedly experienced a server issue. I finally figured out it was an OutOfMemory error (PermGen). So I just wrote a little application that performs the same query over and over against a copy of the database and the Derby Network Server. In just a few hours, the Derby Network Server gave up the ghost with a OutOfMemory(PermGen) error.
This is running against Derby 10.8.2.2. There is no other access to the database from any other process during this test.
Note that the query returns no data as there are no records in the database that satisfy the query. Also, note that the table NPAResults is actually a View that looks like:
CREATE VIEW NPARESULTS
(
ID,
REPORTKEY,
MASTERIP,
BOOTCOUNT,
TESTRESULTID,
PROFILEREFID,
ADDRESSREFID,
STARTDATETIME,
ACCURACYLEVEL,
RESULTFLAG,
PACKETSSENT,
ROUNDTRIPPACKETS,
DROPPEDPACKETS,
OUTOFORDERPACKETS,
MINROUNDTRIPLATENCY,
MAXROUNDTRIPLATENCY,
TOTALROUNDTRIPLATENCY,
AVGROUNDTRIPLATENCY,
LATENCYBUCKETVALUE1,
LATENCYBUCKETVALUE2,
LATENCYBUCKETVALUE3,
LATENCYBUCKETVALUE4,
LATENCYBUCKETVALUE5,
LATENCYBUCKETVALUE6,
LATENCYBUCKETVALUE7,
LATENCYBUCKETVALUE8,
LATENCYBUCKETVALUE9,
LATENCYBUCKETVALUE10,
JITTERMEASUREMENT,
MINLOCALREMOTEJITTER,
MAXLOCALREMOTEJITTER,
TOTALLOCALREMOTEJITTER,
AVGLOCALREMOTEJITTER,
LOCALREMOTEJITTERBUCKETVALUE1,
LOCALREMOTEJITTERBUCKETVALUE2,
LOCALREMOTEJITTERBUCKETVALUE3,
LOCALREMOTEJITTERBUCKETVALUE4,
LOCALREMOTEJITTERBUCKETVALUE5,
LOCALREMOTEJITTERBUCKETVALUE6,
LOCALREMOTEJITTERBUCKETVALUE7,
LOCALREMOTEJITTERBUCKETVALUE8,
LOCALREMOTEJITTERBUCKETVALUE9,
MINREMOTELOCALJITTER,
MAXREMOTELOCALJITTER,
TOTALREMOTELOCALJITTER,
AVGREMOTELOCALJITTER,
REMOTELOCALJITTERBUCKETVALUE1,
REMOTELOCALJITTERBUCKETVALUE2,
REMOTELOCALJITTERBUCKETVALUE3,
REMOTELOCALJITTERBUCKETVALUE4,
REMOTELOCALJITTERBUCKETVALUE5,
REMOTELOCALJITTERBUCKETVALUE6,
REMOTELOCALJITTERBUCKETVALUE7,
REMOTELOCALJITTERBUCKETVALUE8,
REMOTELOCALJITTERBUCKETVALUE9,
CIRCUIT1REFID,
CIRCUIT2REFID,
UNAVAILABLEEXCLUDED
) AS
SELECT
ID,
REPORTKEY,
MASTERIP,
BOOTCOUNT,
TESTRESULTID,
PROFILEREFID,
ADDRESSREFID,
STARTDATETIME,
ACCURACYLEVEL,
RESULTFLAG,
PACKETSSENT,
ROUNDTRIPPACKETS,
DROPPEDPACKETS,
OUTOFORDERPACKETS,
MINROUNDTRIPLATENCY,
MAXROUNDTRIPLATENCY,
TOTALROUNDTRIPLATENCY,
AVGROUNDTRIPLATENCY,
LATENCYBUCKETVALUE1,
LATENCYBUCKETVALUE2,
LATENCYBUCKETVALUE3,
LATENCYBUCKETVALUE4,
LATENCYBUCKETVALUE5,
LATENCYBUCKETVALUE6,
LATENCYBUCKETVALUE7,
LATENCYBUCKETVALUE8,
LATENCYBUCKETVALUE9,
LATENCYBUCKETVALUE10,
JITTERMEASUREMENT,
MINLOCALREMOTEJITTER,
MAXLOCALREMOTEJITTER,
TOTALLOCALREMOTEJITTER,
AVGLOCALREMOTEJITTER,
LOCALREMOTEJITTERBUCKETVALUE1,
LOCALREMOTEJITTERBUCKETVALUE2,
LOCALREMOTEJITTERBUCKETVALUE3,
LOCALREMOTEJITTERBUCKETVALUE4,
LOCALREMOTEJITTERBUCKETVALUE5,
LOCALREMOTEJITTERBUCKETVALUE6,
LOCALREMOTEJITTERBUCKETVALUE7,
LOCALREMOTEJITTERBUCKETVALUE8,
LOCALREMOTEJITTERBUCKETVALUE9,
MINREMOTELOCALJITTER,
MAXREMOTELOCALJITTER,
TOTALREMOTELOCALJITTER,
AVGREMOTELOCALJITTER,
REMOTELOCALJITTERBUCKETVALUE1,
REMOTELOCALJITTERBUCKETVALUE2,
REMOTELOCALJITTERBUCKETVALUE3,
REMOTELOCALJITTERBUCKETVALUE4,
REMOTELOCALJITTERBUCKETVALUE5,
REMOTELOCALJITTERBUCKETVALUE6,
REMOTELOCALJITTERBUCKETVALUE7,
REMOTELOCALJITTERBUCKETVALUE8,
REMOTELOCALJITTERBUCKETVALUE9,
CIRCUIT1REFID,
CIRCUIT2REFID,
UNAVAILABLEEXCLUDED
FROM TABLE (PCS_V1.NPARESULTS_TABLE()) S;
The NPARESULTS_TABLE() is a table function that does a UNION of 5 tables. Just some background.
Since this is a PermGen error, it seem to be related to class generation, which I gather Derby does for queries. Using JVisualVM, I do see the loaded classes go up a good clip and the PermGen used heap go up (it also came down a little and then backup again) and then hit the max. The system is an Oracle M3000 (64Gb of memory) running Java 1.6.0_27 and has the options "-d64 -Xms8192m -Xmx8192m -XX:MaxPermSize=1024m"
Each time I run this sample, after a few hours, the OutOfMemory occurs. Any ideas on this will be greatly appreciated.
Here is the little sample code that I used to cause the condition.
/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
package pmresultsretriever;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
*
* @author brett
*/
public class PMResultsRetriever {
/**
* @param args the command line arguments
*/
public static void main(String[] args) {
new PMResultsRetriever().run();
}
private static final String SQLgetCVReportResults = "SELECT CSEM.ip_to_string(MasterIp) as MASTERIP, "
+ "CSEM.ip_to_string(TestResultIpAddress) as REMOTEIP, "
+ "TestResultVlanId as VLANID, "
+ "ProfileName, "
+ "PCS_V1.NPAResultsProfile.ProfileRefId as PROFILEREFID, "
+ "StartDateTime, "
+ "PCS_V1.NPAResults.ReportKey as ReportKey, "
+ "PacketsSent, "
+ "RoundTripPackets, "
+ "DroppedPackets, "
+ "OutOfOrderPackets, "
+ "case when PacketsSent = 0 then 100 else ((RoundTripPackets * 100) / PacketsSent) end as DATADELIVERY_RATIO, "
+ "LATENCYBUCKETSINUSE, "
+ "JITTERBUCKETSINUSE, "
+ "LatencyBucketValue1, "
+ "LatencyBucketValue2, "
+ "LatencyBucketValue3, "
+ "LatencyBucketValue4, "
+ "LatencyBucketValue5, "
+ "LatencyBucketValue6, "
+ "LatencyBucketValue7, "
+ "LatencyBucketValue8, "
+ "LatencyBucketValue9, "
+ "LatencyBucketValue10, "
+ "MinRoundTripLatency, "
+ "MaxRoundTripLatency, "
+ "TotalRoundTripLatency, "
+ "AvgRoundTripLatency, "
+ "LocalRemoteJitterBucketValue1, "
+ "LocalRemoteJitterBucketValue2, "
+ "LocalRemoteJitterBucketValue3, "
+ "LocalRemoteJitterBucketValue4, "
+ "LocalRemoteJitterBucketValue5, "
+ "LocalRemoteJitterBucketValue6, "
+ "LocalRemoteJitterBucketValue7, "
+ "LocalRemoteJitterBucketValue8, "
+ "LocalRemoteJitterBucketValue9, "
+ "MinLocalRemoteJitter, "
+ "MaxLocalRemoteJitter, "
+ "TotalLocalRemoteJitter, "
+ "AvgLocalRemoteJitter, "
+ "RemoteLocalJitterBucketValue1, "
+ "RemoteLocalJitterBucketValue2, "
+ "RemoteLocalJitterBucketValue3, "
+ "RemoteLocalJitterBucketValue4, "
+ "RemoteLocalJitterBucketValue5, "
+ "RemoteLocalJitterBucketValue6, "
+ "RemoteLocalJitterBucketValue7, "
+ "RemoteLocalJitterBucketValue8, "
+ "RemoteLocalJitterBucketValue9, "
+ "MinRemoteLocalJitter, "
+ "MaxRemoteLocalJitter, "
+ "TotalRemoteLocalJitter, "
+ "AvgRemoteLocalJitter, "
+ "c1.Circuit as Circuit1, "
+ "c2.Circuit as Circuit2, "
+ "TestResultDescription, "
+ "TestResultDescription2, "
+ "ResultFlag, "
+ "ID "
+ " FROM "
+ "PCS_V1.NPAResults_WEEK_1, "
+ "PCS_V1.NPAResultsProfile, "
+ "PCS_V1.NPAResultsAddress, "
+ "PCS_V1.NPAResultsCircuit as c1, "
+ "PCS_V1.NPAResultsCircuit as c2 "
+ " WHERE "
+ "PCS_V1.NPAResults.ProfileRefId = PCS_V1.NPAResultsProfile.ProfileRefId "
+ "AND PCS_V1.NPAResults.AddressRefId = PCS_V1.NPAResultsAddress.AddressRefId "
+ "AND PCS_V1.NPAResults.Circuit1RefId = c1.CircuitRefId "
+ "AND PCS_V1.NPAResults.Circuit2RefId = c2.CircuitRefId "
+ "AND PCS_V1.NPAResults.ID > ? "
+ "AND PCS_V1.NPAResults.ID <= ? "
+ "AND PCS_V1.NPAResults.ProfileRefId in (select ProfileRefId from PCS_V1.NPAResultsProfile where (ProfileName = 'EVPL-RT') or (ProfileName = 'ERS-RT')) ";
private Connection connection;
/**
* Get the value of connection
*
* @return the value of connection
*/
public Connection getConnection() {
return connection;
}
/**
* Set the value of connection
*
* @param connection new value of connection
*/
public void setConnection(Connection connection) {
this.connection = connection;
}
private PreparedStatement stmt;
/**
* Get the value of stmt
*
* @return the value of stmt
*/
public PreparedStatement getStmt() {
return stmt;
}
/**
* Set the value of stmt
*
* @param stmt new value of stmt
*/
public void setStmt(PreparedStatement stmt) {
this.stmt = stmt;
}
public void run() {
loadDbDriver();
createConnection();
createPreparedStatement();
for (int i = 1; i < Integer.MAX_VALUE; i += 5000) {
performQuery(i, i + 4999);
}
}
public void loadDbDriver() {
try {
Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance();
} catch (Exception exception) {
System.err.println("Failed to load database driver:" + exception.toString());
System.exit(1);
}
}
public void createConnection() {
try {
connection = DriverManager.getConnection("jdbc:derby://192.169.1.75:1527/csemdb", "CSEM", "CSEM");
} catch (SQLException ex) {
System.err.println("Failed to create Connection " + ex.toString());
System.exit(1);
}
}
public void createPreparedStatement() {
try {
stmt = connection.prepareStatement(SQLgetCVReportResults);
} catch (SQLException ex) {
System.err.println("Failed to create PreparedStatement " + ex.toString());
}
}
public void performQuery(int from, int to) {
System.out.println("Executing query for results from " + from + " to " + to);
try {
stmt.setInt(1, from);
stmt.setInt(2, to);
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
long id = rs.getLong("ID");
System.out.println("Got result: " + id);
}
rs.close();
} catch (SQLException ex) {
System.err.println("Failed to execute PreparedStatement " + ex.toString());
}
}
}