Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Mar 6, 2008

Measuring Disk I/O—A Vendor View

By James Koopmann

Introduction

How do you know if the disks you will be using from ANY particular vendor can muster up the IOPS and MBPS required to satisfy your current or future workloads?

In the last article, Measuring Disk I/O, we took a quick look at the amount of IOPS and MBPS, or workload, that our Oracle database is generating. These numbers are very important when we start to look at our system for available throughput, especially out to the disk subsystem.

Why are these numbers important? As a very simple example, and no real meaning, suppose you, after running the scripts from that article, find out that your database is requesting, and getting, 100,000 IOPS. Well, if your disk subsystem has 1,000 disks and if every disk is participating in satisfying 100,000 IOPS, you could sort of say that each disk is performing about 100 IOPS. You then have to ask yourself the following questions:

Is this good on a per disk basis?

Do I have room to grow if my throughput were to double or triple?

How much breathing room do I really have?

These are all very important questions as your IT or C-level management comes to you and asks about future growth.

Aside from actually creating a test environment to simulate, which means actually purchasing more hardware, you can do some quick number crunching to take some very simplistic but accurate answers. You see, the wonderful thing about a disk, being different from CPUs or memory, is that there are actual hard limits that they can perform under. The disk platters can only rotate so fast and the read/write arm can only seek so fast. There are just plain mechanics involved that limit the number of IOPS you can actually perform.

So let’s take a quick dive into these from a truly vendor perspective. Every, well most creditable vendors will have somewhere on their website the actual specifications that go along with the disks they sell. The most common types of specifications given, and actually usable for us are the following:

RPM—Revolutions Per Minute. The number of rotations completed in one minute.

Seek Time—The time required to position the head over the target track. (less for read than write)

Average Latency—(Rotational Latency) Average time after head is over the track for the target sector to rotate under the head before a read or write.

Here is a cut-out of a very popular vendor’s specification sheet:

Please note that regardless of the size of the disk, this vendor is saying that the specifications are the same for spindle speed, average latency, and seek time. Personally, I would question this straight up but for this paper let’s not venture down that path.

Specifications 300GB 146.8GB 73.4GB
Spindle Speed (RPM) 15K 15K 15K
Average Latency (msec) 2.0 2.0 2.0
Seek Time (Average Read/Write (msec) 3.5/4.0 3.5/4.0 3.5/4.0

What we can do is take these numbers and plug them into the table below, complete the equations and come up with a per disk, REAL number, IOPS that is expected for these disks. For the numbers above you basically just plug in the RPM, do the calculations across to end up with the Full Rotation (ms), plug in the Average Seek Time, and then calculate the IO Time and IOPS. Quite simple!

RPM
Rotations
Per
Minute

Rotations
Per
Second

Rotations
Per
Mili-second

Full Rotation

Rotational Latency
(Half Rotation)

Average
Seek
Time

IO
Time

IOPS

(x)

(x/60)

(x/60,000)

(1/ [x/60000] )

(1/ [x/60000] ) / 2

     
       

Y

Z

(Y+Z)

(1/[Y+Z])*1000

15,000

15,000/60

15,000/
60,000

4ms

2ms

4ms

6ms

167

10,000

10,000/60

10,000/
60,000

6ms

3ms

5.15ms

8.15ms

122

     

10ms

5ms

9ms

14ms

71

7,200

7200/60

7,200/
60,000

8.4ms

4.2ms

9.9ms

14.1ms

71

Now not all vendors will give you as many of the specifications given above. But they typically will give you RPM and one of the other two specifications. Just so long as you are given at least two of the numbers, you can calculate IOPS. Take for instance the third example where RPM was not given. The vendor might have given Full Rotation or Latency. If they gave Full Rotation, Latency is typically on half of a full rotation. If you are given Latency, just double it to get Full Rotational speed. Play with the numbers and you will soon be doing this in your sleep.

Now evaluation of the actual IOPS a disk can provide is the next step. Taking the results from the table and looking at our initial 100,000 IOPS example you can quickly see that in order to supply 100,000 IOPS we would need to purchase either the first or second flavor of disks. Granted this does not take into account some of the caching that might take place on a disk sub system, but depending on your application your database might not be able to take advantage of it anyway.

Taking this one step further, we can also calculate the number of MBPS per disk that can be supported on a per disk basis. This is an even easier calculation than IOPS. Basically you take the IOPS a disk can provide and calculate it by the segment size of the I/O request. The following table provides some simple examples.

IOPS

Number of Disks

Segment Size

MBPS

X

Y

Z

(X*Y*Z)/1024

167

1

128K

20MBPS

122

1

128K

15MBPS

71

1

128K

9MBPS

71

1

128K

9MBPS

Comparing the actual workload of a database, down to the actual mechanical capabilities of individual disks can provide the DBA or storage administrator some valuable information. The question of scaling your database to handle additional load should not be a stab in the dark. Do a few calculations, get a real number, and then plan accordingly. But I will warn you, there are many out there that would like you to believe that the only requirement for a disk subsystem is the amount of space they can allocate and use. You are forewarned, if you take this route and your database actually requires a higher level of performance the database will fail and no performance tuning attempts from any “guru” can solve your problem. Sometimes mechanical speeds rule!

» See All Articles by Columnist James Koopmann



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM