Method for improving performance of large databases

• US 6,035,306 A
• Filed: 11/24/1997
• Issued: 03/07/2000
• Est. Priority Date: 11/24/1997
• Status: Expired due to Term

• Current Assignee: Emc IP Holding Company LLC (Dell Technologies Inc.)
• Original Assignee: Terascape Software, Inc. (Dell Technologies Inc.)
• Inventors: Lowenthal, Sheldon, Sharpe, Gary E., Farnham, Mark W.
• Primary Examiner(s): Lintz, Paul R.
• Assistant Examiner(s): ALAM, SHAHID AL
• Application Number:
  US08/977,268
• Time in Patent Office: 834 Days
• Field of Search: 707/2, 707/3, 707/4, 707/10, 707/104, 707/200, 707/201, 395/200.31, 395/200.49, 395/200.54, 395/200.68, 395/675
• US Class Current: 1/1
• CPC Class Codes: G06F 11/32 with visual or acoustical indication of the functioning of the machine
• CPC Class Codes: G06F 11/3466 Performance evaluation by tracing or monitoring
• CPC Class Codes: G06F 11/3485 for I/O devices
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation
• CPC Class Codes: Y10S 707/99952 Coherency, e.g. same view to multiple users

Abstract

The present invention provides a tool with which a DBA can analyze a VLDB at all levels of data storage, both logical and physical, to analyze performance problems. The invention is particularly applicable to systems in which database files are striped over multiple disk drives. In the present invention, storage of information is monitored at three levels: database files, file system files, and individual disk drives Parameters indicating the activity and performance level of each of the levels of storage are taken at selected intervals and stored. An analysis tool is provided that allows a DBA to select a time window during which the database performance is be assessed. The analysis tool has a map of the logical and physical arrangement of the database being monitored and allows a DBA to move from level to level of both logical and physical structures involved in storing the data while displaying I/O activity in an easily understood manner.

Claims

1. A method for analyzing disk performance in a database system of the type which includes (a) database program means for responding to external queries for data input and output from users of the database system, (b) a data storage system for storing data used by the database system, on a plurality of individual disk drives, and (c) file system means, responsive to database file I/O requests from the database program to store and retrieve database files, for sending file system I/O commands to the data storage system to store and retrieve the database files on the data storage system arranged as a plurality of logical disks, at least some of the logical disks being striped across a plurality of the individual disk drives, the method including the steps of:

defining a series of successive sampling periods during which sample data reflecting the performance of the database system is to be taken;

monitoring the database program means to collect data representative of database file I/O requests that occur during each sampling period and storing the collected data as a plurality of first data samples, each first data sample including the time of the data sample, the name of the associated database file, and a plurality of parameters of different types representative of the speed of response of the file system means to the database file I/O request;

monitoring the file system means to collect data representative of file system I/O commands that occur during each sampling period and storing the collected data as a plurality of second data samples, each second data sample including the time of the data sample, the name of the associated logical disk, and a plurality of parameters of different types representative of the speed of response of the data storage system to the file system I/O request;

monitoring the data storage system to collect data representative of the speed of response of each individual disk drive as data is stored and retrieved during each sampling period and storing the collected data as a plurality of third data samples, each third data sample including the time of the data sample, the name of the associated disk drive, and a plurality of parameters of different types representative of the speed of response of the disk drive as data is stored on and retrieved from the disk drive;

providing a display representative of database performance as reflected by the stored statistics, including the steps of;

designating a display time interval;

selecting one I/O type from among a group comprising database files, logical disks, and disk drives;

selecting one of the types of stored parameters for the selected I/O type;

selecting, from the stored data samples, a subset of samples for the selected I/O type that fall within the designated interval, so that the selected subset of samples will include a plurality of samples for either database files, logical disks, or disk drives, depending on the I/O type selected;

grouping the selected data samples into a plurality of groups so that each group includes data samples having the same name;

for each of said groups, mathematically combining the selected parameter in each of the data samples of the group to provide a single statistical parameter associated with the name of the groups data samples; and

displaying the statistical parameters along with the associated name for each of the names present in the selected subset.

2. The method of claim 1 wherein further including the steps of:

selecting one of the names in the displayed statistical parameters;

selecting a second I/O type from among the group comprising database files, logical files, and disk drives, but excluding the I/O type of the displayed parameters;

selecting, from the stored data samples, a second subset of samples for the selected I/O type that fall within the designated interval and that are related to the selected item;

mathematically processing the parameters for each set of samples with the same name in the second group of selected samples to provide a single statistical parameter representative of performance associated with the named item; and

displaying the statistical parameters for each of the items present in the selected subset.

3. The method of claim 1 further including the steps of:

providing a schema data store which includes the name of each stripe set and the names of all disk drives that are associated with each stripe set;

wherein the groups of I/O types from which the first and second I/O types are select includes stripe sets;

wherein, when stripe set is the I/O type selected, the step of selecting a subset of samples includes the step of selecting, from the stored third data samples, a subset of samples that fall within the designated interval, so that the selected subset of samples will include a plurality of samples for disk drives; and

wherein the step of grouping includes grouping the selected subset of third data samples so that each group includes data samples from disks associated with a name of a stripe set.

4. The method of claim 1 wherein at least some of the logical disks are mirrored disks in which identical information is stored on a plurality of separate logical drives, the plurality of separate logical drive making up a volume and further including the steps of:

providing a schema data store which includes a name for each volume and the names of the logical disks which make up the volume;

wherein the groups of I/O types from which the first and second I/O types are select includes volumes;

wherein, when volumes is the I/O type selected, the step of selecting a subset of samples includes the step of selecting, from the stored second data samples, a subset of samples that fall within the designated interval, so that the selected subset of samples will include a plurality of samples for logical drives; and

wherein the step of grouping includes grouping the selected subset of second data samples so that each group includes data samples from logical disks associated with a name of a volume.

5. The method of claim 1 wherein the database system is an Oracle database which includes tablespaces, each of which has database files associated therewith and further including the steps of:

providing a data store which includes a name for each tablespace and the names of the database files associated with the tablespace;

wherein the groups of I/O types from which the first and second I/O types are select includes tablespaces;

wherein, when tablespaces is the I/O type selected, the step of selecting a subset of samples includes the step of selecting, from the stored first data samples, a subset of samples that fall within the designated interval, so that the selected subset of samples will include a plurality of samples for database files; and

wherein the step of grouping includes grouping the selected subset of first data samples so that each group includes data samples from database files associated with a name of a tablespace.

6. The method of claim 1 wherein the step of mathematically combining includes the step of providing a choice of one from among a plurality of statistical methods of analysis to provide said single statistical parameter;

and

wherein the step of processing includes the step of combining the parameters associated with each named item using the chosen method to provide said single statistical parameter for each named item.

7. The method of claim 6 wherein the step of processing a choice includes the step of providing a choice between calculating minimum, maximum, average, mean, or R.M.S. values for the samples associated with each name.

8. The method of claim 6 wherein the choice of statistical methods includes a weighted I/O method and further includes the step of, when said weighted I/O method is chosen, assigning a relative proportional value to each of the parameters associated with the samples of the selected I/O type, wherein said relative percentage can be zero percent, and wherein the step of mathematically combining includes combining the parameters associated with the each sample in each group in accordance with the respective assigned proportional values to provide said single statistical parameter.

9. The method of claim 6 wherein the step of mathematically combining includes the steps of:

providing a schema data store which includes for each named disk drive a speed value representing the speed of I/O performance for that disk drive relative to the other disk drives; and

wherein, when the selected I/O type is disk drives, the step of mathematically combining includes the step of normalizing the set of each data samples by, for each named drive, dividing the result of said combining step by the speed value associated with that drive.

10. The method of claim 1 wherein the plurality of parameters stored in each of the first, second, and third data samples representative of the speed of response of the associated I/O type includes, for each sampling period, the number of kilo-bytes written, the number of kilobytes read, the number of read operations, and the number of write operations.

11. The method of claim 10 wherein the plurality of parameters stored in each of the third data sample includes queue length and service time.

12. The method of claim 1 wherein the step of displaying includes the step of displaying a graphical representation of each statistical parameter.

13. The method of claim 1 wherein the step of displaying includes the step of displaying each statistical parameter as a bar of a bar graph, and wherein the bars are arranged in order of the values of the statistical parameters.

14. A method for speeding up disk performance by improving placement in a database system of the type which includes (a) database program means for responding to external queries for data input and output from users of the database system, (b) a data storage system for storing data used by the database system, on a plurality of individual disk drives, and (c) file system means, responsive to database file I/O requests from the database program to store and retrieve database files, for sending file system I/O commands to the data storage system to store and retrieve the database files on the data storage system arranged as a plurality of logical disks, at least some of the logical disks being striped across a plurality of the individual disk drives, the method including the steps of:

defining a series of successive sampling periods during which sample data reflecting the performance of the database system is to be taken;

monitoring the file system means to collect data representative of file system I/O commands that occur during each sampling period and storing the collected data as a plurality of first data samples, each second data sample including the time of the data sample, the name of the associated logical disk, and a plurality of parameters of different types representative of the speed of response of the data storage system to the file system I/O request;

monitoring the data storage system to collect data representative of the speed of response of each individual disk drive as data is stored and retrieved during each sampling period and storing the collected data as a plurality of second data samples, each data sample including the time of the data sample, the name of the associated disk drive, and a plurality of parameters of different types representative of the speed of response of the disk drive as data is stored on and retrieved from the disk drive;

providing a schema data store which includes (1) the name of each stripe set and the names of all disk drives that are associated with that stripe set, and (2) the name of each logical drive and the names of all disk drives that are associated with that logical drive;

designating a time interval during which the database performance is to be analyzed for improvement;

selecting one of the types of parameters stored in each third data set;

selecting, from the stored data samples, a subset that includes all first data samples that fall within the designated interval, so that the selected subset of samples will include a plurality of samples for disk drives that occurred in the designated interval;

grouping the selected data samples into a plurality of first groups so that each group includes data samples associated with stripe a named set;

for each of first said groups, mathematically combining the selected parameter in each of the data samples of the group to provide a set of first statistical parameters representative of I/O activity associated with each stripe set;

determining the busiest stripe set which is the stripe set with the highest I/O activity, as reflected by the statistical parameters;

determining if the selected stripe set includes more than one logical drive, and if not, recommending that more individual disk drives be used to store the selected stripeset, otherwise performing the following steps;

determining the logical disks on which the selected stripe set is stored;

selecting, from the stored data samples, a second subset that includes all second data samples that fall within the designated interval and that are associated with each logical disk that is part of the selected stripe set, so that the second subset of samples will include a plurality of samples for logical disks that compose the selected stripe set and which occurred in the designated interval;

grouping the second subset of selected data samples into a plurality of second groups so that each group includes data samples associated with a named logical disk;

for each of said second groups, mathematically combining the selected parameter in each of the data samples of the group to provide a set of second statistical parameters representative of I/O activity associated with each named logical disk;

determining second busiest logical disk which is the logical disk with the second highest I/O activity, as reflected by said first statistical parameters;

determining a least busy stripe set which is the stripe set with the lowest I/O activity, as reflected by the statistical parameters;

determining if there is enough room on the disk drives on which the least busy stripe set is stored to move the second busiest logical disk from the busiest stripe set to the least busy stripe set, and if so, recommending that such move be made.