Обсуждение: java.lang.OutOfMemoryError: GC overhead limit exceeded

On 7/23/20 6:30 AM, Christophe combet wrote:
> 
> Thank you for pointing the link between auto commit an buffering.
> 
> autocommit=false: same exception.
> 
> We try fetchSize.
> 
> Thanks.
> Le jeudi 23 juillet 2020 à 13:47:14 UTC+2, Vladimir Sitnikov 
> <sitnikov.vladimir@gmail.com> a écrit :
> 
> 
>  >can you try switching to autocommit=false?
> 
> + you probably need to configure fetchSize as well.
> 
> Vladimir
> 
You set the 8G limit on the java process but did you tell up the 
available RAM?  This query would require that about 3G be available to 
hold all the data. Can you use jConsole to establish how much memory is 
available at the time of issuing the query?

The only change that could affect between 42.2.10 (which works based
on your first message), and 42.2.11+ is the introduction of the tuple
abstraction.

The autocommit=false + fetchSize combo should help with large tables:
https://jdbc.postgresql.org/documentation/head/query.html#fetchsize-example


On Thu, Jul 23, 2020 at 4:19 PM Christophe combet <chrisc_pro@yahoo.fr> wrote:
>
> Dear Dave,
>
> The table definition is:
>
> CREATE TABLE mytable (
>     column1 character varying(14) NOT NULL,
>     column2 integer NOT NULL,
>     column3 bytea NOT NULL
> );
>
> ALTER TABLE ONLY mytable
>     ADD CONSTRAINT mytable_pkey PRIMARY KEY (column1);
>
> Column column3 contains zipped data (BLOB and toast table).
>
> The full dump of the table (pg_dump as SQL text) is 24GB.
>
> Thanks.
> Le jeudi 23 juillet 2020 à 15:24:54 UTC+2, Dave Cramer <davecramer@postgres.rocks> a écrit :
>
>
>
>
> On Thu, 23 Jul 2020 at 09:21, Christophe combet <chrisc_pro@yahoo.fr> wrote:
>
>
> autocommit=false & fetchSize=100 or 10000 : same exception.
>
> 42.2.10 still running.
>
>
> Is there a very large column in there
>
>
> Dave Cramer
> www.postgres.rocks
>
>
>
>
>
> Le jeudi 23 juillet 2020 à 14:30:56 UTC+2, Christophe combet <chrisc_pro@yahoo.fr> a écrit :
>
>
>
> Thank you for pointing the link between auto commit an buffering.
>
> autocommit=false: same exception.
>
> We try fetchSize.
>
> Thanks.
> Le jeudi 23 juillet 2020 à 13:47:14 UTC+2, Vladimir Sitnikov <sitnikov.vladimir@gmail.com> a écrit :
>
>
> >can you try switching to autocommit=false?
>
> + you probably need to configure fetchSize as well.
>
> Vladimir
>

On 7/23/20 8:33 AM, Christophe combet wrote:
> Dear Rob,
> 
> Total RAM is 512GB.
> 
> Runtime.getRuntime().freeMemory() says: 7,630,918,904 at the time of 
> issuing the query.
> 
> jConsole timed out and it is impossible to reconnect. I did a screen 
> capture at the time of the freeze.
> 
> Le jeudi 23 juillet 2020 à 15:33:34 UTC+2, Rob Sargent 
> <robjsargent@gmail.com> a écrit :
> 
> 
> 
> 
> On 7/23/20 6:30 AM, Christophe combet wrote:
>  >
>  > Thank you for pointing the link between auto commit an buffering.
>  >
>  > autocommit=false: same exception.
>  >
>  > We try fetchSize.
>  >
>  > Thanks.
>  > Le jeudi 23 juillet 2020 à 13:47:14 UTC+2, Vladimir Sitnikov
>  > <sitnikov.vladimir@gmail.com <mailto:sitnikov.vladimir@gmail.com>> a 
> écrit :
>  >
>  >
>  >  >can you try switching to autocommit=false?
>  >
>  > + you probably need to configure fetchSize as well.
>  >
>  > Vladimir
> 
>  >
> You set the 8G limit on the java process but did you tell up the
> available RAM?  This query would require that about 3G be available to
> hold all the data. Can you use jConsole to establish how much memory is
> available at the time of issuing the query?
> 
> 
> 
Upthread the select statement returned only "column1"
* We are doing a "SELECT column1 FROM table1 ODER BY column1;" (column1 
* is   varchar(14) and the table contains 130,080,790 rows), when the * 
* exception occurs.
Are you reading in the bytea column as well?

And does it finish if you add more memory to the jvm (just for testing 
purposes).

Christophe combet schrieb am 23.07.2020 um 17:41:
> Indeed, the autocommit=false + fetchSize combo is working. I did a
> mistake when trying the first time as suggested by Vladimir: I used
> createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY)
> which are not the good parameters to be used (bad copy/paste).

You would need

    createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY)

Thomas