Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 0B1A2633D6F for ; Mon, 23 Mar 2009 17:26:21 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 15991-02 for ; Mon, 23 Mar 2009 17:26:16 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from smtp1.ca.com (smtp1.ca.com [141.202.248.21]) by mail.postgresql.org (Postfix) with ESMTP id B4D77633725 for ; Mon, 23 Mar 2009 17:26:18 -0300 (ADT) Received: from athena.ca.com ([130.200.164.17]) by smtp1.ca.com with Microsoft SMTPSVC(6.0.3790.3959); Mon, 23 Mar 2009 16:26:16 -0400 Message-ID: <49C7F050.7030505@ca.com> Date: Mon, 23 Mar 2009 13:25:52 -0700 From: Brian Cox User-Agent: Thunderbird 2.0.0.19 (X11/20081209) MIME-Version: 1.0 To: "Tom Lane [tgl@sss.pgh.pa.us]" CC: "pgsql-performance@postgresql.org" Subject: Re: multiple threads inserting into the same table Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-OriginalArrivalTime: 23 Mar 2009 20:26:16.0938 (UTC) FILETIME=[9E62C4A0:01C9ABF5] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200903/335 X-Sequence-Number: 33257 Tom Lane [tgl@sss.pgh.pa.us] wrote: > pg_stat_activity says those five threads are doing nothing except > sitting around with open transactions. You sure you don't have a bug on > the application side? > > regards, tom lane This is a java app. A thread dump reveals that these 5 threads are all asleep on a socket read to postgres (see below). DbUtils.java:2265 is: session.connection().createStatement() .executeUpdate(((DatabaseInsert) insertObject).getInsertStmt(session)); This generates and executes a single SQL insert. Since, as you point out, postgres seems to think that this transaction isn't doing anything, it's hard to figure out what the read is doing. Brian "DatabasePool.Thread1" prio=10 tid=0x27f04c00 nid=0x3b38 runnable [0x29e27000..0x29e281b0] java.lang.Thread.State: RUNNABLE at java.net.SocketInputStream.socketRead0(Native Method) at java.net.SocketInputStream.read(SocketInputStream.java:129) at org.postgresql.core.VisibleBufferedInputStream.readMore(VisibleBufferedInputStream.java:135) at org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:104) at org.postgresql.core.VisibleBufferedInputStream.read(VisibleBufferedInputStream.java:73) at org.postgresql.core.PGStream.ReceiveChar(PGStream.java:259) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1182) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:194) - locked <0x8975c878> (a org.postgresql.core.v3.QueryExecutorImpl) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:451) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:336) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:282) at com.mchange.v2.c3p0.impl.NewProxyStatement.executeUpdate(NewProxyStatement.java:64) at com.timestock.tess.util.DbUtils$DatabaseInsertTask.insertObject(DbUtils.java:2265) at com.timestock.tess.util.DbUtils$DatabaseInsertTask.call(DbUtils.java:2200) at com.timestock.tess.util.DbUtils$DatabaseInsertTask.call(DbUtils.java:2157) at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303) at java.util.concurrent.FutureTask.run(FutureTask.java:138) at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908) at java.lang.Thread.run(Thread.java:619)