[Pljava-dev] out of memory

Hello Thomas,
I have a problem with pljava - it seems that it takes to much memory,
which cause that postgresql process hangs.

This is what I have in serverlog:
SPI TupTable: 8192 total in 1 blocks; 7352 free (0 chunks); 840 used
Operator class cache: 8192 total in 1 blocks; 4936 free (0 chunks); 3256
used
MessageContext: 8192 total in 1 blocks; 6400 free (3 chunks); 1792 used
smgr relation table: 8192 total in 1 blocks; 808 free (0 chunks); 7384 used
Portal hash: 8380416 total in 10 blocks; 1501976 free (34 chunks);
6878440 used
PortalMemory: 8380416 total in 10 blocks; 1325528 free (14 chunks);
7054888 used
PortalHeapMemory: 15360 total in 4 blocks; 8064 free (0 chunks); 7296 used
ExecutorState: 24576 total in 2 blocks; 12352 free (2 chunks); 12224 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
PortalHeapMemory: 15360 total in 4 blocks; 8064 free (0 chunks); 7296 used
ExecutorState: 24576 total in 2 blocks; 12352 free (2 chunks); 12224 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
PortalHeapMemory: 15360 total in 4 blocks; 8064 free (0 chunks); 7296 used
ExecutorState: 24576 total in 2 blocks; 12352 free (2 chunks); 12224 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
PortalHeapMemory: 15360 total in 4 blocks; 8064 free (0 chunks); 7296 used
ExecutorState: 24576 total in 2 blocks; 12352 free (2 chunks); 12224 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
... and so on...

The code is very simple, I execute the query (large query, with ~120000
rows) and process each single row:

Connection connection =
DriverManager.getConnection("jdbc:default:connection");
Statement statement = connection.createStatement();
ResultSet result;

statement.execute("....");
result = statement.getResultSet();

// process each row
while (result.next()) {
... some other things, new Statements (but each single statement is
closed)
}

statement.close();

Any ideas ?

Best regards,
Marek Lewczuk

Hi Marek,
The memory consumption shown in the serverlog doesn't seem to be very
high. How much memory is the process consuming all in all?

Which version of PL/Java are you running? There is a known memory leak
in 1.2 that has been fixed. So if you're using that, perhaps you could
try the latest from cvs?

You create nested statements that you close. Any chance you leave a
ResultSet open somewhere (should be closed when it's statement is
closed, I'm just trying to eliminate possibilities)?

Kind Regards,
Thomas Hallgren

Marek Lewczuk wrote:
> Hello Thomas,
> I have a problem with pljava - it seems that it takes to much memory,
> which cause that postgresql process hangs.
>
> This is what I have in serverlog:
> SPI TupTable: 8192 total in 1 blocks; 7352 free (0 chunks); 840 used
> Operator class cache: 8192 total in 1 blocks; 4936 free (0 chunks); 3256
> used
> MessageContext: 8192 total in 1 blocks; 6400 free (3 chunks); 1792 used
> smgr relation table: 8192 total in 1 blocks; 808 free (0 chunks); 7384 used
> Portal hash: 8380416 total in 10 blocks; 1501976 free (34 chunks);
> 6878440 used
> PortalMemory: 8380416 total in 10 blocks; 1325528 free (14 chunks);
> 7054888 used
> PortalHeapMemory: 15360 total in 4 blocks; 8064 free (0 chunks); 7296 used
> ExecutorState: 24576 total in 2 blocks; 12352 free (2 chunks); 12224 used
> ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
> PortalHeapMemory: 15360 total in 4 blocks; 8064 free (0 chunks); 7296 used
> ExecutorState: 24576 total in 2 blocks; 12352 free (2 chunks); 12224 used
> ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
> PortalHeapMemory: 15360 total in 4 blocks; 8064 free (0 chunks); 7296 used
> ExecutorState: 24576 total in 2 blocks; 12352 free (2 chunks); 12224 used
> ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
> PortalHeapMemory: 15360 total in 4 blocks; 8064 free (0 chunks); 7296 used
> ExecutorState: 24576 total in 2 blocks; 12352 free (2 chunks); 12224 used
> ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
> ... and so on...
>
> The code is very simple, I execute the query (large query, with ~120000
> rows) and process each single row:
>
> Connection connection =
> DriverManager.getConnection("jdbc:default:connection");
> Statement statement = connection.createStatement();
> ResultSet result;
>
> statement.execute("....");
> result = statement.getResultSet();
>
> // process each row
> while (result.next()) {
> ... some other things, new Statements (but each single statement is
> closed)
> }
>
> statement.close();
>
> Any ideas ?
>
> Best regards,
> Marek Lewczuk
>
> _______________________________________________
> Pljava-dev mailing list
> Pljava-dev at gborg.postgresql.org
> http://gborg.postgresql.org/mailman/listinfo/pljava-dev
>

Thomas Hallgren napisa?(a):
> Hi Marek,
> The memory consumption shown in the serverlog doesn't seem to be very
> high. How much memory is the process consuming all in all?
If it isn't high, so why pljava hangs - it doesn't throw any error,
postgresql process is working but pljava function do nothing (I need to
kill process manually in order unlock postgresql resources). I will try
to execute it again and see how much memory it consumes.

> Which version of PL/Java are you running? There is a known memory leak
> in 1.2 that has been fixed. So if you're using that, perhaps you could
> try the latest from cvs?
Latest, stable release - 1.2. My pljava function doesn't return a set,
so I don't think that this will fix my problem.

> You create nested statements that you close. Any chance you leave a
> ResultSet open somewhere (should be closed when it's statement is
> closed, I'm just trying to eliminate possibilities)?
I close every statement and this should close automatically all
ResultSet related to the statement.

Best regards,
ML

Marek Lewczuk wrote:
> If it isn't high, so why pljava hangs - it doesn't throw any error,
> postgresql process is working but pljava function do nothing (I need
> to kill process manually in order unlock postgresql resources). I will
> try to execute it again and see how much memory it consumes.
>
I don't know why it hangs. An out-of-memory would typically cause a
termination, not a hanging. Which JVM are you using and on which
platform? What is the behavior of your hung process? Is it consuming
lots of CPU cycles or none at all?

> Latest, stable release - 1.2. My pljava function doesn't return a set,
> so I don't think that this will fix my problem.
>
Well, if you have a chance, please try out the new stuff. The fix
regarding set returning functions is not the only change since 1.2 and
I'd hate to spend time on something that's not an issue in the new code.

> I close every statement and this should close automatically all
> ResultSet related to the statement.
OK, to rephrase my question - Do you rely on that ResultSets are closed
by the Statement close or do you close them too explicitly?

Regards,
Thomas Hallgren

Thomas Hallgren napisa?(a):
> I don't know why it hangs. An out-of-memory would typically cause a
> termination, not a hanging. Which JVM are you using and on which
> platform? What is the behavior of your hung process? Is it consuming
> lots of CPU cycles or none at all?

This "top" was taken after start:
-------------
top - 07:46:50 up 73 days, 14:31, 1 user, load average: 1.39, 0.67, 0.24
Tasks: 98 total, 5 running, 93 sleeping, 0 stopped, 0 zombie
Cpu(s): 47.3% user, 5.7% system, 0.0% nice, 47.0% idle
Mem: 904848k total, 898940k used, 5908k free, 608k buffers
Swap: 2048276k total, 348036k used, 1700240k free, 29460k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
29590 postgres 17 0 1133m 806m 18m R 95.0 91.2 2:58.75 postmaster
------------

After couple of minutes pljava stops (but no error is returned) and
postgresl process is still working:
------------
top - 07:55:38 up 73 days, 14:40, 1 user, load average: 1.23, 1.42, 0.83
Tasks: 105 total, 1 running, 104 sleeping, 0 stopped, 0 zombie
Cpu(s): 0.5% user, 4.4% system, 0.0% nice, 95.2% idle
Mem: 904848k total, 898668k used, 6180k free, 1068k buffers
Swap: 2048276k total, 1794380k used, 253896k free, 50136k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
29590 postgres 10 0 2472m 752m 34m D 0.6 85.2 8:55.40 postmaster
------------

> Well, if you have a chance, please try out the new stuff. The fix
> regarding set returning functions is not the only change since 1.2 and
> I'd hate to spend time on something that's not an issue in the new code.
I did that - but it doesn't affect the behavior of my function.

> OK, to rephrase my question - Do you rely on that ResultSets are closed
> by the Statement close
Yes

>or do you close them too explicitly?
No... should I do it ?

ML

Marek,
You definitely have a memory leak somewhere. The process consumes 2.5
Gigabytes when it seems to hang. From the looks of it, it doesn't
actually hang but it is very, very slow (consumes 0.6 %CPU). Your
main-thread may well hang at this point, the rest of the activity might
be a struggling garbage collector. Is there any chance that you have a
huge map or array building up somewhere?

>
> I did that - but it doesn't affect the behavior of my function.
>
OK, good to know.

>> or do you close them too explicitly?
> No... should I do it ?
Not normally no. As I said previously, I'm trying to eliminate possible
causes for the leak. The more I know about your code and your setup, the
better the prognoses that I find something. Ideally, I'd like to look at
your function. You still keep your configuration a secret. What system
are you using? PostgreSQL version, OS version, Java version?

- thomas

Thomas Hallgren napisa?(a):
> Marek,
> You definitely have a memory leak somewhere. The process consumes 2.5
> Gigabytes when it seems to hang. From the looks of it, it doesn't
> actually hang but it is very, very slow (consumes 0.6 %CPU). Your
> main-thread may well hang at this point, the rest of the activity might
> be a struggling garbage collector. Is there any chance that you have a
> huge map or array building up somewhere?
There are no maps at all, but I find the part of the code which (when
enabled) leads to memory leak.

// insert into regional registrations table
{
Statement s = connection.createStatement();

ResultSet r = s.executeQuery("SELECT oCarTypeId FROM
samarapp_data.sapp_cda_pl_cep_rre WHERE oCarTypeId = " + co + " AND
nCarTypeId = " + cn + " AND nMakeId = " + mk + " AND nModelId = " + mo +
" AND nDistrictW = " + rw + " AND nDistrictP = " + rp + " AND
productionYear = " + result.getInt("productionyear") + " AND
registrationYear = " + result.getInt("registrationyear"));

if (r.next() == false) s.execute("INSERT INTO
samarapp_data.sapp_cda_pl_cep_rre (oCarTypeId, nCarTypeId, nMakeId,
nModelId, nDistrictW, nDistrictP, productionYear, registrationYear,
registrationMonth" + result.getInt("registrationmonth") + ") VALUES (" +
co + "," + cn + "," + mk + "," + mo + "," + rw + "," + rp + "," +
result.getInt("productionyear") + "," +
result.getInt("registrationyear") + "," +
result.getInt("registrationamount") + ")");

else s.execute("UPDATE samarapp_data.sapp_cda_pl_cep_rre SET
registrationMonth" + result.getInt("registrationmonth") + "=" +
result.getInt("registrationamount") + " WHERE oCarTypeId = " + co + "
AND nCarTypeId = " + cn + " AND nMakeId = " + mk + " AND nModelId = " +
mo + " AND nDistrictW = " + rw + " AND nDistrictP = " + rp + " AND
productionYear = " + result.getInt("productionyear") + " AND
registrationYear = " + result.getInt("registrationyear"));

r.close();

s.close();
}

I thought that change from "execute" to "executeUpdate" will fix this
but unfortunatelly it didn't. Would you like to see the full source code ?

> Not normally no. As I said previously, I'm trying to eliminate possible
> causes for the leak. The more I know about your code and your setup, the
> better the prognoses that I find something. Ideally, I'd like to look at
> your function. You still keep your configuration a secret. What system
> are you using? PostgreSQL version, OS version, Java version?
PostgreSQL 8.1.0, Linux Debian, Java JDK1.5.0_04

Marek Lewczuk napisa?(a):
> Thomas Hallgren napisa?(a):
> There are no maps at all, but I find the part of the code which (when
> enabled) leads to memory leak.
One more thing to add... there are 3 hashtables but they are quite small
(each contains about 100 elements). The source code which I have send in
my previous mail will be executed about 120 000 times.

ML

Hi Marek,
I'll look into this. One last question before I do. In the code that you
sent, do you ever get an exception? I.e. is there any chance that it
might miss the close of the Statement and ResultSet?

Regards,
Thomas Hallgren

Marek Lewczuk wrote:
> Marek Lewczuk napisa?(a):
>> Thomas Hallgren napisa?(a):
>> There are no maps at all, but I find the part of the code which (when
>> enabled) leads to memory leak.
> One more thing to add... there are 3 hashtables but they are quite
> small (each contains about 100 elements). The source code which I have
> send in my previous mail will be executed about 120 000 times.
>
> ML
>
>

Thomas Hallgren napisa?(a):
> Hi Marek,
> I'll look into this. One last question before I do. In the code that you
> sent, do you ever get an exception? I.e. is there any chance that it
> might miss the close of the Statement and ResultSet?
No... I will send you full source code...

Marek Lewczuk napisa?(a):
> Thomas Hallgren napisa?(a):
>> I'll look into this. One last question before I do. In the code that you
>> sent, do you ever get an exception? I.e. is there any chance that it
>> might miss the close of the Statement and ResultSet?
Hi Thomas,
any solution for my problem ? Can you reproduce a memory leak ? It seems
that the problem is not in the java code but in a huge number of rows
that are inserted within this code. I will try to prepare a test case.

ML

Marek Lewczuk wrote:
> any solution for my problem ? Can you reproduce a memory leak ? It seems
> that the problem is not in the java code but in a huge number of rows
> that are inserted within this code. I will try to prepare a test case.
>
No, nothing yet (awaiting the full source you said you'd send). But yes, please prepare a
test case. It will save me some time and I'll will have something that really verifies that
the problem is solved. Test cases are always very welcome.

Regards,
Thomas Hallgren