Обсуждение: Hardware performance

От:
"Balazs Wellisch"
Дата:

Hi all,
 
first of all I'd like to thank everyone who responded to my earlier post. I have a much better understanding of postgres performance tuning now. In case anyone's interested we've decided to go with RH9 and PostgreSQL 7.3 and we'll do the OS and DB tuning ourselves. (should be a good learning experience)
 
We are now getting ready to purchase the hardware that will be used to run the database server. We're spending quite a bit of money on it because this will eventually, if things go well within two months, become a production server. We're getting all RH certified hardware from Dell. (Dell 2650)
 
We're now stuck on the question of what type of RAID configuration to use for this server. RAID 5 offers the best fault tolerance but doesn't perform all that well. RAID 10 offers much better performance, but no hot swap. Or should we not use RAID at all. I know that ideally the log (WAL) files should reside on a separate disk from the rest of the DB. Should we use 4 separate drives instead? One for the OS, one for data, one for WAL, one for swap? Or RAID 10 for everything plus 1 drive for WAL? Or RAID 5 for everything?
 
We have the budget for 5 drives. Does anyone have any real world experience with what hard drive configuration works best for postgres? This is going to be a dedicated DB server. There are going to be a large number of transactions being written to the database. (Information is logged from a separate app through ODBC to postgres) And there will be some moderately complex queries run concurrently to present this information in the form of various reports on the web. (The app server is a separate machine and will connect to the DB through JDBC to create the HTML reports)
 
Any thoughts, ideas, comments would be appreciated.
 
Thank you,
 
Balazs Wellisch
Neu Solutions
 
От:
"Roman Fail"
Дата:

I've got a Dell 2650 set up with 5 drives and a separate app server connecting with JDBC.  Since you've only got 5
drives,my conclusion regarding the best balance of performance and redundancy was:
 
 
2 drives have the OS, swap, and WAL in RAID-1
3 drives have the data in RAID-5
 
If you can afford it, get the 2+3 split backplane and make the 3 data drives the biggest, fastest you can afford.
Currentlythat means the 15k 73GB drives, which would give you 146GB for data.  Make the OS drives smaller and slower if
youneed to save cash.  
 
 
If only it had six drive bays....you could use 4 drives for the data and do RAID-10.  If you've got the additional
rackspaceavailable, you could get the 5U Dell 2600 instead for the same ballpark cost.  If you order it with rack
rails,it comes all set up for rack installation...a special sideways faceplate and everything.
 
 
By the way, RAID-5 is not the best fault tolerance, RAID-1 or RAID-10 is.  And you can certainly hot-swap RAID-10
arrays. I've actually done it....recently!  I am of the mind that single drives are not an option for production
servers- I just don't need the pain of the server going down at all.  Although they DO go down despite redundancy...I
justhad a SCSI backplane go out in a Dell 6600 that has every bit of redundancy you can order.  While uncommon, the
backplaneis one one of the many single points of failure!  
 
 
Roman Fail
POS Portal, Inc.
 
 

    -----Original Message----- 
    From: Balazs Wellisch [mailto:] 
    Sent: Wed 7/16/2003 7:57 PM 
    To:  
    Cc: 
    Subject: [PERFORM] Hardware performance
    
    
    Hi all,
     
    first of all I'd like to thank everyone who responded to my earlier post. I have a much better understanding of
postgresperformance tuning now. In case anyone's interested we've decided to go with RH9 and PostgreSQL 7.3 and we'll
dothe OS and DB tuning ourselves. (should be a good learning experience)
 
     
    We are now getting ready to purchase the hardware that will be used to run the database server. We're spending
quitea bit of money on it because this will eventually, if things go well within two months, become a production
server.We're getting all RH certified hardware from Dell. (Dell 2650)
 
     
    We're now stuck on the question of what type of RAID configuration to use for this server. RAID 5 offers the best
faulttolerance but doesn't perform all that well. RAID 10 offers much better performance, but no hot swap. Or should we
notuse RAID at all. I know that ideally the log (WAL) files should reside on a separate disk from the rest of the DB.
Shouldwe use 4 separate drives instead? One for the OS, one for data, one for WAL, one for swap? Or RAID 10 for
everythingplus 1 drive for WAL? Or RAID 5 for everything?
 
     
    We have the budget for 5 drives. Does anyone have any real world experience with what hard drive configuration
worksbest for postgres? This is going to be a dedicated DB server. There are going to be a large number of transactions
beingwritten to the database. (Information is logged from a separate app through ODBC to postgres) And there will be
somemoderately complex queries run concurrently to present this information in the form of various reports on the web.
(Theapp server is a separate machine and will connect to the DB through JDBC to create the HTML reports)
 
     
    Any thoughts, ideas, comments would be appreciated.
     
    Thank you,
     
    Balazs Wellisch
    Neu Solutions
    
     


От:
Joe Conway
Дата:

Balazs Wellisch wrote:
> first of all I'd like to thank everyone who responded to my earlier
> post. I have a much better understanding of postgres performance
> tuning now. In case anyone's interested we've decided to go with RH9
> and PostgreSQL 7.3 and we'll do the OS and DB tuning ourselves.
> (should be a good learning experience)

Good choice! I think you'll find that this list will be a great resource
as you learn. One point here is that you should use 7.3.3 (latest
release version) instead of the version of Postgres in the distribution.
Also, you might want to rebuild the RPMs from source using
"--target i686".

> We have the budget for 5 drives. Does anyone have any real world
> experience with what hard drive configuration works best for
> postgres? This is going to be a dedicated DB server. There are going
> to be a large number of transactions being written to the database.

To an extent it depends on how big the drives are and how large you
expect the database to get. For maximal performance you want RAID 1+0
for data and WAL; and you want OS, data, and WAL each on their own
drives. So with 5 drives one possible configuration is:

1 drive OS: OS on it's own drive makes it easy to upgrade, or restore
the OS from CD if needed
2 drives, RAID 1+0: WAL
2 drives, RAID 1+0: data

But I've seem reports that with fast I/O subsystems, there was no
measurable difference with WAL separated from data. And to be honest,
I've never personally found it necessary to separate WAL from data. You
may want to test with WAL on the same volume as the data to see if there
is enough difference to warrant separating it or not given your load and
your actual hardware. If not, use 1 OS drive and 4 RAID 1+0 drives as
one volume.

You never want find any significant use of hard disk based swap space --
if you see that, you are probably misconfigured, and performance will be
poor no matter how you've set up the drives.

> And there will be some moderately complex queries run concurrently to
> present this information in the form of various reports on the web.

Once you have some data on your test server, and you have complex
queries to tune, there will be a few details you'll get asked every time
if you don't provide them when posting a question to the list:

1) Have you been running VACUUM and ANALYZE (or VACUUM ANALYZE) at
    appropriate intervals?
2) What are the table definitions and indexes for all tables involved?
3) What is the output of EXPLAIN ANALYZE?

HTH,

Joe



От:
Hannu Krosing
Дата:

Joe Conway kirjutas N, 17.07.2003 kell 07:52:
> To an extent it depends on how big the drives are and how large you
> expect the database to get. For maximal performance you want RAID 1+0
> for data and WAL; and you want OS, data, and WAL each on their own
> drives. So with 5 drives one possible configuration is:
>
> 1 drive OS: OS on it's own drive makes it easy to upgrade, or restore
> the OS from CD if needed
> 2 drives, RAID 1+0: WAL
> 2 drives, RAID 1+0: data

How do you do RAID 1+0 with just two drives ?

--------------
Hannu



От:
Vincent van Leeuwen
Дата:

On 2003-07-16 19:57:22 -0700, Balazs Wellisch wrote:
> We're now stuck on the question of what type of RAID configuration to use
> for this server. RAID 5 offers the best fault tolerance but doesn't perform
> all that well. RAID 10 offers much better performance, but no hot swap. Or
> should we not use RAID at all. I know that ideally the log (WAL) files
> should reside on a separate disk from the rest of the DB. Should we use 4
> separate drives instead? One for the OS, one for data, one for WAL, one for
> swap? Or RAID 10 for everything plus 1 drive for WAL? Or RAID 5 for
> everything?
>

We have recently run our own test (simulating our own database load) on a new
server which contained 7 15K rpm disks. Since we always want to have a
hot-spare drive (servers are located in a hard-to-reach datacenter) and we
always want redundancy, we tested two different configurations:
- 6 disk RAID 10 array, holding everything
- 4 disk RAID 5 array holding postgresql data and 2 disk RAID 1 array holding
  OS, swap and WAL logs

Our database is used for a very busy community website, so our load contains a
lot of inserts/updates for a website, but much more selects than there are
updates.

Our findings were that the 6 disk RAID 10 set was significantly faster than
the other setup.

So I'd recommend a 4-disk RAID 10 array. I'd use the 5th drive for a hot-spare
drive, but that's your own call. However, it would be best if you tested some
different setups under your own database load to see what works best for you.


Vincent van Leeuwen
Media Design - http://www.mediadesign.nl/

От:
Joe Conway
Дата:

Hannu Krosing wrote:
> How do you do RAID 1+0 with just two drives ?
>

Hmm, good point -- I must have been tired last night ;-). With two
drives you can do mirroring or striping, but not both.

Usually I've seen a pair of mirrored drives for the OS, and a RAID 1+0
array for data. But that requires 6 drives, not 5. On non-database
servers usually the data array is RAID 5, and you could get away with 5
drives (as someone else pointed out).

As I said, I've never personally found it necessary to move WAL off to a
different physical drive. What do you think is the best configuration
given the constraint of 5 drives? 1 drive for OS, and 4 for RAID 1+0 for
data-plus-WAL? I guess the ideal would be to find enough money for that
6th drive, use the mirrored pair for both OS and WAL.

Joe



От:
Adam Witney
Дата:

> As I said, I've never personally found it necessary to move WAL off to a
> different physical drive. What do you think is the best configuration
> given the constraint of 5 drives? 1 drive for OS, and 4 for RAID 1+0 for
> data-plus-WAL? I guess the ideal would be to find enough money for that
> 6th drive, use the mirrored pair for both OS and WAL.

I think the issue from the original posters point of view is that the Dell
PE2650 can only hold a maximum of 5 internal drives


--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.


От:
Joe Conway
Дата:

Adam Witney wrote:
> I think the issue from the original posters point of view is that the Dell
> PE2650 can only hold a maximum of 5 internal drives
>

True enough, but maybe that's a reason to be looking at other
alternatives. I think he said the hardware hasn't been bought yet.

Joe




От:
Adam Witney
Дата:

On 17/7/03 4:09 pm, "Joe Conway" <> wrote:

> Adam Witney wrote:
>> I think the issue from the original posters point of view is that the Dell
>> PE2650 can only hold a maximum of 5 internal drives
>>
>
> True enough, but maybe that's a reason to be looking at other
> alternatives. I think he said the hardware hasn't been bought yet.

Actually I am going through the same questions myself at the moment.... I
would like to have a 2 disk RAID1 and a 4 disk RAID5, so need at least 6
disks....

Anybody have any suggestions or experience with other hardware manufacturers
for this size of setup? (2U rack, up to 6 disks, 2 processors, ~2GB RAM, if
possible)

Thanks

adam


--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.


От:
Jean-Luc Lachance
Дата:

I am currious. How can you have RAID 1+0 with only 2 drives?
If you are thinking about partitioning the drives, wont this defeate the
purpose?

JLL

Joe Conway wrote:
>
> [...]
> 2 drives, RAID 1+0: WAL
> 2 drives, RAID 1+0: data
> [...]

От:
Joe Conway
Дата:

Jean-Luc Lachance wrote:
> I am currious. How can you have RAID 1+0 with only 2 drives?
> If you are thinking about partitioning the drives, wont this defeate the
> purpose?

Yeah -- Hannu already pointed out that my mind was fuzzy when I made
that statement :-(. See subsequent posts.

Joe




От:
Joe Conway
Дата:

Adam Witney wrote:
> Actually I am going through the same questions myself at the moment.... I
> would like to have a 2 disk RAID1 and a 4 disk RAID5, so need at least 6
> disks....
>
> Anybody have any suggestions or experience with other hardware manufacturers
> for this size of setup? (2U rack, up to 6 disks, 2 processors, ~2GB RAM, if
> possible)

I tend to use either 1U or 4U servers, depending on the application. But
I've had good experiences with IBM recently, and a quick look on their
site shows the x345 with these specs:

•  2U, 2-way server delivers extreme performance and availability for
demanding applications
•  Up to 2 Intel Xeon processors up to 3.06GHz with 533MHz front-side
bus speed for outstanding performance
•  Features up to 8GB of DDR memory, 5 PCI (4 PCI-X) slots and up to 6
hard disk drives for robust expansion
•  Hot-swap redundant cooling, power and hard disk drives for high
availability
•  Integrated dual Ultra320 SCSI with RAID-1 for data protection

This may not wrap well, but here is the url:

http://www-132.ibm.com/webapp/wcs/stores/servlet/CategoryDisplay?catalogId=-840&storeId=1&categoryId=2559454&langId=-1&dualCurrId=73

Handles 6 drives; maybe that fits the bill?

Joe


От:
Jean-Luc Lachance
Дата:

Sorry for the redundant duplication of the repetition.
I should have read the follow-up messages.


Joe Conway wrote:
>
> Jean-Luc Lachance wrote:
> > I am currious. How can you have RAID 1+0 with only 2 drives?
> > If you are thinking about partitioning the drives, wont this defeate the
> > purpose?
>
> Yeah -- Hannu already pointed out that my mind was fuzzy when I made
> that statement :-(. See subsequent posts.
>
> Joe

От:
Jord Tanner
Дата:

On Thu, 2003-07-17 at 08:20, Adam Witney wrote:


> Anybody have any suggestions or experience with other hardware manufacturers
> for this size of setup? (2U rack, up to 6 disks, 2 processors, ~2GB RAM, if
> possible)
>
> Thanks
>
> adam

Check out http://www.amaxit.com It is all white box stuff, but they have
some really cool gear.

--
Jord Tanner <>


От:
Andrew Sullivan
Дата:

On Thu, Jul 17, 2003 at 07:57:53AM -0700, Joe Conway wrote:
>
> As I said, I've never personally found it necessary to move WAL off to a
> different physical drive. What do you think is the best configuration

On our Solaris test boxes (where, alas, we do not have the luxury of
1/2 TB external RAID boxes :-( ), putting WAL on a disk of its own
yielded something like 30% improvement in throughput on high
transaciton volumes.  So it's definitely important in some cases.

A

----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<>                              M2P 2A8
                                         +1 416 646 3304 x110


От:
"Magnus Hagander"
Дата:

>Adam Witney wrote:
>> Actually I am going through the same questions myself at the
>moment.... I
>> would like to have a 2 disk RAID1 and a 4 disk RAID5, so
>need at least 6
>> disks....
>>
>> Anybody have any suggestions or experience with other
>hardware manufacturers
>> for this size of setup? (2U rack, up to 6 disks, 2
>processors, ~2GB RAM, if
>> possible)
>
>I tend to use either 1U or 4U servers, depending on the
>application. But
>I've had good experiences with IBM recently, and a quick look on their
>site shows the x345 with these specs:
>
>*  2U, 2-way server delivers extreme performance and availability for
>demanding applications
>*  Up to 2 Intel Xeon processors up to 3.06GHz with 533MHz front-side
>bus speed for outstanding performance
>*  Features up to 8GB of DDR memory, 5 PCI (4 PCI-X) slots and up to 6
>hard disk drives for robust expansion
>*  Hot-swap redundant cooling, power and hard disk drives for high
>availability
>*  Integrated dual Ultra320 SCSI with RAID-1 for data protection
>
>This may not wrap well, but here is the url:
>http://www-132.ibm.com/webapp/wcs/stores/servlet/CategoryDispla
> y?catalogId=-840&storeId=1&categoryId=2559454&langId=-1&dualCurrId=73
>
> Handles 6 drives; maybe that fits the bill?

[naturally, there should be one for each of the major server vendors,
eh?]

I've used mainly HP (as in former Compaq) machines here, with nothing
but good experience.  HPs machine in the scame class is the DL380G3.
Almost identical specs to the IBM (I'd expect all major vendors have
fairly similar machines). Holds 12Gb RAM. Only 3 PCI-X slots (2 of them
hotplug). RPS. 6 disk slots (Ultra-320) that can be put on one or two
SCSI chains (builtin RAID controller only handles a single channel,
though, so you'd need an extra SmartArray controller if you want to
split them). RAID0/1/1+0/5.

If you would go with that one, make sure to get the optional BBWC
(Battery Backed Write Cache). Without it the controller won't enable the
write-back cache (which it really shouldn't, since it wouldn't be safe
without the batteries). WB cache can really speed things on in many db
situations - it's sort of like "speed of fsync off, security of fsync
on". I've seen huge speedups with both postgresql and other databases on
that.


If you want to be "ready for more storage", I'd suggest looking at a 1U
server with a 3U external disk rack. That'll give you 16 disks in 4U (2
in the server + 14 in the rack on 2 channels), which is hard to beat. If
you have no need to go there, then sure, the 2U machine will be better.
But I've found the "small machine with external rack" a lot more
flexible than the "big machine with disks inside it". (For example, you
can put two 1U servers to it, and have 7 disks assigned to each server)
In HP world that would mean DL360G3 and the StorageWorks 4354.

The mandatory link:
http://h18004.www1.hp.com/products/servers/platforms/index-dl-ml.html



Though if you are already equipped with servers from one vendor, I'd
suggest sticking to it as long as the specs are fairly close. Then you
only need one set of management software etc.


//Magnus

От:
Robert Creager
Дата:

On Thu, 17 Jul 2003 16:20:42 +0100
Adam Witney <> said something like:

>
> Actually I am going through the same questions myself at the
> moment.... I would like to have a 2 disk RAID1 and a 4 disk RAID5, so
> need at least 6 disks....
>
> Anybody have any suggestions or experience with other hardware
> manufacturers for this size of setup? (2U rack, up to 6 disks, 2
> processors, ~2GB RAM, if possible)
>

We recently bought a couple of Compaq Proliant DL380 units.  They are
2u, and support 6 disks, 2 CPU's, 12Gb max.

We purchased 2 units of 1CPU, 4x72Gb RAID 0+1, 1Gb mem, redundant fans
and power supplies for around $11,000 total.  Unfortunately they are
running Win2K with SQLAnywhere (ClearQuest/Web server) ;-)  So far (5
months), they're real board...

Cheers,
Rob

--
 21:16:04 up  1:19,  1 user,  load average: 2.04, 1.99, 1.38

От:
Ron Johnson
Дата:

On Wed, 2003-07-16 at 23:25, Roman Fail wrote:
[snip]
> has every bit of redundancy you can order.  While uncommon, the
> backplane is one one of the many single points of failure!

Unless you go with a shared-disk cluster (Oracle 9iRAC or OpenVMS)
or replication.

Face it, if your pockets are deep enough, you can make everything
redundant and burden-sharing (i.e., not just waiting for the master
system to die).  (And with some enterprise FC controllers, you can
mirror the disks many kilometers away.)

--
+-----------------------------------------------------------------+
| Ron Johnson, Jr.        Home:              |
| Jefferson, LA  USA                                              |
|                                                                 |
| "I'm not a vegetarian because I love animals, I'm a vegetarian  |
|  because I hate vegetables!"                                    |
|    unknown                                                      |
+-----------------------------------------------------------------+



От:
Ron Johnson
Дата:

On Thu, 2003-07-17 at 13:55, Magnus Hagander wrote:
> >Adam Witney wrote:
[snip]
> If you would go with that one, make sure to get the optional BBWC
> (Battery Backed Write Cache). Without it the controller won't enable the
> write-back cache (which it really shouldn't, since it wouldn't be safe
> without the batteries). WB cache can really speed things on in many db
> situations - it's sort of like "speed of fsync off, security of fsync
> on". I've seen huge speedups with both postgresql and other databases on
> that.

Don't forget to check the batteries!!!  And if you have an HPaq service
contract, don't rely on them to do it...

--
+-----------------------------------------------------------------+
| Ron Johnson, Jr.        Home:              |
| Jefferson, LA  USA                                              |
|                                                                 |
| "I'm not a vegetarian because I love animals, I'm a vegetarian  |
|  because I hate vegetables!"                                    |
|    unknown                                                      |
+-----------------------------------------------------------------+



От:
"Magnus Hagander"
Дата:

> > >Adam Witney wrote:
> [snip]
> > If you would go with that one, make sure to get the optional BBWC
> > (Battery Backed Write Cache). Without it the controller
> won't enable
> > the write-back cache (which it really shouldn't, since it
> wouldn't be
> > safe without the batteries). WB cache can really speed things on in
> > many db situations - it's sort of like "speed of fsync off,
> security
> > of fsync on". I've seen huge speedups with both postgresql
> and other
> > databases on that.
>
> Don't forget to check the batteries!!!  And if you have an
> HPaq service contract, don't rely on them to do it...

That's what management software is for.. :-) (Yes, it does check the
batteries. They are also reported on reboot, but you don't want to do
that often, of course)

Under the service contract, HP will *replace* the batteries for free,
though - but you have to know when to replace them.

//Magnus