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@xxxxxxxxxxxxx> wrote:
Rainer Bauer <usenet@xxxxxxxxxx> 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
