SEO

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

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
>


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

> 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;
}

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
>


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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.

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


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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
>


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend