Hi list
I have been posting about this problem with Dave Page and Magnus
Hagander on pgadmin-support because i first thought I had a problem with
pgAdmin. After further investigation Dave suggested that I post to this
list as I seem to have a more general problem.
In short the problem is that even very simple queries take forever on
some machines, most notably on the server itself. My test query is
'SELECT * FROM table' where the table has ~37000 rows.
At first we had a win 2k test server with postgres 8.0.1 installed with
the pginstaller. The machine is a standard 600 MHz Intel, 1 GB ram and
standard IDE disks. Besides the server itself we have two clients. They
are both pretty new PCs I call them "fast" and "slow" according to their
querying times.
Executing the test query on the dbserver and the two clients using both
psql and pgAdmin gives the following timings:
Fast client->test server: 10 secs
Slow client->test server: 440 secs
Test server->test server: 160 secs
When using pgAdmin to execute the 'SELECT * FROM table' on the slow
client this happens: The network traffic is constant at 0.3 Mbit (nic is
1GBit) for approx 410 secs. Retrieving the 37000 rows takes approx 19
secs.
On the fast client the same query results in 10Mbits of network traffic
(nic is 100 MBit) and the timings are approx 10000 ms + 19000 ms.
So this could indicate network problems. However when using pgAdminIII
or psql on the server itself there is no network traffic and the query
takes 160 secs to complete. There is no cpu use during the 160 seconds.
In all cases limiting the query to a small number of rows (~10) will
make the query complete in <1 sec.
I've tried different versions of libpq.dll but still get the same
results. What should be said is that the fast client (my personal
machine) has lots of postgressoftware (like mapserver and ogr) whereas
the slow client is absolutly clean.
In pure desperation (I have to convince my boss to use PostgreSQL) I
have now installed postgresql 8.0.1 on one of our production servers and
tried to query it from different machines. The server is a win2k on 3GHz
hyperthreading cpu, 2GB ram, SCSI disks, Gbit nic etc.... Timings are
now (old timings in parentheses)
Fast client->new server: 5 secs (10 secs)
Slow client->new server: 480 secs (410 secs)
Test server->new server: 13 secs (160 secs to a db on test server)
New server ->new server: 35 secs (-)
New server ->test server: 26 secs (-)
Lets just assume, that the slow client has some sort of network problem,
although I don't think so. But what about queries from the server
itself?
Regards
Asger Petersen
