X-Original-To: pgsql-interfaces-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 8BD1BD1B20E for ; Mon, 5 Jul 2004 23:43:55 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 41339-02 for ; Tue, 6 Jul 2004 02:43:50 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 5BE8DD1B1CB for ; Mon, 5 Jul 2004 23:43:45 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i662hlxI014767; Mon, 5 Jul 2004 22:43:47 -0400 (EDT) To: L J Bayuk Cc: "Nigel J. Andrews" , pgsql-interfaces@postgresql.org Subject: Re: pgin.tcl pg_exec_prepared slow (was: Released...) In-reply-to: <20040706010731.GA15183@bxlbisnugqvi.mindspring.com> References: <24110.1087945736@sss.pgh.pa.us> <20040706010731.GA15183@bxlbisnugqvi.mindspring.com> Comments: In-reply-to L J Bayuk message dated "Mon, 05 Jul 2004 21:07:31 -0400" Date: Mon, 05 Jul 2004 22:43:47 -0400 Message-ID: <14766.1089081827@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.1 tagged_above=0.0 required=5.0 tests=MAILTO_TO_SPAM_ADDR X-Spam-Level: * X-Archive-Number: 200407/11 X-Sequence-Number: 5017 L J Bayuk writes: > The delay we are seeing (about 40 ms) is from the time the client sends the > Bind message to PostgreSQL until it gets a TCP ACK back. (It should get > a BindComplete message back, but the backend does't flush after sending > this - no pq_flush() at the end of exec_bind_message(). Hello, Tom, is this > a bug? No. If you wanted a flush just there, send a Flush message. But AFAICS the only reason why you'd do that is if you needed to look at the BindComplete before sending your next message --- which you do not. > pgin.tcl sets its Tcl connection socket channel to "unbuffered", > which means each PostgreSQL message it sends will be go into the TCP > buffers immediately, since each message is written in a single "puts". This is certainly bogus. A good implementation would arrange to flush the TCP outbound queue immediately after sending either a Flush or a Sync message --- and *no place else*. Those are exactly the points where you are starting to wait for a backend reply. More frequent flushing simply results in more separate network packets, which is not a win. Also, if you haven't sent one of these message types, you are not entitled to expect any immediate reply (which is essentially what's happening with pgin.tcl, evidently). The V3 protocol is designed around the assumption that each side will batch multiple logical messages into single network packets whenever possible. If you don't do this, it's not surprising that performance sucks :-( The reason error recovery is defined as doing skip-to-Sync is exactly so that you can shove off a batch of messages without having first checked the results of the earlier messages. regards, tom lane