Patent Application
20090248764
The present invention relates generally to the data processing field, and more particularly, relates to a method, apparatus and computer program product for implementing dynamic processor allocation based upon data density.
Performance of queries in a database management system is critical. In a world of ever expanding data, customers continue to grow their files at an increasing rate. As a result, performance will deteriorate unless something is done. Better mechanisms invented and implemented by an operating system to keep pace with customers' needs is therefore essential.
A database management system typically includes a query optimizer that attempts to optimize the performance of a query. The query optimizer selects from multiple access plans or possible implementations of a query to execute the query with the greatest efficiency.
The query optimizer determines how to run the query for best performance, such as allocation of system resources, determining which access plan to use, which index to use, and many implementation techniques intelligently taken within the optimizer without customer awareness. The optimizer therefore will decide which index is best for a given query, from a list of radix indexes and bitmap indexes that may be built over the file that is being queried. In some cases, the optimizer may even build an index on its own if the customer has not already built a good index over the file.
When the optimizer determines that using a bitmap index is the best way to process a query, a need exists for an improved mechanism to the bitmap art for optimizing database query processing with a faster way to provide query results using the bitmap data in the index in a database management system.
Principal aspects of the present invention are to provide a method, apparatus and computer program product for implementing dynamic processor allocation based upon bitmap data density. Other important aspects of the present invention are to provide such method, apparatus and computer program product for implementing dynamic processor allocation based upon bitmap data density substantially without negative effect and that overcome many of the disadvantages of prior art arrangements.
In brief, a method, apparatus and computer program product are provided for implementing dynamic processor allocation based upon bitmap data density. A bitmap index is used to process the query. A bitmap is created for the query. The bitmap is partitioned into single I/O operations or blocked operations, where there is a block of records retrieved in one I/O operation, and that each record is represented by one bit in the bitmap. Data density for each partition is calculated. Processors are assigned based upon data density of each partition. Then the partitions are processed and query results are returned.
In accordance with features of the invention, dynamically allocating processor resources for a Symmetric Multiprocessing (SMP) system is effectively and efficiently performed based on the data density returned from a result bitmap that is created from scanning a bitmap index, when performing a database query.
The present invention together with the above and other objects and advantages may best be understood from the following detailed description of the preferred embodiments of the invention illustrated in the drawings, wherein:
Having reference now to the drawings, in
Computer system 100 is, for example, a Symmetric Multiprocessing (SMP) system including a plurality of processors 101 coupled to a system bus. The multiple processors 101 are coupled to a user input 102 and a memory 104. Computer system 100 includes a database management system (DBMS) 106 and an operating system 108 stored in memory 104.
Computer system 100 includes a display 112 and a mass storage 114, such as a direct access storage device (DASD), storing a database 116, or alternatively the database 116 is stored on another computer coupled to the computer system 100 via a network 118, such as a client computer 120.
Referring also to
Other components may be incorporated into the database management system (DBMS) 106, which can include other suitable database management architectures. Other database programming and organizational architectures may also be used consistent with the invention. It should be understood that the present invention is not limited to the illustrated database management system (DBMS) 106.
Computer system 100 is shown in simplified form sufficient for an understanding of the present invention. It should be understood that the present invention is not limited for use with the illustrated computer system 100. The illustrated processor system 100 is not intended to imply architectural or functional limitations. The present invention can be used with various hardware implementations and systems and various other internal hardware devices.
Various commercially available processor systems could be used for computer system 100, for example, a selected server computer system manufactured and sold by International Business Machines Corporation.
Those skilled in the art will recognize that the exemplary environment illustrated in
In accordance with features of the preferred embodiment, a method of the invention provides a faster way to provide query results using the bitmap data in the index. The method efficiently dynamically allocates processor resources for Symmetric Multiprocessing (SMP), based upon the data density returned from a result bitmap that is created from scanning an index, when performing a database query.
Referring to
When bitmap processing is identified by the optimizer to process the query at decision block 304, then a bitmap is created or else an existing bitmap is used for the query including a structure for identifying data density as indicated at a block 308.
The bitmap is an array of bits where each bit represents a row in the table. The bits are used to determine which rows in a table contain values for the columns, which are being selected. The bitmap is generated by scanning an index to identify the rows which should be selected.
The bitmap is partitioned into single I/O operations as indicated at a block 310. Data density for each partition is calculated as indicated at a block 312. Processors are assigned based upon data density of each partition as indicated at a block 314. Then the partitions are processed as indicated at a block 316 and query results are returned as indicated at a block 318 to complete the query processing.
Consider the following example, and query
Example: (with index over column City in file Table):
Select Name from Table where City=‘Byron’
The system can use the bitmap for skip sequential processing to sequentially scan the file, skipping rows in the table that are not selected based on the bitmap created during processing of the query. Because the bitmap represents the physical sequence of the rows in the table bitmaps are advantageous for SMP because the bitmaps are easily partitioned. The partitions can then be easily assigned to a processor for retrieval of the result rows.
Current known query processing implementations typically breakup these partitions with a partition size whose bits represent the number of rows which can be retrieved in a single I/O operation.
In accordance with features of the invention, the processing method takes advantage of the previous support by dynamically allocating the processors based on the data density of the number of ON bits in a partition. For a given partition, when only 1 bit is on, the processor will have very little work to perform. However, if all bits are on in a partition, the processor will be very busy performing further selection and projection operations on the selected rows.
In accordance with features of the invention, instead of allocating these partitions to a fixed size, the size is dynamically tuned so that a partition that selects a high number of rows is instead broken up into smaller partitions so that multiple processors can be used to process more dense ranges of the data, which would then achieve a higher degree of parallelism. Likewise, for portions of the bitmap which are sparse, wider ranges of bits are assigned because most of the processing time will be I/O bound.
During the building of the bitmap, a value or structure can be used to determine how many bits are on in a particular section of the bitmap. Using this information along with the bitmap, the system will be able to determine what sections of a file will require more processor power to retrieve the rows, and allocate the current processor capabilities according to the data density of the file based on the bitmap feedback.
Consider the following Example:
When building the bitmap index, keeping track of the density of rows being selected is easily implemented and is taken into account when determining where to apply the available processor power to maximize system resources.
Following is a bitmap for a particular key of an index.
0000100000000001000010000011111111111010000010101010000000000000000000000
In accordance with features of the invention, the processing method includes a structure that is passed with the bitmap to determine data density, such as, for the above Example:
RRN 1-26 (3 bits ON=Low density)
RRN 27-39 (11 bits ON=High density)
RRN 40-52 (4 bits ON=Medium density)
RRN 53-74 (0 bits ON=No density)
Having this information when skip sequential table scanning the file, more processors are applied to the area of the file that requires more CPU, for the higher data density, to process the records. In this example, if we have 4 processors available, one processor is used to process RRNs 1-26, two processors for 27-39, one processor for 40-52 and 0 processors for 53-74; rather than breaking the file into equal partitions as done conventionally, and thereby providing enhanced performance with the method of the invention.
Referring now to
A sequence of program instructions or a logical assembly of one or more interrelated modules defined by the recorded program means 404, 406, 408, 410, direct the computer system 100 for dynamically allocating processors based upon data density for optimizing database query processing of the preferred embodiment.
While the present invention has been described with reference to the details of the embodiments of the invention shown in the drawing, these details are not intended to limit the scope of the invention as claimed in the appended claims.
