In database management systems, decision support queries involve scanning large amounts of data. This data is typically stored in structures referred to as indexes, e.g., B-trees, and/or B+ trees. Typically when an index is created, the I/O (input/output) performance of queries that scan the index is good. However, as data is inserted, updated and deleted over time, an index can get fragmented.
One type of fragmentation is internal fragmentation, which occurs when leaf pages of an index are only partially filled, thus increasing the number of pages that need to be scanned to locate the queried data. Another type is external fragmentation, which occurs when the logical order of leaf pages in the index tree differs from the physical order of the pages, thereby increasing the number of disk seeks needed to locate the queried data.
In general, the I/O performance of queries depends significantly on fragmentation in the index, e.g., queries that scan an index may suffer significant degradation of I/O performance as a result of index fragmentation. Thus, defragmentation may be needed to help system performance.
The task of determining if an index needs to be defragmented is challenging for database administrators because contemporary database engines offer no support for quantifying the impact of defragmenting an index on query I/O performance. Further, database management systems only support defragmentation at the granularity of an entire index. This can be very restrictive, because defragmentation is an expensive operation.
This Summary is provided to introduce a selection of representative concepts in a simplified form that are further described below in the Detailed Description. This Summary is not intended to identify key features or essential features of the claimed subject matter, nor is it intended to be used in any way that would limit the scope of the claimed subject matter.
Briefly, various aspects of the subject matter described herein are directed towards a technology by which an online range defragmenter defragments one or more ranges of an index. A range tracker tracks and uses statistics corresponding to actual I/O operations to determine whether a benefit of defragmenting a range of an index sufficiently exceeds a cost of defragmenting the range. If so, the online range defragmenter automatically defragments the range in an online manner, that is, while allowing concurrent queries and updates to other ranges to proceed. In one aspect, the range tracker is configurable to monitor less than all ranges of the index.
In one aspect, defragmentation policy criteria may be used to determine whether the benefit sufficiently exceeds the cost. The defragmentation policy criteria may further include data (such as a maintenance window for deferring the defragmentation operation) that may be used in determining when to trigger a defragmentation operation on the range.
In one aspect, described is tracking statistics including actual I/O operations corresponding to index page nodes at an index level that references leaf node pages of the index. The statistics are used to determine a range of the index to defragment based upon benefit data corresponding to the actual I/O operations. Defragmenting of the range may be performed in an online operation that allows other ranges to be accessed with concurrent queries and updates.
Other advantages may become apparent from the following detailed description when taken in conjunction with the drawings.
The present invention is illustrated by way of example and not limited in the accompanying figures in which like reference numerals indicate similar elements and in which:
Various aspects of the technology described herein are generally directed towards a workload driven and online index defragmentation functionality in a database system. In one aspect, the technology tracks the potential benefit of defragmenting an index on I/O performance at low overhead. Further, the technology provides the ability to defragment a range of a database index online, that is, a selected part of the index without locking the entire index. Still further, the technology deals with a cost/benefit tradeoff, as to how cost/benefit may be controlled in a policy driven manner, thereby enabling automatic workload driven index defragmentation resulting in reduced database administrator intervention.
More particularly, in one aspect, logical ranges of an index are identified, and the benefit and cost of defragmentation of each range are tracked at the granularity of each logical range. These benefits and costs are computed for the workload that executes on the system. Index defragmentation of a logical range is performed in an online manner, that is, with relatively minimal locking. A policy may be used to determine if and when online index defragmentation is to be triggered and for which logical ranges, e.g., when a determined benefit sufficiently exceeds a cost value.
It should be understood that any of the examples herein are non-limiting. As such, the present invention is not limited to any particular embodiments, aspects, concepts, structures, functionalities or examples described herein. Rather, any of the embodiments, aspects, concepts, structures, functionalities or examples described herein are non-limiting, and the present invention may be used various ways that provide benefits and advantages in computing and database technology in general.
In one implementation, monitoring is performed with low overhead by piggybacking on execution of queries in the system. More particularly, the range tracker 102 gathers statistics 220 corresponding to actual I/Os based upon data tracked at the L1 level as represented in
The range tracker 102 subscribes for notifications of page splits, so that in the event an index page is split, the statistics may be adjusted. For example, the benefit data may be cleared and recomputed on the next scan, if any, that hits the appropriate page or pages.
In one aspect represented in
In one aspect, a defragmentation policy 112 is provided for automatically deciding whether, and if so when, an index (or range of an index) is to be defragmented. The defragmentation policy 112, as processed by a policy processing mechanism 114, takes into account the benefit of defragmentation as well as the cost. Note that in
In general, the defragmentation policy 112/policy processing mechanism 114 looks for “sufficient evidence” based on the workload W before triggering defragmentation of the index 104. The defragmentation policy 112 may be configured by a database administrator in different ways, e.g., to establish how aggressive or conservative the system is to be, whether defragmentation is to be deferred to a maintenance window (e.g., at night after normal working hours), and so forth.
Turning to additional details, unlike rebuilding an entire index, online defragmentation of ranges fills unused holes in disk space or appends defragmented pages to disk space following the index. In a first, compaction phase, internal fragmentation is removed by moving rows across pages. For example, in
More particularly, as represented in
An Index range refers to those leaf node pages corresponding to an L1 page at which the statistics are kept. The online range defragmenter 110 is able to defragment a single index range or a set of index ranges. The cost of defragmenting a range R may be modeled using the following formula:
DefragCost(R)=k1NR+k2NR(1−CR(R))+k3CR(R)NREF(R)
where the first two terms in the above formula represent the cost of removing internal fragmentation. This involves piggybacking on the scan of that range to detect the amount of internal and external fragmentation. The defragmentation cost thus depends on the compaction ratio CR(R) which depends on internal fragmentation and the degree of external fragmentation EF(I). The constants k1, k2, and k3 may be set by calibrating the cost model for a given system such as Microsoft® SQL Server®).
In one implementation, statistics are kept by tracking scans at the tree's L1 level corresponding to the indexes into the leaves, e.g., including the number of times each index is accessed during a scan. This avoids the need to track actual scanned ranges, which is very complex and computationally expensive.
Note that fragmentation may not be uniform across an entire index, as it is common to have updates that are skewed towards certain ranges of the indexes compared to other ranges. Thus, the fragmentation in the index can also be skewed. In such cases, defragmenting only the range (or ranges) with large fragmentation may be adequate. Moreover, the workload may be skewed, e.g., if most queries in the workload access a certain range of an index, then fragmenting that range may be sufficient.
One online workload driven approach to online index defragmentation is based upon the ability to quantify the impact of defragmenting the index on the I/O cost of a query. Because defragmenting an index is an expensive operation, this needs to be done without having to actually defragment the index and execute the query. Thus, described herein is a “what-if” analysis, which for example may be implemented for access via an API in a database management system. In the event that the full index is defragmented, the reduction in the number of I/Os for a range scan query Q if index I is fully defragmented as Benefit(Q, I); for a set of ranges R, the benefit is denoted by Benefit(Q, I, R).
When the range R is defragmented, the benefit of defragmenting the range for a scan query Q is the reduction in the number of I/Os for Q if the range is defragmented, i.e. Benefit(Q, R)=NumIOs(Q,R)−NumIOsPostDefrag(Q, R), where NumIOs(Q,R) is the number of I/Os required to execute the range scan Q, and NumIOsPostDefrag(Q,R) is the number of I/Os over the defragmented range. To compute each of the terms efficiently, i.e. without actually defragmenting the range or executing Q, an estimate of the number of I/Os for a range scan query is computed.
Due to the semantics of the defragmentation operation, once a range is defragmented, the range has no internal or external fragmentation. Thus, estimating NumIOs(Q, Defrag(R)) needs to estimate the number of pages in the range after defragmentation. For this purpose, when the data pages in any range are scanned, the fullness of the data pages may be used for the estimate.
Step 506 represents determining the benefit for a range, e.g., the number of actual I/Os needed versus the hypothetical computed number of how many I/Os needed had the range been defragmented. Step 508 accesses the policy criteria to determine whether to defragment the range, e.g., based in part on whether the benefit sufficiently exceeds the cost. Note that the policy may specify that the defragmentation of a range is to be deferred, e.g., only defragment a range after normal working hours, and so forth. The cost data may be computed as described above. Also note that step 508 may be bypassed, such as if the benefit is zero, which occurs when a range is already defragmented/contiguous.
Step 510 evaluates whether the benefit sufficiently exceeds the cost as determined via the policy data. If so, step 512 is executed to defragment the range. Step 514 represents waiting for the next scan; note that many scans may be received in parallel, and thus any of the steps of
The invention is operational with numerous other general purpose or special purpose computing system environments or configurations. Examples of well known computing systems, environments, and/or configurations that may be suitable for use with the invention include, but are not limited to: personal computers, server computers, hand-held or laptop devices, tablet devices, multiprocessor systems, microprocessor-based systems, set top boxes, programmable consumer electronics, network PCs, minicomputers, mainframe computers, distributed computing environments that include any of the above systems or devices, and the like.
The invention may be described in the general context of computer-executable instructions, such as program modules, being executed by a computer. Generally, program modules include routines, programs, objects, components, data structures, and so forth, which perform particular tasks or implement particular abstract data types. The invention may also be practiced in distributed computing environments where tasks are performed by remote processing devices that are linked through a communications network. In a distributed computing environment, program modules may be located in local and/or remote computer storage media including memory storage devices.
With reference to
The computer 610 typically includes a variety of computer-readable media. Computer-readable media can be any available media that can be accessed by the computer 610 and includes both volatile and nonvolatile media, and removable and non-removable media. By way of example, and not limitation, computer-readable media may comprise computer storage media and communication media. Computer storage media includes volatile and nonvolatile, removable and non-removable media implemented in any method or technology for storage of information such as computer-readable instructions, data structures, program modules or other data. Computer storage media includes, but is not limited to, RAM, ROM, EEPROM, flash memory or other memory technology, CD-ROM, digital versatile disks (DVD) or other optical disk storage, magnetic cassettes, magnetic tape, magnetic disk storage or other magnetic storage devices, or any other medium which can be used to store the desired information and which can accessed by the computer 610. Communication media typically embodies computer-readable instructions, data structures, program modules or other data in a modulated data signal such as a carrier wave or other transport mechanism and includes any information delivery media. The term “modulated data signal” means a signal that has one or more of its characteristics set or changed in such a manner as to encode information in the signal. By way of example, and not limitation, communication media includes wired media such as a wired network or direct-wired connection, and wireless media such as acoustic, RF, infrared and other wireless media. Combinations of the any of the above may also be included within the scope of computer-readable media.
The system memory 630 includes computer storage media in the form of volatile and/or nonvolatile memory such as read only memory (ROM) 631 and random access memory (RAM) 632. A basic input/output system 633 (BIOS), containing the basic routines that help to transfer information between elements within computer 610, such as during start-up, is typically stored in ROM 631. RAM 632 typically contains data and/or program modules that are immediately accessible to and/or presently being operated on by processing unit 620. By way of example, and not limitation,
The computer 610 may also include other removable/non-removable, volatile/nonvolatile computer storage media. By way of example only,
The drives and their associated computer storage media, described above and illustrated in
The computer 610 may operate in a networked environment using logical connections to one or more remote computers, such as a remote computer 680. The remote computer 680 may be a personal computer, a server, a router, a network PC, a peer device or other common network node, and typically includes many or all of the elements described above relative to the computer 610, although only a memory storage device 681 has been illustrated in
When used in a LAN networking environment, the computer 610 is connected to the LAN 671 through a network interface or adapter 670. When used in a WAN networking environment, the computer 610 typically includes a modem 672 or other means for establishing communications over the WAN 673, such as the Internet. The modem 672, which may be internal or external, may be connected to the system bus 621 via the user input interface 660 or other appropriate mechanism. A wireless networking component 674 such as comprising an interface and antenna may be coupled through a suitable device such as an access point or peer computer to a WAN or LAN. In a networked environment, program modules depicted relative to the computer 610, or portions thereof, may be stored in the remote memory storage device. By way of example, and not limitation,
An auxiliary subsystem 699 (e.g., for auxiliary display of content) may be connected via the user interface 660 to allow data such as program content, system status and event notifications to be provided to the user, even if the main portions of the computer system are in a low power state. The auxiliary subsystem 699 may be connected to the modem 672 and/or network interface 670 to allow communication between these systems while the main processing unit 620 is in a low power state.
While the invention is susceptible to various modifications and alternative constructions, certain illustrated embodiments thereof are shown in the drawings and have been described above in detail. It should be understood, however, that there is no intention to limit the invention to the specific forms disclosed, but on the contrary, the intention is to cover all modifications, alternative constructions, and equivalents falling within the spirit and scope of the invention.