Memory management system employing multiple buffer caches

• US 6,061,763 A
• Filed: 09/19/1997
• Issued: 05/09/2000
• Est. Priority Date: 07/12/1994
• Status: Expired due to Term

• Current Assignee: Sybase Incorporated (SAP SE)
• Original Assignee: Sybase Incorporated (SAP SE)
• Inventors: Marshall, David A., Mihalyi, Robert E., Rubin, David S.
• Primary Examiner(s): Chan, Eddie P.
• Assistant Examiner(s): Ellis, Kevin L.
• Application Number:
  US08/934,351
• Time in Patent Office: 963 Days
• Field of Search: 711/118, 711/119, 711/129, 711/133, 711/601, 711/602, 707/1, 707/2
• US Class Current: 711/129
• CPC Class Codes: G06F 12/023 Free address space management
• CPC Class Codes: G06F 12/0866 for peripheral storage systems, e.g. disk cache
• CPC Class Codes: G06F 12/0875 with dedicated cache, e.g. instruction or stack
• CPC Class Codes: G06F 16/24552 Database cache management
• CPC Class Codes: G06F 2212/311 In host system

Abstract

Computer systems and computer implemented methods are provided for managing memory in a database management system. The computer memory is partitioned into a plurality of buffer caches, each of which is separately addressable. One buffer cache is set aside as a default buffer cache, while the other buffer caches are reserved for specific data objects meeting certain predefined criteria. Those objects meeting the predefined criteria are stored in reserved buffer caches where they are likely to remain for a relatively long period of time (in comparison to data objects stored in the default buffer caches). A buffer cache may have a plurality of memory pools, each of which contains multiple storage blocks. The storage blocks in a given memory pool are identically sized, while the storage blocks in one memory pool are sized differently from the storage blocks in another memory pool.

Claims

1. A method of buffering data objects in a database of a computer system, the computer system having one or more storage devices on which the data objects of the database are stored, the computer system also having a memory on which the data objects may be stored temporarily and accessed relatively faster than from the one or more storage devices, the method comprising:

allocating at least two portions of the memory as buffer caches, each buffer cache comprising a named buffer cache capable of being bound to a particular data object;

in response to user commands, binding a particular one of said buffer caches to a specified data object, so that the particular buffer cache is reserved for caching only the specified data object;

receiving a database guery specifying operations which require access to the specified data object;

during execution of operations of the query, providing access to the specified data object by caching at least a portion of the specified data object in said particular buffer cache, wherein portions of any other data objects cached by the system are cached in portions of the memory other than said particular buffer cache, including checking a database catalog to determine whether the specified data object is bound to a particular named buffer cache; and

storing a data object to be accessed in an unreserved default buffer cache said checking the database catalog shows that the data object to be accessed is not bound to any named buffer cache.

2. The method of claim 1, wherein providing access to the particular one of the specified data objects by caching at least a portion of the particular one of the specified data objects in said particular buffer cache bound to that particular data object includes:

selecting a memory pool from a plurality of memory pools within said particular buffer cache in which to store the particular data object, the selected memory pool having multiple storage blocks each having identical storage capacities, and wherein each memory pool has storage blocks having storage capacities which differs from that of other memory pools.

3. The method of claim 1, wherein said step of binding a particular one of said buffer caches to a particular one of the specified data objects is performed in response to execution of a stored procedure for binding data objects to named buffer caches.

4. The method of claim 1, further comprising:

partitioning at least one of the buffer caches into a plurality of separate and identically configured sub-buffer caches, each such sub-buffer cache for caching a separate data object and being separately addressable so that only one sub-buffer cache is locked when the separate data object from the partitioned buffer cache is accessed.

5. The method of claim 1, further comprising:

queuing portions of the particular one of the specified data objects in the particular buffer cache bound to that particular data object according to how recently each portion is used, wherein least-recently used portions are displaced from the buffer cache by more-recently used portions.

6. The method of claim 1, wherein the step of providing access to the particular one of the specified data objects by caching at least a portion of that specified data object in said particular buffer cache bound to that particular data object includes storing portions of a database table object.

7. A computer system for managing the storage of data objects in a database, the computer system, comprising:

one or more storage devices in which the data objects of a database are stored;

a memory for temporarily storing the data objects and for providing access to the data objects at a relatively faster rate than from the one or more storage devices, wherein a plurality of portions of the memory are allocated as buffer caches, each of which is separately addressable wherein at least one of the buffer caches includes a plurality of memory pools, each memory pool having multiple storage blocks of identical storage capacity, and wherein the storage capacities of the storage blocks differ from one memory pool to another,

means, operating in response to user commands, for reserving one of said buffer caches for a specified data object;

means for binding said specified data object to the reserved buffer cache in response to user commands; and

buffering means for copying at a portion of said specified data object from, said one or more storage devices to the reserved buffer cache,

means for selecting a particular memory pool to which a data object will be copied in response to a request for access;

means for copying the data object to the particular memory pool; and

means for estimating an amount of data is to be copied to the buffer cache,

wherein the means for selecting a particular memory pool selects a memory pool which efficiently stores the estimated amount of data.

8. The computer system of claim 7, further comprising a catalog which associates the specified data object with the reserved buffer cache, whereby the means for copying data objects employs the catalog to determine in to which buffer cache a particular data object is to be copied.

9. The computer system of claim 7, further comprising means for deleting portions of a data object cached in a buffer cache when the collective size of the portions in the buffer cache approach the buffer cache'"'"'s maximum storage capacity.

10. The computer system of claim 9, wherein the means for deleting portions of a data object deletes those portions which are least-recently used.

11. In a computer system having one or more storage devices on which the data objects of a database are stored, the computer system including a memory on which the data objects may be stored temporarily and accessed relatively faster than from the one or more storage devices, the computer system also supporting database processing in which data objects of a first type are preferably accessed by a non-sequential, random access of individual data objects, and in which data objects of a second type are preferably accessed by sequentially scanning groups of data objects, a method of buffering data objects comprising:

(a) allocating a portion of the memory as a buffer cache;

(b) allocating at least a first memory pool and a second memory pool each having a user-configurable size within the buffer cache, the first memory pool having multiple identical storage blocks, each having a first storage capacity, and the second memory pool also having multiple identical storage blocks, each having a second storage capacity, the second storage capacity being greater than the first storage capacity;

(c) receiving a request for access to a particular data object;

(d) determining which type the particular data object is; and

(e) based the determined type for the particular data object, storing the particular data object in the first memory pool if the particular data object is preferably accessed by sequentially scanning groups of data objects and storing the particular data object in the second memory pool if data objects of a second type are preferably accessed by a non-sequential, random access of individual data objects.

12. The method of claim 11, further comprising a step of determining whether the particular data object is selected randomly before determining in which memory pool to copy the selected data object.

13. The method of claim 11, wherein said scanning groups of data objects comprises scanning portions of a database table.

14. The method of claim 11, wherein said scanning groups of data objects comprises scanning portions of a database index.

15. The method of claim 11, wherein the step of allocating a portion of the memory as a buffer cache allocates at least two buffer caches, each of which is separately addressable.

16. The method of claim 11, further comprising a step of queuing data objects in the each memory pool of the buffer cache according to how often the data objects are used in the system.

17. The method of claim 11, wherein the step of allocating at least a first memory pool and a second memory pool within the buffer cache further includes allocating at least a third memory pool with a plurality of identical storage blocks, said storage blocks of the third memory pool having a third storage capacity that is intermediate in size between the first and second storage capacities.

18. A computer system for managing the storage of data objects in a database, the computer system comprising:

one or more storage devices on which the data objects of a database are stored;

a memory in which the data objects can be stored temporarily and accessed at relatively faster rate than from the one or more storage devices;

means for copying data objects from the one or more storage devices to the memory;

a portion of the memory allocated as a buffer cache having at least a first memory pool and a second memory pool each having a user configurable size, the first memory pool having multiple identical storage blocks, each of a first storage capacity, and the second memory pool also having identical multiple storage blocks each of a second storage capacity, the second storage capacity being greater than the first storage capacity; and

means for copying a selected data object to the first or second memory pool based on whether the selected data object is randomly selected,

wherein the means for copying copies the selected data object to the first memory pool if the selected data object is randomly selected in an On-Line Transaction Processing (OLTP) environment, and copies the selected data object to the second memory pool if the selected data object is not randomly selected in a Decision Support System (DSS) environment.

19. The computer system of claim 18, wherein the buffer cache further includes at least a third memory pool having a plurality of identical storage blocks having a third storage capacity that is between the first and second storage capacities.

20. The computer system of claim 18, further comprising:

means for estimating the size of a volume of data to be copied to the buffer cache, the volume of data containing a selected data object; and

means for copying a data object from the volume of data to a selected memory pool based on the estimated size of the volume of data.

21. The computer system of claim 20, wherein the means for estimating the size of a volume of data to be copied includes a means for determining whether the volume of data is randomly selected, and wherein the means for copying the volume of data to a selected memory pool copies, the data object to the first memory pool if the data is randomly selected.

22. In a computer comprising a processor, a memory, and a storage device, said computer including a database system storing a plurality of database tables, each table comprising a plurality of data records, a method for accessing said data records, the method comprising:

allocating a plurality of buffers in said memory, for caching objects accessed from said storage device;

receiving a first request for assigning a first buffer from said plurality of buffers to a first database table from said plurality of database tables, such that said first buffer exclusively caches data from said first database table;

receiving a second request for assigning a second buffer from said plurality of buffers to a second database table from said plurality of database tables, such that said second buffer exclusively caches data from said second database table;

receiving a third request for performing a database operation which requires access to both said first and second database tables;

for each data record required during said database operation, accessing the data record by;

(i) if the data record required is from said first database table, attempting to access the data record from said first buffer,

(ii) if the data record required cannot be accessed from said first buffer retrieving the data record from said storage device, storing a copy of the data record in said first buffer, and thereafter accessing the data record from said first buffer,

if the data record required is from said second database table, attempting to access the data record from said second buffer, and

if the data record required cannot be accessed from said second buffer retrieving the data record from said storage device, storing a copy of the data record in said second buffer, and thereafter accessing the data record from said second buffer;

wherein data records from said first database table are exclusively cached in said first buffer and wherein data records from said second database table are exclusively cached in said second buffer;

wherein said first buffer comprises first and second buffer pools, said first buffer pool having storage units which are 2K in size, said second buffer pool having storage units which are greater than 2K in size; and

wherein copies of data records from said first database table are stored in said first buffer using storage units from said first buffer pool, when said database operation comprises a database query which results in random access of data records from said first database table.

23. The method of claim 22, wherein said database operation comprises a database query operation.

24. The method of claim 22, wherein each of said first, second, and third requests comprises a Structured Query Language (SQL) command submitted by a client to said computer.

25. The method of claim 22, wherein said first and second buffers each comprise a plurality of buffer pools, each buffer pool having a storage unit of a particular size.

26. The method of claim 22, wherein said second buffer pool comprises storage units which are at least 64K in size.

27. In a computer comprising a processor, a memory, and a storage device, said computer including a database system storing a plurality of database tables, each table comprising a plurality of data records, a method for accessing said data records, the method comprising:

allocating a plurality of buffers in said memory, for caching objects accessed from said storage device;

receiving a first request for assigning a first buffer from said plurality of buffers to a first database table from said plurality of database tables, such that said first buffer exclusively caches data from said first database table;

receiving a second request for assigning a second buffer from said plurality of buffers to a second database table from said plurality of database tables, such that said second buffer exclusively caches data from said second database table;

receiving a third request for performing a database operation which requires access to both said first and second database tables;

for each data record required during said database operation, accessing the data record by;

(i) if the data record required is from said first database table, attempting to access the data record from said first buffer,

(ii) if the data record required cannot be accessed from said first buffer retrieving the data record from said storage device, storing a copy of the data record in said first buffer, and thereafter accessing the data record from said first buffer,

if the data record required is from said second database table, attempting to access the data record from said second buffer, and

if the data record required cannot be accessed from said second buffer retrieving the data record from said storage device, storing a copy of the data record in said second buffer, and thereafter accessing the data record from said second buffer;

wherein data records from said first database table are exclusively cached in said first buffer and wherein data records from said second database table are exclusively cached in said second buffer;

wherein said first buffer comprises first and second buffer pools, said first buffer pool having storage units which are 2K in size, said second buffer pool having storage units which are greater than 2K in size; and

wherein copies of data records from said first database table are stored in said first buffer using storage units from said second buffer pool, when said database operation comprises a database query which results in non-random access of data records from said first database table.

33. The method of claim 27, wherein said first buffer pool comprises storage blocks which are 2K in size.

28. In a computer comprising a processor, a memory, and a storage device, said computer including a database system storing a plurality of database tables, each table comprising a plurality of data records, a method for accessing said data records, the method comprising:

(a) allocating a portion of the memory as a buffer cache;

(b) allocating at least a first memory pool and a second memory pool each having a user configurable size within the buffer cache, the first memory pool having multiple storage blocks with a storage capacity predominantly of a first size, and the second memory pool having multiple storage blocks with a storage capacity predominantly of a second size, the second size being greater than the first size;

(c) receiving a request for performing a database operation which requires access to data objects stored within at least one of said plurality of database tables;

(d) determining whether said request requires access to a single record in order to process the query or requires access to multiple records in order to process the query; and

(e) based on said determining step, copying at least one of said data objects to said second memory pool if said request is determined to require access to multiple records, and copying at least one of said data objects to said first memory pool if said request is determined to require access to a single record.

29. The method of claim 28, wherein said first buffer pool has storage blocks which are 2K in size and said second buffer pool has storage blocks which are greater than 2K in size.

30. The method of claim 28, wherein said determining step includes determining whether the query requires random access of a data record from a database table or whether the query requires sequential access of multiple data records from a database table.

31. The method of claim 28, wherein said determining step includes determining whether the query is an On-Line Transaction Processing (OLTP) query or whether the query comprises a Decision Support System (DSS) query.

32. The method of claim 28, wherein said second buffer pool comprises storage blocks which are at least 64K in size.

34. The method of claim 28, wherein said database operation comprises a database query operation.

35. The method of claim 34, wherein the database query operation comprises a Structured Query Language (SQL) command submitted by a client to said computer.