Thread

1. slow server

   Marc Wrubleski <mlwruble@sorexsoftware.com> — 2001-04-03T17:37:16Z

   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
   
   
   

2. Re: slow server

   Tom Lane <tgl@sss.pgh.pa.us> — 2001-04-03T18:47:47Z

   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
   
   

3. Re: slow server

   Steve Wolfe <steve@iboats.com> — 2001-04-03T18:52:47Z

   > 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
   
   
   
   

4. Re: slow server

   Marc Wrubleski <mlwruble@sorexsoftware.com> — 2001-04-03T19:10:06Z

   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)
   
   
   

5. Problem starting postmaster

   Homayoun Yousefi'zadeh <homayounyz@home.com> — 2001-04-03T19:11:13Z

   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
   
   
   
   
   
   

6. Re: slow server

   Mitch Vincent <mitch@venux.net> — 2001-04-03T19:13:35Z

   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)
   >
   
   
   

7. Re: slow server

   Rod Taylor <rod.taylor@inquent.com> — 2001-04-03T19:17:25Z

   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)
   >
   
   
   

8. Re: Problem starting postmaster

   Tom Lane <tgl@sss.pgh.pa.us> — 2001-04-03T19:59:42Z

   "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
   
   

9. Re: slow server

   Richard Huxton <dev@archonet.com> — 2001-04-03T20:41:25Z

   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
   
   
   

10. Re: slow server

    Mitch Vincent <mitch@venux.net> — 2001-04-03T20:46:02Z

    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
    >
    
    
    

11. Re: Re: slow server

    Marc Wrubleski <mlwruble@sorexsoftware.com> — 2001-04-03T22:22:10Z

    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
    
    
    

12. Re: Re: slow server

    Marc Wrubleski <mlwruble@sorexsoftware.com> — 2001-04-03T22:48:20Z

    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
    
    
    

13. Re: slow server

    Tom Lane <tgl@sss.pgh.pa.us> — 2001-04-03T23:18:11Z

    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
    
    

14. Re: Re: slow server

    Tom Lane <tgl@sss.pgh.pa.us> — 2001-04-03T23:30:54Z

    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
    
    

15. Building ERServer 7.0?

    Homayoun Yousefi'zadeh <homayounyz@home.com> — 2001-04-07T05:31:05Z

    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