|
In order to get the maximum performance from your disk system you must
understand the IO characteristics (the profile) of your database
system, be it Oracle, SQL Server, Informix, UDB or MySQL. You must
tune your disk architecture to support the expected IO profile and
must tune the database system to take advantage of the disk
architecture. For example, an Oracle database has different IO
characteristics depending on whether it is reading or writing data
and what type of read or write it is doing. Other databases have
fixed read/write sizes.
You must determine the IO profile for your database and then use the
IO profile of the database to determine the maximum and minimum IO
size. The IO profile will tell you what percentage of IO is large IO
and what percentage is small IO, it will also give you the expected
IO rate in IO/second.
Once you have the IO per second you can determine the IO capacity
(number of drives) needed to support your database.
The first rule of tuning your disk system is:
Size first for IO capacity, then for volume.
Some back of the envelope calculations for the number of spindles
needed to support IO rate are:
RAID10 with active read/write to all mirrors:
MAX(CEILING(IOR/(NSIOR*M),M),2*M)
Where:
IOR is expected maximum IO rate in IO/sec
NSIOR is the average non-sequential IO rate of the disks in IO/sec
(range of 90-100 for RAID10)
M is the number of mirrors
(The maximum of the IO rate divided by the average non-sequential IO
rate per disk times the number of mirrors to the nearest power of M
or 2*M)
RAID5 assuming 1 parity disk:
MAX((IOR/CNSIOR)+1,3)
Where:
IOR is expected maximum IO rate in IO/sec
CNSIOR is the corrected average non-sequential IO rate of the disks
in IO/sec (range of 60-90 for RAID5)
(The maximum of the IO rate divided by the average non-sequential IO
rate per disk corrected for RAID5 penalties plus 1 disk for the
parity disk)
The correction for the non-sequential IO rate for RAID is due to the
up to 400% penalty on writes (writes take 4 times linger than reads
on the same drive). In some cases on RAID5 I have seen this go as
high as 6400% (writes take 64 times as long as reads for the same
file) when combined with other problems such as fragmentation.
A case in point, early RAID architectures utilized the "stripe
shallow and wide" mind set where files where broken into small
pieces and spread over a large number of disks. For example, stripe
unites per disk of as small as 8K were common. Many system read in
IO sizes of 64K or larger. This means that to satisfy a single IO
request 8 disks of the RAID set were required, if there were fewer
than 8 disks in the set. disks would have to undergo 2 or more IOs
to satisfy the request. This sounds fine if you are talking about a
single user wanting to read a large file from a large group of disks
very quickly, however, what happens when you have 10 or 100 or 1000
concurrent users all wanting to do the same thing?
This is an excerpt from my book "Oracle Disk I/O Tuning, and it's
only $19.95 at this link:
http://www.dba-oracle.com/bp/bp_book15_oi_disk.htm
|
