Re: Data transfer very slow when connected via DSL

Tom,

seems to me the problem here is rather simple: current issue depends
completely on the low level 'implementation' of SELECT query in the
application. In case it's implemented with using of "DECLARE ...
CURSOR ..." and then "FETCH NEXT" by default (most common case) it
brings application into "ping-pong condition" with database server:
each next FETCH is possible only if the previous one is finished and
server received feedback from client with explicit fetch next order.
In this condition query response time became completely network
latency dependent:
- each packet send/receive has a significant cost
- you cannot reduce this cost as you cannot group more data within
a single packet and you waste your traffic
- that's why TCP_NODELAY become so important here
- with 150ms network latency the cost is ~300ms per FETCH (15sec(!)
for 50 lines)

You may think if you're working in LAN and your network latency is
0.1ms you're not concerned by this issue - but in reality yes, you're
impacted! Each network card/driver has it's own max packet/sec
traffic capability (independent to volume) and once you hit it - your
response time may only degrade with more concurrent sessions (even if
your CPU usage is still low)...

The solution here is simple:
- don't use CURSOR in simple cases when you just reading/printing a
SELECT results
- in case it's too late to adapt your code or you absolutely need
CURSOR for some reasons: replace default "FETCH" or "FETCH NEXT" by
"FETCH 100" (100 rows generally will be enough) normally it'll work
just straight forward (otherwise check you're verifying PQntuples()
value correctly and looping to read all tuples)

To keep default network workload more optimal, I think we need to
bring "FETCH N" more popular for developers and enable it (even
hidden) by default in any ODBC/JDBC and other generic modules...

Rgds,
-Dimitri

On 6/22/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Rainer Bauer <usenet(at)munnin(dot)com> writes:
> > Fetching the 50 rows takes 12 seconds (without logging 8 seconds) and
> > examining the log I found what I suspected: the performance is directly
> > related to the ping time to the server since fetching one tuple requires a
> > round trip to the server.
>
> Hm, but surely you can get it to fetch more than one row at once?
>
> This previous post says that someone else solved an ODBC
> performance problem with UseDeclareFetch=1:
> http://archives.postgresql.org/pgsql-odbc/2006-08/msg00014.php
>
> It's not immediately clear why pgAdmin would have the same issue,
> though, because AFAIK it doesn't rely on ODBC.
>
> I just finished looking through our archives for info about
> Windows-specific network performance problems. There are quite a few
> threads, but the ones that were solved seem not to bear on your problem
> (unless the one above does). I found one pretty interesting thread
> suggesting that the problem was buffer-size dependent:
> http://archives.postgresql.org/pgsql-performance/2006-12/msg00269.php
> but that tailed off with no clear resolution. I think we're going to
> have to get someone to watch the problem with a packet sniffer before
> we can get much further.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>