Connection hanging on INSERT apparently due to large batch size and 4 CPU cores

From: jgassner(at)gmail(dot)com
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Connection hanging on INSERT apparently due to large batch size and 4 CPU cores
Date: 2008-10-21 06:27:11
Message-ID: c44a1bc0-dcb1-4b57-8106-e50f9303b7d1@79g2000hsk.googlegroups.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

I am experiencing a problem while using the JDBC driver where one or
more connections hang soon after application startup. The connections
don't hang immediately, but instead hang after performing a few
operations (such as SELECTS) - we are using a connection pool. The
number of operations it takes for a connection to hang varies, but
typically we are able to reproduce the issue within an hour or two.
In all cases, however, when they do hang, it is while performing an
INSERT into one particular table of ours. I'm not aware if it is
always the first INSERT for a particular connection that results in a
hang, but I have seen this to be the case in many db log traces. The
table where the hanging INSERTS are occurring is the table that
receives, by far, the most number of INSERTS.

We are using a batched INSERT to perform the insert operation into
this table. When we attempt to insert batches of size 600 rows, we
observe this hang issue. However, we evetually found that lowering
the batch size to 100 avoided the hang issue. While it is nice to
apparently get around the issue using a lower batch size, it would be
nice to understand what the actual problem is so we can be sure it
won't pop up again. This is my question... Anyone know why lowering
the batch size fixed the problem and why the problem occurred in the
first place? Is there a maximum amount of data that can be inserted
in one batch?

Here are some observations on the problem:

From a stack trace of the java side of the connection, I see that it
consistenly is hanging in a socketWrite and while binding the values:

Thread-94 [RUNNABLE]
java.net.SocketOutputStream.socketWrite0(native method)
java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:92)
java.net.SocketOutputStream.write(SocketOutputStream.java:136)
java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:65)
java.io.BufferedOutputStream.write(BufferedOutputStream.java:109)
java.io.FilterOutputStream.write(FilterOutputStream.java:80)
org.postgresql.core.PGStream.SendInteger2(PGStream.java:205)
org.postgresql.core.v3.QueryExecutorImpl.sendBind(QueryExecutorImpl.java:
840)
org.postgresql.core.v3.QueryExecutorImpl.sendOneQuery(QueryExecutorImpl.java:
1053)
org.postgresql.core.v3.QueryExecutorImpl.sendQuery(QueryExecutorImpl.java:
644)
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:
345)
org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:
2674)

Performing an strace on the PostgreSQL server process corresponding to
the hung connection shows that it is stuck inside a call to "sendto".

Using lsof I can see that the tcp connection is still in the
ESTABLISHED state.

So it appears that boths side are trying to write to each other at the
same time and the underlying tcp connection is good.

Threading: This is a multi-threaded java server application. All
threads take connections from the same connection pool. Most threads
aren't very busy, other than the threads inserting into the particular
table where the INSERTS are hanging.

Info on my setup:
JDBC driver: postgresql-8.3-603.jdbc3.jar
PostgreSQL Server: postgresql-8.3.3.tar.bz2 (built with the options --
without-readline --disable-rpath --with-openssl on x86-64)
- Although it is built with ssl, our tests are not using ssl
JVM: Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_15-
b04)
Machine: SLES 10 SP2 (x86_64); 4 CPU cores
- Doesn't seem to happen on machines with only 2 CPU cores

All idea are appreciated.

Thanks,
John

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message Kris Jurka 2008-10-22 17:48:22 Re: Connection hanging on INSERT apparently due to large batch size and 4 CPU cores
Previous Message Achilleas Mantzios 2008-10-18 19:24:35 Re: issues with SQL size st.execute(SQL) in 8.3.3