I've asked for some help here a few months ago and got some really helpfull
answers regarding RAID controllers and server configuration. Up until
recently I've been running PostgreSQL on a two year old Dual Xeon 3.06Ghz
machine with a single channel RAID controller (previously Adaptec 2200S, but
now changed to LSI MegaRAID). The 2U unit is from a generic vendor using what
I believe is a SuperMicro motherboard. In the last week after upgrading the
RAID controller, the machine has had disk failure and some other issues. I
would like to build a very reliable dedicated postgreSQL server that has the
ultimate possible performance and reliabily for around $20,000. The data set
size is only currently about 4GB, but is increasing by approximately 50MB
daily. The server also requires about 500 connections and I have been
monitoring about 100-200 queries per second at the moment. I am planning to
run FreeBSD 6.1 if possible, but I am open to any other suggestions if it
improves performance.

I am considering a setup such as this:
- At least dual cpu (possibly with 2 cores each)
- 4GB of RAM
- 2 disk RAID 1 array for root disk
- 4 disk RAID 1+0 array for PGDATA
- 2 disk RAID 1 array for pg_xlog

Does anyone know a vendor that might be able provide such setup? Any
critique in this design? I'm thinking having a 2 channel RAID controller to
seperate the PGDATA, root and pg_xlog.

Sincerely,
Kenji

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

>
> I am considering a setup such as this:
> - At least dual cpu (possibly with 2 cores each)
> - 4GB of RAM
> - 2 disk RAID 1 array for root disk
> - 4 disk RAID 1+0 array for PGDATA
> - 2 disk RAID 1 array for pg_xlog
>
> Does anyone know a vendor that might be able provide such setup?

I would look at the HP DL 385 or 585. The 385 is going to max a (2) dual
core cpus. The 585 is (4) dual core cpus.

For 20k you should be able to go with a bit more then what you have
above, specifically in the RAM department.

Joshua D. Drake




Any
> critique in this design? I'm thinking having a 2 channel RAID controller to
> seperate the PGDATA, root and pg_xlog.
>
> Sincerely,
> Kenji
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>


--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/



---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

The 1+0 on the WAL is better than on PGDATA? I guess I'm confused about the
write sequence of the data. I will research more, thank you!
-Kenji

On Tue, Aug 08, 2006 at 04:59:09PM -0500, Thomas F. O'Connell wrote:
>
> On Aug 8, 2006, at 4:49 PM, Joshua D. Drake wrote:
>
> >>I am considering a setup such as this:
> >> - At least dual cpu (possibly with 2 cores each)
> >> - 4GB of RAM
> >> - 2 disk RAID 1 array for root disk
> >> - 4 disk RAID 1+0 array for PGDATA
> >> - 2 disk RAID 1 array for pg_xlog
> >>Does anyone know a vendor that might be able provide such setup?
>
> Wouldn't it be preferable to put WAL on a multi-disk RAID 10 if you
> had the opportunity? This gives you the redundancy of RAID 1 but
> approaches the performance of RAID 0, especially as you add disks to
> the array. In benchmarking, I've seen consistent success with this
> approach.
>
> --
> Thomas F. O'Connell
> Sitening, LLC
>
> http://www.sitening.com/
> 3004B Poston Avenue
> Nashville, TN 37203-1314
> 615-469-5150 x802
> 615-469-5151 (fax)

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

On Tue, 2006-08-08 at 15:43, Kenji Morishige wrote:
> I've asked for some help here a few months ago and got some really helpfull
> answers regarding RAID controllers and server configuration. Up until
> recently I've been running PostgreSQL on a two year old Dual Xeon 3.06Ghz
> machine with a single channel RAID controller (previously Adaptec 2200S, but
> now changed to LSI MegaRAID). The 2U unit is from a generic vendor using what
> I believe is a SuperMicro motherboard. In the last week after upgrading the
> RAID controller, the machine has had disk failure and some other issues. I
> would like to build a very reliable dedicated postgreSQL server that has the
> ultimate possible performance and reliabily for around $20,000. The data set
> size is only currently about 4GB, but is increasing by approximately 50MB
> daily. The server also requires about 500 connections and I have been
> monitoring about 100-200 queries per second at the moment. I am planning to
> run FreeBSD 6.1 if possible, but I am open to any other suggestions if it
> improves performance.

This really depends on your usage patterns.

OLAP or OLTP workloads? Do you need 24/7 reliability and therefore a
two machine setup? There's a lot of variety in load.

Generally, you spend your money on disks, then memory, then CPU, in that
order.

Look at the Areca cards, they've come highly recommended here. Look at
LOTS of drives. Given the size of your db, you can go with LOTS of
smaller drives and get good performance. If you can find a good box to
hold 12 to 16 drives and fill it with 37 gig 15k RPM drives, you'll have
lots of storage, even in RAID 1+0 config. That's aiming at
transactional throughput.

Toss as much memory as is reasonably affordable at it. That's normally
in the 4 to 8 gig range. After that things start to get expensive fast.

Multiple - dual core CPUs are a good idea. Opterons seem to be better
"data pumps" with large memory and >2 CPUs than Intels right now.
Better to have a 2xdual core opteron with slower processors than a
single dual core or dual single core CPU(s) with a faster clock speed.
As long as the memory access is equivalent, the more CPUs the better in
Opterons, where their interconnect speed increases as you increase the
number of CPUs. Intel Xeons are the opposite. Better with fewer faster
CPUs / cores.

I just ran through a configurator on a site selling quad dual core
opteron servers. 8 Seagate cheetah 15k rpm drives, 8 gig ram, and the
slowest (1.8 GHz) AMD dual core CPUs (4 of them) for 8 cores, came out
to $13,500 or so.

I'd take the other $7.5 grand and buy a backup server that can old as
much but isn't quite as beefy and set up slony to have a live hot spare
sitting ready. Oh, and maybe to buy some spare parts to sit in the desk
drawer in case things break.

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Great info, which vendor were you looking at for these Opterons? I am goign
to be purchasing 2 of these. I do need 24/7 reliability.

On Tue, Aug 08, 2006 at 05:08:29PM -0500, Scott Marlowe wrote:
> On Tue, 2006-08-08 at 15:43, Kenji Morishige wrote:
> > I've asked for some help here a few months ago and got some really helpfull
> > answers regarding RAID controllers and server configuration. Up until
> > recently I've been running PostgreSQL on a two year old Dual Xeon 3.06Ghz
> > machine with a single channel RAID controller (previously Adaptec 2200S, but
> > now changed to LSI MegaRAID). The 2U unit is from a generic vendor using what
> > I believe is a SuperMicro motherboard. In the last week after upgrading the
> > RAID controller, the machine has had disk failure and some other issues. I
> > would like to build a very reliable dedicated postgreSQL server that has the
> > ultimate possible performance and reliabily for around $20,000. The data set
> > size is only currently about 4GB, but is increasing by approximately 50MB
> > daily. The server also requires about 500 connections and I have been
> > monitoring about 100-200 queries per second at the moment. I am planning to
> > run FreeBSD 6.1 if possible, but I am open to any other suggestions if it
> > improves performance.
>
> This really depends on your usage patterns.
>
> OLAP or OLTP workloads? Do you need 24/7 reliability and therefore a
> two machine setup? There's a lot of variety in load.
>
> Generally, you spend your money on disks, then memory, then CPU, in that
> order.
>
> Look at the Areca cards, they've come highly recommended here. Look at
> LOTS of drives. Given the size of your db, you can go with LOTS of
> smaller drives and get good performance. If you can find a good box to
> hold 12 to 16 drives and fill it with 37 gig 15k RPM drives, you'll have
> lots of storage, even in RAID 1+0 config. That's aiming at
> transactional throughput.
>
> Toss as much memory as is reasonably affordable at it. That's normally
> in the 4 to 8 gig range. After that things start to get expensive fast.
>
> Multiple - dual core CPUs are a good idea. Opterons seem to be better
> "data pumps" with large memory and >2 CPUs than Intels right now.
> Better to have a 2xdual core opteron with slower processors than a
> single dual core or dual single core CPU(s) with a faster clock speed.
> As long as the memory access is equivalent, the more CPUs the better in
> Opterons, where their interconnect speed increases as you increase the
> number of CPUs. Intel Xeons are the opposite. Better with fewer faster
> CPUs / cores.
>
> I just ran through a configurator on a site selling quad dual core
> opteron servers. 8 Seagate cheetah 15k rpm drives, 8 gig ram, and the
> slowest (1.8 GHz) AMD dual core CPUs (4 of them) for 8 cores, came out
> to $13,500 or so.
>
> I'd take the other $7.5 grand and buy a backup server that can old as
> much but isn't quite as beefy and set up slony to have a live hot spare
> sitting ready. Oh, and maybe to buy some spare parts to sit in the desk
> drawer in case things break.

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

On Aug 8, 2006, at 1:43 PM, Kenji Morishige wrote:

>
>
> I am considering a setup such as this:
> - At least dual cpu (possibly with 2 cores each)
> - 4GB of RAM
> - 2 disk RAID 1 array for root disk
> - 4 disk RAID 1+0 array for PGDATA
> - 2 disk RAID 1 array for pg_xlog
>
> Does anyone know a vendor that might be able provide such setup? Any
> critique in this design? I'm thinking having a 2 channel RAID
> controller to
> seperate the PGDATA, root and pg_xlog.

It's fairly similar to the system I'm using - I have 6 spindles for
PGDATA rather than 4. It's a 9550SX SATA based box with
26 bays (16 empty right now) dual Opterons and 4 gigs of
RAM. About $7k from asacomputers.com. Performance is
good, drastically better than the SCSI Dell server it replaced,
but I've not benchmarked it.

You mention 500 simultaneous connections. If that's really
500 simultaneous database connections (rather than, say,
500 simultaneous web users feeding into a pool of fewer
database connections) then that's a lot. I might go with a
bit more RAM than 4GB.

You should have a lot left over from
$20k, and spending some of it on more RAM would be a good
investment (as might a better RAID controller than the 9550SX,
some dual core CPUs and maybe some more PGDATA spindles).

Cheers,
Steve


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Thomas F. O'Connell wrote:
>
> On Aug 8, 2006, at 4:49 PM, Joshua D. Drake wrote:
>
>>> I am considering a setup such as this:
>>> - At least dual cpu (possibly with 2 cores each)
>>> - 4GB of RAM
>>> - 2 disk RAID 1 array for root disk
>>> - 4 disk RAID 1+0 array for PGDATA
>>> - 2 disk RAID 1 array for pg_xlog
>>> Does anyone know a vendor that might be able provide such setup?
>
> Wouldn't it be preferable to put WAL on a multi-disk RAID 10 if you had
> the opportunity? This gives you the redundancy of RAID 1 but approaches
> the performance of RAID 0, especially as you add disks to the array. In
> benchmarking, I've seen consistent success with this approach.

WALL is written in order so RAID 1 is usually fine. We also don't need
journaling for WAL so the speed is even faster.

Joshua D. Drake

>
> --
> Thomas F. O'Connell
> Sitening, LLC
>
> http://www.sitening.com/
> 3004B Poston Avenue
> Nashville, TN 37203-1314
> 615-469-5150 x802
> 615-469-5151 (fax)
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>


--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/



---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

On Aug 8, 2006, at 5:28 PM, Joshua D. Drake wrote:

> Thomas F. O'Connell wrote:
>> On Aug 8, 2006, at 4:49 PM, Joshua D. Drake wrote:
>>>> I am considering a setup such as this:
>>>> - At least dual cpu (possibly with 2 cores each)
>>>> - 4GB of RAM
>>>> - 2 disk RAID 1 array for root disk
>>>> - 4 disk RAID 1+0 array for PGDATA
>>>> - 2 disk RAID 1 array for pg_xlog
>>>> Does anyone know a vendor that might be able provide such setup?
>> Wouldn't it be preferable to put WAL on a multi-disk RAID 10 if
>> you had the opportunity? This gives you the redundancy of RAID 1
>> but approaches the performance of RAID 0, especially as you add
>> disks to the array. In benchmarking, I've seen consistent success
>> with this approach.
>
> WALL is written in order so RAID 1 is usually fine. We also don't
> need journaling for WAL so the speed is even faster.

In which case, which is theoretically better (since I don't have a
convenient test bed at the moment) for WAL in a write-heavy
environment? More disks in a RAID 10 (which should theoretically
improve write throughput in general, to a point) or a 2-disk RAID 1?
Does it become a price/performance question, or is there virtually no
benefit to throwing more disks at RAID 10 for WAL if you turn off
journaling on the filesystem?

--
Thomas F. O'Connell
Sitening, LLC

http://www.sitening.com/
3004B Poston Avenue
Nashville, TN 37203-1314
615-469-5150 x802
615-469-5151 (fax)

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

>
> In which case, which is theoretically better (since I don't have a
> convenient test bed at the moment) for WAL in a write-heavy environment?
> More disks in a RAID 10 (which should theoretically improve write
> throughput in general, to a point) or a 2-disk RAID 1? Does it become a
> price/performance question, or is there virtually no benefit to throwing
> more disks at RAID 10 for WAL if you turn off journaling on the filesystem?

Over 4 drives, I would gather that RAID 10 wouldn't gain you anything.
Possibly over 6 or 8 however, it may be faster because you are writing
smaller chunks of data, even if two copies of each.

Sincerely,

Joshua D. Drake


--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/



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

On Aug 8, 2006, at 6:24 PM, Joshua D. Drake wrote:

>> In which case, which is theoretically better (since I don't have a
>> convenient test bed at the moment) for WAL in a write-heavy
>> environment? More disks in a RAID 10 (which should theoretically
>> improve write throughput in general, to a point) or a 2-disk RAID
>> 1? Does it become a price/performance question, or is there
>> virtually no benefit to throwing more disks at RAID 10 for WAL if
>> you turn off journaling on the filesystem?
>
> Over 4 drives, I would gather that RAID 10 wouldn't gain you
> anything. Possibly over 6 or 8 however, it may be faster because
> you are writing smaller chunks of data, even if two copies of each.

Yeah, where I've seen the benefits in practice, the scenarios have
involved the availability of a minimum of 6 drives for a RAID 10 for
WAL. I really should do a comparison of a 2-disk RAID 1 with a
variety of multi-disk RAID 10 configurations at some point.

--
Thomas F. O'Connell
Sitening, LLC

http://www.sitening.com/
3004B Poston Avenue
Nashville, TN 37203-1314
615-469-5150 x802
615-469-5151 (fax)

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org