Method and apparatus for identifying database I/O activity

A SQL database tool maps database objects to storage addresses. The SQL database translates SQL requests to I/O requests on specific extents of OS files. A data structure maps the OS file extents to database objects. Using this mapping, I/O requests on files can be correlated to their relevant database objects, providing analysis of I/O activity and I/O wait time for database objects. Based on the computed database object activity level, the database objects may be allocated to different storage tiers according to their I/O needs.

CROSS REFERENCE TO RELATED APPLICATIONS

FIELD

The field relates to identifying database object activity.

DESCRIPTION OF THE RELATED ART

A computer includes one or more central processing units and other hardware that together execute operations to allow the computer to process data. Storage arrays have been developed to store data for one or more computer systems. Storage arrays generally include multiple physical disks drives, and a storage system allocates storage capacity offered by the disk drives to enable external computer systems to store data and obtain access to data stored in the storage array.

In general, smaller faster disk drives are more expensive, while larger slower disk drives are less expensive. To take advantage of the cost savings associated with the slower disk drives, while still providing fast access to data, a tiered storage array may be created using storage devices of multiple types. Automated systems for moving data between storage tiers have been developed which will automatically plan and non-disruptively execute changes to the storage mappings to enable more heavily accessed data to be stored in higher performing storage tiers. An example system of this nature is disclosed in U.S. patent application Ser. No. 13/431,071, filed Mar. 27, 2012, entitled Method and Apparatus for Enabling Access to Tiered Shared Storage Using Dynamic Tier Partitioning, the content of which is hereby incorporated herein by reference.

Tiered storage systems operate at the physical level to optimize storage of data by allocating more frequently used data to higher performing storage tiers. There are instances, however, where it may be preferable from a business standpoint to organize storage of data based on logical data object basis, to enable performance of the database system to more closely align with business objectives. For example, a database operator may prefer to specify access times to the data objects to enable the database performance to meet particular performance expectations.

Unfortunately, some database servers are not able to provide information about how long an application is required to wait for particular database objects to be accessed. This complicates making tier placement recommendations to the tiered storage system to enable the database to achieve a specified set of business objectives. Moreover, the tiered storage system does not have visibility as to the type of data being stored in the system, and rather is configured to allocate physical storage space based on the usage patterns of the physical extents. Thus, aligning database objects with physical storage in the tiered storage system also is not straightforward using the underlying tiered storage system directly, since the tiered storage system operates to allocate physical extents based on usage patterns without regard to the content of the data. Accordingly, it would be advantageous to provide a method and apparatus for identifying database object activity to enable database objects to be allocated to storage tiers in a tiered storage system.

SUMMARY

The following Summary and the Abstract set forth at the end of this application are provided herein to introduce some concepts discussed in the Detailed Description below. The Summary and Abstract sections are not comprehensive and are not intended to delineate the scope of protectable subject matter which is set forth by the claims presented below.

A SQL database tool maps database objects to storage addresses. The SQL database translates SQL requests to I/O requests on specific extents of OS files. A data structure maps the OS file extents to database objects. Using this mapping, I/O requests on files can be correlated to their relevant database objects, providing analysis of I/O activity and I/O wait time for database objects. Based on the computed database object activity level, the database objects may be allocated to different storage tiers according to their I/O needs.

DETAILED DESCRIPTION

The following detailed description sets forth numerous specific details to provide a thorough understanding of one or more embodiments of the invention. However, those skilled in the art will appreciate that the invention may be practiced without these specific details. In other instances, well-known methods, procedures, components, protocols, algorithms, and circuits have not been described in detail so as not to obscure the invention.

FIG. 1shows an embodiment of a system that is designed to enable the activity levels of database objects to be determined. As shown inFIG. 1, a host10configured to operate as a Structured Query Language (SQL) server is allocated storage by tiered storage system12. Storage space offered by the tiered storage system12appears to host10as device14. Operating system24on host10performs read operations and write operations to device14as it would to any conventional storage volume such as a disk drive or storage array.

A SQL database16on host10includes database objects18. The database objects are stored in storage device14. As data is written to the device, the information about the location of the objects18on the device is written to a data dictionary20. The information in the data dictionary thus includes information about the physical location where the objects are stored in the device14and maps database objects18of the SQL database16to regions22offered by device14. Information within the data dictionary20is used by the database to fetch data requested via SQL by sending I/O requests to host10specifying to the Operating System (OS)24the exact file and block needed to be read from the device. This information is available using the SQL “dbcc extent info” command and is part of the normal set of tools available on a SQL server implementing a SQL database.

According to an embodiment, a Database Object Mapping Controller26is provided to correlate file/block extent usage with database objects, to enable activity levels of logical storage volumes such as database objects to be determined and used to control placement of information within the tiered storage system. In one embodiment, the database object mapping controller26obtains information about where database objects18are stored on device14by using “dbcc extent info”, and creates a data structure correlating database objects18with blocks22. This mapping is performed periodically, for example once per day.

According to an embodiment, the physical location (file/block) of all objects in the database is read from the data dictionary20. This information is passed to the database object mapping controller26. The database object mapping controller26creates a data structure such as a table mapping the device file/block and SQL objects. For example a table containing a list of file/block identifiers and database object associations may be created. Many data structures may be implemented to enable the device file/block identifiers of device14to be correlated with database objects18.

During actual I/O activity, each I/O request to the operating system24is analyzed and translated from the file/block to the database objects, keeping all of the session information. The I/O activity may be obtained by the database object mapping controller using an OS hook or other OS activity monitoring system. File extent usage monitoring component30obtains the I/O request information from the operating system24and uses the data structure28to correlate I/O requests with database object requests. Further, the file extent usage monitoring component30may also receive I/O fulfillment information from the operating system24when the device14fulfills the request to enable the responsiveness of the device14to be determined as well. This allows the wait time for database objects to be computed along with the database object activity level.

Since high I/O activity may require identifying a large number of objects per second, the data structure that is used to enable database objects to be identified is designed to enable a binary search to be implemented by file and extent (which are provided by the database). This enables the system to support large numbers of object seeks per second, and hence identify and store statistical I/O information at the database object level.

Information about database object usage is stored in object usage data structure32, which may be separate from data structure28or interrelated with data structure28. The object usage data structure enables information about session and database objects to be stored for further analysis. Using this information, it is possible to provide recommendations on placing of the database objects on the appropriate storage tier to maximize performance according to business priority rather than allocating storage based only on physical activity level.

FIG. 2shows a flow chart of an example process according to an embodiment. As shown inFIG. 2, a data structure is created, or updated, periodically based on information available in the data dictionary20. The data structure includes a list of file/block numbers where database objects are stored within the device and an identifier of the database objects stored at each file/block (200).

As database objects are accessed, the file/block I/O activity information is obtained from the Operating System (202). The identity of the file/block experiencing activity is used to key into the data structure to identify database objects stored at the active extents (204). Since the physical activity is able to be identified and correlated back to logical database objects, the data structure provides activity level information of the database objects. As physical activity occurs, a database object I/O activity record is updated (206). Over time, this activity level of the database objects may be collected and then used to cause the more active objects to be identified (208). Likewise, the wait time for completion of database object access operations may be computed to identify objects experiencing high wait times between request and fulfillment. Based on the activity levels of the database objects, wait time, and business priorities to be achieved in connection with operation of the database, recommendations for placement of objects in storage tiers of a tiered storage system may be provided (210).

The methods described herein may be implemented as software configured to be executed in control logic such as contained in a CPU of an electronic device such as a computer. In particular, the functions described herein may be implemented as sets of program instructions implemented utilizing programming techniques known to those of ordinary skill in the art that are stored in a computer readable memory within the computer or loaded onto the computer and executed on computer's microprocessor. However, it will be apparent to a skilled artisan that all logic described herein can be embodied using discrete components, integrated circuitry, programmable logic used in conjunction with a programmable logic device such as a Field Programmable Gate Array (FPGA) or microprocessor, or any other device including any combination thereof. Programmable logic can be fixed temporarily or permanently in a tangible computer readable medium such as random access memory, a computer memory, a disk, or other storage medium. All such embodiments are intended to fall within the scope of the present invention.