Tip

Oracle disk I/O tuning: Disk performance statistics

This is the third tip in a series on the different aspects of disk I/O performance and optimization for Oracle databases.

The following is the third tip in a series on the different aspects of disk I/O performance and optimization for Oracle databases. Each tip is excerpted from the not-yet-released Rampant TechPress book, "Oracle disk I/O tuning," by Mike Ault. Check back to the main series page for upcoming installments.


Disk performance statistics

When you review disk performance statistics provided by the manufacturer you will get a table that looks something like figure 1-5.

 

Physical Specifications
Formatted Capacity 250,059 MB
Interface 1.5 Gb/s Interface(Serial ATA 1.0)
Bytes Per Sector (STD) 512
User Sectors Per Drive 488,397,168
Dedicated Landing Zone Yes
Actuator Latch/Auto Park Yes

 

Performance Specifications
Data Transfer Rate- Buffer to Host- Buffer to Disk 150 MB/s 2748 Mbits/s max
Average Read Seek 8.9 ms (average)
Track-to-Track Seek 2.0 ms (average)
Full Stroke Read Seek 21 ms (average)
Average Latency 4.2 ms
Rotational Speed 7200 RPM
Read Cache Adaptive
Write Cache Yes
Buffer 8 MB
Drive Ready Time 9.0 sec (average)
Start/Stop Cycles 50,000 minimum
LBA Support Yes
Error Rate(non-recoverable) <1 in 1014 bits read
Figure 1-5: Typical Drive Specifications (Provided courtesy of Western Digital Corp. www.westerndigital.com)

Mike Ault

Mike Ault is one of SearchOracle.com's Oracle Internals experts. Mike is senior Oracle consultant with Burleson Consulting, and one of the leading names in Oracle technology.

To view Mike's expert responses or to ask him a question, click here.

 So what do these statistics mean? The first section of figure 1-5 shows the layout for the drive giving formatted capacity (almost, why drive manufacturers insist on not using the standard values for megabytes and gigabytes is beyond me), the interface speed, bytes per sector and number of sectors. This section is fairly self explanatory. What we are really interested in is the second section of figure 1-5 where they provide actual performance statistics.

In the second section of figure 1-5 the following statistics are provided:

  • Data Transfer Rate(s) - This is broken into two parts, disk buffer to host and disk buffer to disk. Notice how the disk buffer to host is 150 MB/s (megabytes per second) while the buffer to disk is specified as 748 Mbits/s max (megabits per second). If you are using 32 bit bytes (most modern systems are, if not 64 bit) then this means a transfer rate of around 23 Mbytes per second to disk.

     

  • Average Read Seek - Anytime you see seek, think actuator arm movement. This statistic is telling us on the average how long it takes the actuator arm to position itself for a read of a sector.

     

  • Track-to-Track Seek - Again, this deals with actuator arm movement and tells us the average time for movement when the arm is already positioned over a specific track and must move to a different track.

     

  • Full Stroke Read Seek - This tells us how long it will take for a read if the arm is positioned in the absolute worst position when it receives the move command.

     

  • Average Latency - Generally when you see latency you are speaking of the amount of time it takes for the proper sector to rotate into position under the read/write head once the read/write head is in position. The faster the drive rotates the lower the latency numbers should be.

In general seek times are controlled by how fast the arm on the actuator can move. This is controlled by the power of the actuator mechanism and the weight of the actuator arm and read/write heads. The more powerful the disk actuator and the stiffer/lighter the actuator arm the lower the seek times.

Latency is controlled by rotational speed of the disk. The higher the disk speed the lower the latency.

Some disk companies also provide expanded statistics, for example figure 1-6 shows the specifications provided by Seagate for their Cheetah series of drives.

 

Cheetah 10K.6
Technical Specifications
Description
Model number ST3146807FC
Model Name Cheetah 10K.6
Form Factor(width) 3.5-inch
Form Factor(height) 1-inch
Physical Specifications
Height 25.4 mm
Width 101.6 mm
Length 146.05 mm
Weight 0.73 kg
Capacity/Interface
Formatted Capacity 146.8 Gbytes
Interface Type 40-pin SCA
Performance
Transfer Rates
Internal Transfer Rate (min) 475 Mbits/sec
Internal Transfer Rate (max) 841 Mbits/sec
Formatted Int Transfer Rate (min) 43 MBytes/sec
Formatted Int Transfer Rate (max) 78 MBytes/sec
External (I/O) Transfer Rate (max) 200 MBytes/sec
Avg Formatted Transfer Rate 66 MBytes/sec
Seek Times
Average Seek Time, Read 4.7 msec typical
Average Seek Time, Write 5.3 msec typical
Track-to-Track Seek, Read 0.35 msec typical
Track-to-Track Seek, Write 0.55 msec typical
Average Latency 2.99 msec
Other
Default Buffer (cache) Size 8,000 Kbytes
Spindle Speed 10000 RPM
Spinup Time 20 sec
Configuration
Number of Discs (physical) 4
Number of Heads (physical) 8
Total Cylinders 49,855
Bytes Per Sector 512
Areal Density (Mbits/sq in) 36,000 Mbits/square inch
Track Density (TPI) 64,000 tracks/inch
Recording Density (BPI, max) 570,000 bits/inch

Figure 1-6: More Detailed Specifications from Seagate

Seagate has added the average write seek times as well as giving formatted transfer rates. This gives a more complete picture of the disks' abilities. Notice that Seagate has also included the number of disks and cylinders as well as the number of heads. The number of heads indicates that the platter is two-sided and both sides are being used for data. The four platters this Seagate drive provide 8 tracks per cylinder (2 on each disk, an upper track and lower track).

However the biggest factors in single disk performance will be the disk speed and speed of the actuator arm as measured by the average latency (rotational latency) and seek times. More platters in a drive will do little to improve write or read time for database related IO. The number of platters is more related to capacity since the read/write heads are generally not independent but are mounted on a single actuator with multiple, parallel arms. This means that only one disk surface is read from or written to per operation but multiple operations may take place in a single IO.

This dependence on the rotational speed and actuator arm speed is what limits the IO capability of the drives. Comparisons 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. Figure 1-7 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

Figure 1-7: 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.


Dig Deeper on Oracle database performance problems and tuning

Data Management
Business Analytics
SearchSAP
TheServerSide.com
Data Center
Content Management
HRSoftware
Close