The following equation is helpful in thinking about I/O performance.
response time = waiting time + service time
Service time is the time that it takes the hardware to service an I/O request.
Waiting time may be needed if the hardware is busy.
In disk load balancing, we want to minimize waiting time by spreading the I/O load as evenly as possible across all the available hardware components. If this is not the case, load must be moved from hot disks to cold disks, either by moving datafiles or by moving database segments such as tables and indexes.
However, balanced load is not an end in itself. One disk may be kept very busy by a single process performing intensive sequential I/O, whereas another disk may be only lightly used by a number of processes performing random I/O. This unbalanced load is perfectly satisfactory, because there is no waiting time.
The objective of disk load balancing is to improve I/O response times by eliminating waiting time. There is no waiting time in single-threaded sequential I/O, and so disk load balancing is not required. Some waiting time may be apparent if asynchronous sequential I/O is used, however this is merely illusory, because one process does not wait for another. So do not try to balance a heavy sequential workload with a light random workload. There is no real waiting time to regain, and you will merely degrade the service times.
However, it may be appropriate to balance a heavy random workload with a light sequential workload. In this case the saving in waiting time for the random I/O may well compensate for the increased service times for the logically but no longer physically sequential I/O.
To balance disk workloads you need statistics. The most obvious statistic to use is the waiting time itself. But unfortunately, the operating system statistics on waiting time are not reliable. Much of what the operating system sees as service time may actually be waiting time in the controller or disk array, and many operating systems just estimate the breakdown between waiting and service time rather than measuring it. Also, because the operating system I/O statistics are measured at the device driver level, they do not include waiting time sustained at higher levels in the operating system such as in the LVM or file system cache layers.
An alternative measure of disk workload is busyness. But unfortunately the disk busyness statistics are no more reliable than the service time statistics from which they are derived. Also, what seems like a moderately idle disk when performing sequential I/O may suddenly become a very busy disk if you move some of the random I/O workload to it, because the efficiencies of sequential I/O will be lost.
Therefore, we recommend that you use the number of I/O operations performed as the measure of disk workload. This will give you a better appreciation for the potential effect of mixing random and otherwise sequential workloads.
The number of I/O operations performed is measured both by Oracle and by the operating system. Oracle provides statistics for each datafile in V$FILESTAT, and the operating system provides statistics for each disk. For raw databases, both sets of statistics are useful, because there is seldom a one to one relationship between disks and datafiles. However, for file system based databases it is best to disregard the operating system data, because of the impact of split reads on the statistics, and to use the Oracle statistics exclusively. Of course it goes almost without saying that there should be no non-Oracle load on the disks, and that you should not share disks between databases, lest your Oracle statistics be misleading.
This simple figure illustrates the concept of using these statistics for disk load balancing.
Of course, there is a lot more to disk load balancing than this simplistic diagram illustrates. For example, if datafile 3 contains a key application table and datafile 1 contains its primary key index, then although we have balanced the number of I/O operations, the service times on disk 1 will be poor because the disk heads will thrash back and forth between the table and index datafiles, whenever the table is accessed via the index in a nested loops join.
In general, the principles of I/O separation outlined in our tip Planning Tablespaces should be observed when collocating datafiles on disks, as should the I/O characteristics of the datafiles themselves.
|Copyright © Ixora Pty Ltd||