Re: Tuning PostgreSQL

Wow, I never figured how many different RAID configurations one could think
of :)

After reading lots of material, forums and of course, this mailing-list, I
think I am going for a RAID5 configuration of 6 disks (18Gb, 15.000 rpm
each), one of those six disks will be a 'hot spare'. I will just put the OS,
the WAL and the data one one volume. RAID10 is way to expensive :)

If I understand correctly, this will give great read-performance, but less
write-performance. But since this server will be equipped with an embedded
RAID controller featuring 128Mb of battery-backed cache, I figure that this
controller will negate that (at least somewhat). I will need to find out
whether this cache can be configured so that it will ONLY cache WRITES, not
READS....

Also because of this battery backed cache controller, I will go for the ext2
file system, mounted with 'noatime'. I will use a UPS, so I don't think I
need the journaling of ext3. XFS is not natively supported by RedHat and I
will go for the easy way here :)

1 Gb of RAM should be enough, I think. That is about the only point that
almost everyone agrees on :) Do you think ECC is very important? The
server I have in mind does not support it. Another one does, but is is about
1.000 euros more expensive :(

One CPU should also be enough.

As for postgresql.conf settings, I think I will start with the following :

max_connections = 128
superuser_reserved_connections = 1
shared_buffers = 8192
max_fsm_relations = 1000
max_fsm_pages = 100000
wal_buffers = 32
sort_mem = 2048
vacuum_mem = 32768
effective_cache_size = 28672 (this one I'm not sure about, maybe this one
needs to be higher)
random_page_cost = 2
geq0_threshold = 20

This pretty much sums it up. What do you think about this config? It may not
be the fastest, but a server like this will cost about 4750 euros, and that
is including an Intel Xeon 2.4GHz cpu, redundant power supply, WITHOUT the
UPS. Seems very reasonable to me...

Kind regards,
Alexander Priem.

----- Original Message -----
From: "Vincent van Leeuwen" <pgsql(dot)spam(at)vinz(dot)nl>
To: <pgsql-performance(at)postgresql(dot)org>
Sent: Tuesday, July 22, 2003 11:40 AM
Subject: Re: [PERFORM] Tuning PostgreSQL

> On 2003-07-22 09:04:42 +0200, Alexander Priem wrote:
> > Hi all,
> >
> > Vincent, You said that using RAID1, you don't have real redundancy. But
> > RAID1 is mirroring, right? So if one of the two disks should fail, there
> > should be no data lost, right?
> >
>
> Right. But the proposal was a single disk for WAL, without redundancy, and
I
> argued that wasn't really safe. RAID1 by itself is extremely safe,
possibly
> even the safest RAID type there is.
>
> > I have been thinking some more. 18Gb drives are cheaper than 36 or 72Gb
> > drives. I don't know if I can get the money for this, but how would the
> > following setup sound?
> >
> > Two 18Gb (15.000rpm) disks in RAID1 array for Operating System + WAL.
> > Four 18Gb (15.000rpm) disks in RAID5 array for data.
> >
>
> Our own testing has shown that a 6 disk RAID-10 array is faster than what
you
> describe. Of course, this is very much dependant on how much
INSERT/UPDATES
> you generate (which taxes your WAL more), so your mileage may vary.
>
> > For the same amount of money, I could also get:
> >
> > Two 36Gb (10.000rpm) disks in RAID1 array for Operating System + WAL.
> > Five/Six 36Gb (10.000rpm) disks in RAID5 array for data.
> >
>
> It is said that a higher RPM is particularly useful for a WAL disk. So you
> might consider using two 18GB 15K rpm drives for a RAID-1 WAL disk (+OS
and
> swap), and using 36GB 10K rpm disks in a RAID-5 array if you need that
> diskspace.
>
> > Which would be the best of the above? The one with four 15k-rpm disks or
the
> > one with five/six 10k-rpm disks?
> > Would these configs be better than all disks in one huge RAID5 array?
There
> > are so many possible configs with RAID.......
> >
>
> 15K rpm disks are significantly faster than 10K rpm disks. If your only
> concern is performance, buy 15K rpm disks. If you want more diskspace for
your
> money, fall back to larger 10K rpm disks.
>
> I personally think seperate WAL disks are vastly overrated, since they
haven't
> shown a big performance gain in our own tests. But as I have said, this is
> extremely dependant on the type of load you generate, so only your own
tests
> can tell you what you should do in this respect.
>
> About RAID types: the fastest RAID type by far is RAID-10. However, this
will
> cost you a lot of useable diskspace, so it isn't for everyone. You need at
> least 4 disks for a RAID-10 array. RAID-5 is a nice compromise if you want
as
> much useable diskspace as possible and still want to be redundant. RAID-1
is
> very useful for small (2-disk) arrays.
>
> If you have the time and are settled on buying 6 disks, I'd test the
following
> scenarios:
> - 6-disk RAID-10 array (should perform best)
> - 4-disk RAID-10 array containing data, 2-disk RAID-1 array for WAL, OS,
etc
> - 4-disk RAID-5 array containing data, 2-disk RAID-1 array for WAL, OS,
etc
> - 6-disk RAID-5 array (will probably perform worst)
>
>
> Hope this helps.
>
> Vincent van Leeuwen
> Media Design - http://www.mediadesign.nl/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster