Обсуждение: Data ingestion failing when using higher Batch size

On 06/14/2016 09:33 AM, Rupesh Choudhary wrote:
> Hi Team ,
>
> We have been  trying to insert data (batch inserts) using a JAVA app
> which uses prepared statements  with parameterized queries to insert
> data .We are able to insert data with small batch size  however when we
> increase the batch size to a higher value data ingestion fails . We are
> using pgbouncer for connection pooling on database side which is running
> in session mode  and server_reset_query set to "DISCARD ALL" . Please
> suggest the how can we bulk load data with higher batch size .Below the
> error logs that we are getting from the application end.

Interpreting the below is difficult without information on the actual
code/SQL being run.

So:

What is the SQL you are running?

How is that being run in the code?

Define small vs large batch sizes?

What is the tool chain you are using to go from Java to Postgres?

>
> Below are the logs from application :-
>
> *2016-06-07T11:33:04.50-0700 [App/2]*      OUT at
> org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2959)
> ~[postgresql-9.4-1205-jdbc41.jar!/:9.4]
>
> *2016-06-07T11:33:04.50-0700 [App/2]*      OUT at
> com.exelon.drillinginfo.service.PdenDescService.createDrillingInfo(PdenDescService.java:337)
> ~[app/:na]
>
> *2016-06-07T11:34:35.04-0700 [App/1]*      OUT 2016-06-07 18:34:35.048
> INFO 29 --- [io-64887-exec-1] c.e.d.service.PdenDescService            :
> Run id: 33842
>
> *2016-06-07T11:34:35.05-0700 [App/1]*      OUT 2016-06-07 18:34:35.051
> INFO 29 --- [io-64887-exec-1] c.e.d.service.PdenDescService            :
> running inserts
> ********---------------------------------------------------------------***********
>
> *2016-06-07T11:34:35.05-0700 [App/1]*      OUT 2016-06-07 18:34:35.058
> INFO 29 --- [io-64887-exec-1] c.e.d.service.PdenDescService            :
> running inserts
> ********---------------------------------------------------------------***********
>
> *2016-06-07T11:34:35.06-0700 [App/1]*      ERR
> org.postgresql.util.PSQLException: ERROR: prepared statement "S_3" does
> not exist
>
> *2016-06-07T11:34:35.06-0700 [App/1]*      ERR at
> org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2182)
>
> *2016-06-07T11:34:35.06-0700 [App/1]*      ERR at
> org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1911)
>
> *2016-06-07T11:34:35.06-0700 [App/1]*      ERR at
> org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:338)
>
> *2016-06-07T11:34:35.06-0700 [App/1]*      ERR at
> org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2959)
>
> *2016-06-07T11:34:35.06-0700 [App/1]*      ERR at
> com.exelon.drillinginfo.service.PdenDescService.createDrillingInfo(PdenDescService.java:337)
>
> *2016-06-07T11:34:35.06-0700 [App/1]*      ERR at
> com.exelon.drillinginfo.service.PdenDescService$$FastClassBySpringCGLIB$$3080db7d.invoke(<generated>)
>
> *2016-06-07T11:34:35.06-0700 [App/1]*      ERR at
> org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
>
>
>
> Thanks & Regards,
> Rupesh Choudhary


--
Adrian Klaver
adrian.klaver@aklaver.com

On 06/14/2016 10:20 PM, Rupesh Choudhary wrote:
> Hi Adrian,
>
> Please find anwer in line :
>
> What is the SQL you are running?
> -We have a table with about 167 columns and we are trying to run batch
> updates on the table . I am attaching the part of code for your reference .
> How is that being run in the code?
> Yes , it is runs as a part of the code .
>
> Define small vs large batch sizes?
> smaller batch size of =3
> larger batch size =100

In your original post the error was:

ERROR: prepared statement "S_3" does not
exist

I do not see anything mentioning S_3 in your code sample. Not sure if
that is something the Java tools are supplying or not. Might be
instructive to look at the Postgres server logs when you run the the code.

Have you tried running the code directly against the Postgres server
without going through PgBouncer?


>
> Thanks & Regards,
> Rupesh Choudhary
>


--
Adrian Klaver
adrian.klaver@aklaver.com