Hello!

We are having a lot of trouble trying to use JDBC with PostgreSQL.

This is what we want to achieve:

- Select all rows in a table (people)
- Select addresses belonging to each person. By subselect or join
(addresses)
- Select phone numbers belonging to each person. By subselect or join
(contacts)
- Loop through the results, building a Lucene index.


Our tables:
- people: ~ 8 million records (could be larger in the future, maybe
50 million)
- addresses ~8 million records
- contacts: ~10 million records


Hardware:
2x dual core 3GHz intel Xeon
1-2 Gb RAM



Problem:

It seems that the PostgreSQL driver fetches the whole result, storing
it in memory, causing Java to run out of memory.
java.lang.OutOfMemoryError
We have tried increasing memory for the program (-Xmn100M -Xms512M -
Xmx512M), but we only have one gigabyte of memory to work with.

Does anyone have any suggestions on how to deal with this, other that
throwing more hardware at the problem? Cursors?


Any help is much appreciated!
Thanks

Bendik Rognlien Johansen schrieb:
> Hello!
>
> We are having a lot of trouble trying to use JDBC with PostgreSQL.
>
> This is what we want to achieve:
>
> - Select all rows in a table (people)
> - Select addresses belonging to each person. By subselect or join
> (addresses)
> - Select phone numbers belonging to each person. By subselect or join
> (contacts)
> - Loop through the results, building a Lucene index.
>
>
> Our tables:
> - people: ~ 8 million records (could be larger in the future, maybe 50
> million)
> - addresses ~8 million records
> - contacts: ~10 million records
>
>
> Hardware:
> 2x dual core 3GHz intel Xeon
> 1-2 Gb RAM
>
>
>
> Problem:
>
> It seems that the PostgreSQL driver fetches the whole result, storing
> it in memory, causing Java to run out of
> memory. java.lang.OutOfMemoryError
> We have tried increasing memory for the program (-Xmn100M -Xms512M
> -Xmx512M), but we only have one gigabyte of memory to work with.
>
> Does anyone have any suggestions on how to deal with this, other that
> throwing more hardware at the problem? Cursors?
>
>
> Any help is much appreciated!
> Thanks
>
Look here:

http://archives.postgresql.org/pgsql...1/msg00004.php

--
Roland Walter mailto: rwa (at) mosaic-ag (dot) com
MOSAIC SOFTWARE AG phone: +49 (0) 22 25 / 88 2-44 9
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 2: Don't 'kill -9' the postmaster

Thank you both!
I will have a closer look tomorrow
.



On Aug 24, 2006, at 10:38 AM, Koth, Christian (DWBI) wrote:
>
> Bendik,
>
> - Loop through the results, building a Lucene index.
>
> I guess you are trying to search within the contact data and
> therefore using lucene? Why not using the database build in
> possibilities for searching using SQL?
>
>
> - Does anyone have any suggestions on how to deal with this, other
> that throwing more hardware at the problem? Cursors?
>
> We are using cursors to access large result sets which works quite
> well. Also with postgres JDBC you can use scrollable result sets
> (only forward scrolling) and a limited fetch size.
>
> Christian
>






On Aug 24, 2006, at 10:30 AM, Roland Walter wrote:

> Bendik Rognlien Johansen schrieb:
>> Hello!
>>
>> We are having a lot of trouble trying to use JDBC with PostgreSQL.
>>
>> This is what we want to achieve:
>>
>> - Select all rows in a table (people)
>> - Select addresses belonging to each person. By subselect or join
>> (addresses)
>> - Select phone numbers belonging to each person. By subselect or
>> join (contacts)
>> - Loop through the results, building a Lucene index.
>>
>>
>> Our tables:
>> - people: ~ 8 million records (could be larger in the future,
>> maybe 50 million)
>> - addresses ~8 million records
>> - contacts: ~10 million records
>>
>>
>> Hardware:
>> 2x dual core 3GHz intel Xeon
>> 1-2 Gb RAM
>>
>>
>>
>> Problem:
>>
>> It seems that the PostgreSQL driver fetches the whole result,
>> storing it in memory, causing Java to run out of memory.
>> java.lang.OutOfMemoryError
>> We have tried increasing memory for the program (-Xmn100M -Xms512M
>> -Xmx512M), but we only have one gigabyte of memory to work with.
>>
>> Does anyone have any suggestions on how to deal with this, other
>> that throwing more hardware at the problem? Cursors?
>>
>>
>> Any help is much appreciated!
>> Thanks
>>
> Look here:
>
> http://archives.postgresql.org/pgsql...1/msg00004.php
>
> --
> Roland Walter mailto: rwa (at) mosaic-ag (dot) com
> MOSAIC SOFTWARE AG phone: +49 (0) 22 25 / 88 2-44 9
> 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 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