Re: same question little different test MSSQL vrs Postgres

Joel Fradkin wrote:
> The postgres is running on Linux Fedora core 3 (production will be redhat on
> Dell 4 proc 8 gig box).
>
> My client pgadminIII is running on XP.
>
> Sorry I was not clearer on this.

Ah! you're the gent who had the problems with SE-Linux on Fedora 3.
Sorry - should have made the connection, but there's so much traffic on
the lists it's easy to miss.

> I am playing with the settings now, I got it to return in 100 secs (the view
> that is that took 135 on MSSQL). My testing is using identical Dell desktops
> for the MSSQL and the Linux, with a third machine for the clients.
>
> I do not mind getting up to speed on the proper setting to optimize the
> hardware, I am worried that as production environment can be somewhat
> dynamic that I will have issues getting a optimized environment and that it
> will work for our needs. My whole reason for being here is that our duel
> proc production MSSQL server is just no longer keeping up with the demand,
> so it is important that whatever I implement is going to up to the
> challenge.

You might want to look at the overall design of the database at some
point too. Also, don't forget the compromises you made when designing
for MSSQL might not be useful (or even harmful) with PG.

> I am still convinced Postgres was the correct choice, especially
> with all the guidance I have been able to get here. 100 seconds will be fine
> compared to the 135 of MSSQL, I just was getting worse responses before
> adjusting. At the moment I think I went too far as I see it using swap and
> going slower, but it never used much of the 756 meg (137 max was all I ever
> saw it use).

If you're on Linux then 135MB sounds like too much (for one client, far
too much).

> I guess the swap buffers and cache are the important settings (least that
> seems to be what is affecting the memory). Not sure exactly what would cause
> it to use seq vrs index, but I will try the force and see if it helps the
> speed.

Try starting with your shared-buffers at say 4000-8000 (32MB to 64MB),
sort-mem/work-mem at 8000-32000 (8MB-32MB), random-page-cost somewhere
between 2 and 4. Then, judge how much RAM your box is using to cache
disk-space (free -m) and set effective-cache-size accordingly. That's it
- you may want to play around with the figures slightly, but pick the
lowest numbers above and restart PG and it'll run OK.

--
Richard Huxton
Archonet Ltd