Re: TCP_NO_DELAY & TcpAckFrequency (Re: Slow query through

Since it works with different speeds in different clients, it *probably*
isn't on the server side. I'm far from sure on that though, there may be
other paramters that are implicitly changed when ODBC is used.

It's interesting to note that your problem is with a single large field.
If you query for approximatly the same amount of data *but in several
small fields*, do you get the same behaviour? Or does it happen only
with large fields?

> I posted a TCP/IP dump of the dialog between my workstation
> and the postgresql server to a TCP/IP newsgroup.
> I fixed the problem by changing a parameter in my TCP/IP
> configuration (on my workstation, not on the server).
> I have set the TcpAckFrequency key to 1, as suggested by
> someone on the comp.protocols.tcp-ip NG. The procedure is
> described in :
> http://support.microsoft.com/?scid=kb%3Ben-us%3B328890&x=15&y=10

Interesting - I wouldn't have expected that to make a difference.

<snip>

> > The short answer:
> >
> > "TCP_NODELAY" should be enabled on the TCP connection to
> the database
> > on both the client and server side of the connection.
> > (This is also known as "disabling the Nagle Algorithm").
> > (Based upon your capture I can almost guarantee that TCP_NODELAY is
> > *not* enabled on your Database TCP connection on the server side).
> >
> > The use of TCP_NODELAY is a database option for a different
> database
> > server with which I'm familiar. I suggest you consult your
> DBA with respect to PostgreSQL.
> > (Also Google "database tcp_nodelay" & etc for information).

We enable TCP_NODELAY on both server and client, when available. And it
is available - I've double-checked and that code is indeed compiled into
both libpq and the server on 8.1 at least.

> > I have an area for you to explore: There's a method of socket
> > programming in windows called "io completion ports" (or somesuch).
> > Rather than streaming data into the socket UNIX style, the
> application
> > code allocates a buffer somewhere, fills it and then tells
> the stack:
> > "The buffer is here, please send it, then let me know when
> I can have
> > the buffer back."
> >
> > Some see an advantage to this type of coding because it
> eliminates a
> > copy of data from the application memory area into stack
> memory area.

We don't use this.
We do, however, buffer it in an application side driver and then send it
off with a single call to send(). On the server, that is. Perhaps it can
have a similar effect.

But I don't see how this can make a difference between pgadmin and odbc
and psql, since they all use the same code!
Might be interesting to write up a small test program that uses just
libpq and see what is required to repro the problem there.

//Magnus

Magnus Hagander a écrit :
> Since it works with different speeds in different clients, it *probably*
> isn't on the server side. I'm far from sure on that though, there may be
> other paramters that are implicitly changed when ODBC is used.

Yes, that was my first impression also. But the fact that the
server waits for an ACK from the client before it goes on sending
packet is quite strange, isn't it ?

> It's interesting to note that your problem is with a single large field.
> If you query for approximatly the same amount of data *but in several
> small fields*, do you get the same behaviour? Or does it happen only
> with large fields?

I have splitted the data into 4 fields.
The largest field was ~23.000 characters long, so I made 4 fields
containing. The first one contains the first 6000, the second one
from 6001 to 12000, etc...
The query took about half the time to run !

> We enable TCP_NODELAY on both server and client, when available. And it
> is available - I've double-checked and that code is indeed compiled into
> both libpq and the server on 8.1 at least.

I run 8.1.2, so I assume that it is enabled on my workstation and
on the server.

> But I don't see how this can make a difference between pgadmin and odbc
> and psql, since they all use the same code!

Yes, I really don't understand either !
But even though all use libpq.dll, they might not handle the data
the same way. Mayboe ODBC needs to check for data type, field
length, etc... and the other frontends do not need this kind of
information ?

Regards
--
Arnaud

Arnaud Lesauvage <thewild(at)freesurf(dot)fr> writes:

> Magnus Hagander a écrit :
>> Since it works with different speeds in different clients, it *probably*
>> isn't on the server side. I'm far from sure on that though, there may be
>> other paramters that are implicitly changed when ODBC is used.
>
> Yes, that was my first impression also. But the fact that the server
> waits for an ACK from the client before it goes on sending packet is
> quite strange, isn't it ?

I don't think that's what happens.

Those are two very different but unfortunately related things.

1) the Nagle algorithm (disabled by TCP_NO_DELAY) is an optimization
when sending. It prevents sending many small packets, by waiting a
little bit for them to become bigger. The price is a higher latency.

2) the half-ACK frequency is an similar optimization when
receiving. It cuts the number of sent acknowledgements by two, by
waiting a little bit to receive a 2nd packet, acknowledging the two
packets using only one ACK. You can disable it with TcpAckFrequency in
the registry.

So far, those two have nothing in common. But the thing is, TCP
_piggybacks_ acknowledgements packets of data flowing in one direction
together with data packets flowing in the opposite direction.

So, if you force TCP to send data more frequently with TCP_NO_DELAY,
then it will also send ACKs (of the opposite communication) more
frequently as a side effect. Once you decided to send a packet, then
better put the maximum information in it!

Same thing if you force TCP to send ACKs more frequently using
TcpAckFrequency: then you'll get some TCP_NO_DELAY effect as a
bonus. Once you decided to send an ACK, then better send the staging
data in the same packet!

So you see that TcpAckFrequency can (in some scenarios) play the role
of TCP_NO_DELAY. And vice-versa. That's probably what you experienced.

Marc Herbert a écrit :
> Same thing if you force TCP to send ACKs more frequently using
> TcpAckFrequency: then you'll get some TCP_NO_DELAY effect as a
> bonus. Once you decided to send an ACK, then better send the staging
> data in the same packet!

OK, but Magnus is affirmative that TCP_NO_DELAY is enabled on both
the client and the server.
So sending ACKs more frequently should not change the behaviour of
the Nagle algorithm. Or am I still misunderstanding something ?

Regards
--
Arnaud

Arnaud Lesauvage <thewild(at)freesurf(dot)fr> writes:
> OK, but Magnus is affirmative that TCP_NO_DELAY is enabled on both
> the client and the server.
> So sending ACKs more frequently should not change the behaviour of
> the Nagle algorithm.

Yeah. The speculations quoted in
http://archives.postgresql.org/pgsql-odbc/2006-02/msg00046.php
as to the server behavior were completely wrong: we do use NODELAY
and we don't have any weird zero-copy algorithm. However, it is
true that we push out data in 8K bufferloads, which doesn't match
well to typical TCP MTU sizes. I've occasionally wondered whether
we should not try to make the bufferload a multiple of the MTU
size when using a TCP connection, just so that no "short" packets
get sent unnecessarily. (For a known MTU length, it'd be trivial
to change the hard-wired buffer size on the server side just to
experiment and see if there's any speedup.)

None of this explains why odbc is slow when psql isn't, though,
especially not if you are using one of the recent odbc releases
that relies on libpq. The wire-level behavior certainly ought
to be the same for anything using libpq ...

regards, tom lane

Tom Lane a écrit :
> None of this explains why odbc is slow when psql isn't, though,
> especially not if you are using one of the recent odbc releases
> that relies on libpq. The wire-level behavior certainly ought
> to be the same for anything using libpq ...

Yes.
I am trying to figure out what the difference is between an ODBC
call and a "direct" call, from another application...
The issue might be at a higher level, but I have no clue right now...

Regards
--
Arnaud