| Author |
Message |
Benno
Guest
|
 Posted:
Fri Jan 14, 2005 8:01 am Post subject:
Disk subsystem design questions for new database server. |
|
|
I need to build a new database server (SQL server on new HP or Dell
hardware) and I have a question about the disk subsystem. I want to
separate the OS, logfiles and database files on separate disk arrays.
The OS will be placed on RAID1 on the machine's internal RAID
controller. I intend to install a two channel RAID controller and a
split disk backplane in the machine (or an external disk cabinet) with
two separate SCSI channels. What would give the best performance?
Place the disks for the logfiles and database both on their own separate
RAID and SCSI backplane channels or...
distribute all disks evenly between the RAID and SCSI backplane channels
and assign the logfile and data filesystems to disks on both channels?
So:
RAID channel A ---> SCSI backplane A ---> disk-disk-disk-disk (logfile
filesystem)
RAID channel B ---> SCSI backplane B ---> disk-disk-disk-disk-disk-disk
(database filesystem)
Or (crappy ASCII art ahoy):
----------- ----------------
RAID A ---> SCSI A ---> | disk-disk || disk-disk-disk |
RAID B ---> SCSI B ---> | disk-disk || disk-disk-disk |
|logfiles FS|| database FS |
----------- ----------------
I want to use 10 to 14 disks (36GB 15K rpm)in total to have as much
spindels as possible. Would I need to seperate RAID controllers or will
a two channel controller have enough bandwith? |
|
| Back to top |
|
 |
Maxim S. Shatskih
Guest
|
| Posted:
Fri Jan 14, 2005 3:02 pm Post subject:
Re: Disk subsystem design questions for new database server. |
|
|
What is your database size? If several GBs - then just buy more RAM, and
the disk IO will cease to be a performance problem, all database will be in
cache.
--
Maxim Shatskih, Windows DDK MVP
StorageCraft Corporation
maxim@storagecraft.com
http://www.storagecraft.com
"Benno" <benno.usenet@gmail.com> wrote in message
news:41e77bab$0$14119$4d4ebb8e@news.nl.uu.net...
| Quote: | I need to build a new database server (SQL server on new HP or Dell
hardware) and I have a question about the disk subsystem. I want to
separate the OS, logfiles and database files on separate disk arrays.
The OS will be placed on RAID1 on the machine's internal RAID
controller. I intend to install a two channel RAID controller and a
split disk backplane in the machine (or an external disk cabinet) with
two separate SCSI channels. What would give the best performance?
Place the disks for the logfiles and database both on their own separate
RAID and SCSI backplane channels or...
distribute all disks evenly between the RAID and SCSI backplane channels
and assign the logfile and data filesystems to disks on both channels?
So:
RAID channel A ---> SCSI backplane A ---> disk-disk-disk-disk (logfile
filesystem)
RAID channel B ---> SCSI backplane B ---> disk-disk-disk-disk-disk-disk
(database filesystem)
Or (crappy ASCII art ahoy):
----------- ----------------
RAID A ---> SCSI A ---> | disk-disk || disk-disk-disk |
RAID B ---> SCSI B ---> | disk-disk || disk-disk-disk |
|logfiles FS|| database FS |
----------- ----------------
I want to use 10 to 14 disks (36GB 15K rpm)in total to have as much
spindels as possible. Would I need to seperate RAID controllers or will
a two channel controller have enough bandwith? |
|
|
| Back to top |
|
|
Benno
Guest
|
| Posted:
Fri Jan 14, 2005 4:56 pm Post subject:
Re: Disk subsystem design questions for new database server. |
|
|
Maxim S. Shatskih wrote:
| Quote: | What is your database size? If several GBs - then just buy more RAM, and
the disk IO will cease to be a performance problem, all database will be in
cache.
The DB is only around 4GB. The problem is that Windows can not handle |
more then 4GB memory (Windows enterprice can but not very efficient as
far as I can tell). And I don't know if SQL server can even address all
that memory (4GB, let alone more). If you have more concrete information
about this I would greatly appreciate it. |
|
| Back to top |
|
|
AWS
Guest
|
| Posted:
Fri Jan 14, 2005 7:58 pm Post subject:
Re: Disk subsystem design questions for new database server. |
|
|
| Quote: | ----------- ----------------
RAID A ---> SCSI A ---> | disk-disk || disk-disk-disk |
RAID B ---> SCSI B ---> | disk-disk || disk-disk-disk |
|logfiles FS|| database FS |
----------- ----------------
I want to use 10 to 14 disks (36GB 15K rpm)in total to have as much
spindels as possible. Would I need to seperate RAID controllers or
will
a two channel controller have enough bandwith?
|
IMO controller bandwidth will probably not be the bottleneck. Chances
are either the disks or CPU will be topped out before you start edging
up on the controller bandwidth.
The question here is separating your sequential writes (logfile
writing) and your random reads (database and OS filesystem queries).
The more spindles you throw at random reads (except in parity RAID
situations-- RAID3, RAID5) the higher the performance of the database.
However, if you create one large RAID group, those random reads will be
interupting the sequential writes from your logfile fs.
IMHO, the performance needs of the logfile fs would be secondary to the
performance of the database and OS filesystems; therefore, I would
probably suggest one large RAID group with 3 slices (OS, logfile, db)
spread across both controllers.
Aaron |
|
| Back to top |
|
|
Maxim S. Shatskih
Guest
|
| Posted:
Fri Jan 14, 2005 8:02 pm Post subject:
Re: Disk subsystem design questions for new database server. |
|
|
| Quote: | The DB is only around 4GB. The problem is that Windows can not handle
more then 4GB memory (Windows enterprice can but not very efficient as
|
Any Windows capable of PAE can handle > 4GB memory perfectly. This does not
allow, surely, the process address spaces of > 4GB, they are still the same
size as previously, just the physical memory is larger.
| Quote: | far as I can tell). And I don't know if SQL server can even address all
that memory (4GB, let alone more).
|
Later MSSQLServer can use huge address spaces, this is another Windows feature,
called AWE, and is specially intended for things which needs address spaces >
4G all full of physical RAM.
IIRC PAE and AWE are mutually exclusive, but I can be wrong here.
So, your solution is to have the latest version of MSSQLServer, which is
AWE-enabled.
--
Maxim Shatskih, Windows DDK MVP
StorageCraft Corporation
maxim@storagecraft.com
http://www.storagecraft.com |
|
| Back to top |
|
|
Bill Todd
Guest
|
| Posted:
Fri Jan 14, 2005 8:31 pm Post subject:
Re: Disk subsystem design questions for new database server. |
|
|
Maxim S. Shatskih wrote:
....
| Quote: | IIRC PAE and AWE are mutually exclusive, but I can be wrong here.
|
Well, yes: AWE is the Windows software support that gives applications
the ability to make use of the PAE facilities.
But the rest of what you said sounded about right, save that there
actually can be noticeable increased overhead when an application
manages amounts of physical memory larger than the 2 GB (or 3 GB) which
32-bit Windows normally maps: the additional system calls to manage the
remapping are considerably more expensive than the native virtual memory
mechanisms.
- bill |
|
| Back to top |
|
|
Al Dykes
Guest
|
| Posted:
Fri Jan 14, 2005 8:36 pm Post subject:
Re: Disk subsystem design questions for new database server. |
|
|
In article <cs8mrs$2vr8$1@gavrilo.mtu.ru>,
Maxim S. Shatskih <maxim@storagecraft.com> wrote:
| Quote: | The DB is only around 4GB. The problem is that Windows can not handle
more then 4GB memory (Windows enterprice can but not very efficient as
Any Windows capable of PAE can handle > 4GB memory perfectly. This does not
allow, surely, the process address spaces of > 4GB, they are still the same
size as previously, just the physical memory is larger.
far as I can tell). And I don't know if SQL server can even address all
that memory (4GB, let alone more).
Later MSSQLServer can use huge address spaces, this is another Windows feature,
called AWE, and is specially intended for things which needs address spaces
4G all full of physical RAM.
IIRC PAE and AWE are mutually exclusive, but I can be wrong here.
So, your solution is to have the latest version of MSSQLServer, which is
AWE-enabled.
--
Maxim Shatskih, Windows DDK MVP
|
Your motherboard needs to be PAE-capable, which I assume any x86 board
that comes with slots for more than 4GB is. (my experience is limited
to PAE).
Depending on the OP's requirements and budget, there is a growing
database product niche called "in-memory database". Google will get
lots of hits and a couple vendors. An IMBD is much simpler becasue a
DB like Oracle or MSSQL has lots of code that decides what indexes and
data to keep in memory and when to flush it to disk, and to manage
disk buffers.
I assume a IMBD on an AMD64 (with a 64 bit OS) box will scream, partly
because of the large, simple address space. (remember to use a
UPS. :-) )
--
a d y k e s @ p a n i x . c o m
Don't blame me. I voted for Gore. |
|
| Back to top |
|
|
Al Dykes
Guest
|
| Posted:
Fri Jan 14, 2005 8:39 pm Post subject:
Re: Disk subsystem design questions for new database server. |
|
|
In article <oO2dnY94ZJtDeHrcRVn-sQ@metrocastcablevision.com>,
Bill Todd <billtodd@metrocast.net> wrote:
| Quote: | Maxim S. Shatskih wrote:
...
IIRC PAE and AWE are mutually exclusive, but I can be wrong here.
Well, yes: AWE is the Windows software support that gives applications
the ability to make use of the PAE facilities.
But the rest of what you said sounded about right, save that there
actually can be noticeable increased overhead when an application
manages amounts of physical memory larger than the 2 GB (or 3 GB) which
32-bit Windows normally maps: the additional system calls to manage the
remapping are considerably more expensive than the native virtual memory
mechanisms.
- bill
|
I know Oracle was PAE-aware, and I assume they did a smart job of
using muliple 4GB memory segments to store tables or indexes
optimally. They're not dummies. yes, context switches are expensive.
--
a d y k e s @ p a n i x . c o m
Don't blame me. I voted for Gore. |
|
| Back to top |
|
|
Benno
Guest
|
| Posted:
Fri Jan 14, 2005 8:42 pm Post subject:
Re: Disk subsystem design questions for new database server. |
|
|
Maxim S. Shatskih wrote:
| Quote: | The DB is only around 4GB. The problem is that Windows can not handle
more then 4GB memory (Windows enterprice can but not very efficient as
Any Windows capable of PAE can handle > 4GB memory perfectly. This does not
allow, surely, the process address spaces of > 4GB, they are still the same
size as previously, just the physical memory is larger.
far as I can tell). And I don't know if SQL server can even address all
that memory (4GB, let alone more).
Later MSSQLServer can use huge address spaces, this is another Windows feature,
called AWE, and is specially intended for things which needs address spaces
4G all full of physical RAM.
IIRC PAE and AWE are mutually exclusive, but I can be wrong here.
So, your solution is to have the latest version of MSSQLServer, which is
AWE-enabled.
|
Do you have any idea about the average performance gain with enough
memory to contain the database in cache v.s. a disk sub-system as I
described in my original post? Even a very rough estimate would be
appreciated.
The cost of the Enterprise versions of Windows 2003 & SQL server 2000 +
6GB of memory v.s. the normal versions and 2GB memory is probably higher
than the disk subsystem design I described.
Thanks. |
|
| Back to top |
|
|
Faeandar
Guest
|
| Posted:
Sat Jan 15, 2005 1:36 am Post subject:
Re: Disk subsystem design questions for new database server. |
|
|
On Fri, 14 Jan 2005 08:58:35 +0100, Benno <benno.usenet@gmail.com>
wrote:
| Quote: | I need to build a new database server (SQL server on new HP or Dell
hardware) and I have a question about the disk subsystem. I want to
separate the OS, logfiles and database files on separate disk arrays.
The OS will be placed on RAID1 on the machine's internal RAID
controller. I intend to install a two channel RAID controller and a
split disk backplane in the machine (or an external disk cabinet) with
two separate SCSI channels. What would give the best performance?
Place the disks for the logfiles and database both on their own separate
RAID and SCSI backplane channels or...
distribute all disks evenly between the RAID and SCSI backplane channels
and assign the logfile and data filesystems to disks on both channels?
So:
RAID channel A ---> SCSI backplane A ---> disk-disk-disk-disk (logfile
filesystem)
RAID channel B ---> SCSI backplane B ---> disk-disk-disk-disk-disk-disk
(database filesystem)
Or (crappy ASCII art ahoy):
----------- ----------------
RAID A ---> SCSI A ---> | disk-disk || disk-disk-disk |
RAID B ---> SCSI B ---> | disk-disk || disk-disk-disk |
|logfiles FS|| database FS |
----------- ----------------
I want to use 10 to 14 disks (36GB 15K rpm)in total to have as much
spindels as possible. Would I need to seperate RAID controllers or will
a two channel controller have enough bandwith?
|
Why not an add-on NVRAM card? Battery backed and with some vendors
you can make it look like a disk drive avoiding any context switching
due to memory map issues.
More expensive than plain 'ol memory but it has benefits.
~F |
|
| Back to top |
|
|
|
|
|
|
FAQ
Memberlist
Register
Profile
Log in to check your private messages
Log in