Re: Slow query through ODBC

From: Arnaud Lesauvage <thewild(at)freesurf(dot)fr>
To: pgsql-odbc(at)postgresql(dot)org
Cc: Dave Page <dpage(at)vale-housing(dot)co(dot)uk>, Ludek Finstrle <luf(at)pzkagis(dot)cz>, mha(at)sollentuna(dot)net
Subject: Re: Slow query through ODBC
Date: 2006-02-10 09:23:27
Message-ID: 43EC5B8F.4080002@freesurf.fr
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-odbc

Hi List !

I have some news about this problem !
Just to remind you the facts :
A query runs very slowly when executed through the psqlodbc
driver, but very fast when executed through psql or pgAdmin.
The query returns very long rows containing only one field, but
this field can be as long as 40.000 characters (it is a text field).

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

*But* the interesting thing is that some people suggested that the
problem was lying in the way that the server and the client
talked to each other.

Let me first post the explanation from Bill Meier, on the
Ethereal-users mailing-list, then the one from Chris Marget on the
comp.protocols.tcp-ip newsgroup.

Bill Meier wrote :
> 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).
>
> The longer answer:
>
> Delays may be observerd in client/server query/response types
> of applications over a TCP connection with the Nagle Algorithm enabled.
>
> The presence or absence of a delay is dependent upon the length of
> the query and/or the query response; this causes much confusion when trying
> to analyze delay problems because the delays will seem to "come and go"
> depending upon the exact length of the query and/or response.
>
> There is also much confusion as to the exact nature of the problem.
>
> I believe the current edition of Stevens' "TCPIP Illustrated" gives a pretty clear
> explanation of the issue.
>
>
> The details in your case:
>
> I believe that due to the Nagle Algorithm, for the specific query reponse
> shown in the capture, each time your server sends a "short" packet,
> the server waits for an ACK from the client before sending the next packet.
> In your case, the server application is sending the response in chunks of
> 8192 data bytes which results in a "short" packet every 6 packets).
>
> (I would suspect that a capture of a query/response from one of the
> workstations on which the query runs "very fast" may show that the query response
> is of a different length).

Chris Marget wrote :
>> > The developper of the psqlodbc driver told me that he had used the
>> > same library that is used in pgAdmin (libpq.dll) to talk to the
>> > server !
>
> Are these two applications hitting different socket code on the server
> end?
>
> 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.
>
> The problem comes from the fact that the buffer cannot be reused by the
> application until the receiving TCP has ACKed all the data.
>
> Here's what I think is happening:
>
> Your server is using one of these zero-copy stack methods. It needs to
> send 44078 bytes to the client. The buffer I mentioned is only 8KB, so
> it must be reused 6 times in order to send all 44078 bytes.
>
> So, 8KB is copied in and the stack is asked to send.
> Bad luck causes it to take ~200ms to receive ACKs for all that data
> because of your stack tuning and timing issues.
>
> The buffer becomes available again and another 8KB is copied in.
> ~200ms again.
>
> This cycle repeats 6 times until all 44078 bytes are send and ACKed.
> Nearly 1 second has elapsed.
>
> A smarter application on the server would have used a bigger buffer.
>
> An even smarter application would have used many buffers. 8KB buffers
> are okay if you fill buffer A, notify the stack, fill buffer B, notify
> the stack, etc...
>
> Something else entirely may be going on, but this is my guess.

Both these explanations are far beyond my knowledge !
I am quite confident though that the developers of the libpq.dll
will understand what this is all about.

Was this information of some help to you ?

Best regards,
--
Arnaud

In response to

Browse pgsql-odbc by date

  From Date Subject
Next Message Magnus Hagander 2006-02-10 09:58:33 Re: Slow query through ODBC
Previous Message Shelby Cain 2006-02-09 15:53:03 Re: [ODBC] Problem using ODBC from .NET framework