db-derby-dev mailing list archives

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



Mime
View raw message