Postgres Pro
Downloads
Home   >   mailing lists

Thread: outOfMemoryError

outOfMemoryError

From
"Vidas Makauskas"
Date:
Hi,

I've upgrade
SLES9 with pg73b1jdbc3.jar, IBMjava1.4.2, postgres 7.4.3
to
SLES10 with postgresql-8.1-404.jdbc3.jar, sunjava1.4.2, postgres 8.1.4
and get error in well working program previously with 10,000,000+ rows:
 Exception in thread "main" java.lang.OutOfMemoryError
on ResultSet.insertRow() in application source row after 100,000+ inserted
rows from Oracle to Postgres table.

With java -Xmx128M i can insert 200,000+ rows.
But i need 10,000,000+ rows - previosly inserted without problems.

Then I make suppose what it's memory java managment isue
there fore atempt close and open outputResultSet every 100,000 inserted
records
and reach 1,000,000+ inserted records.

But i need 10,000,000,000+ and with java -Xmx can't.

Next i check with pg73b1jdbc3.jar and get the exacly same result.
I've check SELECT * FROM outputTable WHERE oid=0,
because i need append only.

I found in google "Reduce the amount of memory you need during processing of
the ResultSets".
setFetchSize(1) not impact.
How to do?

Next I plan remove sun java and will install IBM java?
Why my program work previously with default configuration?

What next?

I can't believe that it is problem for me only.
May be I'm too new to java and miss some requirements.

Any advices are wellcome.

Vidas

Re: outOfMemoryError

From
Dave Cramer
Date:
Vidas,

Can you post your basic loop ?

 From what I can see your expectations are a little unrealistic how
do you expect to process 10G rows at a time?

Dave
On 29-Aug-06, at 3:46 AM, Vidas Makauskas wrote:

> Hi,
>
> I've upgrade
> SLES9 with pg73b1jdbc3.jar, IBMjava1.4.2, postgres 7.4.3
> to
> SLES10 with postgresql-8.1-404.jdbc3.jar, sunjava1.4.2, postgres 8.1.4
> and get error in well working program previously with 10,000,000+
> rows:
>  Exception in thread "main" java.lang.OutOfMemoryError
> on ResultSet.insertRow() in application source row after 100,000+
> inserted
> rows from Oracle to Postgres table.
>
> With java -Xmx128M i can insert 200,000+ rows.
> But i need 10,000,000+ rows - previosly inserted without problems.
>
> Then I make suppose what it's memory java managment isue
> there fore atempt close and open outputResultSet every 100,000
> inserted
> records
> and reach 1,000,000+ inserted records.
>
> But i need 10,000,000,000+ and with java -Xmx can't.
>
> Next i check with pg73b1jdbc3.jar and get the exacly same result.
> I've check SELECT * FROM outputTable WHERE oid=0,
> because i need append only.
>
> I found in google "Reduce the amount of memory you need during
> processing of
> the ResultSets".
> setFetchSize(1) not impact.
> How to do?
>
> Next I plan remove sun java and will install IBM java?
> Why my program work previously with default configuration?
>
> What next?
>
> I can't believe that it is problem for me only.
> May be I'm too new to java and miss some requirements.
>
> Any advices are wellcome.
>
> Vidas
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>

Re: outOfMemoryError

From
"Vidas Makauskas"
Date:
> Can you post your basic loop ?
>

ResultSet oracle = <"SELECT * FROM oracle">
ResultSet postgres = <SELECT oid,* FROM postgres WHERE oid=0>
ResultSetMetaData rsmd = postgres.getMetaData();
int vnt = rsmd.getColumnCount();
int rows = 0;
while (oracle.next()) {
<***** SEE BELLOW ******>
 postgres.moveToInsertRow();
 for (int i=1; i <= vnt; i++)
  if ( ! "oid".equals(rsmd.getColumnName(i)))
   if (oracle.getObject(rsmd.getColumnName(i)) != null)
    postgres.updateObject(i, oracle.getObject(rsmd.getColumnName(i)));
 postgres.insertRow();
}

> From what I can see your expectations are a little unrealistic how  do you
> expect to process 10G rows at a time?
rows += 1;
if ( rows > 100000 ) {
 postgres = <SELECT oid,* FROM postgres WHERE oid=0>
 rsmd = postgres.getMetaData();
 rows = 0;
}

Re: outOfMemoryError

From
Dave Cramer
Date:
This design is limited by memory.

You would be far better off inserting directly instead of using a
ResultSet

Dave
On 29-Aug-06, at 9:22 AM, Vidas Makauskas wrote:

>> Can you post your basic loop ?
>>
>
> ResultSet oracle = <"SELECT * FROM oracle">
> ResultSet postgres = <SELECT oid,* FROM postgres WHERE oid=0>
> ResultSetMetaData rsmd = postgres.getMetaData();
> int vnt = rsmd.getColumnCount();
> int rows = 0;
> while (oracle.next()) {
> <***** SEE BELLOW ******>
>  postgres.moveToInsertRow();
>  for (int i=1; i <= vnt; i++)
>   if ( ! "oid".equals(rsmd.getColumnName(i)))
>    if (oracle.getObject(rsmd.getColumnName(i)) != null)
>     postgres.updateObject(i, oracle.getObject(rsmd.getColumnName(i)));
>  postgres.insertRow();
> }
>
>> From what I can see your expectations are a little unrealistic
>> how  do you
>> expect to process 10G rows at a time?
> rows += 1;
> if ( rows > 100000 ) {
>  postgres = <SELECT oid,* FROM postgres WHERE oid=0>
>  rsmd = postgres.getMetaData();
>  rows = 0;
> }
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org
>

Re: outOfMemoryError

From
Luis Vilar Flores
Date:
I wrote a program for moving full databases between different db engines, the main loop for inserting is: 

The main difference is that I use batch insert on prepared statements.

public class Move
{
public static final int MAX_BATCH_ROWS = 4096;


public static move( Connection CON_SRC, Connection CON_DEST, String table )
throws Exception
{
Statement stm = CON_SRC.createStatement( ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY );
ResultSet rs = statement.executeQuery( "SELECT * FROM " + table );
StringBuffer buff = new StringBuffer( "INSERT INTO " );
StringBuffer args = new StringBuffer();
buff.append( table );
buff.append( " ( " );
ResultSetMetaData rsmd = rs.getMetaData();
for( int j = 1; j <= rsmd.getColumnCount(); ++j )
{
if( j != 1 )
{
buff.append( ", " );
args.append( ", " );
}
buff.append( rsmd.getColumnName( j ) );
args.append( "?" );
}
buff.append( " ) VALUES ( " );
buff.append( args );
buff.append( " )" );
String insert = buff.toString();
PreparedStatement pstm = CON_DEST.prepareStatement( insert );
System.out.println( "I: " + i + " " + TABLES[ i ] );
int typesCache[] = new int[ rsmd.getColumnCount() + 1 ];
for( int j = 1; j <= rsmd.getColumnCount(); ++j )
{
typesCache[ j ] = rsmd.getColumnType( j );
}


int rows = 0;
while( rs.next() )
{
for( int j = 1; j < typesCache.length; ++j )
{
Object data = rs.getObject( j );
pstm.setObject( j, rs.getObject( j ), typesCache[ j ] );
}
++rows;
if( ( rows % MAX_BATCH_ROWS ) == 0 )
{
pstm.executeBatch();
}
}
rs.close();
pstm.close();
}


}

Please have attention, this code was copy/paste from 2 classes (my program is multi-threaded for performance - async read/write and multiple tables at once)
This method works on at least 5 million rows.


Luis Flores

Analista de Sistemas

Evolute - Consultoria Informática

Email: lflores@evolute.pt

Tel: (+351) 212949689


AVISO DE CONFIDENCIALIDADE
Esta mensagem de correio electrónico e eventuais ficheiros anexos são confidenciais e destinados apenas à(s) pessoa(s) ou entidade(s) acima referida(s), podendo conter informação privilegiada e confidencial, a qual não poderá ser divulgada, copiada, gravada ou distribuída nos termos da lei vigente. Caso não seja o destinatário da mensagem, ou se ela lhe foi enviada por engano, agradecemos que não faça uso ou divulgação da mesma. A distribuição ou utilização da informação nela contida é interdita. Se recebeu esta mensagem por engano, por favor notifique o remetente e apague este e-mail do seu sistema. Obrigado. 

CONFIDENTIALITY NOTICE
This e-mail transmission and eventual attached files are intended only for the use of the individual(s) or entity(ies) named above and may contain information that is both privileged and confidential and is exempt from disclosure under applicable law. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution or use of any of the information contained in this transmission is strictly restricted. If by any means you have received this transmission in error, please immediately notify the sender and delete this e-mail from your system. Thank you.


Re: outOfMemoryError

From
Kris Jurka
Date:

On Tue, 29 Aug 2006, Dave Cramer wrote:

> This design is limited by memory.
>
> You would be far better off inserting directly instead of using a ResultSet

Actually I think there is a point to be made here.  If the ResultSet is
not scrollable then there is no need to store all the inserts that are
made to it, so there should be no memory limit.

Kris Jurka

Re: outOfMemoryError

From
Dave Cramer
Date:
On 29-Aug-06, at 8:47 PM, Kris Jurka wrote:

>
>
> On Tue, 29 Aug 2006, Dave Cramer wrote:
>
>> This design is limited by memory.
>>
>> You would be far better off inserting directly instead of using a
>> ResultSet
>
> Actually I think there is a point to be made here.  If the
> ResultSet is not scrollable then there is no need to store all the
> inserts that are made to it, so there should be no memory limit.

True, but this is hardly documented. It also suggests adding
complexity for a special case.
>
> Kris Jurka
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>