Re: TCP network cost - Mailing list pgsql-performance

On Tue, Feb 17, 2009 at 03:14:55PM -0600, Ross J. Reedstrom wrote:
> On Tue, Feb 17, 2009 at 01:59:55PM -0700, Rusty Conover wrote:
> >
> > What is the client software you're using?  libpq?
> >
>
> python w/ psycopg (or psycopg2), which wraps libpq. Same results w/
> either version.
>

It's not python networking per se's fault: sending the file via a
SimpleHTTPServer, adn fetching w/ wget takes on the order of 0.5 sec.
as well.

> I think I'll try network sniffing to see if I can find where the
> delays are happening.

I'm no TCP/IP expert, but some packet capturing, and wireshark analysis
makes me suspicious about flow control. the 'netcat' transfer shows lots
of packets from server -> client, w/ deltaTs of 8 - 200 usec (that's
micro-sec), mostly in the 10-20 range. The client -> server 'ack's seem
bursty, happening only every 50-100 packets, then a few back-to-back,
all taking 10-20 usec.

I also see occasional lost packets, retransmits, and TCP Window Updates
in this stream. FIN packet is after 8553 packets.

For the libpq driven transfer, I see lots of packets flowing both ways.
Seems about every other packet from server to client is 'ack'ed. Each of
these 'ack's takes 10 uS to send, but seem to cause the transfer to
'reset', since the next packet from the server doesn't arrive for 2-2.5
ms (that's milli-sec!) FIN happens at 63155 packets.

No lost packets, no renegotiation, etc.

Capturing a localhost transfer shows the same pattern, although now
almost every single packet from server -> client takes ~ 3 ms

So, TCP experts out there, what's the scoop? Is libpq/psycopg being very
conservative, or am I barking up the wrong tree? Are there network
socket properities I need to be tweaking?

Does framing up for TCP just take that long when the bits are coming
from the DB? I assume the unix-domain socket case still uses the full
postgresql messaging protocol, but wouldn't need to worry about
network-byte-order, etc.

All the postgres tunable knobs I can see seem to talk about disk IO,
rather than net IO. Can someone point me at some doco about net IO?

Ross
--
Ross Reedstrom, Ph.D.                                 reedstrm@rice.edu
Systems Engineer & Admin, Research Scientist        phone: 713-348-6166
The Connexions Project      http://cnx.org            fax: 713-348-3665
Rice University MS-375, Houston, TX 77005
GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E  F888 D3AE 810E 88F0 BEDE