PostgreSQL JDBC - Memory leak or client side caching?

Author

Memory leak or client side caching?

Kovács Péter

2006-01-11, 9:24 am

Hi,

Executing the following code results in an increase of 1GB in memory
usage by the process.

String sql = "SELECT
cd_id,cd_fp1,cd_fp2,
cd_fp3,cd_fp4,cd_fp5
,cd_fp6,cd_fp7,cd_fp
8,cd_fp9,cd_fp10,cd_
fp11,cd_fp12,cd_fp13
,cd_fp14,cd_fp15,cd_
fp16,cd_smiles
FROM editexample1";
PreparedStatement ps = conn. prepareStatement(sql
);
try {
ResultSet rs = ps.executeQuery();
try {
while (rs.next()) {
int cdId = rs.getInt(1);
for (int i = 0; i < 16; i++) {
int fp = rs.getInt(i+2);
}
String cdSmiles = rs.getString(18);
}
} finally {
rs.close();
}
} finally {
ps.close();
}

The result set contains 1 250 000 rows.

I am not sure if this is a memory leak or the driver just caches the
result set on the client side. If this is the latter, is there a way to
turn off client side caching? If the former, how should I proceed to
find the problem?

Thank you!

Peter

---------------------------(end of broadcast)---------------------------
TIP 1: 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

Roland Walter

2006-01-11, 9:24 am

Kovács Péter <peter.kovacs@chemaxon.hu> writes:

> Hi,
>
> Executing the following code results in an increase of 1GB in memory
> usage by the process.
>
> String sql = "SELECT
> cd_id,cd_fp1,cd_fp2,
cd_fp3,cd_fp4,cd_fp5
,cd_fp6,cd_fp7,cd_fp
8,cd_fp9,cd_fp10,cd_
fp11,cd_fp12,cd_fp13
,cd_fp14,cd_fp15,cd_
fp16,cd_smiles
> FROM editexample1";
> PreparedStatement ps = conn. prepareStatement(sql
);
> try {
> ResultSet rs = ps.executeQuery();
> try {
> while (rs.next()) {
> int cdId = rs.getInt(1);
> for (int i = 0; i < 16; i++) {
> int fp = rs.getInt(i+2);
> }
> String cdSmiles = rs.getString(18);
> }
> } finally {
> rs.close();
> }
> } finally {
> ps.close();
> }
>
> The result set contains 1 250 000 rows.
>
> I am not sure if this is a memory leak or the driver just caches the
> result set on the client side. If this is the latter, is there a way
> to turn off client side caching? If the former, how should I proceed
> to find the problem?
>

If you do not use a cursor, the ResultSet tries to load all rows
into the memory.

Before executing the query you should set autocommit to false and
set the fetchsize on the PreparedStatement to the maximal count
of rows that are loaded into the memory:

con. setAutoCommit(false)
;
ps.setFetchSize(1000);

--
Roland Walter mailto: rwa (at) mosaic-ag (dot) com
MOSAIC SOFTWARE AG phone: +49 (0) 22 25 / 88 2-41 1
Am Pannacker 3 fax: +49 (0) 22 25 / 88 2-20 1
D-53340 Meckenheim http://www.mosaic-ag.com

Die in dieser E-Mail enthaltenen Nachrichten und Anhaenge sind ausschliesslich
fuer den bezeichneten Adressaten bestimmt. Sie koennen rechtlich geschuetzte,
vertrauliche Informationen enthalten. Falls Sie nicht der bezeichnete Empfaenger
oder zum Empfang dieser E-Mail nicht berechtigt sind, ist die Verwendung,
Vervielfaeltigung oder Weitergabe von Nachrichten und Anhaengen untersagt.
Falls Sie diese E-Mail irrtuemlich erhalten haben, informieren Sie bitte
unverzueglich den Absender und vernichten Sie die E-Mail.

This e-mail message and any attachment are intended exclusively for the named
addressee. They may contain confidential information which may also be protected
by professional secrecy. Unless you are the named addressee (or authorised to
receive for the addressee) you may not copy or use this message or any attachment
or disclose the contents to anyone else. If this e-mail was sent to you by mistake
please notify the sender immediately and delete this e-mail.

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Sponsored Links