Thread: Memroy leak with jdbc
Using this test case with 9000 interations I get this output:
used: 474,296 free: 1,557,320 total: 2,031,616 max: 67,108,864
....................................................................................................
used: 4,646,760 free: 3,643,544 total: 8,290,304 max: 67,108,864
=================
used: 4,607,728 free: 3,682,576 total: 8,290,304 max: 67,108,864
I have no idea why this memory leak is happening. It only happens with this one select
statement, not all select statements.
/**
* PostgresMemTest.java
*
*
* Created: Mon May 5 18:40:29 2003
*
* @author <a href="mailto:jks@selectacast.net">Joseph Shraibman</a>
* @version 1.0
*/
import java.sql.*;
public class PostgresMemTest {
transient protected Connection db;
public PostgresMemTest(String url,String usr,String pwd) throws Exception{
// try {
// Load the driver
Class.forName("org.postgresql.Driver");
// } catch (ClassNotFoundException e){
// e.printStackTrace();
//return;
//}
// try{
db = DriverManager.getConnection(url, usr, pwd);
// } catch (SQLException e){
//e.printStackTrace();
//return;
// }
Statement st = null;
st = db.createStatement();
st.executeUpdate("BEGIN;");
st.executeUpdate("create temp table taba(pkey int, puindex int);");
st.executeUpdate("create temp table tabpu(pukey int, ukey int, pu text);");
st.executeUpdate("create temp table tabu(ukey int, akey int);");
st.executeUpdate("insert into taba values(1,2);");
st.executeUpdate("insert into tabu values(3,1);");
st.executeUpdate("insert into tabpu values(2,3,'blah');");
} // PostgresMemTest constructor
void doQuery() throws Exception{
String line = null;
//None of these remmed out lines produce the memory leak
//line = "SELECT 'blah'";
//line = "SELECT (SELECT 'blah');";
//line = "select pu from tabpu where pukey = 2 and ukey = 3";
line = "select (select pu from tabpu where pukey = taba.puindex and ukey =
tabu.ukey) from tabu where ukey = 3 and akey = 1;";
Statement st = null;
st = db.createStatement();
ResultSet rs = st.executeQuery(line);
}
private static String uft(){
Runtime rt = Runtime.getRuntime();
long free = rt.freeMemory(), total = rt.totalMemory(), used = total - free;
long max = rt.maxMemory();
long max2 = max - 67108864;
java.text.NumberFormat nf = java.text.NumberFormat.getInstance() ;
return "used: "+nf.format(used)+" free: "+nf.format(free)+
" total: "+nf.format(total)+" max: "+nf.format(max2);
}
//Usage java PostgresMemTest <iterations> <url> <username> <passwd>
public static void main(String args[])throws Exception{
PostgresMemTest pmt = new PostgresMemTest(args[1],args[2],args[3]);
System.out.println(uft());
int times = Integer.parseInt(args[0]);
final int perc = times/100;
for(int i = 0; i < times; i++){
pmt.doQuery();
if (i % perc == 0){
System.out.print('.');
System.out.flush();
}
}
System.out.println();
System.out.println(uft());
System.gc();
System.gc();
System.gc();
System.out.println("=================");
System.out.println(uft());
}
} // PostgresMemTest
--
Joseph Shraibman
joseph@xtenit.com
Increase signal to noise ratio. http://xis.xtenit.com
Joseph Shraibman wrote: > Using this test case with 9000 interations I get this output: > BTW forgot to mention, this is with postgres 7.3.2 and jdk 1.4.1_02 on linux -- Joseph Shraibman joseph@xtenit.com Increase signal to noise ratio. http://xis.xtenit.com
On Mon, May 05, 2003 at 07:32:25PM -0400, Joseph Shraibman wrote: > Using this test case with 9000 interations I get this output: > > used: 474,296 free: 1,557,320 total: 2,031,616 max: 67,108,864 > .................................................................................................... > used: 4,646,760 free: 3,643,544 total: 8,290,304 max: 67,108,864 > ================= > used: 4,607,728 free: 3,682,576 total: 8,290,304 max: 67,108,864 > > I have no idea why this memory leak is happening. It only happens with this > one select statement, not all select statements. The "leak" is SQLWarning objects piling up on the (single) Connection object you are using: java.sql.SQLWarning: NOTICE: Adding missing FROM-clause entry in subquery for table "taba" (intuitively I'd have thought these should appear on the Statement not the Connection, but I haven't looked at the JDBC spec to check) > line = "select (select pu from tabpu where pukey = taba.puindex and ukey = tabu.ukey) from tabu where ukey = 3and akey = 1;"; > Statement st = null; > st = db.createStatement(); > ResultSet rs = st.executeQuery(line); -O
Joseph Shraibman wrote: > Joseph Shraibman wrote: > >> Using this test case with 9000 interations I get this output: >> > BTW forgot to mention, this is with postgres 7.3.2 and jdk 1.4.1_02 on > linux > > OK I figured out that the warnings held by the Connection weren't being cleared between calls. Why would the warnings we held by the Connection and not the Statement or ResultSet? The warnings were: NOTICE: Adding missing FROM-clause entry in subquery for table "taba" So is the proper fix to clear the warnings from the Connection, or to put the warnings somewhere else in the first place?
Oliver Jowett wrote: > > The "leak" is SQLWarning objects piling up on the (single) Connection object > you are using: Yup, I just figured it out, but thanks anyway. So how do I fix the driver?
Specifically, this code in QueryExecutor:
case 'N': // Error Notification
connection.addWarning(pgStream.ReceiveString(connection.getEncoding()));
break;
At that point the ResultSet doesn't necc. exist, but the Statement does. There are a few
approaches to fixing this:
1) Clear the warnings before executing the query
2) Appeneding the warnings to the Statement
3) Keeping our own warnings chain, then appending the whole chain after the ResultSet is
created.
Personally I perfer #3, but could there be a case where the ResultSet isn't created
because of some other error, and then we don't want to lose the warning?
Joseph,
The reason this is happening is because this particular select statement
is generating a warning message from the server. If you turn on logging
in the driver (add ?loglevel=2 to the jdbc URL) you will see the
following warning generated for each execution of this sql statement:
NOTICE: Adding missing FROM-clause entry in subquery for table "taba"
These warning messages are stored up until you read them, or call
clearWarnings().
This is all correct behavior AFAICT.
thanks,
--Barry
Joseph Shraibman wrote:
> Using this test case with 9000 interations I get this output:
>
> used: 474,296 free: 1,557,320 total: 2,031,616 max: 67,108,864
> ....................................................................................................
>
> used: 4,646,760 free: 3,643,544 total: 8,290,304 max: 67,108,864
> =================
> used: 4,607,728 free: 3,682,576 total: 8,290,304 max: 67,108,864
>
> I have no idea why this memory leak is happening. It only happens with
> this one select statement, not all select statements.
>
>
> /**
> * PostgresMemTest.java
> *
> *
> * Created: Mon May 5 18:40:29 2003
> *
> * @author <a href="mailto:jks@selectacast.net">Joseph Shraibman</a>
> * @version 1.0
> */
> import java.sql.*;
> public class PostgresMemTest {
>
> transient protected Connection db;
>
> public PostgresMemTest(String url,String usr,String pwd) throws
> Exception{
>
> // try {
> // Load the driver
> Class.forName("org.postgresql.Driver");
> // } catch (ClassNotFoundException e){
> // e.printStackTrace();
> //return;
> //}
> // try{
> db = DriverManager.getConnection(url, usr, pwd);
> // } catch (SQLException e){
> //e.printStackTrace();
> //return;
> // }
> Statement st = null;
> st = db.createStatement();
> st.executeUpdate("BEGIN;");
> st.executeUpdate("create temp table taba(pkey int, puindex
> int);");
> st.executeUpdate("create temp table tabpu(pukey int, ukey
> int, pu text);");
> st.executeUpdate("create temp table tabu(ukey int, akey
> int);");
> st.executeUpdate("insert into taba values(1,2);");
> st.executeUpdate("insert into tabu values(3,1);");
> st.executeUpdate("insert into tabpu values(2,3,'blah');");
>
> } // PostgresMemTest constructor
> void doQuery() throws Exception{
> String line = null;
> //None of these remmed out lines produce the memory leak
> //line = "SELECT 'blah'";
> //line = "SELECT (SELECT 'blah');";
> //line = "select pu from tabpu where pukey = 2 and ukey = 3";
> line = "select (select pu from tabpu where pukey = taba.puindex
> and ukey = tabu.ukey) from tabu where ukey = 3 and akey = 1;";
> Statement st = null;
> st = db.createStatement();
> ResultSet rs = st.executeQuery(line);
>
> }
> private static String uft(){
> Runtime rt = Runtime.getRuntime();
> long free = rt.freeMemory(), total = rt.totalMemory(), used =
> total - free;
> long max = rt.maxMemory();
> long max2 = max - 67108864;
>
> java.text.NumberFormat nf = java.text.NumberFormat.getInstance() ;
> return "used: "+nf.format(used)+" free: "+nf.format(free)+
> " total: "+nf.format(total)+" max: "+nf.format(max2);
> }
> //Usage java PostgresMemTest <iterations> <url> <username> <passwd>
> public static void main(String args[])throws Exception{
> PostgresMemTest pmt = new PostgresMemTest(args[1],args[2],args[3]);
> System.out.println(uft());
> int times = Integer.parseInt(args[0]);
> final int perc = times/100;
> for(int i = 0; i < times; i++){
> pmt.doQuery();
> if (i % perc == 0){
> System.out.print('.');
> System.out.flush();
> }
> }
> System.out.println();
> System.out.println(uft());
> System.gc();
> System.gc();
> System.gc();
> System.out.println("=================");
> System.out.println(uft());
>
> }
> } // PostgresMemTest
>
>
>
>
Barry Lind wrote:
> Joseph,
>
> The reason this is happening is because this particular select statement
> is generating a warning message from the server. If you turn on logging
> in the driver (add ?loglevel=2 to the jdbc URL) you will see the
> following warning generated for each execution of this sql statement:
>
> NOTICE: Adding missing FROM-clause entry in subquery for table "taba"
>
> These warning messages are stored up until you read them, or call
> clearWarnings().
>
> This is all correct behavior AFAICT.
>
I think the warnings should be added to the Statement or ResultSet, not the Connection.
I'm trying to hack the cvs version of the driver, but it won't compile for some reason.
Here's the patch:
*** org/postgresql/core/BaseResultSet.java.orig Tue May 6 14:41:13 2003
--- org/postgresql/core/BaseResultSet.java Tue May 6 14:55:02 2003
***************
*** 46,50 ****
public void reInit (Field[] fields, Vector tuples, String status,
int updateCount, long insertOID, boolean binaryCursor);
public void setStatement(BaseStatement statement);
!
}
--- 46,53 ----
public void reInit (Field[] fields, Vector tuples, String status,
int updateCount, long insertOID, boolean binaryCursor);
public void setStatement(BaseStatement statement);
! /**
! * This adds a warning to the warning chain.
! */
! public void addWarnings(java.sql.SQLWarning warnings);
}
*** org/postgresql/core/QueryExecutor.java.orig Tue May 6 14:41:21 2003
--- org/postgresql/core/QueryExecutor.java Tue May 6 15:08:41 2003
***************
*** 148,154 ****
int t = pgStream.ReceiveChar();
break;
case 'N': // Error Notification
! connection.addWarning(pgStream.ReceiveString(connection.getEncoding()));
break;
case 'P': // Portal Name
String pname = pgStream.ReceiveString(connection.getEncoding());
--- 148,155 ----
int t = pgStream.ReceiveChar();
break;
case 'N': // Error Notification
!
//connection.addWarning(pgStream.ReceiveString(connection.getEncoding()));
! statement.addWarnings(new
java.sql.SQLWarning(pgStream.ReceiveString(connection.getEncoding())));
break;
case 'P': // Portal Name
String pname = pgStream.ReceiveString(connection.getEncoding());
But when I try to compile, I get:
-------------------------------------
/usr/local/ant/bin/ant -buildfile ./build.xml all
Buildfile: ./build.xml
all:
prepare:
check_versions:
[available] DEPRECATED - <available> used to override an existing property.
[available] Build file should not reuse the same property name for different values.
check_driver:
driver:
compile:
[javac] Compiling 53 source files to
/home/jks/work/pgtemp/pgsql/src/interfaces/jdbc/build
[javac]
/home/jks/work/pgtemp/pgsql/src/interfaces/jdbc/org/postgresql/core/QueryExecutor.java:152:
cannot resolve symbol
[javac] symbol : method addWarnings (java.sql.SQLWarning)
[javac] location: interface org.postgresql.core.BaseStatement
[javac] statement.addWarnings(new
java.sql.SQLWarning(pgStream.ReceiveString(connection.getEncoding())));
[javac] ^
[javac] Note: Some input files use or override a deprecated API.
[javac] Note: Recompile with -deprecation for details.
[javac] 1 error
BUILD FAILED
file:/home/jks/work/pgtemp/pgsql/src/interfaces/jdbc/build.xml:108: Compile failed; see
the compiler error output for details.
Total time: 16 seconds
make: *** [all] Error 1
-------------------------------------
As you can see I did add addWarnings() to the interface, so why is javac complaining?
This is with java 1.4.1_02. Does anyone know how to use jikes with ant(1.5.1)? I've been
poking around the documentation, but I can't find anything.
> > These warning messages are stored up until you read them, or call > > clearWarnings(). > > > > This is all correct behavior AFAICT. What's the "correct" or "typical" way that people get these warnings out? I mean, who calls clearWarnings() on a connection? Is it part of an exception handler? When an SQLException is thrown, we do process it and often display the potentially chained exceptions, but we've never cleared something. How do you even know that a warning is pending? David
Typically this is the job of the connection broker, when you return the connection the broker should make sure it's "clean", otherwise just close the connection when you are done with it. If you are leaving it open, then I suggest you do a finally after every operation and clean it up. Dave On Tue, 2003-05-06 at 21:11, David Wall wrote: > > > These warning messages are stored up until you read them, or call > > > clearWarnings(). > > > > > > This is all correct behavior AFAICT. > > What's the "correct" or "typical" way that people get these warnings out? I > mean, who calls clearWarnings() on a connection? Is it part of an exception > handler? When an SQLException is thrown, we do process it and often display > the potentially chained exceptions, but we've never cleared something. How > do you even know that a warning is pending? > > David > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster -- Dave Cramer <Dave@micro-automation.net>
Joseph, After rereading the javadoc on Connection.getWarnings(), Statement.getWarnings() and ResultSet.getWarnings(), I agree with you. I have a fix ready to checkin for this. It is basically the same as you are doing below, but using the addWarning(String) method on Statement (I am not sure why you are trying to add a addWarnings(SQLWarning) method). thanks, --Barry Joseph Shraibman wrote: > Barry Lind wrote: > >> Joseph, >> >> The reason this is happening is because this particular select >> statement is generating a warning message from the server. If you >> turn on logging in the driver (add ?loglevel=2 to the jdbc URL) you >> will see the following warning generated for each execution of this >> sql statement: >> >> NOTICE: Adding missing FROM-clause entry in subquery for table "taba" >> >> These warning messages are stored up until you read them, or call >> clearWarnings(). >> >> This is all correct behavior AFAICT. >> > I think the warnings should be added to the Statement or ResultSet, not > the Connection. I'm trying to hack the cvs version of the driver, but it > won't compile for some reason. > > Here's the patch: > *** org/postgresql/core/BaseResultSet.java.orig Tue May 6 14:41:13 2003 > --- org/postgresql/core/BaseResultSet.java Tue May 6 14:55:02 2003 > *************** > *** 46,50 **** > public void reInit (Field[] fields, Vector tuples, String status, > int updateCount, long insertOID, boolean > binaryCursor); > public void setStatement(BaseStatement statement); > ! > } > --- 46,53 ---- > public void reInit (Field[] fields, Vector tuples, String status, > int updateCount, long insertOID, boolean > binaryCursor); > public void setStatement(BaseStatement statement); > ! /** > ! * This adds a warning to the warning chain. > ! */ > ! public void addWarnings(java.sql.SQLWarning warnings); > } > *** org/postgresql/core/QueryExecutor.java.orig Tue May 6 14:41:21 2003 > --- org/postgresql/core/QueryExecutor.java Tue May 6 15:08:41 2003 > *************** > *** 148,154 **** > int t = pgStream.ReceiveChar(); > break; > case 'N': // Error Notification > ! > connection.addWarning(pgStream.ReceiveString(connection.getEncoding())); > break; > case 'P': // Portal Name > String pname = > pgStream.ReceiveString(connection.getEncoding()); > --- 148,155 ---- > int t = pgStream.ReceiveChar(); > break; > case 'N': // Error Notification > ! > //connection.addWarning(pgStream.ReceiveString(connection.getEncoding())); > ! > statement.addWarnings(new > java.sql.SQLWarning(pgStream.ReceiveString(connection.getEncoding()))); > break; > case 'P': // Portal Name > String pname = > pgStream.ReceiveString(connection.getEncoding()); > > > But when I try to compile, I get: > ------------------------------------- > /usr/local/ant/bin/ant -buildfile ./build.xml all > Buildfile: ./build.xml > > all: > > prepare: > > check_versions: > [available] DEPRECATED - <available> used to override an existing property. > [available] Build file should not reuse the same property name for > different values. > > check_driver: > > driver: > > compile: > [javac] Compiling 53 source files to > /home/jks/work/pgtemp/pgsql/src/interfaces/jdbc/build > [javac] > /home/jks/work/pgtemp/pgsql/src/interfaces/jdbc/org/postgresql/core/QueryExecutor.java:152: > cannot resolve symbol > [javac] symbol : method addWarnings (java.sql.SQLWarning) > [javac] location: interface org.postgresql.core.BaseStatement > [javac] > statement.addWarnings(new > java.sql.SQLWarning(pgStream.ReceiveString(connection.getEncoding()))); > [javac] ^ > [javac] Note: Some input files use or override a deprecated API. > [javac] Note: Recompile with -deprecation for details. > [javac] 1 error > > BUILD FAILED > file:/home/jks/work/pgtemp/pgsql/src/interfaces/jdbc/build.xml:108: > Compile failed; see the compiler error output for details. > > Total time: 16 seconds > make: *** [all] Error 1 > ------------------------------------- > As you can see I did add addWarnings() to the interface, so why is javac > complaining? This is with java 1.4.1_02. Does anyone know how to use > jikes with ant(1.5.1)? I've been poking around the documentation, but I > can't find anything. > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html >
Joseph, This is now fixed in cvs. thanks, --Barry Barry Lind wrote: > Joseph, > > After rereading the javadoc on Connection.getWarnings(), > Statement.getWarnings() and ResultSet.getWarnings(), I agree with you. > > I have a fix ready to checkin for this. It is basically the same as you > are doing below, but using the addWarning(String) method on Statement (I > am not sure why you are trying to add a addWarnings(SQLWarning) method). > > thanks, > --Barry > > Joseph Shraibman wrote: > >> Barry Lind wrote: >> >>> Joseph, >>> >>> The reason this is happening is because this particular select >>> statement is generating a warning message from the server. If you >>> turn on logging in the driver (add ?loglevel=2 to the jdbc URL) you >>> will see the following warning generated for each execution of this >>> sql statement: >>> >>> NOTICE: Adding missing FROM-clause entry in subquery for table "taba" >>> >>> These warning messages are stored up until you read them, or call >>> clearWarnings(). >>> >>> This is all correct behavior AFAICT. >>> >> I think the warnings should be added to the Statement or ResultSet, >> not the Connection. I'm trying to hack the cvs version of the driver, >> but it won't compile for some reason. >> >> Here's the patch: >> *** org/postgresql/core/BaseResultSet.java.orig Tue May 6 14:41:13 >> 2003 >> --- org/postgresql/core/BaseResultSet.java Tue May 6 14:55:02 2003 >> *************** >> *** 46,50 **** >> public void reInit (Field[] fields, Vector tuples, String status, >> int updateCount, long insertOID, boolean >> binaryCursor); >> public void setStatement(BaseStatement statement); >> ! >> } >> --- 46,53 ---- >> public void reInit (Field[] fields, Vector tuples, String status, >> int updateCount, long insertOID, boolean >> binaryCursor); >> public void setStatement(BaseStatement statement); >> ! /** >> ! * This adds a warning to the warning chain. >> ! */ >> ! public void addWarnings(java.sql.SQLWarning warnings); >> } >> *** org/postgresql/core/QueryExecutor.java.orig Tue May 6 14:41:21 >> 2003 >> --- org/postgresql/core/QueryExecutor.java Tue May 6 15:08:41 2003 >> *************** >> *** 148,154 **** >> int t = pgStream.ReceiveChar(); >> break; >> case 'N': // Error Notification >> ! >> connection.addWarning(pgStream.ReceiveString(connection.getEncoding())); >> break; >> case 'P': // Portal Name >> String pname = >> pgStream.ReceiveString(connection.getEncoding()); >> --- 148,155 ---- >> int t = pgStream.ReceiveChar(); >> break; >> case 'N': // Error Notification >> ! >> //connection.addWarning(pgStream.ReceiveString(connection.getEncoding())); >> >> ! >> statement.addWarnings(new >> java.sql.SQLWarning(pgStream.ReceiveString(connection.getEncoding()))); >> break; >> case 'P': // Portal Name >> String pname = >> pgStream.ReceiveString(connection.getEncoding()); >> >> >> But when I try to compile, I get: >> ------------------------------------- >> /usr/local/ant/bin/ant -buildfile ./build.xml all >> Buildfile: ./build.xml >> >> all: >> >> prepare: >> >> check_versions: >> [available] DEPRECATED - <available> used to override an existing >> property. >> [available] Build file should not reuse the same property name for >> different values. >> >> check_driver: >> >> driver: >> >> compile: >> [javac] Compiling 53 source files to >> /home/jks/work/pgtemp/pgsql/src/interfaces/jdbc/build >> [javac] >> /home/jks/work/pgtemp/pgsql/src/interfaces/jdbc/org/postgresql/core/QueryExecutor.java:152: >> cannot resolve symbol >> [javac] symbol : method addWarnings (java.sql.SQLWarning) >> [javac] location: interface org.postgresql.core.BaseStatement >> [javac] >> statement.addWarnings(new >> java.sql.SQLWarning(pgStream.ReceiveString(connection.getEncoding()))); >> [javac] ^ >> [javac] Note: Some input files use or override a deprecated API. >> [javac] Note: Recompile with -deprecation for details. >> [javac] 1 error >> >> BUILD FAILED >> file:/home/jks/work/pgtemp/pgsql/src/interfaces/jdbc/build.xml:108: >> Compile failed; see the compiler error output for details. >> >> Total time: 16 seconds >> make: *** [all] Error 1 >> ------------------------------------- >> As you can see I did add addWarnings() to the interface, so why is >> javac complaining? This is with java 1.4.1_02. Does anyone know how >> to use jikes with ant(1.5.1)? I've been poking around the >> documentation, but I can't find anything. >> >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 5: Have you checked our extensive FAQ? >> >> http://www.postgresql.org/docs/faqs/FAQ.html >> > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org >
On Tue, 6 May 2003, Barry Lind wrote: > Joseph, > > This is now fixed in cvs. OK Thanks.