db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bergquist, Brett" <BBergqu...@canoga.com>
Subject Have Derby Network Server having an out of memory (PermGen)
Date Wed, 21 Nov 2012 00:09:53 GMT
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());
        }
    }
}



Mime
View raw message