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.
|
||||||||||||||||||||||||||||||||||||||||||
| Figure 1-5: Typical Drive Specifications (Provided courtesy of Western Digital Corp. www.westerndigital.com) | ||||||||||||||||||||||||||||||||||||||||||
|
|
||||||||||||||||||||||||||||||||||||||||||
 |
|
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.
