The present invention relates to storage management for a database management system, and more particularly to controlling buffer pool performance.
Latches and locks are used to control access to shared database information. A latch is a serialization mechanism used to guarantee physical consistency of shared data structures or resources (e.g., pages in a buffer pool). Page latching is an efficient way to ensure physical consistency of a page, while page locks ensure that data is committed. Page latches consist of S-latches (i.e., shared latches) and X-latches (exclusive latches). Using an S-latch avoids a need to obtain Internal Resource Lock Manager (IRLM) locks. Current shared information access techniques request S-latches in order to read pages. Using an X-latch serializes access with S-latches and other X-latches for an update process. Current shared information access techniques request X-latches in order to write pages.
In one embodiment, the present invention provides a computer-implemented method. The method includes based on statistics about read operations and write operations in a range of pages, determining, by one or more processors, that the range of pages is a candidate for being a hot read range for which page locks and page latches are avoided in processing operations in the hot read range. The method further includes creating, by the one or more processors and using an on-time trigger process or a pre-trigger process, the hot read range from the range of pages by marking a start point of the hot read range in the range of pages and an end point of the hot read range in the range of pages. The method further includes determining, by the one or more processors, that one or more write operations are included in the hot read range by marking an object control block. The method further includes based on the one or more write operations being included in the hot read range, adding, by the one or more processors, the one or more write operations to a write pending list. The method further includes performing, by the one or more processors, the read operations in the hot read range without using a latch or a lock for any page in the hot read page. The method further includes in response to the performing the read operations, merging, by the one or more processors, the one or more write operations from the write pending list to the range of the pages and performing the one or more write operations.
A computer program product and a computer system corresponding to the above-summarized method are also described and claimed herein.
Overview
Some database query workloads have most of their operations being read operations with very few write operations. For each read and write operation, a latch and a lock are requested to guarantee consistency for the data. Requesting latches and locks can hinder database performance.
Embodiments of the present invention address the aforementioned unique challenges of the traditional approaches to controlling access to shared information by improving buffer pool information in cases in which read operations significantly outnumber write operations, thereby accelerating buffer pool performance. In one embodiment, a buffer pool optimization system determines that a page is likely not to be changed by any write operations and therefore does not request a latch for the page during a read period (i.e., a period of time in which read operations are performed on the page). Further, the buffer pool optimization system applies any update or insert requirements in a period for handling pending write operations via a buffer pool. A buffer pool manager guarantees that the page is not updated during the read period. The buffer pool optimization system provides improved database performance and prevents data integrity issues.
In one embodiment, a buffer pool optimization system (1) finds candidate ranges of pages for lock and latch avoidance; (2) creates an action list that designates a range as a hot read range, marks start and end points of the hot read range, indicates a status of the hot read range, and maintains statistics about the hot read range; (3) marks up an object control block to determine whether write operations are within the hot read range; (4) performs the read operations in the hot read range while avoiding a usage of a read lock or a read latch and adds the write operations to a write pending list; (5) after the read operations are completed, merges write operations from the write pending list to the range of pages; and (6) processes new read operations after the write operations are performed.
System for Optimizing a Database Buffer Pool
Database buffer pool optimization system 104 identifies ranges of pages that are a candidates to be hot read ranges. In one embodiment, a given range of pages is a candidate for being a hot read range based on statistics about the number or read operations and the number of write operations in the given range of pages. Database buffer pool optimization system 104 creates action list 108 that designates a given range of pages as a hot read range by marking the start and end points of the range of pages, storing an indicator of a status of the range of pages, and storing statistics about the range of pages.
Database buffer pool optimization system 104 marks the object control block 110 to indicate whether a new write operation is within a given range of pages designated in the action list 108 as a hot read range. If the new write operation is within the given range of pages designated as a hot read range, then database buffer pool optimization system 104 adds the new write operation to write pending list 112.
Buffer pool manager vector table 114 stores (i) database objects associated with the candidate ranges of pages, (ii) entries in the ranges of pages designated as hot read ranges, and (iii) information about the hot read ranges (i.e., range start point, range end point, range status, and range statistics, including the number of read and write operations).
Buffer pool manager 106 can read from buffer pool 116 using, for example, sequential reference, block reference, random with hot blocks reference, and other scenarios with hot blocks.
The functionality of the components shown in
Process for Optimizing a Database Buffer Pool
In one embodiment, using the aforementioned statistics, database buffer pool optimization system 104 (see
In step 204, database buffer pool optimization system 104 (see
In one embodiment, a directory of hot read ranges searches each page in a given range of pages and database buffer pool optimization system 104 (see
In step 206, database buffer pool optimization system 104 (see
In step 208, in response to the write operation(s) being within the hot read range as indicated by the marking of the object control block 110 (see
In step 210, database buffer pool optimization system 104 (see
In step 212, in response to completing the performance of the read operations in the hot read range in step 210, database buffer pool optimization system 104 (see
Following step 212, the process of
In one embodiment, after the range of pages is designated as a hot read range, database buffer pool optimization system 104 (see
In one embodiment, database buffer pool optimization system 104 (see
Creating a Hot Read Range
The process of
In step 304, database buffer pool optimization system 104 (see
In step 306, database buffer pool optimization system 104 (see
In step 308, based on the ratio exceeding the defined threshold amount, database buffer pool optimization system 104 (see
Following step 308, the process of
The process of
In step 404, database buffer pool optimization system 104 (see
Prior to step 406, database buffer pool optimization system 104 (see
In step 408, database buffer pool optimization system 104 (see
In step 410, based on the determinations made in steps 402, 406, and 408, database buffer pool optimization system 104 (see
Following step 410, the process of
Removing a Hot Read Range
The method of
Prior to step 504, database buffer pool optimization system 104 (see
In step 504, database buffer pool optimization system 104 (see
In step 506, in response to the determination in step 504 that the ratio does not exceed the defined threshold amount, database buffer pool optimization system 104 (see
Following step 506, the process of
The process of
In step 604, in response to the determination in step 602 that the ratios are decreasing, database buffer pool optimization system 104 (see
Following step 604, the process of
Alternately, the process of
Mirror Page Generation
In one embodiment, database buffer pool optimization system 104 (see
(i) add old read operations to old pages in buffer pool 116 (see
(ii) add a write operation to a first page in buffer pool 116 (see
(iii) using the old read operations in the old pages and using the first page in the buffer pool 116 (see
(iv) in response to the reading in step (iii), place the first page in a free page list;
(v) apply the write operation in the first page to write second data to the database;
(vi) in response to applying the write operation in step (v), create a mirror page in the buffer pool 116 (see
(vii) in response to applying the write operation in step (v), add new read operations to new pages in the buffer pool 116 (see
(viii) using the new read operations in the new pages and using the mirror page in the buffer pool 116 (see
In block 704, a page 708 includes a write operation and the other pages (i.e., the pages that have the vertical line pattern) in block 704 include read operations and no write operations. In step 306 (see
Alternately, database buffer pool optimization system 104 (see
In block 804, each of pages 816, 818, 820, 822, and 824 includes a write operation and the other pages (i.e., the pages that have the vertical line pattern) in block 804 include read operations and no write operations. In step 502 (see
Alternately, in step 602 (see
Computer System
Memory 904 includes a known computer readable storage medium, which is described below. In one embodiment, cache memory elements of memory 904 provide temporary storage of at least some program code (e.g., program code 914) in order to reduce the number of times code must be retrieved from bulk storage while instructions of the program code are executed. Moreover, similar to CPU 902, memory 904 may reside at a single physical location, including one or more types of data storage, or be distributed across a plurality of physical systems or a plurality of computer readable storage media in various forms. Further, memory 904 can include data distributed across, for example, a local area network (LAN) or a wide area network (WAN).
I/O interface 906 includes any system for exchanging information to or from an external source. I/O devices 910 include any known type of external device, including a display, keyboard, etc. Bus 908 provides a communication link between each of the components in computer 102, and may include any type of transmission link, including electrical, optical, wireless, etc.
I/O interface 906 also allows computer 102 to store information (e.g., data or program instructions such as program code 914) on and retrieve the information from computer data storage unit 912 or another computer data storage unit (not shown). Computer data storage unit 912 includes one or more known computer readable storage media, where a computer readable storage medium is described below. In one embodiment, computer data storage unit 912 is a non-volatile data storage device, such as, for example, a solid-state drive (SSD), a network-attached storage (NAS) array, a storage area network (SAN) array, a magnetic disk drive (i.e., hard disk drive), or an optical disc drive (e.g., a CD-ROM drive which receives a CD-ROM disk or a DVD drive which receives a DVD disc).
Memory 904 and/or storage unit 912 may store computer program code 914 that includes instructions that are executed by CPU 902 via memory 904 to optimize a database buffer pool. Although
Further, memory 904 may include an operating system (not shown) and may include other systems not shown in
In one embodiment, computer data storage unit 912 includes a data repository (not shown in
As will be appreciated by one skilled in the art, in a first embodiment, the present invention may be a method; in a second embodiment, the present invention may be a system; and in a third embodiment, the present invention may be a computer program product.
Any of the components of an embodiment of the present invention can be deployed, managed, serviced, etc. by a service provider that offers to deploy or integrate computing infrastructure with respect to optimizing a database buffer pool. Thus, an embodiment of the present invention discloses a process for supporting computer infrastructure, where the process includes providing at least one support service for at least one of integrating, hosting, maintaining and deploying computer-readable code (e.g., program code 914) in a computer system (e.g., computer 102) including one or more processors (e.g., CPU 902), wherein the processor(s) carry out instructions contained in the code causing the computer system to optimize a database buffer pool. Another embodiment discloses a process for supporting computer infrastructure, where the process includes integrating computer-readable program code into a computer system including a processor. The step of integrating includes storing the program code in a computer-readable storage device of the computer system through use of the processor. The program code, upon being executed by the processor, implements a method of optimizing a database buffer pool.
While it is understood that program code 914 for optimizing a database buffer pool may be deployed by manually loading directly in client, server, and proxy computers (not shown) via loading a computer-readable storage medium (e.g., computer data storage unit 912), program code 914 may also be automatically or semi-automatically deployed into computer 102 by sending program code 914 to a central server or a group of central servers. Program code 914 is then downloaded into client computers (e.g., computer 102) that will execute program code 914. Alternatively, program code 914 is sent directly to the client computer via e-mail. Program code 914 is then either detached to a directory on the client computer or loaded into a directory on the client computer by a button on the e-mail that executes a program that detaches program code 914 into a directory. Another alternative is to send program code 914 directly to a directory on the client computer hard drive. In a case in which there are proxy servers, the process selects the proxy server code, determines on which computers to place the proxy servers' code, transmits the proxy server code, and then installs the proxy server code on the proxy computer. Program code 914 is transmitted to the proxy server and then it is stored on the proxy server.
Another embodiment of the invention provides a method that performs the process steps on a subscription, advertising and/or fee basis. That is, a service provider can offer to create, maintain, support, etc. a process of optimizing a database buffer pool. In this case, the service provider can create, maintain, support, etc. a computer infrastructure that performs the process steps for one or more customers. In return, the service provider can receive payment from the customer(s) under a subscription and/or fee agreement, and/or the service provider can receive payment from the sale of advertising content to one or more third parties.
The present invention may be a system, a method, and/or a computer program product at any possible technical detail level of integration. The computer program product may include a computer readable storage medium (or media) (i.e., memory 904 and computer data storage unit 912) having computer readable program instructions 914 thereon for causing a processor (e.g., CPU 902) to carry out aspects of the present invention.
The computer readable storage medium can be a tangible device that can retain and store instructions (e.g., program code 914) for use by an instruction execution device (e.g., computer 102). The computer readable storage medium may be, for example, but is not limited to, an electronic storage device, a magnetic storage device, an optical storage device, an electromagnetic storage device, a semiconductor storage device, or any suitable combination of the foregoing. A non-exhaustive list of more specific examples of the computer readable storage medium includes the following: a portable computer diskette, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or Flash memory), a static random access memory (SRAM), a portable compact disc read-only memory (CD-ROM), a digital versatile disk (DVD), a memory stick, a floppy disk, a mechanically encoded device such as punch-cards or raised structures in a groove having instructions recorded thereon, and any suitable combination of the foregoing. A computer readable storage medium, as used herein, is not to be construed as being transitory signals per se, such as radio waves or other freely propagating electromagnetic waves, electromagnetic waves propagating through a waveguide or other transmission media (e.g., light pulses passing through a fiber-optic cable), or electrical signals transmitted through a wire.
Computer readable program instructions (e.g., program code 914) described herein can be downloaded to respective computing/processing devices (e.g., computer 102) from a computer readable storage medium or to an external computer or external storage device (e.g., computer data storage unit 912) via a network (not shown), for example, the Internet, a local area network, a wide area network and/or a wireless network. The network may comprise copper transmission cables, optical transmission fibers, wireless transmission, routers, firewalls, switches, gateway computers and/or edge servers. A network adapter card (not shown) or network interface (not shown) in each computing/processing device receives computer readable program instructions from the network and forwards the computer readable program instructions for storage in a computer readable storage medium within the respective computing/processing device.
Computer readable program instructions (e.g., program code 914) for carrying out operations of the present invention may be assembler instructions, instruction-set-architecture (ISA) instructions, machine instructions, machine dependent instructions, microcode, firmware instructions, state-setting data, configuration data for integrated circuitry, or either source code or object code written in any combination of one or more programming languages, including an object oriented programming language such as Smalltalk, C++, or the like, and procedural programming languages, such as the “C” programming language or similar programming languages. The computer readable program instructions may execute entirely on the user's computer, partly on the user's computer, as a stand-alone software package, partly on the user's computer and partly on a remote computer or entirely on the remote computer or server. In the latter scenario, the remote computer may be connected to the user's computer through any type of network, including a local area network (LAN) or a wide area network (WAN), or the connection may be made to an external computer (for example, through the Internet using an Internet Service Provider). In some embodiments, electronic circuitry including, for example, programmable logic circuitry, field-programmable gate arrays (FPGA), or programmable logic arrays (PLA) may execute the computer readable program instructions by utilizing state information of the computer readable program instructions to personalize the electronic circuitry, in order to perform aspects of the present invention.
Aspects of the present invention are described herein with reference to flowchart illustrations (e.g.,
These computer readable program instructions may be provided to a processor (e.g., CPU 902) of a general purpose computer, special purpose computer, or other programmable data processing apparatus (e.g., computer 102) to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, create means for implementing the functions/acts specified in the flowchart and/or block diagram block or blocks. These computer readable program instructions may also be stored in a computer readable storage medium (e.g., computer data storage unit 912) that can direct a computer, a programmable data processing apparatus, and/or other devices to function in a particular manner, such that the computer readable storage medium having instructions stored therein comprises an article of manufacture including instructions which implement aspects of the function/act specified in the flowchart and/or block diagram block or blocks.
The computer readable program instructions (e.g., program code 914) may also be loaded onto a computer (e.g. computer 102), other programmable data processing apparatus, or other device to cause a series of operational steps to be performed on the computer, other programmable apparatus or other device to produce a computer implemented process, such that the instructions which execute on the computer, other programmable apparatus, or other device implement the functions/acts specified in the flowchart and/or block diagram block or blocks.
The flowchart and block diagrams in the Figures illustrate the architecture, functionality, and operation of possible implementations of systems, methods, and computer program products according to various embodiments of the present invention. In this regard, each block in the flowchart or block diagrams may represent a module, segment, or portion of instructions, which comprises one or more executable instructions for implementing the specified logical function(s). In some alternative implementations, the functions noted in the block may occur out of the order noted in the Figures. For example, two blocks shown in succession may, in fact, be accomplished as one step, executed concurrently, substantially concurrently, in a partially or wholly temporally overlapping manner, or the blocks may sometimes be executed in the reverse order, depending upon the functionality involved. It will also be noted that each block of the block diagrams and/or flowchart illustration, and combinations of blocks in the block diagrams and/or flowchart illustration, can be implemented by special purpose hardware-based systems that perform the specified functions or acts or carry out combinations of special purpose hardware and computer instructions.
While embodiments of the present invention have been described herein for purposes of illustration, many modifications and changes will become apparent to those skilled in the art. Accordingly, the appended claims are intended to encompass all such modifications and changes as fall within the true spirit and scope of this invention.
Number | Name | Date | Kind |
---|---|---|---|
7689607 | Oks | Mar 2010 | B2 |
9069484 | Zhang | Jun 2015 | B2 |
9336258 | Bhattacharjee | May 2016 | B2 |
9928264 | Lomet | Mar 2018 | B2 |
20050267891 | Shimizu | Dec 2005 | A1 |
20110219169 | Zhang | Sep 2011 | A1 |
20130024609 | Gorobets | Jan 2013 | A1 |
20140032851 | Lightstone | Jan 2014 | A1 |
20140279840 | Chan | Sep 2014 | A1 |
20150186051 | Gurajada | Jul 2015 | A1 |
20160275171 | Barber | Sep 2016 | A1 |
20170371912 | Kimura | Dec 2017 | A1 |
20180253468 | Gurajada | Sep 2018 | A1 |
20210263779 | Haghighat | Aug 2021 | A1 |
Number | Date | Country |
---|---|---|
101863206 | May 2018 | KR |
Entry |
---|
Kodandaramaih, Raghavendra Thallam et al.; Concurrent Updates to Pages with Fixed-Size Rows Using Lock-Free Algorithms; Proceedings of the Very Large Databases Endowment, vol. 13, No. 12; Sep. 3, 2020; 9 pages. |
Number | Date | Country | |
---|---|---|---|
20220253444 A1 | Aug 2022 | US |