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());

        }

    }

}