Thread: Memory usage with Postgres JDBC
Hi,
We are running a Java application which can run against either a Postgres or
Oracle database using jdbc.
I noticed a dramatic difference in memory usage between the two databases,
presumably stemming from the different jdbc drivers.
The simple test program below connects to a database and does a select from
a table containing about 40000 records (select * from mytable). When using
Oracle, the memory usage peaked at about 11Mb. With the Postgres driver it
peaked at 75Mb. I suspect that the PG jdbc driver brings back the entire
ResultSet all at once and keeps it in memory on the client, while the Oracle
driver probably fetches in blocks.
Is there any way to reduce memory usage with Postgres?
I know there is a setFetchSize method in the Statement interface which
likely was intended for this very purpose. Unfortunately, it isn't
implemented by the Postgres JDBC driver (...so much for standards).
Any help on this matter would be greatly appreciated.
By the way, we cannot have different SQL code for the different databases.
It must be standard. So using PostgreSQL specific commands is not an
option.
Cheers,
Mike.
(Other info: In both cases, the application is running on Windows2000. The
Postgres database is on a Linux machine while Oracle is on Windows2000.)
/** Postgres Code **/
import java.sql.*;
import java.io.*;
public class JdbcCheckupPG
{
public static void main (String args [])
throws SQLException, IOException
{
DriverManager.registerDriver(new org.postgresql.Driver());
String user;
String password;
String database;
database="jdbc:postgresql://myserver:1234/mydatabase";
user ="postgres";
password="";
System.out.flush ();
Connection conn = DriverManager.getConnection (database, user,
password);
Statement stmt = conn.createStatement ();
ResultSet rset = stmt.executeQuery ("select * from mytable");
while (rset.next ())
System.out.println (rset.getString (1));
rset.close();
stmt.close();
conn.close();
}
}
/** Oracle Code **/
import java.sql.*;
import java.io.*;
public class JdbcCheckupORA
{
public static void main (String args [])
throws SQLException, IOException
{
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
String user;
String password;
String database;
database="jdbc:oracle:thin:@myserver:1521:mydatabase";
user ="test";
password="test";
Connection conn = DriverManager.getConnection (database, user,
password);
Statement stmt = conn.createStatement ();
ResultSet rset = stmt.executeQuery ("select * from mytable");
while (rset.next ())
System.out.println (rset.getString (1));
rset.close();
stmt.close();
conn.close();
}
}
_________________________________________________________________
MSN Photos is the easiest way to share and print your photos:
http://photos.msn.com/support/worldwide.aspx
Mike,
use cursors, unfortunately you will have to do this manually, as the
driver doesn't support them internally ( yet ).
Dave
On Fri, 2002-07-19 at 10:21, Mike R wrote:
> Hi,
> We are running a Java application which can run against either a Postgres or
> Oracle database using jdbc.
> I noticed a dramatic difference in memory usage between the two databases,
> presumably stemming from the different jdbc drivers.
> The simple test program below connects to a database and does a select from
> a table containing about 40000 records (select * from mytable). When using
> Oracle, the memory usage peaked at about 11Mb. With the Postgres driver it
> peaked at 75Mb. I suspect that the PG jdbc driver brings back the entire
> ResultSet all at once and keeps it in memory on the client, while the Oracle
> driver probably fetches in blocks.
>
> Is there any way to reduce memory usage with Postgres?
> I know there is a setFetchSize method in the Statement interface which
> likely was intended for this very purpose. Unfortunately, it isn't
> implemented by the Postgres JDBC driver (...so much for standards).
>
> Any help on this matter would be greatly appreciated.
> By the way, we cannot have different SQL code for the different databases.
> It must be standard. So using PostgreSQL specific commands is not an
> option.
>
> Cheers,
> Mike.
>
> (Other info: In both cases, the application is running on Windows2000. The
> Postgres database is on a Linux machine while Oracle is on Windows2000.)
>
>
> /** Postgres Code **/
> import java.sql.*;
> import java.io.*;
>
> public class JdbcCheckupPG
> {
> public static void main (String args [])
> throws SQLException, IOException
> {
> DriverManager.registerDriver(new org.postgresql.Driver());
>
> String user;
> String password;
> String database;
>
> database="jdbc:postgresql://myserver:1234/mydatabase";
> user ="postgres";
> password="";
>
> System.out.flush ();
>
> Connection conn = DriverManager.getConnection (database, user,
> password);
> Statement stmt = conn.createStatement ();
> ResultSet rset = stmt.executeQuery ("select * from mytable");
>
> while (rset.next ())
> System.out.println (rset.getString (1));
>
> rset.close();
> stmt.close();
> conn.close();
> }
> }
>
> /** Oracle Code **/
> import java.sql.*;
> import java.io.*;
>
> public class JdbcCheckupORA
> {
> public static void main (String args [])
> throws SQLException, IOException
> {
> DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
>
> String user;
> String password;
> String database;
>
> database="jdbc:oracle:thin:@myserver:1521:mydatabase";
> user ="test";
> password="test";
>
> Connection conn = DriverManager.getConnection (database, user,
> password);
> Statement stmt = conn.createStatement ();
> ResultSet rset = stmt.executeQuery ("select * from mytable");
>
> while (rset.next ())
> System.out.println (rset.getString (1));
>
> rset.close();
> stmt.close();
> conn.close();
> }
> }
>
>
>
> _________________________________________________________________
> MSN Photos is the easiest way to share and print your photos:
> http://photos.msn.com/support/worldwide.aspx
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>
Anyone working on "internal cursor" support? (Or would not it be a better
way to call it "implied cursor" support?)
Pete
-----Original Message-----
From: Dave Cramer [mailto:Dave@micro-automation.net]
Sent: Friday, July 19, 2002 4:51 PM
To: Mike R
Cc: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] Memory usage with Postgres JDBC
Mike,
use cursors, unfortunately you will have to do this manually, as the
driver doesn't support them internally ( yet ).
Dave
On Fri, 2002-07-19 at 10:21, Mike R wrote:
> Hi,
> We are running a Java application which can run against either a Postgres
or
> Oracle database using jdbc.
> I noticed a dramatic difference in memory usage between the two databases,
> presumably stemming from the different jdbc drivers.
> The simple test program below connects to a database and does a select
from
> a table containing about 40000 records (select * from mytable). When
using
> Oracle, the memory usage peaked at about 11Mb. With the Postgres driver
it
> peaked at 75Mb. I suspect that the PG jdbc driver brings back the entire
> ResultSet all at once and keeps it in memory on the client, while the
Oracle
> driver probably fetches in blocks.
>
> Is there any way to reduce memory usage with Postgres?
> I know there is a setFetchSize method in the Statement interface which
> likely was intended for this very purpose. Unfortunately, it isn't
> implemented by the Postgres JDBC driver (...so much for standards).
>
> Any help on this matter would be greatly appreciated.
> By the way, we cannot have different SQL code for the different databases.
> It must be standard. So using PostgreSQL specific commands is not an
> option.
>
> Cheers,
> Mike.
>
> (Other info: In both cases, the application is running on Windows2000.
The
> Postgres database is on a Linux machine while Oracle is on Windows2000.)
>
>
> /** Postgres Code **/
> import java.sql.*;
> import java.io.*;
>
> public class JdbcCheckupPG
> {
> public static void main (String args [])
> throws SQLException, IOException
> {
> DriverManager.registerDriver(new org.postgresql.Driver());
>
> String user;
> String password;
> String database;
>
> database="jdbc:postgresql://myserver:1234/mydatabase";
> user ="postgres";
> password="";
>
> System.out.flush ();
>
> Connection conn = DriverManager.getConnection (database, user,
> password);
> Statement stmt = conn.createStatement ();
> ResultSet rset = stmt.executeQuery ("select * from mytable");
>
> while (rset.next ())
> System.out.println (rset.getString (1));
>
> rset.close();
> stmt.close();
> conn.close();
> }
> }
>
> /** Oracle Code **/
> import java.sql.*;
> import java.io.*;
>
> public class JdbcCheckupORA
> {
> public static void main (String args [])
> throws SQLException, IOException
> {
> DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
>
> String user;
> String password;
> String database;
>
> database="jdbc:oracle:thin:@myserver:1521:mydatabase";
> user ="test";
> password="test";
>
> Connection conn = DriverManager.getConnection (database, user,
> password);
> Statement stmt = conn.createStatement ();
> ResultSet rset = stmt.executeQuery ("select * from mytable");
>
> while (rset.next ())
> System.out.println (rset.getString (1));
>
> rset.close();
> stmt.close();
> conn.close();
> }
> }
>
>
>
> _________________________________________________________________
> MSN Photos is the easiest way to share and print your photos:
> http://photos.msn.com/support/worldwide.aspx
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
I know that Barry Lind has plans to do so, but I'm not sure of his
schedule?
Dave
On Tue, 2002-07-23 at 04:49, Kovács Péter wrote:
> Anyone working on "internal cursor" support? (Or would not it be a better
> way to call it "implied cursor" support?)
>
> Pete
>
> -----Original Message-----
> From: Dave Cramer [mailto:Dave@micro-automation.net]
> Sent: Friday, July 19, 2002 4:51 PM
> To: Mike R
> Cc: pgsql-jdbc@postgresql.org
> Subject: Re: [JDBC] Memory usage with Postgres JDBC
>
>
> Mike,
>
> use cursors, unfortunately you will have to do this manually, as the
> driver doesn't support them internally ( yet ).
>
> Dave
> On Fri, 2002-07-19 at 10:21, Mike R wrote:
> > Hi,
> > We are running a Java application which can run against either a Postgres
> or
> > Oracle database using jdbc.
> > I noticed a dramatic difference in memory usage between the two databases,
>
> > presumably stemming from the different jdbc drivers.
> > The simple test program below connects to a database and does a select
> from
> > a table containing about 40000 records (select * from mytable). When
> using
> > Oracle, the memory usage peaked at about 11Mb. With the Postgres driver
> it
> > peaked at 75Mb. I suspect that the PG jdbc driver brings back the entire
> > ResultSet all at once and keeps it in memory on the client, while the
> Oracle
> > driver probably fetches in blocks.
> >
> > Is there any way to reduce memory usage with Postgres?
> > I know there is a setFetchSize method in the Statement interface which
> > likely was intended for this very purpose. Unfortunately, it isn't
> > implemented by the Postgres JDBC driver (...so much for standards).
> >
> > Any help on this matter would be greatly appreciated.
> > By the way, we cannot have different SQL code for the different databases.
>
> > It must be standard. So using PostgreSQL specific commands is not an
> > option.
> >
> > Cheers,
> > Mike.
> >
> > (Other info: In both cases, the application is running on Windows2000.
> The
> > Postgres database is on a Linux machine while Oracle is on Windows2000.)
> >
> >
> > /** Postgres Code **/
> > import java.sql.*;
> > import java.io.*;
> >
> > public class JdbcCheckupPG
> > {
> > public static void main (String args [])
> > throws SQLException, IOException
> > {
> > DriverManager.registerDriver(new org.postgresql.Driver());
> >
> > String user;
> > String password;
> > String database;
> >
> > database="jdbc:postgresql://myserver:1234/mydatabase";
> > user ="postgres";
> > password="";
> >
> > System.out.flush ();
> >
> > Connection conn = DriverManager.getConnection (database, user,
> > password);
> > Statement stmt = conn.createStatement ();
> > ResultSet rset = stmt.executeQuery ("select * from mytable");
> >
> > while (rset.next ())
> > System.out.println (rset.getString (1));
> >
> > rset.close();
> > stmt.close();
> > conn.close();
> > }
> > }
> >
> > /** Oracle Code **/
> > import java.sql.*;
> > import java.io.*;
> >
> > public class JdbcCheckupORA
> > {
> > public static void main (String args [])
> > throws SQLException, IOException
> > {
> > DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
> >
> > String user;
> > String password;
> > String database;
> >
> > database="jdbc:oracle:thin:@myserver:1521:mydatabase";
> > user ="test";
> > password="test";
> >
> > Connection conn = DriverManager.getConnection (database, user,
> > password);
> > Statement stmt = conn.createStatement ();
> > ResultSet rset = stmt.executeQuery ("select * from mytable");
> >
> > while (rset.next ())
> > System.out.println (rset.getString (1));
> >
> > rset.close();
> > stmt.close();
> > conn.close();
> > }
> > }
> >
> >
> >
> > _________________________________________________________________
> > MSN Photos is the easiest way to share and print your photos:
> > http://photos.msn.com/support/worldwide.aspx
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> >
> >
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
>
Peter,
It is something I would like to see done for 7.3. I am not sure if I
will have the time to do it though. If you want to work on it that
would be great. I have a couple of ideas on how to implement it such
that you can turn it on or off via the standard jdbc api.
So my ideas are that by default Statement objects work as they do today
(i.e. do not use a cursor and return the entire result set). However if
the user of the driver calls Statement.setFetchSize() this would turn on
using 'implied cusors' and would result in fetches of fetchsize being
used. Perhaps there should be a way to set a default value for fetch
size for all Statements so that you could turn on 'implied cursors' for
all statements instead of individually.
thanks,
--Barry
Kovács Péter wrote:
>Anyone working on "internal cursor" support? (Or would not it be a better
>way to call it "implied cursor" support?)
>
>Pete
>
>-----Original Message-----
>From: Dave Cramer [mailto:Dave@micro-automation.net]
>Sent: Friday, July 19, 2002 4:51 PM
>To: Mike R
>Cc: pgsql-jdbc@postgresql.org
>Subject: Re: [JDBC] Memory usage with Postgres JDBC
>
>
>Mike,
>
>use cursors, unfortunately you will have to do this manually, as the
>driver doesn't support them internally ( yet ).
>
>Dave
>On Fri, 2002-07-19 at 10:21, Mike R wrote:
>
>
>>Hi,
>>We are running a Java application which can run against either a Postgres
>>
>>
>or
>
>
>>Oracle database using jdbc.
>>I noticed a dramatic difference in memory usage between the two databases,
>>
>>
>
>
>
>>presumably stemming from the different jdbc drivers.
>>The simple test program below connects to a database and does a select
>>
>>
>from
>
>
>>a table containing about 40000 records (select * from mytable). When
>>
>>
>using
>
>
>>Oracle, the memory usage peaked at about 11Mb. With the Postgres driver
>>
>>
>it
>
>
>>peaked at 75Mb. I suspect that the PG jdbc driver brings back the entire
>>ResultSet all at once and keeps it in memory on the client, while the
>>
>>
>Oracle
>
>
>>driver probably fetches in blocks.
>>
>>Is there any way to reduce memory usage with Postgres?
>>I know there is a setFetchSize method in the Statement interface which
>>likely was intended for this very purpose. Unfortunately, it isn't
>>implemented by the Postgres JDBC driver (...so much for standards).
>>
>>Any help on this matter would be greatly appreciated.
>>By the way, we cannot have different SQL code for the different databases.
>>
>>
>
>
>
>>It must be standard. So using PostgreSQL specific commands is not an
>>option.
>>
>>Cheers,
>>Mike.
>>
>>(Other info: In both cases, the application is running on Windows2000.
>>
>>
>The
>
>
>>Postgres database is on a Linux machine while Oracle is on Windows2000.)
>>
>>
>>/** Postgres Code **/
>>import java.sql.*;
>>import java.io.*;
>>
>>public class JdbcCheckupPG
>>{
>> public static void main (String args [])
>> throws SQLException, IOException
>> {
>> DriverManager.registerDriver(new org.postgresql.Driver());
>>
>> String user;
>> String password;
>> String database;
>>
>> database="jdbc:postgresql://myserver:1234/mydatabase";
>> user ="postgres";
>> password="";
>>
>> System.out.flush ();
>>
>> Connection conn = DriverManager.getConnection (database, user,
>>password);
>> Statement stmt = conn.createStatement ();
>> ResultSet rset = stmt.executeQuery ("select * from mytable");
>>
>> while (rset.next ())
>> System.out.println (rset.getString (1));
>>
>> rset.close();
>> stmt.close();
>> conn.close();
>> }
>>}
>>
>>/** Oracle Code **/
>>import java.sql.*;
>>import java.io.*;
>>
>>public class JdbcCheckupORA
>>{
>> public static void main (String args [])
>> throws SQLException, IOException
>> {
>> DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
>>
>> String user;
>> String password;
>> String database;
>>
>> database="jdbc:oracle:thin:@myserver:1521:mydatabase";
>> user ="test";
>> password="test";
>>
>> Connection conn = DriverManager.getConnection (database, user,
>>password);
>> Statement stmt = conn.createStatement ();
>> ResultSet rset = stmt.executeQuery ("select * from mytable");
>>
>> while (rset.next ())
>> System.out.println (rset.getString (1));
>>
>> rset.close();
>> stmt.close();
>> conn.close();
>> }
>>}
>>
>>
>>
>>_________________________________________________________________
>>MSN Photos is the easiest way to share and print your photos:
>>http://photos.msn.com/support/worldwide.aspx
>>
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>>
>>
>>
>>
>
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: if posting/reading through Usenet, please send an appropriate
>subscribe-nomail command to majordomo@postgresql.org so that your
>message can get through to the mailing list cleanly
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: Have you searched our list archives?
>
>http://archives.postgresql.org
>
>
>