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

Wazagua, Inc.
2520 Trailmate Dr
Sarasota, Florida 34243
Tel. 941-753-7111 ext 305

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.




-----Original Message-----
From: Richard Huxton [mailto:dev@archonet.com]
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.
>
> 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/Gener...bits/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
operating-system (unlike many other DBs). Make sure Windows is using
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 = 'SAKS'

That sounds like it's about the borderline between using an index and
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
following before running your query:
SET ENABLE_SEQSCAN=FALSE;
This will force PG to use any index it can regardless of whether it
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
postgresql.conf to match your workload, but PG runs on a much wider
range of machines than MSSQL so it's difficult to come up with a
"reasonable" default. Takes me about 5 minutes when I setup an
installation to make sure the figures are reasonable (rather than the
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


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

We've done some pretty extensive benchmarking and load testing on a
couple of platforms including the Xeon and Opteron. You may have already
bought that Dell box, but I'll say it anyway. Xeon quad processors are a
terrible platform for postgres. Trying to use more than 4GB of memory on
a 32 bit machine is a waste of money.

If you want performance, get a quad Opteron with the same amount of
memory. I guarantee you'll see at least an order of magnitude
performance improvement and substantially more under highly concurrent
loads. If you decide to go this way, HP sells a very nice box. I also
strongly recommend you investigate SuSE instead of RedHat. Fedora core
is good technology, but SuSE offers equally good technology with better
support.

Also make sure that your SCSI HBA is actually using the 64 bit PCI bus.
There are cards out there which plug into 64 bit PCI but only actually
address 32 bits (Qlogic's QLA2340 / 2342 for example).

You make no mention of the disk subsystem you plan to use. This is most
critical part of your system. Database performance is almost always
bound by IO. Usually disk IO. Briefly, put PGDATA on the widest RAID 10
array of disks you can manage. It's not worth spending the extra money
to get 15kRPM disks for this. The size of the disks involved is pretty
much irrelevant, only the number of them matters. Put the WAL files on a
dedicated RAID 1 pair of 15kRPM disks. Put the postgres log files (or
syslog) on a seperate filesystem.

- --
Andrew Hammond 416-673-4138 ahammond@ca.afilias.info
Database Administrator, Afilias Canada Corp.
CB83 2838 4B67 D40F D086 3568 81FC E7E5 27AF 4A9A


Joel Fradkin wrote:
| The postgres is running on Linux Fedora core 3 (production will be
redhat on
| Dell 4 proc 8 gig box).
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.5 (GNU/Linux)

iD8DBQFB+BaPgfzn5SevSpoRAgirAKDBbedScL3leQVidZjmsG mxoph8wQCgvhoW
2ZznEkxOMA3btZEBdzHd8TU=
=eg7h
-----END PGP SIGNATURE-----

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Now you tell me.
We had a fellow working here kept screaming AMD, but I am a very paranoid
person and was not aware Linux and Postgres have been running on the new
chips. I don't like to be a first. We have bought the Dell and I cant tell
you if the controller uses 64bits, I just got what they had on their page
for their 4 proc rack mount. Part of my reason for going Dell was we already
have Dell equipment and the Linux support is offered from Dell as well, so I
have one vendor to worry about. Being a developer and Director of IT I want
the fastest best, but sometimes I flavor my opinions with safest and
easiest. The RPM delivery is something I understand (it's easy). What is SU
like? Is there any difference in the performance between the two Vendors?
I am sure we will be buying more Postgres servers in the near future (One of
the big reasons we are taking the time to convert from MSSQL was so we could
afford to invest in more servers MSSQL was cost prohibitive even for one
server). As easy as Fedura was I still had several issues getting to where I
am now, so I am paranoid of something that requires even more knowledge to
pull it off; that being said I never minded getting into the details to get
a better end result. As you said we have made the investment in the Dell
(25K). I feel pretty stupid if it is as you say a waste of money to get 8
gigs on this platform as I just made that same mistake a year ago when I
bought the 2 processor boxes with standard addition MSSQL and 4 gigs (It
only uses 2 gig). I was under the impression this machine would utilize all
8 gigs. Are you saying only 4 will be available for caching etc, or just the
chipset cant deal with numbers 8 gig and will be slower to access them? If
it is the later then I would imagine it would still outperform a similar box
with 4 gig assuming my demand on cache is larger then 4 gig.

Just to confirm you have these quad Opteron (I am assuming a 4 processor
config?) in a production environment running su and postgres with hardware
support from HP and software from su? You indicate three separate physical
drives will give best performance (one for data 10K speeds, one for admin,
one for wall 15 speed)? I am not too sophisticated at knowing how to irder
this arrangement and set it up in Linux, any chance you could detail (1 card
with 2 channels 4 10k drives on one channel, 2 15k drives on the second, do
I need another channel and drive(s) for admin files?), drive layout when
installing config in postgres to utilize?

If need be maybe we can get you to do this as a consultant as I do
understand how important the hardware and the proper config is. I found out
too late with MSSQL that I should have used two seprate drive arrays, one
for data, one for log (this would have required the split back plane).

So not to plug a specific vendor but if you have production environment
example with real equipment suggestions I would be very appreciative.

I know that's a lot to ask so if you don't have time that's cool, thanks so
much for bringing this up so that my next purchase I will seriously look at
quad Opteron technology if it is a tried and true solution for this OS and
Postgres.

Joel Fradkin



-----Original Message-----
From: Andrew Hammond [mailto:ahammond@ca.afilias.info]
Sent: Wednesday, January 26, 2005 5:16 PM
To: Joel Fradkin
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] same question little different test MSSQL vrs Postgres

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

We've done some pretty extensive benchmarking and load testing on a
couple of platforms including the Xeon and Opteron. You may have already
bought that Dell box, but I'll say it anyway. Xeon quad processors are a
terrible platform for postgres. Trying to use more than 4GB of memory on
a 32 bit machine is a waste of money.

If you want performance, get a quad Opteron with the same amount of
memory. I guarantee you'll see at least an order of magnitude
performance improvement and substantially more under highly concurrent
loads. If you decide to go this way, HP sells a very nice box. I also
strongly recommend you investigate SuSE instead of RedHat. Fedora core
is good technology, but SuSE offers equally good technology with better
support.

Also make sure that your SCSI HBA is actually using the 64 bit PCI bus.
There are cards out there which plug into 64 bit PCI but only actually
address 32 bits (Qlogic's QLA2340 / 2342 for example).

You make no mention of the disk subsystem you plan to use. This is most
critical part of your system. Database performance is almost always
bound by IO. Usually disk IO. Briefly, put PGDATA on the widest RAID 10
array of disks you can manage. It's not worth spending the extra money
to get 15kRPM disks for this. The size of the disks involved is pretty
much irrelevant, only the number of them matters. Put the WAL files on a
dedicated RAID 1 pair of 15kRPM disks. Put the postgres log files (or
syslog) on a seperate filesystem.

- --
Andrew Hammond 416-673-4138 ahammond@ca.afilias.info
Database Administrator, Afilias Canada Corp.
CB83 2838 4B67 D40F D086 3568 81FC E7E5 27AF 4A9A


Joel Fradkin wrote:
| The postgres is running on Linux Fedora core 3 (production will be
redhat on
| Dell 4 proc 8 gig box).
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.5 (GNU/Linux)

iD8DBQFB+BaPgfzn5SevSpoRAgirAKDBbedScL3leQVidZjmsG mxoph8wQCgvhoW
2ZznEkxOMA3btZEBdzHd8TU=
=eg7h
-----END PGP SIGNATURE-----


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster