Dynamic database memory management policies

• US 7,840,752 B2
• Filed: 10/30/2006
• Issued: 11/23/2010
• Est. Priority Date: 10/30/2006
• Status: Active Grant

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Kalavakur, Sethu M., Hu, Norbert, Voellm, Anthony F.
• Primary Examiner(s): Krofcheck; Michael C
• Application Number:
  US11/589,664
• Publication Number:
  US 20080104319A1
• Time in Patent Office: 1,485 Days
• Field of Search: 711/113, 711/170, 707/1
• US Class Current: 711/113
• CPC Class Codes: G06F 12/023 Free address space management
• CPC Class Codes: G06F 12/0866 for peripheral storage systems, e.g. disk cache

Abstract

A database engine is provided with memory management policies to dynamically configure an area of memory called a buffer pool into which data pages are held during processing. The data pages are also buffered as an I/O (input/output) stream when read and written to a persistent storage medium, such as a hard disk, through use of a system file cache that is managed by the computer'"'"'s operating system. The memory management policies implement capping the amount of memory used within the buffer pool to minimize the number of data pages that are double-buffered (i.e., held in both the buffer pool and system file cache). In addition, trimming data pages from the buffer pool, after the database engine completes all pending operations and requests, frees additional memory and further minimizes the number of processes associated with the database.

Claims

1. A method for managing a database stored on a persistent storage medium, the method comprising the steps of:

enabling, at a computing device running a database application and one or more other applications, both a kernel mode system file cache of an operating system of the computing device and a user mode buffer pool of a database engine of the database application buffering data pages retrieved by the database engine from the persistent storage medium of the computing device, the user mode buffer pool being implemented using a memory resource in user mode that is also accessible by the kernel mode system file cache of the operating system, the kernel mode system file cache being implemented using a memory resource in kernel mode that is also accessible from user mode by the database application and the one or more other applications running on the computing device, wherein enabling the kernel mode system file cache and the user mode buffer pool for buffering comprises;

setting, by the database engine, a maximum data page target for the user mode buffer pool to impose an upper limit on the number of data pages that are buffered in the user mode buffer pool such that a memory size of the user mode buffer pool is smaller relative to a memory size of the kernel mode system file cache in order to minimize duplicative buffering of data pages by the user mode buffer pool and the kernel mode system file cache;

enabling, from the user mode buffer pool, a direct flush write path to the persistent storage medium;

providing, from the user mode buffer pool, a system file cache write path buffering data pages from the user mode buffer pool to the kernel mode system file cache; and

providing, from the kernel mode system file cache, a cached input/output (I/O) stream to the persistent storage medium enabling the kernel mode system file cache to directly read data pages from and write data pages to the persistent storage medium;

retrieving, by the database engine, data pages stored on the persistent storage medium of the computing device;

buffering, by the database engine, the data pages retrieved from the persistent storage medium of the computing device into the user mode buffer pool;

writing, by the database engine, the data pages from user mode buffer pool to the persistent storage medium via the direct flush write path as a disk I/O stream to the persistent storage medium;

buffering, by the operating system, the data pages of the disk I/O stream into the kernel mode system file cache;

trimming, by the database engine, data pages from the user mode buffer pool to maximize free memory in the memory resource in the user mode in response to detection of the database engine entering an idle state;

determining, by the database engine, whether a required data page that has been trimmed from the user mode buffer pool is held in the kernel mode system file cache; and

retrieving, by the database engine, the required data page from the kernel mode system file cache when the required data page is held in the kernel mode system file cache.

2. The method of claim 1, wherein the direct flush write path is enabled in the operating system by setting a write through flag so that dirty data pages are flushed directly to the persistent storage medium in addition to being buffered in the kernel mode system file cache.

3. The method of claim 1, wherein the memory size of the user mode buffer pool size limit is approximately one-sixteenth of available physical memory of the computing device.

4. The method of claim 1, wherein the memory size of the user mode buffer pool is approximately one-half of a user-selectable value for a maximum size of the user mode buffer pool.

5. The method of claim 1, wherein the database engine comprises idle detection logic for determining whether any operations or requests are pending.

6. The method of claim 1, wherein the persistent storage medium is a hard disk of the computing device.

7. The method of claim 1, wherein log files associated with the data pages are excluded from buffering.

8. The method of claim 1, wherein the database engine retrieves the required data page from the persistent storage medium when the required data page is not held in the kernel mode system file cache.

9. The method of claim 1, wherein trimming the data pages from the user mode buffer pool is delayed by a pre-determined time interval after detection of the database engine entering the idle state.

10. The method of claim 1, wherein the database engine comprises a Structured Query Language database engine.

11. A computer-readable storage medium containing instructions which, when executed by one or more processors of a computing device, causes the computing device to perform a method for managing a database stored on a persistent storage medium, the method comprising the steps of:

enabling, at a computing device running a database application and one or more other applications, both a kernel mode system file cache of an operating system of the computing device and a user mode buffer pool of a database engine of the database application buffering data pages retrieved by the database engine from the persistent storage medium of the computing device, the user mode buffer pool implemented using a memory resource in user mode that is also accessible by the kernel mode system file cache of the operating system, the kernel mode system file cache being implemented using a memory resource in kernel mode that is also accessible from user mode by the database application and the one or more other applications running on the computing device, wherein enabling the kernel mode system file cache and the user mode buffer pool for buffering comprises;

setting, by the database engine, a maximum data page target for the user mode buffer pool to impose an upper limit on the number of data pages that are buffered in the user mode buffer pool such that a memory size of the user mode buffer pool is smaller relative to a memory size of the kernel mode system file cache in order to minimize duplicative buffering of data pages by the user mode buffer pool and the kernel mode system file cache;

enabling, from the user mode buffer pool, a direct flush write path from the buffer pool to the persistent storage medium;

providing, from the user mode buffer pool, a system file cache write path buffering data pages from the user mode buffer pool to the kernel mode system file cache; and

providing, from the kernel mode system file cache, a cached input/output (I/O) stream to the persistent storage medium enabling the kernel mode system file cache to directly read data pages from and write data pages to the persistent storage medium;

retrieving, by the database engine, data pages stored on the persistent storage medium of the computing device;

buffering, by the database engine, the data pages retrieved from the persistent storage medium of the computing device into the user mode buffer pool;

writing, by the database engine, the data pages from user mode buffer pool to the persistent storage medium via the direct flush write path as a disk I/O stream to the persistent storage medium;

buffering, by the operating system, the data pages of the disk I/O stream into the kernel mode system file cache;

trimming, by the database engine, data pages from the user mode buffer pool to maximize free memory in the memory resource in the user mode in response to detection of the database engine entering an idle state;

determining, by the database engine, whether a required data page that has been trimmed from the user mode buffer pool is held in the kernel mode system file cache; and

retrieving, by the database engine, the required data page from the kernel mode system file cache when the required data page is held in the kernel mode system file cache.

12. The computer-readable storage medium of claim 11, wherein enabling the kernel mode system file cache for buffering further comprises not setting a buffer bypass flag to a file operation application programming interface (API) function.

13. The computer-readable storage medium of claim 11, wherein trimming the data pages from the user mode buffer pool is delayed by a pre-determined time interval after detection of the database engine entering the idle state.

14. The computer-readable storage medium of claim 11, wherein the memory size of the user mode buffer pool is one of:

approximately one-sixteenth of available physical memory of the computing device or approximately one-half of a user-selectable value for a maximum size of the user mode buffer pool.

15. The computer-readable storage medium of claim 11, wherein the idle state comprises a state that is entered upon completion of all outstanding operations, the outstanding operations including a query.

16. The computer-readable storage medium of claim 11, wherein the database engine retrieves the required data page from the persistent storage medium when the required data page is not held in the kernel mode system file cache.

17. The computer-readable storage medium of claim 11, wherein the database engine comprises a Structured Query Language database engine.

18. A computing device comprising:

a persistent storage medium storing a database;

memory storing computer executable instructions;

one or more processors executing said computer executable instructions running a database application and one or more other applications and causing the computing device to perform method for managing the database stored on the persistent storage medium, the method comprising the steps of;

enabling, at the computing device, both a kernel mode system file cache of an operating system of the computing device and a user mode buffer pool of a database engine of the database application buffering data pages retrieved by the database engine from the persistent storage medium of the computing device, the user mode buffer pool being implemented using a memory resource in user mode that is also accessible by the kernel mode system file cache of the operating system, the kernel mode system file cache being implemented using a memory resource in kernel mode that is also accessible from user mode by the database application and the one or more other applications running on the computing device, wherein enabling the kernel mode system file cache and the user mode buffer pool for buffering comprises;

setting, by the database engine, a maximum data page target for the user mode buffer pool to impose an upper limit on the number of data pages that are buffered in the user mode buffer pool such that a memory size of the user mode buffer pool is smaller relative to a memory size of the kernel mode system file cache in order to minimize duplicative buffering of data pages by the user mode buffer pool and the kernel mode system file cache;

enabling, from the user mode buffer pool, a direct flush write path to the persistent storage medium;

providing, from the user mode buffer pool, a system file cache write path buffering data pages from the user mode buffer pool to the kernel mode system file cache; and

providing, from the kernel mode system file cache, a cached input/output (I/O) stream to the persistent storage medium enabling the kernel mode system file cache to directly read data pages from and write data pages to the persistent storage medium;

retrieving, by the database engine, data pages stored on the persistent storage medium of the computing device;

buffering, by the database engine, the data pages retrieved from the persistent storage medium of the computing device into the user mode buffer pool;

writing, by the database engine, the data pages from user mode buffer pool to the persistent storage medium via the direct flush write path as a disk I/O stream to the persistent storage medium;

buffering, by the operating system, the data pages of the disk I/O stream into the kernel mode system file cache;

trimming, by the database engine, data pages from the user mode buffer pool to maximize free memory in the memory resource in the user mode in response to detection of the database engine entering an idle state;

determining, by the database engine, whether a required data page that has been trimmed from the user mode buffer pool is held in the kernel mode system file cache; and

retrieving, by the database engine, the required data page from the kernel mode system file cache when the required data page is held in the kernel mode system file cache.

19. The computing device of claim 18, wherein the database engine retrieves the required data page from the persistent storage medium when the required data page is not held in the kernel mode system file cache.

20. The computing device of claim 18, wherein trimming the data pages from the user mode buffer pool is delayed by a pre-determined time interval after detection of the database engine entering the idle state.