Обсуждение: Extremely slow queries in pgAdminIII

Hi list

We are using pgAdminIII to access a postgresql 8.0.0 rc2 on a win2k
machine.

The problem is, that performance of queries done with pgAdmin varies
extremely between clients. For instance we have two winXp machines w.
pgAdminIII v. 1.2.0. Executing a simple 'SELECT * FROM sometable' w.
40000 rows takes 10 secs on one and 450 secs on the other machine.
Executing EXPLAIN is equally fast on the two machines. Network capacity
should not be the problem as the slow machine has 10 times the bandwith
of the fast. Actually the machine running the database also has speed
problems with pgAdminIII.

I would really appreciate any ideas to solve the problem, as this
problem at the moment is prohibiting widespread use of postgres in our
company.

Regards
Asger Petersen

> -----Original Message-----
> From: pgadmin-support-owner@postgresql.org
> [mailto:pgadmin-support-owner@postgresql.org] On Behalf Of
> Asger Petersen
> Sent: 18 March 2005 14:37
> To: pgadmin-support@postgresql.org
> Subject: [pgadmin-support] Extremely slow queries in pgAdminIII
>
> Hi list
>
> We are using pgAdminIII to access a postgresql 8.0.0 rc2 on a win2k
> machine.
>
> The problem is, that performance of queries done with pgAdmin varies
> extremely between clients. For instance we have two winXp machines w.
> pgAdminIII v. 1.2.0. Executing a simple 'SELECT * FROM sometable' w.
> 40000 rows takes 10 secs on one and 450 secs on the other machine.
> Executing EXPLAIN is equally fast on the two machines.
> Network capacity
> should not be the problem as the slow machine has 10 times
> the bandwith
> of the fast. Actually the machine running the database also has speed
> problems with pgAdminIII.

HI Asger,

It sounds like you have the loglevel set to debug on the slower machines
- this causes massive overhead and will slow things to a crawl. You can
check this on the Logging tab under File->Options. The default level is
'Errors Only'.

Regards Dave.

> -----Original Message-----
> From: Asger Petersen [mailto:asger@jo-informatik.dk]
> Sent: 18 March 2005 17:34
> To: Dave Page; pgadmin-support@postgresql.org
> Subject: RE: [pgadmin-support] Extremely slow queries in pgAdminIII
>
> Hi Dave, thanks for your answer.

Drat - I don't ever remember it /not/ being that!

> No, all the machines are set to 'Errors only'. It is a bit of
> a mystery
> to me and I'm beginning to really wonder what makes one machine that
> much faster than the others.
>
> Thinking of it the slow machines all have a clean install of
> pgAdmin and
> no other postgreSQL related software. The fast machine (my
> personal) has
> lots of software using postgres. For instance i have found 6 or 7
> different versions of libpq.dll on it.

OK - that might be a clue. The last version of pgAdmin shipped with
libpq.dll built in Visual C++, however, when we shipped it with the
Windows version of PostgreSQL in the pgInstaller package, we used the
Mingw build of libpq from PostgreSQL (future versions of pgAdmin will
also use the Mingw version).

If you check the libpq.dll's, you can tell the Mingw ones because they
will have version numbers stamped on them, whereas the old VC++ don't
iirc (if they do, they certainly shouldn't be 8.x). I'd be interested to
know if this is the cause.

Regards, Dave.

Hi Dave, thanks for your answer.

No, all the machines are set to 'Errors only'. It is a bit of a mystery
to me and I'm beginning to really wonder what makes one machine that
much faster than the others.

Thinking of it the slow machines all have a clean install of pgAdmin and
no other postgreSQL related software. The fast machine (my personal) has
lots of software using postgres. For instance i have found 6 or 7
different versions of libpq.dll on it.

Regards
Asger

> > Hi list
> >
> > We are using pgAdminIII to access a postgresql 8.0.0 rc2 on a win2k
> > machine.
> >
> > The problem is, that performance of queries done with
> pgAdmin varies
> > extremely between clients. For instance we have two winXp
> machines w.
> > pgAdminIII v. 1.2.0. Executing a simple 'SELECT * FROM sometable' w.
> > 40000 rows takes 10 secs on one and 450 secs on the other machine.
> > Executing EXPLAIN is equally fast on the two machines.
> > Network capacity
> > should not be the problem as the slow machine has 10 times the
> > bandwith of the fast. Actually the machine running the
> database also
> > has speed problems with pgAdminIII.
>
> HI Asger,
>
> It sounds like you have the loglevel set to debug on the
> slower machines
> - this causes massive overhead and will slow things to a
> crawl. You can check this on the Logging tab under
> File->Options. The default level is 'Errors Only'.
>
> Regards Dave.
>

> > Thinking of it the slow machines all have a clean install
> of pgAdmin
> > and no other postgreSQL related software. The fast machine (my
> > personal) has
> > lots of software using postgres. For instance i have found 6 or 7
> > different versions of libpq.dll on it.
>
> OK - that might be a clue. The last version of pgAdmin
> shipped with libpq.dll built in Visual C++, however, when we
> shipped it with the Windows version of PostgreSQL in the
> pgInstaller package, we used the Mingw build of libpq from
> PostgreSQL (future versions of pgAdmin will also use the
> Mingw version).
>
> If you check the libpq.dll's, you can tell the Mingw ones
> because they will have version numbers stamped on them,
> whereas the old VC++ don't iirc (if they do, they certainly
> shouldn't be 8.x). I'd be interested to know if this is the cause.

Ok, i checked the dlls. I assume that pgAdmin will use the first
libpq.dll found in the path. The situation is:
The database host: win2k, pgAdminIII 1.2.0 installed w. pgInstaller rc2,
libpq.dll version 8.0.0.4356. (pgAdmin on this machine is also slow)
Slow client: winXP, pgAdminIII 1.2.0 installed w. installer downloaded
from pgadmin.org, libpq.dll version 8.0.0.0
Fast client: winXP, pgAdminIII 1.2.0 installed w. installer downloaded
from pgadmin.org, libpq.dll version 8.0.0.0

Trying to dig deeper into this, I have used psql on the database host
and on the fast client. On the host my query took forever whereas it
finished in 10 secs on the fast client. So to me it seems pgAdmin is not
really the problem here, but now I have no idea where to look next :-(

Regards
Asger

> -----Original Message-----
> From: pgadmin-support-owner@postgresql.org
> [mailto:pgadmin-support-owner@postgresql.org] On Behalf Of
> Asger Petersen
> Sent: 19 March 2005 11:53
> To: Dave Page; pgadmin-support@postgresql.org
> Subject: Re: [pgadmin-support] Extremely slow queries in pgAdminIII
>
> Ok, i checked the dlls. I assume that pgAdmin will use the first
> libpq.dll found in the path.

Not necessarily - iirc, it'll use one in the working directory, the
system32 directory or the path. It's not quite the same on all versions
of windows either, but I can't find the relevant docs atm.

> The situation is:
> The database host: win2k, pgAdminIII 1.2.0 installed w.
> pgInstaller rc2,
> libpq.dll version 8.0.0.4356. (pgAdmin on this machine is also slow)

OK (you should really upgrade that to 8.0.1!).

> Slow client: winXP, pgAdminIII 1.2.0 installed w. installer downloaded
> from pgadmin.org, libpq.dll version 8.0.0.0
> Fast client: winXP, pgAdminIII 1.2.0 installed w. installer downloaded
> from pgadmin.org, libpq.dll version 8.0.0.0

Hmm, OK. That's my mingw vs vc++ theory out the window :-(

> Trying to dig deeper into this, I have used psql on the database host
> and on the fast client. On the host my query took forever whereas it
> finished in 10 secs on the fast client. So to me it seems
> pgAdmin is not
> really the problem here, but now I have no idea where to look next :-(

No - it's beginning to sound more like a network issue. Anything odd
about the network config on any of those machines, or the switches they
connect to? Firewalls, that sort of thing? Mind you, it's odd that it's
slow running the client on the server as well....

Magnus - this is the case I mentioned yesterday. There is no obvious
relationship between libpq versions and slow/fast machines. One of the
slow machines is the server itself, and it seems that psql is affected
as well. Any ideas?

Regards, Dave.

>> Ok, i checked the dlls. I assume that pgAdmin will use the first
>> libpq.dll found in the path.
>
>Not necessarily - iirc, it'll use one in the working directory, the
>system32 directory or the path. It's not quite the same on all versions
>of windows either, but I can't find the relevant docs atm.

Couple of places to find it, but
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dncode/
html/secure06122003.asp has it.

It changed in XPSP1 and Win2003 server.


>> The situation is:
>> The database host: win2k, pgAdminIII 1.2.0 installed w.
>> pgInstaller rc2,
>> libpq.dll version 8.0.0.4356. (pgAdmin on this machine is also slow)
>
>OK (you should really upgrade that to 8.0.1!).

Absolutely.


>> Slow client: winXP, pgAdminIII 1.2.0 installed w. installer
>downloaded
>> from pgadmin.org, libpq.dll version 8.0.0.0
>> Fast client: winXP, pgAdminIII 1.2.0 installed w. installer
>downloaded
>> from pgadmin.org, libpq.dll version 8.0.0.0
>
>Hmm, OK. That's my mingw vs vc++ theory out the window :-(

Where does libpq.dll 8.0.0.0 come from?  I thought that we'd
date-stamped the final number since back in the early RCs.


>> Trying to dig deeper into this, I have used psql on the database host
>> and on the fast client. On the host my query took forever whereas it
>> finished in 10 secs on the fast client. So to me it seems
>> pgAdmin is not
>> really the problem here, but now I have no idea where to
>look next :-(
>
>No - it's beginning to sound more like a network issue. Anything odd
>about the network config on any of those machines, or the switches they
>connect to? Firewalls, that sort of thing? Mind you, it's odd that it's
>slow running the client on the server as well....
>
>Magnus - this is the case I mentioned yesterday. There is no obvious
>relationship between libpq versions and slow/fast machines. One of the
>slow machines is the server itself, and it seems that psql is affected
>as well. Any ideas?

Not offhand.
Is it the connection that is slow, or the query runtime?
Any dealings with client side encodings that are different?
Is plain TCP flowing fast between these machines? Other kind of
networking stuff?


//Magnus

Hi again

Now i uninstalled 8.0rc2 and installed a 8.0.1 on the server. I also
copied the libpq.dll (v. 8.0.1.5031) to the slow client and did the
simple test again. Same result :-( I'm sure that the slow client uses
the new libpq, as this is the only version of the file on the machine.
The client on the server is still slow and the fast client is still
fast.

When using pgAdmin to execute the 'SELECT * FROM table' on the slow
client this happens:
The network traffic is constant at 0.3 Mbit (connection is 1GBit) for
approx 410 secs. Then I'm asked whether i really want to fetch 37000
rows. If I accept it takes approx 19 secs to retrieve the rows.

On the fast client the same query results in 10Mbits of network traffic
(connection 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'm not sure how to check client side encoding.

The server is a test setup to convince the boss about PostgreSQL. I have
now been allowed to install it on a production server, so hopefully the
problem will be solved by the switch of machines. I still think it
should be investigated why a clean install on a server that is known to
work with other software just doesn't work. If you think it could be of
use to you i will of course do the necessary testing of the problematic
server.

Regards
Asger

> It changed in XPSP1 and Win2003 server.
> >> Trying to dig deeper into this, I have used psql on the
> database host
> >> and on the fast client. On the host my query took forever
> whereas it
> >> finished in 10 secs on the fast client. So to me it seems
> pgAdmin is
> >> not really the problem here, but now I have no idea where to
> >look next :-(
> >
> >No - it's beginning to sound more like a network issue. Anything odd
> >about the network config on any of those machines, or the
> switches they
> >connect to? Firewalls, that sort of thing? Mind you, it's
> odd that it's
> >slow running the client on the server as well....
> >
> >Magnus - this is the case I mentioned yesterday. There is no obvious
> >relationship between libpq versions and slow/fast machines.
> One of the
> >slow machines is the server itself, and it seems that psql
> is affected
> >as well. Any ideas?
>
> Not offhand.
> Is it the connection that is slow, or the query runtime?
> Any dealings with client side encodings that are different?
> Is plain TCP flowing fast between these machines? Other kind of
> networking stuff?
>
>
> //Magnus
>

OK, well I'm pretty stumped. Seeing as the problem occurs in psql as
well though, I would suggest posting to pgsql-general@postgresql.org and
asking there. For simplicity, describe the problem in terms of psql, but
by all means mention that pgAdmin suffers from the same issue. The good
thing about the general list is that the PostgreSQL hackers that wrote
much of the libpq code hang out there.

Sorry I can't be more help - I'd appreciate a CC on your post though so
I can see what the problems ends up being.

Regards, Dave.

> -----Original Message-----
> From: pgadmin-support-owner@postgresql.org
> [mailto:pgadmin-support-owner@postgresql.org] On Behalf Of
> Asger Petersen
> Sent: 21 March 2005 12:12
> To: Magnus Hagander; Dave Page; pgadmin-support@postgresql.org
> Subject: Re: [pgadmin-support] Extremely slow queries in pgAdminIII
>
> Hi again
>
> Now i uninstalled 8.0rc2 and installed a 8.0.1 on the server. I also
> copied the libpq.dll (v. 8.0.1.5031) to the slow client and did the
> simple test again. Same result :-( I'm sure that the slow client uses
> the new libpq, as this is the only version of the file on the machine.
> The client on the server is still slow and the fast client is still
> fast.
>
> When using pgAdmin to execute the 'SELECT * FROM table' on the slow
> client this happens:
> The network traffic is constant at 0.3 Mbit (connection is 1GBit) for
> approx 410 secs. Then I'm asked whether i really want to fetch 37000
> rows. If I accept it takes approx 19 secs to retrieve the rows.
>
> On the fast client the same query results in 10Mbits of
> network traffic
> (connection 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'm not sure how to check client side encoding.
>
> The server is a test setup to convince the boss about
> PostgreSQL. I have
> now been allowed to install it on a production server, so
> hopefully the
> problem will be solved by the switch of machines. I still think it
> should be investigated why a clean install on a server that
> is known to
> work with other software just doesn't work. If you think it
> could be of
> use to you i will of course do the necessary testing of the
> problematic
> server.
>
> Regards
> Asger
>
> > It changed in XPSP1 and Win2003 server.
> > >> Trying to dig deeper into this, I have used psql on the
> > database host
> > >> and on the fast client. On the host my query took forever
> > whereas it
> > >> finished in 10 secs on the fast client. So to me it seems
> > pgAdmin is
> > >> not really the problem here, but now I have no idea where to
> > >look next :-(
> > >
> > >No - it's beginning to sound more like a network issue.
> Anything odd
> > >about the network config on any of those machines, or the
> > switches they
> > >connect to? Firewalls, that sort of thing? Mind you, it's
> > odd that it's
> > >slow running the client on the server as well....
> > >
> > >Magnus - this is the case I mentioned yesterday. There is
> no obvious
> > >relationship between libpq versions and slow/fast machines.
> > One of the
> > >slow machines is the server itself, and it seems that psql
> > is affected
> > >as well. Any ideas?
> >
> > Not offhand.
> > Is it the connection that is slow, or the query runtime?
> > Any dealings with client side encodings that are different?
> > Is plain TCP flowing fast between these machines? Other kind of
> > networking stuff?
> >
> >
> > //Magnus
> >
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>

Asger Petersen wrote:
> Hi again
>
> The network traffic is constant at 0.3 Mbit (connection is 1GBit) for
> approx 410 secs. Then I'm asked whether i really want to fetch 37000
> rows. If I accept it takes approx 19 secs to retrieve the rows.
> 
> On the fast client the same query results in 10Mbits of network traffic
> (connection is 100 MBit) and the timings are approx 10000 ms + 19000 ms.

I've seen problems on a 1GB network some weeks ago, which disappeared as 
soon as an *additional* 1GB switch was inserted into the network. This 
could have been caused by large frames or so, I wasn't able to dig 
further (e.g. NIC parameters).

Regards,
Andreas

> I've seen problems on a 1GB network some weeks ago, which
> disappeared as soon as an *additional* 1GB switch was
> inserted into the network. This could have been caused by
> large frames or so, I wasn't able to dig further (e.g. NIC
> parameters).

There is a thread on pgsql-general which might be related to this problem
http://archives.postgresql.org/pgsql-general/2005-03/msg01086.php.So far the problem hasn't been accurately identified,
butthey also lean towards some kind of networking problem. I think the thread also shows, that the problem isn´t
relatedto pgadmin. 

Thank you all for your help.

Regards
Asger Petersen