Re: Re: slow server

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