Free Oracle Tips

HTML Text
Oracle Consulting Oracle Support
Oracle Training
SQL Tuning Consulting
Oracle Tuning Consulting
Data Warehouse Consulting
Oracle Project Management
Oracle Security Assessment
Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Internals Magazine
Oracle Links
Oracle Monitoring
Remote Support Benefits
Remote Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices
 

Free Oracle Tips


 
HTML Text AOL
 
 

Disks and Oracle

Mike Ault

 

In this day of 200+ gigabyte disks I am often asked how many disks are needed to support a particular Oracle database. Most often the questions deal with the physical size of the database, for example, do they only need 2 disks in a RAID 1 (4 for RAID10) array for a 100 gig database. It seems a reasonable thing to do, after all, disk manufacturers wouldn't put 200 gigabytes on single platter unless they also provided for fast access would they? Let's examine this premise.

Disk technology, at the physical level hasn't really changed since the first hard drive was built. You have a spinning disk coated with a magnetic material and a movable actuator arm containing a read/write head or heads capable of reading or writing the magnetic traces used to store the data onto the disks surface. We have seen improvements in disk speed, the capability to read smaller and smaller amounts of magnetic charge, better substrate stabilities and all of this has led to disks with faster access times and larger capacities. However, the speed at which capacity has increased and the rate at which access rate has increased has not been equal. For example, from table 1, when going from a 9 gigabyte drive to a 180 gigabyte (a factor of 20 increase in capacity) the IO transfer rate only increases by a factor of  approximately 7 (6.8).

Way back in the 80's and 90's I can remember 10 megabyte platters that were somewhere around 14 inches in diameter.   I remember the first 90 megabyte Winchester I worked with, it weighed something like 400 pounds and was rack mounted. Of course now we have 200+ (the last I saw was 250 gigabyte) hard drives that fit into any standard PC. So we have moved from drives that held (rounding the numbers to even powers of ten) 90,000,000 million bytes of information to drives capable of 250,000,000,000 billion bytes of information. That is a factor of 2,778 increase in data storage capability. The same 90 megabyte drive was capable of 10-20 megabyte per second IO rates. Not going back quite so far into antiquity, lets look at recent IO rates, look at table 1.

 

Max Capacity

Drives/terabyte

Average Access Time (msec)

MB/s/Drive

Transfer Rate (total)

9 gig

112

9.9

8.5

952

18 gig

56

7.7

14

728

36 gig

28

5.4

23

544

73 gig

14

5.6

33

462

180 gig

6

4.16

58

348

Table 1: Comparison of IO Capacity Verses Drive Size

Table 1, taken from Disk Tuning for Oracle, Mike Ault, Rampant Publishing, 2004, shows the published data transfer rates for various disk sizes. The table compares the number of disks required to get to a terabyte of data and the associated estimated transfer rate if each disk were accessed at 100 percent of capacity. Look at the values for 9 gig drives and 180 gig drives.

So to achieve the same IO capacity as we had with our 112-9 gigabyte disks we would need to buy 2.7 (952/348) times the needed capacity of 180 gigabyte drives even with their superior access times and MB/s transfer rates. The values in table 1 reflect the performance values when that drive capacity was the maximum available.

The dependence on the rotational speed and actuator arm speed is what limits the IO capability of the drives. Comparison of multiple drives from the same company shows that while the number of platters/sides used determines the capacity of the drive, the IO rates and seek and latency values remain fairly constant. Table 2 shows this for multiple Seagate drives with the same speed disk but different capacities.

Capacity and Interface

Formatted Gbytes (512 bytes/sector)

40

80

120

160

Interface

Ultra ATA/100

Ultra ATA/100

Ultra ATA/100

Ultra ATA/100

 

 Performance

Internal Transfer Rate (Mbits/sec)

683

683

683

683

Max. External Transfer Rate (Mbytes/sec)

100 

100 

100 

100 

Avg. Sustained Transfer Rate (Mbytes/sec)

>58

>58

>58

>58

Average Seek (msec)

8.5

8.5

8.5

8.5

Average Latency (msec)

4.16

4.16

4.16

4.16

Multisegmented Cache

2048

2048

2048

2048

Spindle Speed (RPM)

7200

7200

7200

7200

 

 Configuration/Organization

Discs/Heads

1/1

1/2

2/3

2/4

Bytes per Sector

512

512

512

512

Logical CHS

16383/16/63

16383/16/63

16383/16/63

16383/16/63

Table 2: Platters Effect on IO, Latency and Seek times

To get the same effective IO rate per gigabyte of capacity when going from a single-side, single-platter 40 gigabyte drive to a four-side, dual-platter 160 gigabyte drive you would need four or more of the 160 gigabyte drives (100/40=2.5 MB/sec/GB  verses 100/160= 0.625 MG/sec/GB)! This is the little secret that undermines the entire "bigger is better" myth of disk capacity.

Of course as we see in Table 2, if we replace the old 9 gig drives with the new 40 gig (36 gig formatted) drives with the sustained transfer rate of 58 MB/s we get 28*58 or a total transfer rate of 1624 MB/s which would require 28 of either the new 73 or 180 gig drives in order to match the IO rate from the 28-40 gig drives.

So if we used the new 40 gig drives, we could expect better IO transfer rates of up to 70 percent based on IO rate, while if we bought the 180 gig drives based on storage capacity alone we would see a decrease in IO capacity of over 270%. This is why IO capacity should be the driving factor in disk drive purchase, not storage volume per disk.


If you are planning to be in the New York area Tuesday, November 9th, 2004....stop by the Rihga Royal New York and catch Don Burleson's seminar on 64-Bit Oracle for Windows and Linux.

Click here to register: http://www.unisys.com/datacenter/oracle_seminar/
 



Need Oracle Tuning Support?

Burleson Consulting now offers a one-day performance review for your Oracle database.  Working with top experts who tune hundreds of databases each year, you can get fast expert tuning advice to hypercharge your Oracle database.

We also provide expert upgrades to Oracleand Oracle10g, and our DBAs can quickly show you how to implement the important new features of new Oracle releases.

 

Call now for remote Oracle support.

 

Burleson Consulting
 Kittrell, NC, 27544

Email: info@remote-dba.net • Phone (800) 766-1884

Copyright 1996 - 2015 by Burleson , Inc. All rights reserved.