On Tue, Feb 17, 2009 at 2:30 PM, Ross J. Reedstrom <reedstrm@xxxxxxxx> wrote: > 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? What's the negotiated window size? That's the amount of data allowed "in flight" without an ack. The fact that acks happen regularly shouldn't be a problem, but if the sender is stalling because it has a small window, waiting for an ack to be received that could cause a large slow down. Do the ack's include any data? If so it's indicative of the PG networking protocol overhead- probably not much you can do about that. Without looking at a pcap myself, I'm not sure I can help out any more. -- Aaron Turner http://synfin.net/ http://tcpreplay.synfin.net/ - Pcap editing and replay tools for Unix & Windows Those who would give up essential Liberty, to purchase a little temporary Safety, deserve neither Liberty nor Safety. -- Benjamin Franklin -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
