Re: localhost (windows) performance

I have a friend I asked to load postgres 8.3 on his XP machine. He then
tested a python script which accesses several tables and discovered that it
retrieves data very slowly. It takes about 20 seconds to retrieve the data -
on localhost. However, using a remote connection to a postgres database
(mine) over the internet and running the same python script it takes only 12
seconds. The difference of 8 seconds makes no sense - the remote is
completely on the other coast.

I then thought it had something to do with the data -although the data set is
small. I did a complete dump and restored on his machine and again got the
same results. The machine has a recent motherboard with 2 gb of ram. It
does not appear to be swapping out ram.

Using my local XP (accessing the LINUX database on the LAN) runs the same
python script in just under 3 seconds (most of the time is in loading the
GUI).

One other major difference is I'm running postgres8.2 on linux.

Anybody, have a suggestion - I'm not a windows guru. Or is this normal for
windows?
--
John Fabiani

On Tuesday 07 October 2008 08:20:16 am you wrote:
> On Tue, Oct 7, 2008 at 11:11 AM, johnf <jfabiani(at)yolo(dot)com> wrote:
> > I then thought it had something to do with the data -although the data
> > set is small. I did a complete dump and restored on his machine and
> > again got the same results. The machine has a recent motherboard with 2
> > gb of ram. It does not appear to be swapping out ram.
>
> The standard first question- has the 8.3 db been analyzed?
>
> Compare the plans from the two systems- you may be getting different
> plans that account for the apparent slowdown.

No I did not check. But we are talking about very small data sets. No table
has more than 50 rows. I doubt that analyze will provide anything - but I
could be wrong. There is about 20 SQL statements that read data (selects
only) and they are very straight forward without joins or sub-selects. So
I'm thinking it has something to do with the way windows runs postgres or a
setting in XP.

--
John Fabiani

johnf wrote:
> I have a friend I asked to load postgres 8.3 on his XP machine. He then
> tested a python script which accesses several tables and discovered that it
> retrieves data very slowly. It takes about 20 seconds to retrieve the data -
> on localhost. However, using a remote connection to a postgres database
> (mine) over the internet and running the same python script it takes only 12
> seconds. The difference of 8 seconds makes no sense - the remote is
> completely on the other coast.
>
> I then thought it had something to do with the data -although the data set is
> small. I did a complete dump and restored on his machine and again got the
> same results. The machine has a recent motherboard with 2 gb of ram. It
> does not appear to be swapping out ram.
>
>
> Using my local XP (accessing the LINUX database on the LAN) runs the same
> python script in just under 3 seconds (most of the time is in loading the
> GUI).
>
> One other major difference is I'm running postgres8.2 on linux.
>
> Anybody, have a suggestion - I'm not a windows guru. Or is this normal for
> windows?
Hardware related i'm betting. The client is having to run the python the gui and postgresql on the same hard drive its getting IO bound

What size is the data set 1 meg 20 megs or 100 megs. I have seen small record counts but it was nothing but blobs in the table so the table was 5 gigs.

What is the hardware specs on the XP machine?? Can you post the Select statements???

On Tuesday 07 October 2008 08:54:31 am justin wrote:
> johnf wrote:
> > I have a friend I asked to load postgres 8.3 on his XP machine. He then
> > tested a python script which accesses several tables and discovered that
> > it retrieves data very slowly. It takes about 20 seconds to retrieve the
> > data - on localhost. However, using a remote connection to a postgres
> > database (mine) over the internet and running the same python script it
> > takes only 12 seconds. The difference of 8 seconds makes no sense - the
> > remote is completely on the other coast.
> >
> > I then thought it had something to do with the data -although the data
> > set is small. I did a complete dump and restored on his machine and
> > again got the same results. The machine has a recent motherboard with 2
> > gb of ram. It does not appear to be swapping out ram.
> >
> >
> > Using my local XP (accessing the LINUX database on the LAN) runs the same
> > python script in just under 3 seconds (most of the time is in loading the
> > GUI).
> >
> > One other major difference is I'm running postgres8.2 on linux.
> >
> > Anybody, have a suggestion - I'm not a windows guru. Or is this normal
> > for windows?
>
> Hardware related i'm betting. The client is having to run the python the
> gui and postgresql on the same hard drive its getting IO bound
>
> What size is the data set 1 meg 20 megs or 100 megs. I have seen small
> record counts but it was nothing but blobs in the table so the table was 5
> gigs.
>
> What is the hardware specs on the XP machine?? Can you post the Select
> statements???

SELECT pg_database.datname,
pg_size_pretty(pg_database_size(pg_database.datname)) AS size
FROM pg_database

reveals
"5801 kB"

Like I said very small.

All the statements are similar:
select * from arcust where ccustno = "some var"

No blobs - there are a couple of 'text' fields.

Total data retrieved is very small - just a few thousand bytes.

If haven't said - this is a test database that contains only small amounts of
data.

Looks like we have gotten a major improvement by changing the shared-buffers.

--
John Fabiani

Note that there are also some issues in some flavors of windows with a
QOS setting that gives pgsql network traffic a low priority. I'm not
that familiar with it, I've just seen it mentioned.

There's also some issues that can come up with dns resolution and
network communication.

Just throwing those two out there. I don't use windows anymore
(except to play some classic games) so I'm certainly no expert on this
subject.

Note that there are also some issues in some flavors of windows with a
QOS setting that gives pgsql network traffic a low priority. I'm not
that familiar with it, I've just seen it mentioned.

There's also some issues that can come up with dns resolution and
network communication.

Just throwing those two out there. I don't use windows anymore
(except to play some classic games) so I'm certainly no expert on this
subject.

On Tuesday 07 October 2008 08:11:05 am johnf wrote:
> I have a friend I asked to load postgres 8.3 on his XP machine. He then
> tested a python script which accesses several tables and discovered that it
> retrieves data very slowly. It takes about 20 seconds to retrieve the data
> - on localhost. However, using a remote connection to a postgres database
> (mine) over the internet and running the same python script it takes only
> 12 seconds. The difference of 8 seconds makes no sense - the remote is
> completely on the other coast.
>
> I then thought it had something to do with the data -although the data set
> is small. I did a complete dump and restored on his machine and again got
> the same results. The machine has a recent motherboard with 2 gb of ram.
> It does not appear to be swapping out ram.
>
>
> Using my local XP (accessing the LINUX database on the LAN) runs the same
> python script in just under 3 seconds (most of the time is in loading the
> GUI).
>
> One other major difference is I'm running postgres8.2 on linux.
>
> Anybody, have a suggestion - I'm not a windows guru. Or is this normal for
> windows?
> --
> John Fabiani

The followup:

We decided to switch machines. The performance is now acceptable. Thanks to
all who took the time to consider the issue.

--
John Fabiani