Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 368DE9FB386 for ; Fri, 25 May 2007 05:51:10 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 07384-01 for ; Fri, 25 May 2007 05:51:01 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from pop5-1.us4.outblaze.com (pop5-1.us4.outblaze.com [205.158.62.125]) by postgresql.org (Postfix) with SMTP id 8EE639FB38C for ; Fri, 25 May 2007 05:51:03 -0300 (ADT) Received: (qmail 23613 invoked from network); 25 May 2007 08:51:23 -0000 Received: from unknown (HELO nbd.it.uc3m.es) (ptb:linuxmail.org?linuxmail.org@linuxmail.org@163.117.139.192) by pop5-1.us4.outblaze.com with SMTP; 25 May 2007 08:51:23 -0000 Received: from inv.it.uc3m.es (oboe.it.uc3m.es [163.117.139.101]) by nbd.it.uc3m.es (8.13.1/8.13.1/Debian-15) with ESMTP id l4P8owTa026483 for ; Fri, 25 May 2007 10:50:58 +0200 Received: (from ptb@localhost) by inv.it.uc3m.es (8.11.0/8.11.0) id l4P8owm02940 for pgsql-performance@postgresql.org; Fri, 25 May 2007 10:50:58 +0200 From: "Peter T. Breuer" Message-Id: <200705250850.l4P8owm02940@inv.it.uc3m.es> Subject: general PG network slowness (possible cure) (repost) To: pgsql-performance@postgresql.org Date: Fri, 25 May 2007 10:50:58 +0200 (MET DST) X-Anonymously-To: Reply-To: ptb@inv.it.uc3m.es X-message-flag: Had your Outlook virus, today? http://www.counterpane.com/crypto-gram-0103.html#4 X-WebTV-Stationery: Standard\; BGColor=black\; TextColor=black Reply-By: Sat, 1 Apr 2006 14:21:08 -0700 X-Mailer: ELM [version 2.4ME+ PL66 (25)] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200705/486 X-Sequence-Number: 24876 I set up pg to replace a plain gdbm database for my application. But even running to the same machine, via a unix socket * the pg database ran 100 times slower Across the net it was * about 500 to 1000 times slower than local gdbm with no cpu use to speak of. I'd heard that networked databases are slow. I might have left it at that if curiosity hadn't led me to write a network server for gdbm databases, and talk to _that_ just to get a comparison. Lo and behold and smack me with a corncob if it wasn't _slower_ than pg. On a whim I mapped the network bandwidth per packet size with the NPtcp suite, and got surprising answers .. at 1500B, naturally, the bandwidth was the full 10Mb/s (minus overheads, say 8.5Mb/s) of my pathetic little local net. At 100B the bandwidth available was only 25Kb/s. At 10B, you might as well use tin cans and taut string instead. I also mapped the network flows using ntop, and yes, the average packet size for both gdbm and pg in one direction was only about 100B or so. That's it! Clearly there are a lot of short queries going out and the answers were none too big either ( I had a LIMIT 1 in all my PG queries). About 75% of traffic was in the 64-128B range while my application was running, with the peak bandwidth in that range being about 75-125Kb/s (and I do mean bits, not bytes). Soooo ... I took a look at my implementation of remote gdbm, and did a very little work to aggregate outgoing transmissions together into lumps. Three lines added in two places. At the level of the protocol where I could tell how long the immediate conversation segment would be, I "corked" the tcp socket before starting the segment and "uncorked" it after the segment (for "cork", see tcp(7), setsockopt(2) and TCP_CORK in linux). Surprise, ... I got a speed up of hundreds of times. The same application that crawled under my original rgdbm implementation and under PG now maxed out the network bandwidth at close to a full 10Mb/s and 1200 pkts/s, at 10% CPU on my 700MHz client, and a bit less on the 1GHz server. So * Is that what is holding up postgres over the net too? Lots of tiny packets? And if so * can one fix it the way I fixed it for remote gdbm? The speedup was hundreds of times. Can someone point me at the relevant bits of pg code? A quick look seems to say that fe-*.c is interesting. I need to find where the actual read and write on the conn->sock is done. Very illuminating gnuplot outputs available on request. Peter