From: "Joel Fradkin" <jfradkin@wazagua.com>
To: "'Richard Huxton'" <dev@archonet.com>
Cc: <gsstark@mit.edu>, <pgsql-sql@postgresql.org>
Subject: Re: same question little different test MSSQL vrs Postgres
Date: Wed, 26 Jan 2005 11:02:43 -0500
Message-ID: <000001c503c0$792d8e40$797ba8c0@jfradkin>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
In-Reply-To: <41F7B54C.5020204@archonet.com>

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.

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

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.

Joel Fradkin
=20
Wazagua, Inc.
2520 Trailmate Dr
Sarasota, Florida 34243
Tel.  941-753-7111 ext 305
=20
jfradkin@wazagua.com
www.wazagua.com
Powered by Wazagua
Providing you with the latest Web-based technology & advanced tools.
C 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA, Inc
 This email message is for the use of the intended recipient(s) and may
contain confidential and privileged information.  Any unauthorized =
review,
use, disclosure or distribution is prohibited.  If you are not the =
intended
recipient, please contact the sender by reply email and delete and =
destroy
all copies of the original message, including attachments.
=20

=20

-----Original Message-----
From: Richard Huxton [mailto:dev@archonet.com]=20
Sent: Wednesday, January 26, 2005 10:21 AM
To: Joel Fradkin
Cc: gsstark@mit.edu; pgsql-sql@postgresql.org
Subject: Re: [SQL] same question little different test MSSQL vrs =
Postgres

Joel Fradkin wrote:
> Well last evening (did not try it this morning) it was taking the =
extra
> time.
>=20
> I have made some adjustments to the config file per a few web sites =
that
you
> all recommended my looking at.

The crucial one I'd say is the performance guide at:
   http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php
The first half-dozen settings are the crucial ones.

> It is now using 137 of 756 meg avail.
> it is now taking 8 secs to return 22,000 rows (using pgadminIII in a =
sql
> edit window).

That might be too much RAM. Don't forget PG likes to work with your=20
operating-system (unlike many other DBs). Make sure Windows is using=20
enough RAM to cache diskspace.
I'm curious as to how this takes 8secs whereas you had 1 second earlier. =

Are you sure some of this isn't pgadmin's overhead to display the rows?

> The EXPLAIN ANALYSE still shows the same as below, but the table has
344,000
> recs of which only 22636 are clientnum =3D 'SAKS'

That sounds like it's about the borderline between using an index and=20
not (depending on cache-size, disk speeds etc).

> I am still doing a seq search (this applies to the view question where =
if
it
> is a small result set it used a index search but on a larger return =
set it
> did a seq search) in my view, but with the adjustments to the kernel I =
get
a
> result in 140 secs (MSSQL was 135 secs).

If you want to check whether the index would help, try issuing the=20
following before running your query:
   SET ENABLE_SEQSCAN=3DFALSE;
This will force PG to use any index it can regardless of whether it=20
thinks it will help.

> This is not production, I am still very worried that I have to do all =
this
> tweeking to use this, MSSQL worked out of the box as it does (not =
saying
its
> great, but I never had to adjust a kernel setting etc). Since we =
cannot
> afford the 70,000 dollars they want to license it I am not implying I =
can
> use MSSQL, but I could look at other DB's like MYSQL, or Firebird, =
etc.

I'm a little curious what kernel settings you are changing on Windows. I =

wasn't aware there was much to be done there.

I'm afraid you do have to change half a dozen settings in=20
postgresql.conf to match your workload, but PG runs on a much wider=20
range of machines than MSSQL so it's difficult to come up with a=20
"reasonable" default. Takes me about 5 minutes when I setup an=20
installation to make sure the figures are reasonable (rather than the=20
best they can be).

> I have a lot of time now (two weeks) in this conversion and do not =
wish to
> give up, I will see if I can learn what is needed to get the maximum
> performance. I have seen much information available and this list has =
been
a
> huge resource. I really appreciate all the help.

--
   Richard Huxton
   Archonet Ltd