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

        }

    }

}