Connection hanging on INSERT apparently due to large batch size and 4 CPU cores
От | jgassner@gmail.com |
---|---|
Тема | Connection hanging on INSERT apparently due to large batch size and 4 CPU cores |
Дата | |
Msg-id | c44a1bc0-dcb1-4b57-8106-e50f9303b7d1@79g2000hsk.googlegroups.com обсуждение исходный текст |
Ответы |
Re: Connection hanging on INSERT apparently due to large
batch size and 4 CPU cores
(Kris Jurka <books@ejurka.com>)
|
Список | 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
В списке pgsql-jdbc по дате отправления:
Предыдущее
От: Achilleas MantziosДата:
Сообщение: Re: issues with SQL size st.execute(SQL) in 8.3.3
Следующее
От: Kris JurkaДата:
Сообщение: Re: Connection hanging on INSERT apparently due to large batch size and 4 CPU cores