pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Strange result: UNIX vs. TCP/IP sockets
8+ messages / 5 participants
[nested] [flat]

* Strange result: UNIX vs. TCP/IP sockets
@ 2003-07-04 17:22  Andrew Sullivan <andrew@libertyrms.info>
  0 siblings, 3 replies; 8+ messages in thread

From: Andrew Sullivan @ 2003-07-04 17:22 UTC (permalink / raw)
  To: pgsql-performance

Hi all,

We're run into a rather odd problem here, and we're puzzling out
what's going on.  But while we do, I thought I'd see if anyone else
has anything similar to report.

This is for 7.2.4 on Solaris 8.

We have a query for which EXPLAIN ANALYSE on a local psql connection
always returns a time of between about 325 msec and 850 msec
(depending on other load, whether the result is in cache, &c. -- this
is an aggregates query involving min() and count()).

If I connect using -h 127.0.0.1, however, I can _sometimes_ get the
query to take as long as 1200 msec.  The effect is sporadic (of
course.  If it were totally predictable, the computing gods wouldn't
be having any fun with me), but it is certainly there off and on. 
(We discovered it because our application is regularly reporting
times on this query roughly twice as long as I was able to get with
psql, until I connected via TCP/IP.)

I'll have more to report as we investigate further -- at the moment,
this has cropped up on a production system, and so we're trying to
reproduce it in our test environment.  Naturally, we're looking at
the TCP/IP stack configuration, among other stuff.  In the meantime,
however, I wondered if anyone knows which bits I ought to be prodding
at to look for sub-optimal libraries, &c.; or whether anyone else has
run into similar problems on Solaris or elsewhere.

A

-- 
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Strange result: UNIX vs. TCP/IP sockets
@ 2003-07-04 17:35  The Hermit Hacker <scrappy@postgresql.org>
  parent: Andrew Sullivan <andrew@libertyrms.info>
  2 siblings, 1 reply; 8+ messages in thread

From: The Hermit Hacker @ 2003-07-04 17:35 UTC (permalink / raw)
  To: Andrew Sullivan <andrew@libertyrms.info>; +Cc: pgsql-performance


'K, this is based on "old information", I don't know if Sun changed it
'yet again' ... but, when I was working at the University, one of our IT
directors gave me a report that deal with something Sun did (god, I'm so
detailed  here, eh?) to "mimic" how Microsoft broke the TCP/IP protocol
... the report was in relation to Web services, and how the change
actually made Sun/Solaris appear to be slower then Microsoft ...

And Sun made this the 'default' setting, but it was disablable in
/etc/systems ...

Sorry for being so vague, but if I recall correctly, it had something to
do with adding an extra ACK to each packet ... maybe even as vague as the
above is, it will jar a memory for someone else?


On Fri, 4 Jul 2003, Andrew Sullivan wrote:

> Hi all,
>
> We're run into a rather odd problem here, and we're puzzling out
> what's going on.  But while we do, I thought I'd see if anyone else
> has anything similar to report.
>
> This is for 7.2.4 on Solaris 8.
>
> We have a query for which EXPLAIN ANALYSE on a local psql connection
> always returns a time of between about 325 msec and 850 msec
> (depending on other load, whether the result is in cache, &c. -- this
> is an aggregates query involving min() and count()).
>
> If I connect using -h 127.0.0.1, however, I can _sometimes_ get the
> query to take as long as 1200 msec.  The effect is sporadic (of
> course.  If it were totally predictable, the computing gods wouldn't
> be having any fun with me), but it is certainly there off and on.
> (We discovered it because our application is regularly reporting
> times on this query roughly twice as long as I was able to get with
> psql, until I connected via TCP/IP.)
>
> I'll have more to report as we investigate further -- at the moment,
> this has cropped up on a production system, and so we're trying to
> reproduce it in our test environment.  Naturally, we're looking at
> the TCP/IP stack configuration, among other stuff.  In the meantime,
> however, I wondered if anyone knows which bits I ought to be prodding
> at to look for sub-optimal libraries, &c.; or whether anyone else has
> run into similar problems on Solaris or elsewhere.
>
> A
>
> --
> ----
> Andrew Sullivan                         204-4141 Yonge Street
> Liberty RMS                           Toronto, Ontario Canada
> <andrew@libertyrms.info>                              M2P 2A8
>                                          +1 416 646 3304 x110
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Strange result: UNIX vs. TCP/IP sockets
@ 2003-07-04 17:55  Vincent van Leeuwen <pgsql.spam@vinz.nl>
  parent: The Hermit Hacker <scrappy@postgresql.org>
  0 siblings, 1 reply; 8+ messages in thread

From: Vincent van Leeuwen @ 2003-07-04 17:55 UTC (permalink / raw)
  To: pgsql-performance

http://grotto11.com/blog/slash.html?+1039831658

Summary: IE and IIS cheat at TCP level by leaving out various SYN and ACK
packets, thereby making IE requests from IIS servers blazingly fast, and
making IE requests to non-IIS servers infuriatingly slow.

But since this only relates to making and breaking TCP connections, I don't
think this is relevant for a larger query time. It's probably normal for a TCP
connection to be slightly slower than a unix socket, but I don't think that's
wat Andrew is experiencing.

On 2003-07-04 14:35:18 -0300, The Hermit Hacker wrote:
> 
> 'K, this is based on "old information", I don't know if Sun changed it
> 'yet again' ... but, when I was working at the University, one of our IT
> directors gave me a report that deal with something Sun did (god, I'm so
> detailed  here, eh?) to "mimic" how Microsoft broke the TCP/IP protocol
> ... the report was in relation to Web services, and how the change
> actually made Sun/Solaris appear to be slower then Microsoft ...
> 
> And Sun made this the 'default' setting, but it was disablable in
> /etc/systems ...
> 
> Sorry for being so vague, but if I recall correctly, it had something to
> do with adding an extra ACK to each packet ... maybe even as vague as the
> above is, it will jar a memory for someone else?
> 
> 
> On Fri, 4 Jul 2003, Andrew Sullivan wrote:
> 
> > Hi all,
> >
> > We're run into a rather odd problem here, and we're puzzling out
> > what's going on.  But while we do, I thought I'd see if anyone else
> > has anything similar to report.
> >
> > This is for 7.2.4 on Solaris 8.
> >
> > We have a query for which EXPLAIN ANALYSE on a local psql connection
> > always returns a time of between about 325 msec and 850 msec
> > (depending on other load, whether the result is in cache, &c. -- this
> > is an aggregates query involving min() and count()).
> >
> > If I connect using -h 127.0.0.1, however, I can _sometimes_ get the
> > query to take as long as 1200 msec.  The effect is sporadic (of
> > course.  If it were totally predictable, the computing gods wouldn't
> > be having any fun with me), but it is certainly there off and on.
> > (We discovered it because our application is regularly reporting
> > times on this query roughly twice as long as I was able to get with
> > psql, until I connected via TCP/IP.)
> >
> > I'll have more to report as we investigate further -- at the moment,
> > this has cropped up on a production system, and so we're trying to
> > reproduce it in our test environment.  Naturally, we're looking at
> > the TCP/IP stack configuration, among other stuff.  In the meantime,
> > however, I wondered if anyone knows which bits I ought to be prodding
> > at to look for sub-optimal libraries, &c.; or whether anyone else has
> > run into similar problems on Solaris or elsewhere.
> >
> > A
> >
> > --
> > ----
> > Andrew Sullivan                         204-4141 Yonge Street
> > Liberty RMS                           Toronto, Ontario Canada
> > <andrew@libertyrms.info>                              M2P 2A8
> >                                          +1 416 646 3304 x110
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 8: explain analyze is your friend
> >
> 
> Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
> Systems Administrator @ hub.org
> primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Vincent van Leeuwen
Media Design - http://www.mediadesign.nl/



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Strange result: UNIX vs. TCP/IP sockets
@ 2003-07-04 18:07  Rod Taylor <rbt@rbt.ca>
  parent: Andrew Sullivan <andrew@libertyrms.info>
  2 siblings, 0 replies; 8+ messages in thread

From: Rod Taylor @ 2003-07-04 18:07 UTC (permalink / raw)
  To: Andrew Sullivan <andrew@libertyrms.info>; +Cc: pgsql-performance

> If I connect using -h 127.0.0.1, however, I can _sometimes_ get the
> query to take as long as 1200 msec.  The effect is sporadic (of

SSL plays havoc with our system when using local loopback for the host 
on both Solaris 7 and 8.  It was probably key renegotiation which 7.4
has addressed.

Attachments:

  signature.asc (application/pgp-signature, 186B) download

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Strange result: UNIX vs. TCP/IP sockets
@ 2003-07-04 21:20  Andrew Sullivan <andrew@libertyrms.info>
  parent: Vincent van Leeuwen <pgsql.spam@vinz.nl>
  0 siblings, 1 reply; 8+ messages in thread

From: Andrew Sullivan @ 2003-07-04 21:20 UTC (permalink / raw)
  To: pgsql-performance

On Fri, Jul 04, 2003 at 07:55:12PM +0200, Vincent van Leeuwen wrote:

> But since this only relates to making and breaking TCP connections,
> I don't think this is relevant for a larger query time. It's
> probably normal for a TCP connection to be slightly slower than a
> unix socket, but I don't think that's wat Andrew is experiencing.

No, it's not.  And my colleague Sorin Iszlai pointed out to me
something else about it: we're getting different numbers reported by
EXPLAIN ANALYSE itself.  How is that even possible?

If we try it here on a moderately-loaded Sun box, it seems we're able
to reproduce it, as well. 

How could it be the transport affects the time for the query as
reported by the back end?

A

-- 
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Strange result: UNIX vs. TCP/IP sockets
@ 2003-07-04 21:47  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Andrew Sullivan <andrew@libertyrms.info>
  0 siblings, 1 reply; 8+ messages in thread

From: Tom Lane @ 2003-07-04 21:47 UTC (permalink / raw)
  To: Andrew Sullivan <andrew@libertyrms.info>; +Cc: pgsql-performance

Andrew Sullivan <andrew@libertyrms.info> writes:
> How could it be the transport affects the time for the query as
> reported by the back end?

How much data is being sent back by the query?

Do you have SSL enabled?  SSL encryption overhead is nontrivial,
especially if any renegotiations happen.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Strange result: UNIX vs. TCP/IP sockets
@ 2003-07-05 20:58  Andrew Sullivan <andrew@libertyrms.info>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 8+ messages in thread

From: Andrew Sullivan @ 2003-07-05 20:58 UTC (permalink / raw)
  To: pgsql-performance

On Fri, Jul 04, 2003 at 05:47:27PM -0400, Tom Lane wrote:
> Andrew Sullivan <andrew@libertyrms.info> writes:
> > How could it be the transport affects the time for the query as
> > reported by the back end?
> 
> How much data is being sent back by the query?

In this case, it's an all-aggregate query:

select count(*), min(id) from sometable where owner = int4;

(Yeah, yeah, I know.  I didn't write it.)

But it's the EXPLAIN ANALYSE that's reporting different times
depending on the transport.  That's what I find so strange.

> Do you have SSL enabled?  SSL encryption overhead is nontrivial,
> especially if any renegotiations happen.

No.

-- 
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Strange result: UNIX vs. TCP/IP sockets
@ 2003-07-08 20:54  Andrew Sullivan <andrew@libertyrms.info>
  parent: Andrew Sullivan <andrew@libertyrms.info>
  2 siblings, 0 replies; 8+ messages in thread

From: Andrew Sullivan @ 2003-07-08 20:54 UTC (permalink / raw)
  To: pgsql-performance

Hi all,

You may remember in my last report, I said that it appeared that
TCP/IP connections caused EXPLAIN ANALYSE to return (repeatably but
not consistently) slower times than when connected over UNIX domain
sockets. 

This turns out to be false.  We (well, Chris Browne, actually) ran
some tests which demonstrated that the performance problem turned up
over the UNIX socket, as well.  It was just a statistical fluke that
our smaller sample always found the problem on TCP/IP.

Of course, now we have some other work to do, but we can rule out the
transport at least.  Chalk one up for sane results.  If we discover
any more, I'll post it here.

A
-- 
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110




^ permalink  raw  reply  [nested|flat] 8+ messages in thread


end of thread, other threads:[~2003-07-08 20:54 UTC | newest]

Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-07-04 17:22 Strange result: UNIX vs. TCP/IP sockets Andrew Sullivan <andrew@libertyrms.info>
2003-07-04 17:35 ` The Hermit Hacker <scrappy@postgresql.org>
2003-07-04 17:55   ` Vincent van Leeuwen <pgsql.spam@vinz.nl>
2003-07-04 21:20     ` Andrew Sullivan <andrew@libertyrms.info>
2003-07-04 21:47       ` Tom Lane <tgl@sss.pgh.pa.us>
2003-07-05 20:58         ` Andrew Sullivan <andrew@libertyrms.info>
2003-07-04 18:07 ` Rod Taylor <rbt@rbt.ca>
2003-07-08 20:54 ` Andrew Sullivan <andrew@libertyrms.info>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox