[Pljava-dev] java.lang.OutOfMemoryError

Hello

We use pl java to launch an application

org.postgresql.util.PSQLException: ERREUR: java.lang.OutOfMemoryError
at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1512)
at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1297)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:188)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:437)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:339)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:331)

We have a long treatment in a loop and the memory grow gradually up to a
java.lang.OutOfMemoryError.

What could be the problem ?

- garbage collector ?
- vmoptions ? : pljava.vmoptions = '-Xmx64M'

Our serveur have 1Go RAM and postmaster start with a low consumption of
RAM and grow up to ~300M ...

Thanks for your help

Eric

Eric,
You limit the postmaster to use 64M memory per session. That might be a
problem but it doesn't have to be. It all depends on what your server is
doing. I'm sure it's not the garbage collector (the first thing a JVM
does when it runs short of memory is to collect available garbage).

You have to provide more information. The server log is generally far
more useful then the output from the client that you provided here. The
exact version of the PL/Java, PostgreSQL, and Java VM might be helpful
too. And of course, some hint of what you do when the error occurs.

Common errors include storing tuple data in collections (and thereby
preventing them from being garbage collected). PL/Java 1.2 had a memory
leak that occured when you had functions that returned huge result sets.

Regards,
Thomas Hallgren

Eric BOYER wrote:
> Hello
>
> We use pl java to launch an application
>
> org.postgresql.util.PSQLException: ERREUR: java.lang.OutOfMemoryError
> at
> org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1512)
> at
> org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1297)
> at
> org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:188)
> at
> org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:437)
> at
> org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:339)
> at
> org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:331)
>
> We have a long treatment in a loop and the memory grow gradually up to a
> java.lang.OutOfMemoryError.
>
> What could be the problem ?
>
> - garbage collector ?
> - vmoptions ? : pljava.vmoptions = '-Xmx64M'
>
> Our serveur have 1Go RAM and postmaster start with a low consumption of
> RAM and grow up to ~300M ...
>
> Thanks for your help
>
> Eric
>
> _______________________________________________
> Pljava-dev mailing list
> Pljava-dev at gborg.postgresql.org
> http://gborg.postgresql.org/mailman/listinfo/pljava-dev
>

Thomas Hallgren a ?crit :

>Eric,
>You limit the postmaster to use 64M memory per session.
>
A session is a the trigger ?

> That might be a
>problem but it doesn't have to be. It all depends on what your server is
>doing. I'm sure it's not the garbage collector (the first thing a JVM
>does when it runs short of memory is to collect available garbage).
>
>You have to provide more information. The server log is generally far
>more useful then the output from the client that you provided here. The
>exact version of the PL/Java, PostgreSQL, and Java VM might be helpful
>too. And of course, some hint of what you do when the error occurs.
>
>Common errors include storing tuple data in collections (and thereby
>preventing them from being garbage collected). PL/Java 1.2 had a memory
>leak that occured when you had functions that returned huge result sets.
>
>
We use :
Postgres 8.1
Pljava 1.2.0
jdk 1.4

Our server launch trigger which launch trigger, etc ...
The treatment is long sometimes ...

The java code is a big application which have to manage informations for
an information system

Eric

>Regards,
>Thomas Hallgren
>
>
>Eric BOYER wrote:
>
>
>>Hello
>>
>>We use pl java to launch an application
>>
>>org.postgresql.util.PSQLException: ERREUR: java.lang.OutOfMemoryError
>> at
>>org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1512)
>> at
>>org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1297)
>> at
>>org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:188)
>> at
>>org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:437)
>> at
>>org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:339)
>> at
>>org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:331)
>>
>>We have a long treatment in a loop and the memory grow gradually up to a
>>java.lang.OutOfMemoryError.
>>
>>What could be the problem ?
>>
>>- garbage collector ?
>>- vmoptions ? : pljava.vmoptions = '-Xmx64M'
>>
>>Our serveur have 1Go RAM and postmaster start with a low consumption of
>>RAM and grow up to ~300M ...
>>
>>Thanks for your help
>>
>>Eric
>>
>>_______________________________________________
>>Pljava-dev mailing list
>>Pljava-dev at gborg.postgresql.org
>>http://gborg.postgresql.org/mailman/listinfo/pljava-dev
>>
>>
>>
>
>_______________________________________________
>Pljava-dev mailing list
>Pljava-dev at gborg.postgresql.org
>http://gborg.postgresql.org/mailman/listinfo/pljava-dev
>
>
>

Eric BOYER wrote:
> Thomas Hallgren a ?crit :
>
>> Eric,
>> You limit the postmaster to use 64M memory per session.
>>
> A session is a the trigger ?
>
No, a session is a connection to the database.

> We use :
> Postgres 8.1
> Pljava 1.2.0
> jdk 1.4
>
> Our server launch trigger which launch trigger, etc ...
> The treatment is long sometimes ...
>
I would recommend three things.

1. Upgrade your Java to a 1.5.0_07 version. It performs much better from
a memory consumption perspective.
2. Updgrate to PL/Java 1.3.0
3. If that doesn't help, try -Xmx128M.

Regards,
Thomas Hallgren

Hello,

we use now pljava 1.3 and postgres 8.1.4
we can't use java 1.5 because it depends on xmlrpc 3 which is an alpha
version.

The problem is still there.

We have a big treatment and after a long time, we have this outOfMemory.
We print the warning on the statement :
ERROR - ====>org.postgresql.pljava.internal.ServerException:
java.lang.OutOfMemoryErrorjava.lang.OutOfMemoryError
INFO - Statement WarningWARNING: buffer refcount leak: [779]
(rel=1663/16440/52989, blockNum=3, flags=0x27, refcount=1 2)

What could be this buffer refcount leak.

Is the "buffer refcount leak" the cause of the OufOfMemoryError or its
consequence ?

This long treatment is OK if we have the option : -Xmx512M
If we use -Xmx8M, it crash more quickly !

Thanks for your help

Eric

Thomas Hallgren a ?crit :

> Eric BOYER wrote:
>
>> Thomas Hallgren a ?crit :
>>
>>> Eric,
>>> You limit the postmaster to use 64M memory per session.
>>>
>> A session is a the trigger ?
>>
> No, a session is a connection to the database.
>
>> We use :
>> Postgres 8.1
>> Pljava 1.2.0
>> jdk 1.4
>>
>> Our server launch trigger which launch trigger, etc ...
>> The treatment is long sometimes ...
>>
> I would recommend three things.
>
> 1. Upgrade your Java to a 1.5.0_07 version. It performs much better
> from a memory consumption perspective.
> 2. Updgrate to PL/Java 1.3.0
> 3. If that doesn't help, try -Xmx128M.
>
> Regards,
> Thomas Hallgren
>

Hi Eric,
I just CC'd you on a mail that I posted to the PostgreSQL hackers list
concerning your 'buffer refcount leak'. I'm not sure that's the cause of
your problems though. I need to know more about what you actually do
during your 'long treatment'. Some operations might be very memory
consuming. You're OK with -Xmx512M you say. Have you been able to figure
out where the upper bound is? What happens with -Xmx256M, -Xmx128M, etc.?

Are you doing a lot of XML processing? Do you have code that reuses a
lot of class loaders over and over again (might run out of perm-gen space).

Regards,
Thomas Hallgren

Eric BOYER wrote:
> Hello,
>
> we use now pljava 1.3 and postgres 8.1.4
> we can't use java 1.5 because it depends on xmlrpc 3 which is an alpha
> version.
>
> The problem is still there.
>
> We have a big treatment and after a long time, we have this outOfMemory.
> We print the warning on the statement :
> ERROR - ====>org.postgresql.pljava.internal.ServerException:
> java.lang.OutOfMemoryErrorjava.lang.OutOfMemoryError
> INFO - Statement WarningWARNING: buffer refcount leak: [779]
> (rel=1663/16440/52989, blockNum=3, flags=0x27, refcount=1 2)
>
> What could be this buffer refcount leak.
>
> Is the "buffer refcount leak" the cause of the OufOfMemoryError or its
> consequence ?
>
> This long treatment is OK if we have the option : -Xmx512M
> If we use -Xmx8M, it crash more quickly !
>
> Thanks for your help
>
> Eric
>
>
>
> Thomas Hallgren a ?crit :
>
>> Eric BOYER wrote:
>>
>>> Thomas Hallgren a ?crit :
>>>
>>>> Eric,
>>>> You limit the postmaster to use 64M memory per session.
>>>>
>>> A session is a the trigger ?
>>>
>> No, a session is a connection to the database.
>>
>>> We use :
>>> Postgres 8.1
>>> Pljava 1.2.0
>>> jdk 1.4
>>>
>>> Our server launch trigger which launch trigger, etc ...
>>> The treatment is long sometimes ...
>>>
>> I would recommend three things.
>>
>> 1. Upgrade your Java to a 1.5.0_07 version. It performs much better
>> from a memory consumption perspective.
>> 2. Updgrate to PL/Java 1.3.0
>> 3. If that doesn't help, try -Xmx128M.
>>
>> Regards,
>> Thomas Hallgren
>>
>
>

Thomas Hallgren a ?crit :

>Hi Eric,
>I just CC'd you on a mail that I posted to the PostgreSQL hackers list
>concerning your 'buffer refcount leak'. I'm not sure that's the cause of
>your problems though. I need to know more about what you actually do
>during your 'long treatment'. Some operations might be very memory
>consuming. You're OK with -Xmx512M you say. Have you been able to figure
>out where the upper bound is? What happens with -Xmx256M, -Xmx128M, etc.?
>
>
I will try to explain what are we doing :
We wan't to insert data in a db postgres.
The data come from a LDAP and concern information about computer account.
We are connected to the LDAP and inject data in our db.
In this db, triggers insert some data in the same db but bot in the same
table which can launch triggers again, etc ...

Finally, we generate a new LDAP and an Active Directory !

We have about 5000 people to insert.
with 512, it's OK
for exemple for 256 , it crash for about 2000 account
when we use 128, it crash earlier

Trigger lauch java fonction with an invocation : java.lang.reflect.Method

>Are you doing a lot of XML processing?
>
no

> Do you have code that reuses a
>lot of class loaders over and over again (might run out of perm-gen space).
>
>
Our java trigger lauch method with invocation

>Regards,
>Thomas Hallgren
>
>Eric BOYER wrote:
>
>
>>Hello,
>>
>>we use now pljava 1.3 and postgres 8.1.4
>>we can't use java 1.5 because it depends on xmlrpc 3 which is an alpha
>>version.
>>
>>The problem is still there.
>>
>>We have a big treatment and after a long time, we have this outOfMemory.
>>We print the warning on the statement :
>>ERROR - ====>org.postgresql.pljava.internal.ServerException:
>>java.lang.OutOfMemoryErrorjava.lang.OutOfMemoryError
>>INFO - Statement WarningWARNING: buffer refcount leak: [779]
>>(rel=1663/16440/52989, blockNum=3, flags=0x27, refcount=1 2)
>>
>>What could be this buffer refcount leak.
>>
>>Is the "buffer refcount leak" the cause of the OufOfMemoryError or its
>>consequence ?
>>
>>This long treatment is OK if we have the option : -Xmx512M
>>If we use -Xmx8M, it crash more quickly !
>>
>>Thanks for your help
>>
>>Eric
>>
>>
>>
>>Thomas Hallgren a ?crit :
>>
>>
>>
>>>Eric BOYER wrote:
>>>
>>>
>>>
>>>>Thomas Hallgren a ?crit :
>>>>
>>>>
>>>>
>>>>>Eric,
>>>>>You limit the postmaster to use 64M memory per session.
>>>>>
>>>>>
>>>>>
>>>>A session is a the trigger ?
>>>>
>>>>
>>>>
>>>No, a session is a connection to the database.
>>>
>>>
>>>
>>>>We use :
>>>>Postgres 8.1
>>>>Pljava 1.2.0
>>>>jdk 1.4
>>>>
>>>>Our server launch trigger which launch trigger, etc ...
>>>>The treatment is long sometimes ...
>>>>
>>>>
>>>>
>>>I would recommend three things.
>>>
>>>1. Upgrade your Java to a 1.5.0_07 version. It performs much better
>>>from a memory consumption perspective.
>>>2. Updgrate to PL/Java 1.3.0
>>>3. If that doesn't help, try -Xmx128M.
>>>
>>>Regards,
>>>Thomas Hallgren
>>>
>>>
>>>
>>
>>
>
>_______________________________________________
>Pljava-dev mailing list
>Pljava-dev at gborg.postgresql.org
>http://gborg.postgresql.org/mailman/listinfo/pljava-dev
>
>
>

Hi Eric,
Some followup questions inline:

Eric BOYER wrote:
> I will try to explain what are we doing :
> We wan't to insert data in a db postgres.
> The data come from a LDAP and concern information about computer account.
> We are connected to the LDAP and inject data in our db.
Is this connection established from within a PL/Java function or do you
have a client application for that?

> In this db, triggers insert some data in the same db but bot in the
> same table which can launch triggers again, etc ...
>
> Finally, we generate a new LDAP and an Active Directory !
>
> We have about 5000 people to insert.
How big is your transaction? Is it one insert per commit or do you
insert them all in one go?

> with 512, it's OK
> for exemple for 256 , it crash for about 2000 account
> when we use 128, it crash earlier
>
Sounds like each account consumes 100k of memory or more. Memory that
are never released. Do you have any static variables in your classes
(Map's List's etc.) that might hold on to the data? Are you careful to
close your ResultSet and Statements using try/finally clauses?

Regards,
Thomas Hallgren

Thomas Hallgren a ?crit :

> Hi Eric,
> Some followup questions inline:
>
> Eric BOYER wrote:
>
>> I will try to explain what are we doing :
>> We wan't to insert data in a db postgres.
>> The data come from a LDAP and concern information about computer
>> account.
>> We are connected to the LDAP and inject data in our db.
>
> Is this connection established from within a PL/Java function or do
> you have a client application for that?

within a PL/Java function

>
>> In this db, triggers insert some data in the same db but bot in the
>> same table which can launch triggers again, etc ...
>>
>> Finally, we generate a new LDAP and an Active Directory !
>>
>> We have about 5000 people to insert.
>
> How big is your transaction? Is it one insert per commit or do you
> insert them all in one go?

one insert per commit

>
>> with 512, it's OK
>> for exemple for 256 , it crash for about 2000 account
>> when we use 128, it crash earlier
>>
> Sounds like each account consumes 100k of memory or more. Memory that
> are never released. Do you have any static variables in your classes
> (Map's List's etc.)

a little
We have some static final String, Integer

> that might hold on to the data? Are you careful to close your
> ResultSet and Statements using try/finally clauses?

I will look this again

>
>
> Regards,
> Thomas Hallgren
>
>

just one thing

We always use the fonction execute and never executeUpdate for our
operations ...

is it important ?

Thomas Hallgren a ?crit :

>Hi Eric,
>Some followup questions inline:
>
>Eric BOYER wrote:
>
>
>>I will try to explain what are we doing :
>>We wan't to insert data in a db postgres.
>>The data come from a LDAP and concern information about computer account.
>>We are connected to the LDAP and inject data in our db.
>>
>>
>Is this connection established from within a PL/Java function or do you
>have a client application for that?
>
>
>
>>In this db, triggers insert some data in the same db but bot in the
>>same table which can launch triggers again, etc ...
>>
>>Finally, we generate a new LDAP and an Active Directory !
>>
>>We have about 5000 people to insert.
>>
>>
>How big is your transaction? Is it one insert per commit or do you
>insert them all in one go?
>
>
>
>>with 512, it's OK
>>for exemple for 256 , it crash for about 2000 account
>>when we use 128, it crash earlier
>>
>>
>>
>Sounds like each account consumes 100k of memory or more. Memory that
>are never released. Do you have any static variables in your classes
>(Map's List's etc.) that might hold on to the data? Are you careful to
>close your ResultSet and Statements using try/finally clauses?
>
>Regards,
>Thomas Hallgren
>
>_______________________________________________
>Pljava-dev mailing list
>Pljava-dev at gborg.postgresql.org
>http://gborg.postgresql.org/mailman/listinfo/pljava-dev
>
>
>

Eric BOYER wrote:
> just one thing
>
> We always use the fonction execute and never executeUpdate for our
> operations ...
>
>
> is it important ?
>
It might be. If the query is producing a ResultSet (any 'SELECT ...'
that is not a 'SELECT ... INTO' will do that) and you forget to close
the statement, then you leave the ResultSet dangling.

Regards,
Thomas Hallgren

Thomas Hallgren a ?crit :

>Eric BOYER wrote:
>
>
>>just one thing
>>
>>We always use the fonction execute and never executeUpdate for our
>>operations ...
>>
>>
>>is it important ?
>>
>>
>>
>It might be. If the query is producing a ResultSet (any 'SELECT ...'
>that is not a 'SELECT ... INTO' will do that) and you forget to close
>the statement, then you leave the ResultSet dangling.
>
>
We close all resultset !

The long treatment for the data insertion last about 30 heures !

>Regards,
>Thomas Hallgren
>
>_______________________________________________
>Pljava-dev mailing list
>Pljava-dev at gborg.postgresql.org
>http://gborg.postgresql.org/mailman/listinfo/pljava-dev
>
>
>

Eric BOYER wrote:
> Thomas Hallgren a ?crit :
>
>> Eric BOYER wrote:
>>
>>
>>> just one thing
>>>
>>> We always use the fonction execute and never executeUpdate for our
>>> operations ...
>>>
>>>
>>> is it important ?
>>>
>>>
>> It might be. If the query is producing a ResultSet (any 'SELECT ...'
>> that is not a 'SELECT ... INTO' will do that) and you forget to close
>> the statement, then you leave the ResultSet dangling.
>>
>>
> We close all resultset !

Yes, provided you obtain it. What I meant to say was that the following
code:

stmt.execute("SELECT someFunction()");

will actually create a ResultSet. So unless it's followed by either:

stmt.getResultSet().close();

or simply:

stmt.close(),

you will leave that set dangling. In fact, this code:

stmt.execute("SELECT someFunction1()");
stmt.execute("SELECT someFunction2()");

will leak since there's no close in between. That's a bug in PL/Java,
the execute should make sure that the statement is closed prior to
executing (it does now).

> The long treatment for the data insertion last about 30 heures !
>

For 5000 inserts? That's about 20 seconds per account! I guess you're
mostly waiting on the LDAP connection then?

Regards,
Thomas Hallgren

The connexion with LDAP is fast ...

Our solution is to "cut" our import ...

Thomas Hallgren a ?crit :

>Eric BOYER wrote:
>
>
>>Thomas Hallgren a ?crit :
>>
>>
>>
>>>Eric BOYER wrote:
>>>
>>>
>>>
>>>
>>>>just one thing
>>>>
>>>>We always use the fonction execute and never executeUpdate for our
>>>>operations ...
>>>>
>>>>
>>>>is it important ?
>>>>
>>>>
>>>>
>>>>
>>>It might be. If the query is producing a ResultSet (any 'SELECT ...'
>>>that is not a 'SELECT ... INTO' will do that) and you forget to close
>>>the statement, then you leave the ResultSet dangling.
>>>
>>>
>>>
>>>
>>We close all resultset !
>>
>>
>
>Yes, provided you obtain it. What I meant to say was that the following
>code:
>
> stmt.execute("SELECT someFunction()");
>
>will actually create a ResultSet. So unless it's followed by either:
>
> stmt.getResultSet().close();
>
>or simply:
>
> stmt.close(),
>
>you will leave that set dangling. In fact, this code:
>
> stmt.execute("SELECT someFunction1()");
> stmt.execute("SELECT someFunction2()");
>
>will leak since there's no close in between. That's a bug in PL/Java,
>the execute should make sure that the statement is closed prior to
>executing (it does now).
>
>
>
>>The long treatment for the data insertion last about 30 heures !
>>
>>
>>
>
>For 5000 inserts? That's about 20 seconds per account! I guess you're
>mostly waiting on the LDAP connection then?
>
>Regards,
>Thomas Hallgren
>
>_______________________________________________
>Pljava-dev mailing list
>Pljava-dev at gborg.postgresql.org
>http://gborg.postgresql.org/mailman/listinfo/pljava-dev
>
>
>