Disk-I/O bottlenecks can be a major culprit in slowing
database response times until you balance the load on your disks.
With terabyte-sized and Web-enabled Oracle8 databases becoming more commonplace, the task of disk-load balancing has never been more critical. These databases are too massive to be cached in a buffer, yet they often serve thousands of users who expect instant response times. To minimize response time, you need to do all you can to shorten the most time-consuming aspect of a transaction: disk I/O. And the most important thing you can do to minimize disk I/O is to balance the load on your disks.
By placing data files strategically on the physical disks, you can minimize the likelihood of any one disk becoming stalled while handling simultaneous I/O requests. This article provides a strategy for collecting I/O information into Oracle tables and generating reports, to deliver maximum guidance in the load-balancing process for multiple-physical-disk systems. (Be sure to tune SQL statements before modifying the disk layout. Also note that alternative hardware configurations, such as disk striping, spread the load across multiple disks automatically to improve performance.)
Once you've identified the hot disks, look closely to find out which files and tables on the disks experience most of the activity, so that you can move them to less-active disks as needed.
The actual process of identifying hot files and disks involves running data-collection utilities, such as Oracle's utlbstat and utlestat and the UNIX iostat utility, and then using the collected I/O data to pinpoint the sources of excessive I/O measurements.
In a UNIX environment, you would generally navigate the hierarchy shown in Figure 1 to identify the problem. Each physical disk contains many UNIX mount points (directory locations where system administrators attach the disk storage); each mount point contains many Oracle data files, and each may also contain many database tables. After using data collected by iostat to identify a hot disk, you would use data collected by the Oracle utilities to identify which mount point and file contain the table causing the excessive I/O activity.
(Note: Although the hierarchy in Figure 1 is the usual situation, it is also possible for a mount point to contain multiple disks, rather than vice versa; in such cases, you would identify the hot mount point before narrowing in on the hot disk.)
To get the most-accurate results, you should collect I/O statistics at
frequent intervals—preferably no more than ten minutes between samples—over
a representative time period, such as a week. Because individual application
processes have different I/O patterns, bottlenecks may appear on different disks
at various times during each day. And because Oracle transactions happen very
quickly, a disk may experience an I/O bottleneck for a very short period—but a
short-duration bottleneck may nonetheless repeat itself thousands of times each
day. If you make the mistake of summarizing I/O by the hour, as many DBAs do,
you won't see these bottlenecks because the I/O spikes will not be evident in
the hourly average, as shown in Figure
2.
(Note: This example assumes a UNIX environment, but you can use the same strategy in other environments, with appropriate modifications.) An understanding of the table structures is helpful in reading through the listings that go with this article. Figure 3 shows the structure of the tables used in our example; the two key tables are filestat and iostat.
#!/bin/ksh # First, we must set the environment . . . . ORACLE_SID=prodb1 ORACLE_HOME=`cat /var/opt/oracle/oratab|grep $ORACLE_SID|cut -f2 -d':'` PATH=$ORACLE_HOME/bin:$PATH MON=`echo ~oracle/iostat` #---------------------------------------- # If it is not running, then start it . . . #---------------------------------------- check_stat=`ps -ef|grep get_iostat|grep -v grep|wc -l`; oracle_num=`expr $check_stat` if [ $oracle_num -lt 1 ] then nohup $MON/get_iostat_solaris.ksh > /dev/null 2>&1 & fi
One drawback of this approach is that the data-collection tables will eventually become very large. However, you can manage table size by deleting low-I/O data-file entries. For example, you could delete inactive-file entries with the following SQL (which is included in Listing 1 ):
Although the information collected by iostat and filestat is somewhat redundant, the two sets of results work well together. When the iostat results identify a hot mount point, you can turn to the filestat results to look at the activity for each data file residing on the mount point. The filestat results also provide more-in-depth information, including the overall time required to perform the reads and writes. From elapsed-time information, you can quickly identify the files that are waiting on disk I/O and see the actual number of physical reads and writes.
The trend reports provide a picture of the overall I/O situation, which helps identify potential problems. If you continue collecting I/O data, you can generate reports at regular intervals and distribute them to management as spreadsheets. For example, Listing 5 provides sample SQL code for generating a report of hot data files, with the output of the report shown in Figure 4.
For each five-minute interval, this report displays the name of any Oracle database file with an I/O value—defined in this case by number of reads—that is more than 50 percent of the total I/O during that interval. The actual percentage is shown in the right-hand column.
The alert reports, on the other hand, are intended to identify current bottleneck possibilities—that is, specific data files experiencing a disproportionate amount of I/O (for example, those experiencing 20 percent more activity than the average for the mount point).
Often, companies use automated procedures to generate the alert reports and e-mail them to the DBA staff, so that they can move these files to less-active mount points as soon as possible.
Some specific scripts for generating these reports are available at Oracle Publishing Online and include reports for daily activity, top mount point, top mount point each hour, and hot data files for each mount point. (See "Sample Reports Online.")
It may take several iterations of moving files—a nontrivial action, since it must be done while the database is stopped—to find the most workable arrangement. Generally, however, the process of load balancing is well worth the time it takes. Once you have achieved a fairly balanced load, you won't need to move the files unless new processes change the I/O pattern for the disks.
Nonetheless, isolating bottlenecks can be time-consuming and elusive, with the scripts and tables detailed in this article, you can quickly set up a procedure to provide the maximum information you can use for balancing your I/O load and minimizing disk I/O, which is key to keeping response times low. There are cases where although all indications seem to point to a file as a bottleneck that should be moved, the file is one that should stay put (see "Look Before You Leap").
Strategies such as load balancing can go a long way toward improving the speed of your applications—and keeping users happy across your network.
FIGURE 4: The script shown in Listing 5 generated a
sample of a hot-file report. The files shown here have I/O values (measured as
number of reads) that are higher than 50 percent of the total I/O operations for
the mount point during the elapsed period (the actual percentage is shown in
right-hand column).
With terabyte-sized and Web-enabled Oracle8 databases becoming more commonplace, the task of disk-load balancing has never been more critical. These databases are too massive to be cached in a buffer, yet they often serve thousands of users who expect instant response times. To minimize response time, you need to do all you can to shorten the most time-consuming aspect of a transaction: disk I/O. And the most important thing you can do to minimize disk I/O is to balance the load on your disks.
By placing data files strategically on the physical disks, you can minimize the likelihood of any one disk becoming stalled while handling simultaneous I/O requests. This article provides a strategy for collecting I/O information into Oracle tables and generating reports, to deliver maximum guidance in the load-balancing process for multiple-physical-disk systems. (Be sure to tune SQL statements before modifying the disk layout. Also note that alternative hardware configurations, such as disk striping, spread the load across multiple disks automatically to improve performance.)
Identify Possible Bottlenecks
The first step in balancing the load on disks is to find out where they're out of balance by identifying possible bottlenecks. Start by identifying hot disks—those with a disproportionate amount of activity. For example, if one disk in a ten-disk system were experiencing 25 percent of the I/O—measured as the number of reads, writes, or both—you would consider it hot.Once you've identified the hot disks, look closely to find out which files and tables on the disks experience most of the activity, so that you can move them to less-active disks as needed.
The actual process of identifying hot files and disks involves running data-collection utilities, such as Oracle's utlbstat and utlestat and the UNIX iostat utility, and then using the collected I/O data to pinpoint the sources of excessive I/O measurements.
In a UNIX environment, you would generally navigate the hierarchy shown in Figure 1 to identify the problem. Each physical disk contains many UNIX mount points (directory locations where system administrators attach the disk storage); each mount point contains many Oracle data files, and each may also contain many database tables. After using data collected by iostat to identify a hot disk, you would use data collected by the Oracle utilities to identify which mount point and file contain the table causing the excessive I/O activity.
(Note: Although the hierarchy in Figure 1 is the usual situation, it is also possible for a mount point to contain multiple disks, rather than vice versa; in such cases, you would identify the hot mount point before narrowing in on the hot disk.)
 |
| FIGURE 1: To identify the cause of excessive I/O activity on a disk in a UNIX environment, you would typically navigate the hierarchy of I/O structures shown here. You would trace the cause of the activity to a particular mount point on the disk, then to a file on the mount point, and even to a particular table within the file. |
 |
| FIGURE 2: The spikes in this I/O activity data for Disk 123 show the importance of measuring I/O at frequent intervals. They represent short-duration bottlenecks that you might not notice if the system had gathered data at less-frequent intervals—or if it had simply been averaged over the hour, rather than examined in detail. |
A Table-Based Approach
Rather than running the off-the-shelf utilities, which generate a printed report for a single time period, you can modify the utilities to collect the I/O data over five-minute intervals and store the results in Oracle database tables for easy access and report generation.(Note: This example assumes a UNIX environment, but you can use the same strategy in other environments, with appropriate modifications.) An understanding of the table structures is helpful in reading through the listings that go with this article. Figure 3 shows the structure of the tables used in our example; the two key tables are filestat and iostat.
 |
| FIGURE 3: Several summary tables work together to help in the load-balancing process: After populating the vol_grp table with information about mount points and their associated disks, you can relate the disk-level data to the file-level data and identify which files are causing excessive activity on a disk. You can easily create summary tables such as sum_iostat and sum_filestat (which aggregate the disk- and file-level data by mount point) from the vol_grp, filestat, and iostat tables. |
- The filestat table contains the I/O data collected by modified versions of bstat and estat; it includes mount point, filename, and table name, as well as date and specific I/O information. The I/O data includes the actual number of physical reads and writes, the number of block reads and writes, and the time required to perform them.
- The iostat table also includes read and write times corresponding to
specific dates, but at the disk level. It collects information from the
iostat utility, using the script get_iostat.ksh.
To provide a cross-reference between the filestat and iostat tables, we added the vol_grp (volume/group) table, which links mount points to physical disks. You need to populate this table manually, based on how your disks are partitioned into mount points. The design of the vol_grp, filestat, and iostat tables lets you aggregate, or average, I/O data over time and summarize it by disk, mount point, tablespace, or data file.
In the example, we've added two summary tables: sum_filestat and sum_iostat. - The sum_filestat table is created (in the estat_file.sql script) by summing I/O data from the filestat table over all files and tables for each mount point.
- The sum_iostat table is created (in the get_iostat.ksh script) by aggregating the I/O data from the iostat table by mount point, rather than by disk, using the disk-to-mount-point relationships from the vol_grp table.
Collecting the Data
This data-collection approach is based on existing UNIX and Oracle utilities (iostat and bstat/estat, respectively), but it does require slight modifications to these utilities. The example scripts shown in Listings 1 through 4 will help you use these utilities to collect data into the tables shown in Figure 3.- The get_iostat.ksh script, in Listing 1, is a UNIX shell script that collects disk-level I/O information at five-minute intervals. It runs the iostat utility and captures the output into the iostat table, using the data from the vol_grp table to create the sum_iostat table as well. Once you've run this script, you have the data required to identify your system's hot disks and mount points.
- The get_filestat.ksh script, in Listing 2, is a UNIX shell script that collects I/O information at the file and table levels, as well as by mount point, also at five-minute intervals. It calls two SQL snippets, bstat_file.sql and estat_file.sql (shown in Listing 3 and 4), which are customizations of Oracle's utlbstat and utlestat utilities, modified to store the I/O statistics in Oracle tables, rather than in report.txt output. The UNIX script runs bstat_file.sql first, waits five minutes, and then runs estat_file.sql, causing the system to insert the resulting data into the filestat and sum_filestat tables shown in Figure 3. After running this script, you have the data needed to track hot disks down to the file and table level.
#!/bin/ksh # First, we must set the environment . . . . ORACLE_SID=prodb1 ORACLE_HOME=`cat /var/opt/oracle/oratab|grep $ORACLE_SID|cut -f2 -d':'` PATH=$ORACLE_HOME/bin:$PATH MON=`echo ~oracle/iostat` #---------------------------------------- # If it is not running, then start it . . . #---------------------------------------- check_stat=`ps -ef|grep get_iostat|grep -v grep|wc -l`; oracle_num=`expr $check_stat` if [ $oracle_num -lt 1 ] then nohup $MON/get_iostat_solaris.ksh > /dev/null 2>&1 & fi
One drawback of this approach is that the data-collection tables will eventually become very large. However, you can manage table size by deleting low-I/O data-file entries. For example, you could delete inactive-file entries with the following SQL (which is included in Listing 1 ):
delete from sys.iostat where phys_read < 10 and phys_write < 10;
Bear in mind that deleting these entries will skew long-term averages, since
the averages will be based only on higher-activity entries.
Although the information collected by iostat and filestat is somewhat redundant, the two sets of results work well together. When the iostat results identify a hot mount point, you can turn to the filestat results to look at the activity for each data file residing on the mount point. The filestat results also provide more-in-depth information, including the overall time required to perform the reads and writes. From elapsed-time information, you can quickly identify the files that are waiting on disk I/O and see the actual number of physical reads and writes.
Sample Reports OnlineIf the I/O load on your disks isn't well balanced, you probably have some disk I/O bottlenecks slowing your database response times.We've made some specific scripts below that you can use to generate a variety of activity and alert reports based on the I/O statistics, so that you can move the culprit bottleneck files to less-active disk devices. Download each SQL script then run at the SQL*Plus command prompt.
|
Generating I/O Reports
Using the I/O information from the tables, you can generate trend and alert reports. Both types of reports are easy to generate using SQL, and the combination of the reports will help you identify current bottlenecks as well as spot potential future ones.The trend reports provide a picture of the overall I/O situation, which helps identify potential problems. If you continue collecting I/O data, you can generate reports at regular intervals and distribute them to management as spreadsheets. For example, Listing 5 provides sample SQL code for generating a report of hot data files, with the output of the report shown in Figure 4.
For each five-minute interval, this report displays the name of any Oracle database file with an I/O value—defined in this case by number of reads—that is more than 50 percent of the total I/O during that interval. The actual percentage is shown in the right-hand column.
The alert reports, on the other hand, are intended to identify current bottleneck possibilities—that is, specific data files experiencing a disproportionate amount of I/O (for example, those experiencing 20 percent more activity than the average for the mount point).
Often, companies use automated procedures to generate the alert reports and e-mail them to the DBA staff, so that they can move these files to less-active mount points as soon as possible.
Some specific scripts for generating these reports are available at Oracle Publishing Online and include reports for daily activity, top mount point, top mount point each hour, and hot data files for each mount point. (See "Sample Reports Online.")
A Delicate Balance
Having a wealth of I/O data will take you far in the process of load balancing, but you also need ingenuity and patience. An application's disk-access patterns can vary greatly according to daily or weekly processing needs, so the optimal file placement may not always be obvious. (For example, hdisk32 might be very busy during evening batch processing but largely idle during daytime processing.) And it's possible that relocating a data file may relieve I/O contention for one process, only to cause contention for an unrelated process.It may take several iterations of moving files—a nontrivial action, since it must be done while the database is stopped—to find the most workable arrangement. Generally, however, the process of load balancing is well worth the time it takes. Once you have achieved a fairly balanced load, you won't need to move the files unless new processes change the I/O pattern for the disks.
Nonetheless, isolating bottlenecks can be time-consuming and elusive, with the scripts and tables detailed in this article, you can quickly set up a procedure to provide the maximum information you can use for balancing your I/O load and minimizing disk I/O, which is key to keeping response times low. There are cases where although all indications seem to point to a file as a bottleneck that should be moved, the file is one that should stay put (see "Look Before You Leap").
Strategies such as load balancing can go a long way toward improving the speed of your applications—and keeping users happy across your network.
Look Before You LeapA file with a high I/O rate is not always a bottleneck. Strictly speaking, a disk bottleneck is defined as two or more processes competing to read from or write to the same disk device. However, it can be very difficult to discover which processes are responsible for high I/O on a disk. (Oracle Enterprise Manager can help—look at Top Sessions ordered by physical reads to find out which sessions are currently doing lots of I/O).In addition, you can't always move files with high I/O rates. For example, redo log files will always experience high I/O and you shouldn't move them as part of load balancing (although you can stripe your redo logos across separate disks if archiving, so that the archiver reads from a different disk to the active log). Remember, too, that for databases placed on a small number of disks, some amount of I/O contention may be unavoidable. Consider a 30GB database spread across two disks with 20 processes competing for data. On average, 10 processes would be queued waiting for I/O from each of the two disks. Clearly, these systems will always experience I/O contention. |
| Date | MountPt. | MpReads | FileName | FileReads | Pct |
| 09/2213:41 | file1 | 5 | tablbtabi.dbf | 3 | 60 |
| file10 | 4 | tablespace13d.dbf | 4 | 100 | |
| file4 | 169 | tablespace12d.dbf | 168 | 99 | |
| file5 | 1 | tablbtabd.dbf | 1 | 100 | |
| file7 | 2 | tablsourced.dbf | 2 | 100 | |
| file8 | 57 | tablloadd.dbf | 53 | 93 | |
| file9 | 2 | tablprotd.dbf | 2 | 100 | |
| 09/2213:46 | file1 | 26 | sat_system.dbf | 18 | 69 |
| file10 | 2 | tablespace13d.dbf | 2 | 100 | |
| file2 | 2 | tablespace8i.dbf | 2 | 100 | |
| file3 | 3 | tablpooli.dbf | 3 | 100 | |
| file4 | 11 | tablstabd.dbf | 11 | 100 | |
| file5 | 3 | tablddicd.dbf | 3 | 100 | |
| file7 | 3 | tablsourced.dbf | 3 | 100 | |
| file9 | 4 | tablpoold.dbf | 4 | 100 | |
| 09/2213:51 | file1 | 5 | tablespace12i.dbf | 3 | 60 |
| file10 | 3 | tablespace14d.dbf | 2 | 67 | |
| file4 | 1 | tablstabd.dbf | 1 | 100 | |
| file8 | 7 | tablloadd.dbf | 5 | 71 | |
| file9 | 4 | tabldocud.dbf | 4 | 100 |
No comments:
Post a Comment