From: Richard Huxton <dev@archonet.com>
To: Joel Fradkin <jfradkin@wazagua.com>
Cc: gsstark@mit.edu
Cc: pgsql-sql@postgresql.org
Subject: Re: same question little different test MSSQL vrs Postgres
Date: Wed, 26 Jan 2005 16:27:29 +0000
Message-ID: <41F7C4F1.2050500@archonet.com> (raw)
In-Reply-To: <000001c503c0$792d8e40$797ba8c0@jfradkin>
References: <000001c503c0$792d8e40$797ba8c0@jfradkin>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, jfradkin@wazagua.com, gsstark@mit.edu
Subject: Re: same question little different test MSSQL vrs Postgres
In-Reply-To: <41F7C4F1.2050500@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox