[PERFORM] Pgsql - Red Hat Linux - VS MySQL VS MSSQL
Hi all, I’m in the process of initiating a movement in our company to move towards open source software use. As part of this movement I will be recommending PostgreSQL as an alternative to the currently used MSSQL. I’m going with PostgreSQL over MySQL because of the much more complete feature set it provides. (After having used MSSQL for quite some time not having triggers, foreign keys, sub selects, etc. is not an option.) However, to be able to justify the move I will have to demonstrate that PostgreSQL is up to par with MSSQL and MySQL when it comes to performance. After having read through the docs and the lists it seems obvious that PostgreSQL is not configured for high performance out of the box. I don’t have months to learn the ins and outs of PostgreSQL performance tuning so I looked around to see if there are any preconfigured solutions out there. I found that Red Hat Database 2.1 comes with PostgreSQL installed. However, as far as I can tell it comes with postgreSQL 7.2 and it requires Red Hat 8.0 or Red Hat Advanced Server which is based on Red Hat 7.2. Would I be better off installing Red Hat 9.0 and PostgreSQL 7.3 and try to performance tune the installation myself, or should I buy Red Hat Advanced Server and install Red Hat Database 2.1? (Let’s say money is no object) So, does anyone here have any experience using RH AS and DB 2.1? Any advice would be much appreciated. TIA Balazs
Re: [PERFORM] [NOVICE] Optimizer Parameters
Martin Foster <[EMAIL PROTECTED]> writes: > As a side note, would you recommend disabling > fsync for added performance? Only if you are willing to sacrifice crash-safety in the name of speed. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] [NOVICE] Optimizer Parameters
Tom Lane wrote:
force Postgres into using Indexes when available.So I changed the
following two lines in the .conf file:
enable_seqscan = false
enable_nestloop = false
>This was recommended in the documentation,
Where would you say that setting those off in the config file is
"recommended"?
Now how sane is it to keep those options turned off?
It isn't. If you have to force them off for a particular query, do
so right before you issue that query, and turn them on again after.
Turning them off globally is sure to cause you pain later.
And any
way to have the planner quiet guessing tens of thousands of rows will be
return when there are at most hundred?
AND Po.PostTimestamp > (LOCALTIMESTAMP - INTERVAL '10 minutes')
AND Po.PuppetName IS NOT NULL
-> Seq Scan on post po (cost=0.00..14369.84 rows=40513
width=41) (actual time=2820.88..2826.30 rows=392 loops=1)
Filter: ((posttimestamp > (('now'::text)::timestamp(6)
without time zone - '00:10'::interval)) AND (puppetname IS NOT NULL))
Not with that coding technique; "LOCALTIMESTAMP - INTERVAL '10 minutes'"
isn't a constant and so the planner can't look at its statistics to
see that only a small part of the table will be selected.
There are two standard workarounds for this:
1. Do the timestamp arithmetic on the client side, so that the query
you send the backend has a simple constant:
... AND Po.PostTimestamp > '2003-07-12 16:27'
2. Create a function that is falsely marked immutable, viz:
create function ago(interval) returns timestamp without time zone as
'select localtimestamp - $1' language sql immutable strict;
... AND Po.PostTimestamp > ago('10 minutes')
Because the function is marked immutable, the planner will reduce
"ago('10 minutes')" to a constant on sight, and then use that value
for planning purposes. This technique can cause problems, since
in some contexts the reduction will occur prematurely, but as long
as you only use ago() in interactively-issued queries it works okay.
regards, tom lane
http://www.postgresql.org/docs/7.3/static/indexes-examine.html
The conf file does not make a mention of it, other then perhaps being
used to debug. The above link points to disabling it, but tells you
nothing about potential consequences and what to do if it works better
then it did before.
However, when I tried out your functions things started to work much
better then previously. This to say the least is a great sign as it
will increase overall performance.
So thanks for that! As a side note, would you recommend disabling
fsync for added performance? This would be joined with a healthy dose
of a kernel file system buffer.
Simply curious, as I have been increasing certain options for the WAL to
mean it writes less often (transactions are numerous so that's not an
issue) to the hard drives.
Martin Foster
Creator/Designer Ethereal Realms
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[OT] Such incredible h/w (was Re: [PERFORM] Dual Xeon + HW RAIDquestion)
Back in the day, we got good performance from similar sized tables using VMS, a small VAX with only 256MB RAM and narrow SCSI 1GB disks. The RDBMS was DEC's own Rdb/VMS. A "small" mainframe (6 MIPS, 8MB RAM) also gave good performance. So, this old curmudgeon asks, why such beefy h/w for such small databases. On Sat, 2003-07-12 at 13:25, Nikolaus Dilger wrote: > Alexandre, > > Since you want the fastest speed I would do the 2 data > disks in RAID 0 (striping) not RAID 1 (mirroring). > > If you would care about not loosing any transactions > you would keep all 3 disks in RAID 5. > > Don't know the answer to the Hyperthreading question. > Why don't you run a test to find out? > > Regards, > Nikolaus > > On Thu, 10 Jul 2003 14:43:25 -0300 (BRT), "alexandre > arruda paes :: aldeia digital" wrote: > > > > > Hi, > > > > I have this machine with a 10 million records: > > * Dual Xeon 2.0 (HyperThreading enabled), 3 7200 SCSI > , > > Adaptec 2110S, > > RAID 5 - 32k chunk size, 1 GB Ram DDR 266 ECC, RH 8.0 > - > > 2.4.18 > > > > The database is mirrored with contrib/dbmirror in a P4 > > 1 Gb Ram + IDE > > > > If a disk failure occurs, I can use the server in the > > mirror. > > > > I will format the main server in this weekend and I > > have seen in the list > > some people that recomends a Software RAID instead HW. > > > > I think too remove the RAID 5 and turn a RAID 1 for > > data in 2 HDs. > > SO, WAL and swap in the thrid HD. > > > > My questions: > > > > 1) I will see best disk performance changing the disk > > layout like above > > 2) HyperThreading really improve a procces basead > > program, like postgres -- +---+ | Ron Johnson, Jr. Home: [EMAIL PROTECTED] | | Jefferson, LA USA http://members.cox.net/ron.l.johnson | | | | 4 degrees from Vladimir Putin +---+ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Dual Xeon + HW RAID question
Alexandre, Since you want the fastest speed I would do the 2 data disks in RAID 0 (striping) not RAID 1 (mirroring). If you would care about not loosing any transactions you would keep all 3 disks in RAID 5. Don't know the answer to the Hyperthreading question. Why don't you run a test to find out? Regards, Nikolaus On Thu, 10 Jul 2003 14:43:25 -0300 (BRT), "alexandre arruda paes :: aldeia digital" wrote: > > Hi, > > I have this machine with a 10 million records: > * Dual Xeon 2.0 (HyperThreading enabled), 3 7200 SCSI , > Adaptec 2110S, > RAID 5 - 32k chunk size, 1 GB Ram DDR 266 ECC, RH 8.0 - > 2.4.18 > > The database is mirrored with contrib/dbmirror in a P4 > 1 Gb Ram + IDE > > If a disk failure occurs, I can use the server in the > mirror. > > I will format the main server in this weekend and I > have seen in the list > some people that recomends a Software RAID instead HW. > > I think too remove the RAID 5 and turn a RAID 1 for > data in 2 HDs. > SO, WAL and swap in the thrid HD. > > My questions: > > 1) I will see best disk performance changing the disk > layout like above > 2) HyperThreading really improve a procces basead > program, like postgres > > Thank´s for all > > Alexandre > > > > ---(end of > broadcast)--- > TIP 3: if posting/reading through Usenet, please send > an appropriate > subscribe-nomail command to > [EMAIL PROTECTED] so that your > message can get through to the mailing list > cleanly ---(end of broadcast)--- TIP 8: explain analyze is your friend
