Data transfer very slow when connected via DSL

From: Rainer Bauer <usenet(at)munnin(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Data transfer very slow when connected via DSL
Date: 2007-06-21 14:22:57
Message-ID: s7tk73d57kvb3f4ph1kmkvhdfe0kak5a1r@4ax.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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.

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2007-06-21 14:29:12 Re: Volunteer to build a configuration tool
Previous Message Francisco Reyes 2007-06-21 12:43:07 Re: Hardware suggestions