Rainer Bauer <usenet@munnin.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...8/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...2/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

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@sss.pgh.pa.us> wrote:
> Rainer Bauer <usenet@munnin.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...8/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...2/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
>

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Hello Tom,

>This previous post says that someone else solved an ODBC
>performance problem with UseDeclareFetch=1:

I thought about that too, but enabling UseDeclareFetch will slow down the
query: it takes 30 seconds instead of 8.

>It's not immediately clear why pgAdmin would have the same issue,
>though, because AFAIK it doesn't rely on ODBC.

No it doesn't. That's the reason I used it to verify the behaviour.

But I remember Dave Page mentioning using a virtual list control to display
the results and that means a round trip for every tuple.

>I just finished looking through our archives for info about
>Windows-specific network performance problems.

I don't think it's a Windows-specific problem, because psql is doing the job
blindingly fast. The problem lies in the way my application is coded. See the
response to Dimitri for details.

Rainer

Hello Dimitri,

>Let's stay optimist - at least now you know the main source of your problem! )
>
>Let's see now with CURSOR...
>
>Firstly try this:
>munnin=>\timing
>munnin=>\set FETCH_COUNT 1;
>munnin=>select * from "tblItem";
>
>what's the time you see here? (I think your application is working in
>this manner)

That's it! It takes exactly 8 seconds like my program.

I retrieve the data through a bound column:
SELECT * FROM tblItem WHERE intItemIDCnt = ?

After converting this to
SELECT * FROM tblItem WHERE intItemIDCnt IN (...)
the query is as fast as psql: 409ms

So the problem is identified and the solution is to recode my application.

Rainer

PS: When enabling UseDeclareFetch as suggested by Tom then the runtime is
still three times slower: 1192ms. But I guess that problem is for the ODBC
list.

Rainer, but did you try initial query with FETCH_COUNT equal to 100?...

Rgds,
-Dimitri

On 6/22/07, Rainer Bauer <usenet@munnin.com> wrote:
> Hello Dimitri,
>
> >Let's stay optimist - at least now you know the main source of your
> problem! )
> >
> >Let's see now with CURSOR...
> >
> >Firstly try this:
> >munnin=>\timing
> >munnin=>\set FETCH_COUNT 1;
> >munnin=>select * from "tblItem";
> >
> >what's the time you see here? (I think your application is working in
> >this manner)
>
> That's it! It takes exactly 8 seconds like my program.
>
> I retrieve the data through a bound column:
> SELECT * FROM tblItem WHERE intItemIDCnt = ?
>
> After converting this to
> SELECT * FROM tblItem WHERE intItemIDCnt IN (...)
> the query is as fast as psql: 409ms
>
> So the problem is identified and the solution is to recode my application.
>
> Rainer
>
> PS: When enabling UseDeclareFetch as suggested by Tom then the runtime is
> still three times slower: 1192ms. But I guess that problem is for the ODBC
> list.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
> http://www.postgresql.org/about/donate
>

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Hello Dimitri,

>Rainer, but did you try initial query with FETCH_COUNT equal to 100?...

Yes I tried it with different values and it's like you suspected:

FETCH_COUNT 1 Time: 8642,000 ms
FETCH_COUNT 5 Time: 2360,000 ms
FETCH_COUNT 10 Time: 1563,000 ms
FETCH_COUNT 25 Time: 1329,000 ms
FETCH_COUNT 50 Time: 1140,000 ms
FETCH_COUNT 100 Time: 969,000 ms

\unset FETCH_COUNT Time: 390,000 ms

Rainer

Rainer Bauer wrote:
>> It's not immediately clear why pgAdmin would have the same issue,
>> though, because AFAIK it doesn't rely on ODBC.
>
> No it doesn't. That's the reason I used it to verify the behaviour.
>
> But I remember Dave Page mentioning using a virtual list control to display
> the results and that means a round trip for every tuple.

pgAdmin's Query Tool (which I assume you're using), uses an async query
via libpq to populate a virtual table behind the grid. The query
handling can be seen in pgQueryThread::execute() at
http://svn.pgadmin.org/cgi-bin/viewc...82&view=markup

When the query completes, a dataset object (basically a wrapper around a
PGresult) is attached to the grid control. As the grid renders each
cell, it requests the value to display which results in a call to
PQgetValue. This is how the old display time was eliminated - cells are
only rendered when they become visible for the first time, meaning that
the query executes in pgAdmin in the time it takes for the async query
to complete plus (visible rows * visible columns)PQgetValue calls.

> I don't think it's a Windows-specific problem, because psql is doing the job
> blindingly fast. The problem lies in the way my application is coded. See the
> response to Dimitri for details.

I don't see why pgAdmin should be slow though - it should be only
marginally slower than psql I would think (assuming there are no thinkos
in our code that none of use ever noticed).

Regards, Dave.

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Dave Page wrote:
> I don't see why pgAdmin should be slow though - it should be only
> marginally slower than psql I would think (assuming there are no thinkos
> in our code that none of use ever noticed).

Nevermind...

/D

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Rainer,

>I did not find a solution so far; and for bulk data transfers I now
> >programmed a workaround.
>
> But that is surely based on some component installed on the server, isn't
> it?
>

Correct. I use a pyro-remote server. On request this remote server copies
the relevant rows into a temporary table, uses a copy_to Call to push them
into a StringIO-Objekt (that's Pythons version of "In Memory File"),
serializes that StringIO-Objekt, does a bz2-compression and transfers the
whole block via VPN.

I read on in this thread, and I scheduled to check on psycopg2 and what it
is doing with cursors.

Harald

--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607
fx 01212-5-13695179
-
EuroPython 2007 will take place in Vilnius, Lithuania from Monday 9th July
to Wednesday 11th July. See you there!

>> I did not find a solution so far; and for bulk data transfers I now
>> >programmed a workaround.
>>
>> But that is surely based on some component installed on the server,
>> isn't
>> it?
>>
>
> Correct. I use a pyro-remote server. On request this remote server copies
> the relevant rows into a temporary table, uses a copy_to Call to push
> them
> into a StringIO-Objekt (that's Pythons version of "In Memory File"),
> serializes that StringIO-Objekt, does a bz2-compression and transfers the
> whole block via VPN.
>
> I read on in this thread, and I scheduled to check on psycopg2 and what
> it is doing with cursors.

What about a SSH tunnel using data compression ?
If you fetch all rows from a query in one go, would it be fast ?
Also, PG can now COPY from a query, so you don't really need the temp
table...

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org