Thread: slow server

slow server

From
Marc Wrubleski
Date:
Hi, I have two systems one is a 500Mhz Celeron with 128 MB ram and IDE
Disks, the other is 800Mhz PIII, 512MB RAM, SCSI Disks.

Obviously the PIII should stomp on the performance of the Celeron, but
my postgres installation on the faster system is MUCH slower.

I simple query on two tables joined on the celeron takes about .002
seconds. On the PIII it takes .75 seconds. Same Query, same tables, same
indexes. The results from explain are the same. the results from the
query are the same.

Any Ideas?

One thing to think about is the PIII was installed via RPM and the
Celeron wass compiled on that machine. Could this be the limiting
factor?

Marc


Re: slow server

From
Tom Lane
Date:
Marc Wrubleski <mlwruble@sorexsoftware.com> writes:
> Hi, I have two systems one is a 500Mhz Celeron with 128 MB ram and IDE
> Disks, the other is 800Mhz PIII, 512MB RAM, SCSI Disks.

> Obviously the PIII should stomp on the performance of the Celeron, but
> my postgres installation on the faster system is MUCH slower.

> I simple query on two tables joined on the celeron takes about .002
> seconds. On the PIII it takes .75 seconds. Same Query, same tables, same
> indexes. The results from explain are the same. the results from the
> query are the same.

> Any Ideas?

You've managed to tell us absolutely *nothing* of value here.  What
Postgres version, what queries, what query plans exactly?

> One thing to think about is the PIII was installed via RPM and the
> Celeron wass compiled on that machine. Could this be the limiting
> factor?

Kinda makes me wonder if they are different PG versions and/or different
configuration options ...

            regards, tom lane

Re: slow server

From
"Steve Wolfe"
Date:
> One thing to think about is the PIII was installed via RPM and the
> Celeron wass compiled on that machine. Could this be the limiting
> factor?

  When you say that it was installed via the RPM, was that the RPM that
ships with a particular version of RedHat?

steve



Re: slow server

From
Marc Wrubleski
Date:
More info on this problem ...

The Query: select acc.expiringCredits, acc.earnedCredits,
acc.purchasedCredits,
acc.statusid, acc.expired from accounts acc, gateways g where
g.gwnumber =  '02000000' and g.accountid = acc.accountid ;
_________________
- PIII system. (returns results in .75 seconds per query)
 PostgreSQL 7.0.3 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66

running on RedHat 6.2 (with Kernel 2.2.16)

Merge Join  (cost=8.30..80.93 rows=100 width=25)
  ->  Index Scan using accounts_accountid_key on accounts acc
(cost=0.00..60.00 rows=1000 width=21)
  ->  Sort  (cost=8.30..8.30 rows=10 width=4)
        ->  Index Scan using gateways_gwnumber_key on gateways g
(cost=0.00..8.14 rows=10 width=4)
________________
Celeron system (returns results in .002 seconds per query)
 PostgreSQL 7.0.2 on i686-pc-linux-gnu, compiled by gcc 2.96

running on RedHat 7.0

Merge Join  (cost=8.30..80.93 rows=100 width=25)
  ->  Index Scan using accounts_accountid_key on accounts acc
(cost=0.00..60.00 rows=1000 width=21)
  ->  Sort  (cost=8.30..8.30 rows=10 width=4)
        ->  Index Scan using gateways_gwnumber_key on gateways g
(cost=0.00..8.14 rows=10 width=4)

 I hope this can shed light somewhere. I'm need a starting point where I
should start looking. I don't understand why Postgres 7.03 would be so much
slower than 7.02.

Marc

Tom Lane wrote:

> Marc Wrubleski <mlwruble@sorexsoftware.com> writes:
> > Hi, I have two systems one is a 500Mhz Celeron with 128 MB ram and IDE
> > Disks, the other is 800Mhz PIII, 512MB RAM, SCSI Disks.
>
> > Obviously the PIII should stomp on the performance of the Celeron, but
> > my postgres installation on the faster system is MUCH slower.
>
> > I simple query on two tables joined on the celeron takes about .002
> > seconds. On the PIII it takes .75 seconds. Same Query, same tables, same
> > indexes. The results from explain are the same. the results from the
> > query are the same.
>
> > Any Ideas?
>
> You've managed to tell us absolutely *nothing* of value here.  What
> Postgres version, what queries, what query plans exactly?
>
> > One thing to think about is the PIII was installed via RPM and the
> > Celeron wass compiled on that machine. Could this be the limiting
> > factor?
>
> Kinda makes me wonder if they are different PG versions and/or different
> configuration options ...
>
>                         regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)


Problem starting postmaster

From
"Homayoun Yousefi'zadeh"
Date:
Hello there,

I am migrating from MySQL to PostgreSQL.
I have built pgsql 7.0.3 on Redhat 7.0.
All of the nevironmental variables have been
set properly. When I try to start the database
server using the following

bash-2.04$  /usr/local/pgsql/bin/postmaster

I get

IpcMemoryCreate: shmget failed (Identifier removed) key=5432010,
size=144, permission=700
This type of error is usually caused by an improper
shared memory or System V IPC semaphore configuration.
For more information, see the FAQ and platform-specific
FAQ's in the source directory pgsql/doc or on our
web site at http://www.postgresql.org.
IpcMemoryIdGet: shmget failed (Identifier removed) key=5432010,
size=144, permission=0
IpcMemoryAttach: shmat failed (Invalid argument) id=-2
FATAL 1:  AttachSLockMemory: could not attach segment

I have read the docs. The only probable cause is the
lack of SYS V IPC support or small size of configured
shared memory in the kernel. However, neither one of
these suggestions applies to my case. I have even tried

bash-2.04$ /usr/local/pgsql/bin/postmaster -B 64

and the problem still persists.

Can somebody shed some light here?

Thanks,
HY





Re: slow server

From
"Mitch Vincent"
Date:
Are you starting the postmasters differently on the two machines?

-Mitch
Software development :
You can have it cheap, fast or working. Choose two.

----- Original Message -----
From: "Marc Wrubleski" <mlwruble@sorexsoftware.com>
To: <pgsql-general@postgresql.org>
Sent: Tuesday, April 03, 2001 3:10 PM
Subject: Re: slow server


> More info on this problem ...
>
> The Query: select acc.expiringCredits, acc.earnedCredits,
> acc.purchasedCredits,
> acc.statusid, acc.expired from accounts acc, gateways g where
> g.gwnumber =  '02000000' and g.accountid = acc.accountid ;
> _________________
> - PIII system. (returns results in .75 seconds per query)
>  PostgreSQL 7.0.3 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66
>
> running on RedHat 6.2 (with Kernel 2.2.16)
>
> Merge Join  (cost=8.30..80.93 rows=100 width=25)
>   ->  Index Scan using accounts_accountid_key on accounts acc
> (cost=0.00..60.00 rows=1000 width=21)
>   ->  Sort  (cost=8.30..8.30 rows=10 width=4)
>         ->  Index Scan using gateways_gwnumber_key on gateways g
> (cost=0.00..8.14 rows=10 width=4)
> ________________
> Celeron system (returns results in .002 seconds per query)
>  PostgreSQL 7.0.2 on i686-pc-linux-gnu, compiled by gcc 2.96
>
> running on RedHat 7.0
>
> Merge Join  (cost=8.30..80.93 rows=100 width=25)
>   ->  Index Scan using accounts_accountid_key on accounts acc
> (cost=0.00..60.00 rows=1000 width=21)
>   ->  Sort  (cost=8.30..8.30 rows=10 width=4)
>         ->  Index Scan using gateways_gwnumber_key on gateways g
> (cost=0.00..8.14 rows=10 width=4)
>
>  I hope this can shed light somewhere. I'm need a starting point where I
> should start looking. I don't understand why Postgres 7.03 would be so
much
> slower than 7.02.
>
> Marc
>
> Tom Lane wrote:
>
> > Marc Wrubleski <mlwruble@sorexsoftware.com> writes:
> > > Hi, I have two systems one is a 500Mhz Celeron with 128 MB ram and IDE
> > > Disks, the other is 800Mhz PIII, 512MB RAM, SCSI Disks.
> >
> > > Obviously the PIII should stomp on the performance of the Celeron, but
> > > my postgres installation on the faster system is MUCH slower.
> >
> > > I simple query on two tables joined on the celeron takes about .002
> > > seconds. On the PIII it takes .75 seconds. Same Query, same tables,
same
> > > indexes. The results from explain are the same. the results from the
> > > query are the same.
> >
> > > Any Ideas?
> >
> > You've managed to tell us absolutely *nothing* of value here.  What
> > Postgres version, what queries, what query plans exactly?
> >
> > > One thing to think about is the PIII was installed via RPM and the
> > > Celeron wass compiled on that machine. Could this be the limiting
> > > factor?
> >
> > Kinda makes me wonder if they are different PG versions and/or different
> > configuration options ...
> >
> >                         regards, tom lane
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>


Re: slow server

From
"Rod Taylor"
Date:
Probably more to do with the kernel that compiled Postgres and the
flags used.  -O versus -O2, etc.

Just for kicks, can you try with 7.1?

BTW, did you vacuum both databases shortly before running the queries,
and what happens in a repeated running (say table was in cache for one
system and not on the other).

I also notice a rather significant different in operating system as a
whole.

--
Rod Taylor

There are always four sides to every story: your side, their side, the
truth, and what really happened.
----- Original Message -----
From: "Marc Wrubleski" <mlwruble@sorexsoftware.com>
To: <pgsql-general@postgresql.org>
Sent: Tuesday, April 03, 2001 3:10 PM
Subject: Re: [GENERAL] slow server


> More info on this problem ...
>
> The Query: select acc.expiringCredits, acc.earnedCredits,
> acc.purchasedCredits,
> acc.statusid, acc.expired from accounts acc, gateways g where
> g.gwnumber =  '02000000' and g.accountid = acc.accountid ;
> _________________
> - PIII system. (returns results in .75 seconds per query)
>  PostgreSQL 7.0.3 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66
>
> running on RedHat 6.2 (with Kernel 2.2.16)
>
> Merge Join  (cost=8.30..80.93 rows=100 width=25)
>   ->  Index Scan using accounts_accountid_key on accounts acc
> (cost=0.00..60.00 rows=1000 width=21)
>   ->  Sort  (cost=8.30..8.30 rows=10 width=4)
>         ->  Index Scan using gateways_gwnumber_key on gateways g
> (cost=0.00..8.14 rows=10 width=4)
> ________________
> Celeron system (returns results in .002 seconds per query)
>  PostgreSQL 7.0.2 on i686-pc-linux-gnu, compiled by gcc 2.96
>
> running on RedHat 7.0
>
> Merge Join  (cost=8.30..80.93 rows=100 width=25)
>   ->  Index Scan using accounts_accountid_key on accounts acc
> (cost=0.00..60.00 rows=1000 width=21)
>   ->  Sort  (cost=8.30..8.30 rows=10 width=4)
>         ->  Index Scan using gateways_gwnumber_key on gateways g
> (cost=0.00..8.14 rows=10 width=4)
>
>  I hope this can shed light somewhere. I'm need a starting point
where I
> should start looking. I don't understand why Postgres 7.03 would be
so much
> slower than 7.02.
>
> Marc
>
> Tom Lane wrote:
>
> > Marc Wrubleski <mlwruble@sorexsoftware.com> writes:
> > > Hi, I have two systems one is a 500Mhz Celeron with 128 MB ram
and IDE
> > > Disks, the other is 800Mhz PIII, 512MB RAM, SCSI Disks.
> >
> > > Obviously the PIII should stomp on the performance of the
Celeron, but
> > > my postgres installation on the faster system is MUCH slower.
> >
> > > I simple query on two tables joined on the celeron takes about
.002
> > > seconds. On the PIII it takes .75 seconds. Same Query, same
tables, same
> > > indexes. The results from explain are the same. the results from
the
> > > query are the same.
> >
> > > Any Ideas?
> >
> > You've managed to tell us absolutely *nothing* of value here.
What
> > Postgres version, what queries, what query plans exactly?
> >
> > > One thing to think about is the PIII was installed via RPM and
the
> > > Celeron wass compiled on that machine. Could this be the
limiting
> > > factor?
> >
> > Kinda makes me wonder if they are different PG versions and/or
different
> > configuration options ...
> >
> >                         regards, tom lane
> >
> > ---------------------------(end of
broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister
command
> >     (send "unregister YourEmailAddressHere" to
majordomo@postgresql.org)
>
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to
majordomo@postgresql.org)
>


Re: Problem starting postmaster

From
Tom Lane
Date:
"Homayoun Yousefi'zadeh" <homayounyz@home.com> writes:
> IpcMemoryIdGet: shmget failed (Identifier removed) key=5432010,
> size=144, permission=0

Hm.  As root, try "ipcs -m" to see if there are already any shared
memory segments laying about with that key value (note that ipcs will
show the key value in hex on Linux...).  If so, try zapping 'em with
ipcrm.  [Or you could just reboot, if that seems easier.]

            regards, tom lane

Re: slow server

From
"Richard Huxton"
Date:
From: "Marc Wrubleski" <mlwruble@sorexsoftware.com>

> Hi, I have two systems one is a 500Mhz Celeron with 128 MB ram and IDE
> Disks, the other is 800Mhz PIII, 512MB RAM, SCSI Disks.
>
> Obviously the PIII should stomp on the performance of the Celeron, but
> my postgres installation on the faster system is MUCH slower.
>
> I simple query on two tables joined on the celeron takes about .002
> seconds. On the PIII it takes .75 seconds. Same Query, same tables, same
> indexes. The results from explain are the same. the results from the
> query are the same.

0.002 seconds? Doubtful, but even if it was 0.2 seconds the results are
puzzling. There's someone else with a suspiciously similar question recently
too (Daniel Akerud - but with a circle over the A)

> Any Ideas?
>
> One thing to think about is the PIII was installed via RPM and the
> Celeron wass compiled on that machine. Could this be the limiting
> factor?

Well - RPMs tend to be i386 optimised rather than for Pentiums (ie they're
not), but that'd be all. I take it you're not seeing any disk activity
during this query, which would mean it *must* be CPU related.

Could you post the version of Postgres you have on each machine, along with
the explain for the query? It might mean something to one of the developers.
Oh - OS with versions would be useful too (Linux presumably, but versions
might be useful).

FWIW there are only two things I can think of:

1. Cache issues - maybe the RPM is breaking the caching on the PIII
2. Broken socket code - although I can't think what would do this
3. The RPM and compiled versions are different and something odd has
changed.

If nothing else on the machine seems slow, I can only suggest compiling from
source and seeing what that does for you.

- Richard Huxton


Re: slow server

From
"Mitch Vincent"
Date:
In addition to my previous questions and all other questions, I have another
:-)

Where are you getting your .002 and .75 numbers? Perhaps it's the way in
which you're measuring the queries that has the problem? Oh and what OS are
you using?

-Mitch
Software development :
You can have it cheap, fast or working. Choose two.

----- Original Message -----
From: "Richard Huxton" <dev@archonet.com>
To: "Marc Wrubleski" <mlwruble@sorexsoftware.com>;
<pgsql-general@postgresql.org>
Sent: Tuesday, April 03, 2001 4:41 PM
Subject: Re: slow server


> From: "Marc Wrubleski" <mlwruble@sorexsoftware.com>
>
> > Hi, I have two systems one is a 500Mhz Celeron with 128 MB ram and IDE
> > Disks, the other is 800Mhz PIII, 512MB RAM, SCSI Disks.
> >
> > Obviously the PIII should stomp on the performance of the Celeron, but
> > my postgres installation on the faster system is MUCH slower.
> >
> > I simple query on two tables joined on the celeron takes about .002
> > seconds. On the PIII it takes .75 seconds. Same Query, same tables, same
> > indexes. The results from explain are the same. the results from the
> > query are the same.
>
> 0.002 seconds? Doubtful, but even if it was 0.2 seconds the results are
> puzzling. There's someone else with a suspiciously similar question
recently
> too (Daniel Akerud - but with a circle over the A)
>
> > Any Ideas?
> >
> > One thing to think about is the PIII was installed via RPM and the
> > Celeron wass compiled on that machine. Could this be the limiting
> > factor?
>
> Well - RPMs tend to be i386 optimised rather than for Pentiums (ie they're
> not), but that'd be all. I take it you're not seeing any disk activity
> during this query, which would mean it *must* be CPU related.
>
> Could you post the version of Postgres you have on each machine, along
with
> the explain for the query? It might mean something to one of the
developers.
> Oh - OS with versions would be useful too (Linux presumably, but versions
> might be useful).
>
> FWIW there are only two things I can think of:
>
> 1. Cache issues - maybe the RPM is breaking the caching on the PIII
> 2. Broken socket code - although I can't think what would do this
> 3. The RPM and compiled versions are different and something odd has
> changed.
>
> If nothing else on the machine seems slow, I can only suggest compiling
from
> source and seeing what that does for you.
>
> - Richard Huxton
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>


Re: Re: slow server

From
Marc Wrubleski
Date:
My result speeds are from running a simple loop on my web server in a php script
via a pg_pconnect connection. I run the loop 1000 times and get the time taken
to perform the whole loop and then divy the time by 1000 to get the average time
per query. (The Celeron can finish with 1000 in 1/3 the time the PIII takes to
perform 10)

On the PIII I didn't try over 100, lest it take forever...

I also double checked in psql for each system and there is a definite ~1 second
pause on the PIII that does not exist on the Celeron.

The startup parameters are the same for both systems: -i -B 256 -N 128

Also, I checked 'top' when the query loop is running and postgres is using 99.x
% of CPU.

Marc Wrubleski

Mitch Vincent wrote:

> In addition to my previous questions and all other questions, I have another
> :-)
>
> Where are you getting your .002 and .75 numbers? Perhaps it's the way in
> which you're measuring the queries that has the problem? Oh and what OS are
> you using?
>
> -Mitch
> Software development :
> You can have it cheap, fast or working. Choose two.
>
> ----- Original Message -----
> From: "Richard Huxton" <dev@archonet.com>
> To: "Marc Wrubleski" <mlwruble@sorexsoftware.com>;
> <pgsql-general@postgresql.org>
> Sent: Tuesday, April 03, 2001 4:41 PM
> Subject: Re: slow server
>
> > From: "Marc Wrubleski" <mlwruble@sorexsoftware.com>
> >
> > > Hi, I have two systems one is a 500Mhz Celeron with 128 MB ram and IDE
> > > Disks, the other is 800Mhz PIII, 512MB RAM, SCSI Disks.
> > >
> > > Obviously the PIII should stomp on the performance of the Celeron, but
> > > my postgres installation on the faster system is MUCH slower.
> > >
> > > I simple query on two tables joined on the celeron takes about .002
> > > seconds. On the PIII it takes .75 seconds. Same Query, same tables, same
> > > indexes. The results from explain are the same. the results from the
> > > query are the same.
> >
> > 0.002 seconds? Doubtful, but even if it was 0.2 seconds the results are
> > puzzling. There's someone else with a suspiciously similar question
> recently
> > too (Daniel Akerud - but with a circle over the A)
> >
> > > Any Ideas?
> > >
> > > One thing to think about is the PIII was installed via RPM and the
> > > Celeron wass compiled on that machine. Could this be the limiting
> > > factor?
> >
> > Well - RPMs tend to be i386 optimised rather than for Pentiums (ie they're
> > not), but that'd be all. I take it you're not seeing any disk activity
> > during this query, which would mean it *must* be CPU related.
> >
> > Could you post the version of Postgres you have on each machine, along
> with
> > the explain for the query? It might mean something to one of the
> developers.
> > Oh - OS with versions would be useful too (Linux presumably, but versions
> > might be useful).
> >
> > FWIW there are only two things I can think of:
> >
> > 1. Cache issues - maybe the RPM is breaking the caching on the PIII
> > 2. Broken socket code - although I can't think what would do this
> > 3. The RPM and compiled versions are different and something odd has
> > changed.
> >
> > If nothing else on the machine seems slow, I can only suggest compiling
> from
> > source and seeing what that does for you.
> >
> > - Richard Huxton
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://www.postgresql.org/search.mpl


Re: Re: slow server

From
Marc Wrubleski
Date:
A good ( and simple ) lesson to be learned here. The poor performance was because I
did not run vacuum analyse on the db. Thanks to Rod Taylor who gave that hint. A
massive speed difference even on a very small testing DB.

Thanks people!

Marc Wrubleski

Marc Wrubleski wrote:

> My result speeds are from running a simple loop on my web server in a php script
> via a pg_pconnect connection. I run the loop 1000 times and get the time taken
> to perform the whole loop and then divy the time by 1000 to get the average time
> per query. (The Celeron can finish with 1000 in 1/3 the time the PIII takes to
> perform 10)
>
> On the PIII I didn't try over 100, lest it take forever...
>
> I also double checked in psql for each system and there is a definite ~1 second
> pause on the PIII that does not exist on the Celeron.
>
> The startup parameters are the same for both systems: -i -B 256 -N 128
>
> Also, I checked 'top' when the query loop is running and postgres is using 99.x
> % of CPU.
>
> Marc Wrubleski
>
> Mitch Vincent wrote:
>
> > In addition to my previous questions and all other questions, I have another
> > :-)
> >
> > Where are you getting your .002 and .75 numbers? Perhaps it's the way in
> > which you're measuring the queries that has the problem? Oh and what OS are
> > you using?
> >
> > -Mitch
> > Software development :
> > You can have it cheap, fast or working. Choose two.
> >
> > ----- Original Message -----
> > From: "Richard Huxton" <dev@archonet.com>
> > To: "Marc Wrubleski" <mlwruble@sorexsoftware.com>;
> > <pgsql-general@postgresql.org>
> > Sent: Tuesday, April 03, 2001 4:41 PM
> > Subject: Re: slow server
> >
> > > From: "Marc Wrubleski" <mlwruble@sorexsoftware.com>
> > >
> > > > Hi, I have two systems one is a 500Mhz Celeron with 128 MB ram and IDE
> > > > Disks, the other is 800Mhz PIII, 512MB RAM, SCSI Disks.
> > > >
> > > > Obviously the PIII should stomp on the performance of the Celeron, but
> > > > my postgres installation on the faster system is MUCH slower.
> > > >
> > > > I simple query on two tables joined on the celeron takes about .002
> > > > seconds. On the PIII it takes .75 seconds. Same Query, same tables, same
> > > > indexes. The results from explain are the same. the results from the
> > > > query are the same.
> > >
> > > 0.002 seconds? Doubtful, but even if it was 0.2 seconds the results are
> > > puzzling. There's someone else with a suspiciously similar question
> > recently
> > > too (Daniel Akerud - but with a circle over the A)
> > >
> > > > Any Ideas?
> > > >
> > > > One thing to think about is the PIII was installed via RPM and the
> > > > Celeron wass compiled on that machine. Could this be the limiting
> > > > factor?
> > >
> > > Well - RPMs tend to be i386 optimised rather than for Pentiums (ie they're
> > > not), but that'd be all. I take it you're not seeing any disk activity
> > > during this query, which would mean it *must* be CPU related.
> > >
> > > Could you post the version of Postgres you have on each machine, along
> > with
> > > the explain for the query? It might mean something to one of the
> > developers.
> > > Oh - OS with versions would be useful too (Linux presumably, but versions
> > > might be useful).
> > >
> > > FWIW there are only two things I can think of:
> > >
> > > 1. Cache issues - maybe the RPM is breaking the caching on the PIII
> > > 2. Broken socket code - although I can't think what would do this
> > > 3. The RPM and compiled versions are different and something odd has
> > > changed.
> > >
> > > If nothing else on the machine seems slow, I can only suggest compiling
> > from
> > > source and seeing what that does for you.
> > >
> > > - Richard Huxton
> > >
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> > >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> > http://www.postgresql.org/search.mpl
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html


Re: slow server

From
Tom Lane
Date:
Marc Wrubleski <mlwruble@sorexsoftware.com> writes:
> One thing to think about is the PIII was installed via RPM and the
> Celeron wass compiled on that machine. Could this be the limiting
> factor?

It would be worth trying to compile 7.0.3 from source on the PIII,
rather than using the RPM installation.  That would eliminate a whole
set of variables about configure options, compiler and compiler options,
etc.

Besides which, if you do compile from source, you'll be set up to try
my next suggestion ;-).  Which is to get a profile and find out where
the time is going.  The whole thing seems quite strange to me; I can
think of patches that might have changed 7.0.3's performance, but not
on that query, and not by that much.

If the source compilation doesn't show any change in performance,
then do this to get a profile:

1. Go into src/backend of the built tree and do
    make clean
    make PROFILE=-pg all
This will build a new postgres executable with profiling support.
Install the executable by shutting down the postmaster and doing
"make install-bin" in that same directory.  (You might want to save
the old executable so you don't have to rebuild when you're done
profiling.)

2. Run the problem query.  The way you were doing it (1000 iterations
on one connection) is good.  Then close the connection.

3. You should find a file "gmon.out" in the $PGDATA/base/YOURDB/
directory after the backend exits.  Run
    gprof /path/to/postgres/executable $PGDATA/base/YOURDB/gmon.out
to produce the profile (it's long, so send it into a file).  Be sure
to give the path to the profiling-enabled executable.

4. Then reinstall the non-profiling backend so you don't pay for
profiling all the time.

The profile output is probably too verbose to post here, but if you'll
send it to me I'll see what I can figure out.

            regards, tom lane

Re: Re: slow server

From
Tom Lane
Date:
Marc Wrubleski <mlwruble@sorexsoftware.com> writes:
> A good ( and simple ) lesson to be learned here. The poor performance
> was because I did not run vacuum analyse on the db.

Hmm ... that should have shown up in different plans.  Didn't you say
that you had checked the plans were the same?

            regards, tom lane

Building ERServer 7.0?

From
"Homayoun Yousefi'zadeh"
Date:
I have built and am running pgsql version 7.0.3 on
Redhat Linux 7.0. I downloaded ERServer version 7.0
but the source distribution seems to be missing
at least a Makefile. There is a README.rserv saying

Requirements:

- PostgreSQL >= 7.0.X
   A separate Makefile is required for PostgreSQL 7.0.x and earlier

Distribution Makefile makes a reference to pgsql Makefile.global
that does not exist in pgsql src distribution. I changed that to
Makefile.global.in that does exist. Now, make all command fails
with the following msg:

[h]$ make all
/home/homayoun/pgsql/postgresql-7.0.3/src/Makefile.global.in:236:
/home/homayoun/pgsql/postgresql-7.0.3/src/Makefile.port: No such file or
directory
make: *** No rule to make target
`/home/h/pgsql/postgresql-7.0.3/src/Makefile.port'.  Stop.

Can some one please explain what I am missing here and
where I can find the missing Makefile if at all available?


Regards,
HY