Thread: PGStream.ReceiveTupleV3 and Out of Memory
Hello All! I have a question that I hope someone can help me with. I have a very large query against a PostgreSQL 8.2 database using an iBatis 2.3.4 RowHandler, postgresql-8.2 JDBC4 driver, and a 32-bit Java 1.6 JVM. The reason why we use the RowHandler is because we know this is a big query and want to handle it a row at a time. When I start the query, it takes some time to return the first row. When the query is about to return the first row, there is a giant spike in heap memory use and the the JVM runs out of memory. I was able to snapshot the heap before it crashed. The culprit seems to be this call: 97.4% - 632 MB - 17,776,474 alloc. org.postgresql.core.PGStream.ReceiveTupleV3 I have analyzed the database and we have no rows that exceed 2K in size; so I know the problem is not related to returning a giant single row. Does anyone have any ideas why ReceiveTupleV3 is returning so much data? Thank you! -Dan
digifork <digifork@gmail.com> wrote: > When I start the query, it takes some time to return the first > row. When the query is about to return the first row, there is a > giant spike in heap memory use and the the JVM runs out of memory. Unless you go out of your way to prevent it, the entire result set is cached on the heap during invocation of the execute method. You can avoid this by using a cursor: http://jdbc.postgresql.org/documentation/head/query.html#query-with-cursor -Kevin
Probably because it is retrieving the entire result set at once. You may want to set fetch size to something other than 0 to get the driver to use a cursor (note you have to set autocommit to false) Dave Cramer dave.cramer(at)credativ(dot)ca http://www.credativ.ca On Wed, Apr 27, 2011 at 1:59 PM, digifork <digifork@gmail.com> wrote: > Hello All! > > I have a question that I hope someone can help me with. I have a very > large query against a PostgreSQL 8.2 database using an iBatis 2.3.4 > RowHandler, postgresql-8.2 JDBC4 driver, and a 32-bit Java 1.6 JVM. > The reason why we use the RowHandler is because we know this is a big > query and want to handle it a row at a time. > > When I start the query, it takes some time to return the first row. > When the query is about to return the first row, there is a giant > spike in heap memory use and the the JVM runs out of memory. I was > able to snapshot the heap before it crashed. The culprit seems to be > this call: > > 97.4% - 632 MB - 17,776,474 alloc. org.postgresql.core.PGStream.ReceiveTupleV3 > > I have analyzed the database and we have no rows that exceed 2K in > size; so I know the problem is not related to returning a giant single > row. > > Does anyone have any ideas why ReceiveTupleV3 is returning so much data? > > Thank you! > > -Dan > > -- > Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-jdbc >
That did it! Thank you all! -Dan On Wed, Apr 27, 2011 at 2:23 PM, Dave Cramer <pg@fastcrypt.com> wrote: > Probably because it is retrieving the entire result set at once. > > You may want to set fetch size to something other than 0 to get the > driver to use a cursor (note you have to set autocommit to false) > > Dave Cramer > > dave.cramer(at)credativ(dot)ca > http://www.credativ.ca > > > > > On Wed, Apr 27, 2011 at 1:59 PM, digifork <digifork@gmail.com> wrote: >> Hello All! >> >> I have a question that I hope someone can help me with. I have a very >> large query against a PostgreSQL 8.2 database using an iBatis 2.3.4 >> RowHandler, postgresql-8.2 JDBC4 driver, and a 32-bit Java 1.6 JVM. >> The reason why we use the RowHandler is because we know this is a big >> query and want to handle it a row at a time. >> >> When I start the query, it takes some time to return the first row. >> When the query is about to return the first row, there is a giant >> spike in heap memory use and the the JVM runs out of memory. I was >> able to snapshot the heap before it crashed. The culprit seems to be >> this call: >> >> 97.4% - 632 MB - 17,776,474 alloc. org.postgresql.core.PGStream.ReceiveTupleV3 >> >> I have analyzed the database and we have no rows that exceed 2K in >> size; so I know the problem is not related to returning a giant single >> row. >> >> Does anyone have any ideas why ReceiveTupleV3 is returning so much data? >> >> Thank you! >> >> -Dan >> >> -- >> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-jdbc >> >