Hello all,

one of my customers installed Postgres on a public server to access the data
from several places. The problem is that it takes _ages_ to transfer data from
the database to the client app. At first I suspected a problem with the ODBC
driver and my application, but using pgAdminIII 1.6.3.6112 (on Windows XP)
gives the same result.

In table "tblItem" there are exactly 50 records stored. The table has 58
columns: 5 character varying and the rest integer.

As far as I can tell the Postgres installation is o.k.

SELECT VERSION()
"PostgreSQL 8.2.4 on i386-portbld-freebsd6.2, compiled by GCC cc (GCC) 3.4.6
[FreeBSD] 20060305"

EXPLAIN ANALYZE SELECT * FROM "tblItem"
"Seq Scan on "tblItem" (cost=0.00..2.50 rows=50 width=423) (actual
time=0.011..0.048 rows=50 loops=1)"
"Total runtime: 0.150 ms"

The database computer is connected via a 2MBit SDL connection. I myself have a
768/128 KBit ADSL connection and pinging the server takes 150ms on average.

In the pgAdminIII Query Tool the following command takes 15-16 seconds:
SELECT * FROM "tblItem"

During the first 2 seconds the D/L speed is 10-15KB/s. The remaining time the
U/L and D/L speed is constant at 1KB/s.

My customer reported that the same query takes 2-3 seconds for him (with 6MBit
ADSL and 50ms ping).

So my questions are:
* Could there be anything wrong with the server configuration?
* Is the ping difference between the customers and my machine responsible for
the difference in the query execution time?
* Is this normal behaviour or could this be improved somehow?

Thanks in advance for any help.

Rainer

PS: I tried selecting only selected columns from the table and the speed is
proportional to the no. of rows which must be returned. For example selecting
all 5 character columns takes 2 seconds. Selecting 26 integer columns takes
7-8 seconds and selecting all integer columns takes 14 seconds.

Hello Rainer,

The database computer is connected via a 2MBit SDL connection. I myself have
> a
> 768/128 KBit ADSL connection and pinging the server takes 150ms on
> average.
>

I do not have a solution, but I can confirm the problem

One PostgreSQL-Installation: Server 8.1 and 8.2 on Windows in the central;
various others connected via VPN. Queries are subsecond when run locally
(including data transfer), and up to 10 seconds and more via VPN, even in
"off-hours"

The data-transfer is done via PG-Admin or via psycopg2 Python-Database
adapter; nothing with ODBC or similiar in between.

I did not find a solution so far; and for bulk data transfers I now
programmed a workaround.

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!

Rainer Bauer <usenet@munnin.com> writes:
> one of my customers installed Postgres on a public server to access the data
> from several places. The problem is that it takes _ages_ to transfer data from
> the database to the client app. At first I suspected a problem with the ODBC
> driver and my application, but using pgAdminIII 1.6.3.6112 (on Windows XP)
> gives the same result.

I seem to recall that we've seen similar reports before, always
involving Windows :-(. Check whether you have any nonstandard
components hooking into the network stack on that machine.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Hello Harald,

>I do not have a solution, but I can confirm the problem

At least that rules out any misconfiguration issues :-(

>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?

To be honest I didn't expect top performance, but the speed I got suggested
some error on my part.

Rainer

Hello Tom,

>I seem to recall that we've seen similar reports before, always
>involving Windows :-(. Check whether you have any nonstandard
>components hooking into the network stack on that machine.

I just repeated the test by booting into "Safe Mode with Network Support", but
the results are the same. So I don't think that's the cause.

Apart from that, what response times could I expect?

Rainer

I wrote:

>Hello Harald,
>
>>I do not have a solution, but I can confirm the problem
>
>At least that rules out any misconfiguration issues :-(

I did a quick test with my application and enabled the ODBC logging.

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.

Rainer

PS: I wonder why pgAdminIII requires twice the time to retrieve the data.

Hi Rainer,

but did you try to execute your query directly from 'psql' ?...

Why I'm asking: seems to me your case is probably just network latency
dependent, and what I noticed during last benchmarks with PostgreSQL
the SELECT query become very traffic hungry if you are using CURSOR.
Program 'psql' is implemented to not use CURSOR by default, so it'll
be easy to check if you're meeting this issue or not just by executing
your query remotely from 'psql'...

Rgds,
-Dimitri



On 6/21/07, Rainer Bauer <usenet@munnin.com> wrote:
> Hello Tom,
>
> >I seem to recall that we've seen similar reports before, always
> >involving Windows :-(. Check whether you have any nonstandard
> >components hooking into the network stack on that machine.
>
> I just repeated the test by booting into "Safe Mode with Network Support",
> but
> the results are the same. So I don't think that's the cause.
>
> Apart from that, what response times could I expect?
>
> Rainer
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Hello Dimitri,

>but did you try to execute your query directly from 'psql' ?...

munnin=>\timing
munnin=>select * from "tblItem";
<data snipped>
(50 rows)
Time: 391,000 ms

>Why I'm asking: seems to me your case is probably just network latency
>dependent, and what I noticed during last benchmarks with PostgreSQL
>the SELECT query become very traffic hungry if you are using CURSOR.
>Program 'psql' is implemented to not use CURSOR by default, so it'll
>be easy to check if you're meeting this issue or not just by executing
>your query remotely from 'psql'...

Yes, see also my other post.

Unfortunatelly this means that using my program to connect via DSL to the
Postgres database is not possible.

Rainer

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)

Now, change the FETCH_COUNT to 10, then 50, then 100 - your query
execution time should be better (at least I hope so )

And if it's better - you simply need to modify your FETCH clause with
adapted "FORWARD #" value (the best example is psql source code
itself, you may find ExecQueryUsingCursor function implementation
(file common.c))...

Rgds,
-Dimitri

On 6/22/07, Rainer Bauer <usenet@munnin.com> wrote:
> Hello Dimitri,
>
> >but did you try to execute your query directly from 'psql' ?...
>
> munnin=>\timing
> munnin=>select * from "tblItem";
> <data snipped>
> (50 rows)
> Time: 391,000 ms
>
> >Why I'm asking: seems to me your case is probably just network latency
> >dependent, and what I noticed during last benchmarks with PostgreSQL
> >the SELECT query become very traffic hungry if you are using CURSOR.
> >Program 'psql' is implemented to not use CURSOR by default, so it'll
> >be easy to check if you're meeting this issue or not just by executing
> >your query remotely from 'psql'...
>
> Yes, see also my other post.
>
> Unfortunatelly this means that using my program to connect via DSL to the
> Postgres database is not possible.
>
> Rainer
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>

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

http://archives.postgresql.org

Rainer Bauer wrote:
> Hello Dimitri,
>
>
>> but did you try to execute your query directly from 'psql' ?...
>>
>
> munnin=>\timing
> munnin=>select * from "tblItem";
> <data snipped>
> (50 rows)
> Time: 391,000 ms
>
>
>> Why I'm asking: seems to me your case is probably just network latency
>> dependent, and what I noticed during last benchmarks with PostgreSQL
>> the SELECT query become very traffic hungry if you are using CURSOR.
>> Program 'psql' is implemented to not use CURSOR by default, so it'll
>> be easy to check if you're meeting this issue or not just by executing
>> your query remotely from 'psql'...
>>
>
> Yes, see also my other post.
>
> Unfortunatelly this means that using my program to connect via DSL to the
> Postgres database is not possible.

Note that I'm connected via wireless lan here at work (our wireless lan
doesn't connecto to our internal lan directly due to PCI issues) then to
our internal network via VPN.

We are using Cisco with Cisco's vpn client software. I am running
Fedora core 4 on my laptop and I can fetch 10,000 rather chubby rows (a
hundred or more bytes) in about 7 seconds.

So, postgresql over vpn works fine here. Note, no windows machines were
involved in the making of this email. One is doing the job of tossing
it on the internet when I hit send though.

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq