[note: sending a message that's been sitting in 'drafts' since last week] Summary: C client and large-object API python both send bits in reasonable time, but I suspect there's still room for improvement in libpq over TCP: I'm suspicious of the 6x difference. Detailed analysis will probably find it's all down to memory allocation and extra copying of bits around (client side) Ross On Wed, Feb 18, 2009 at 01:44:23PM +0000, Gregory Stark wrote: > > There's not much Postgres can do to mess up TCP/IP. The only things that come > to mind are a) making lots of short-lived connections and b) getting caught by > Nagle when doing lots of short operations and blocking waiting on results. The hint re: Nagle sent to off hunting. It looks like libpq _should_ be setting NODELAY on both sides of the socket. However, tcptrace output does show (what I understand to be) the stereotypical every-other-packet-acked stairstep of a delayed-ack/Nagle interaction. (as described here: http://www.stuartcheshire.org/papers/NagleDelayedAck/ ) Walking through the libpq code, though, it sets NODELAY, so Nagle should be out of the picture. This may be a red herring, though. See below. > What libpq (or other interface) operations are you doing exactly? I'm using psycopg from python. My cut down test case is: con=psycopg.connect('dbname=mydb user=myuser port=5433 host=myhost') cur=con.cursor() start=DateTime.now() cur.execute("""select file from files where fileid=1""") data = cur.fetchone()[0] end=DateTime.now() f=open('/dev/null','w') f.write(data) f.close() cur.close() print "tcp socket: %s" % str(end - start) I've since written a minimal C app, and it's doing much better, down to about 7 sec for a local TCP connection (either localhost or hostname) So, I get to blame the psycopg wrapper for ~ 30 sec of delay. I'm suspicous of memory allocation, myself. The tcp traces (tcpdump + tcptrace + xplot are cool set of tools, btw) indicate that the backend's taking ~ 0.35 sec to process the query and start sending bits, and using a domain socket w/ that code gets the file in 1.3 - 1.4 sec, so I'm still seeing a 6-fold slowdown for going via TCP (6 sec. vs. 1 sec.) Sending the raw file via apache (localhost) takes ~200 ms. Moving to a large-object based implementation would seem to confirm that: psycopg2 (snapshot of svn head) manages to pull a lo version of the file in times equivalent to the C client (7 sec local) I'll probably move the system to use that, since there's really almost no use-case for access to the insides of these files from SQL. > [also, your Mail-Followup-To has a bogus email address in it. Please don't do > that] Hmm, not on purpose. I'll take a look. -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
