Preferentially retaining memory pages using a volatile database table attribute

• US 10,353,864 B2
• Filed: 12/23/2014
• Issued: 07/16/2019
• Est. Priority Date: 03/26/2014
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Konik, Rafal P., Mittelstadt, Roger A., Muras, Brian R., Theuer, Mark W.
• Primary Examiner(s): Mofiz, Apu M
• Assistant Examiner(s): Agharahimi, Farhad
• Application Number:
  US14/582,175
• Publication Number:
  US 20160171023A1
• Time in Patent Office: 1,666 Days
• Field of Search: None
• CPC Class Codes: G06F 16/116 Details of conversion of file system types or formats
• CPC Class Codes: G06F 16/119 Details of migration of file systems migration mechanisms in storage systems G06F3/0647
• CPC Class Codes: G06F 16/13 File access structures, e.g. distributed indices arrangements of input from, or output to, record carriers G06F3/06
• CPC Class Codes: G06F 16/219 Managing data history or versioning querying versioned data G06F16/2474; querying temporal data G06F16/2477
• CPC Class Codes: G06F 16/2282 Tablespace storage structures; Management thereof
• CPC Class Codes: G06F 16/2393 Updating materialised views
• CPC Class Codes: G06F 16/24542 Plan optimisation

Abstract

A respective volatility attribute associated with each of one or more tables of a computerized database is used to determine circumstances under which a page of table data is paged out of memory, by preferentially retaining pages from volatile database tables in memory. Various optional additional uses of a volatility attribute to manage a database are disclosed. Preferably, database parameters are automatically monitored over time and database table volatility state is automatically determined and periodically adjusted.

Claims

1. A computer program product for managing a computerized database, the computer program product comprising a plurality of computer-executable instructions recorded in a non-transitory computer-readable media, wherein said instructions, when executed by at least one computer system, cause the at least one computer system to perform:

storing at least one respective database table volatility attribute for each of at least one database table of said computerized database, each database table volatility attribute expressing volatility of at least a portion of a respective database table of said computerized database, said volatility of at least a portion of a respective database table being a property of the respective database table that is a function of changes to data recorded in said at least a portion of the respective database table with respect to time;

using the at least one database table volatility attribute corresponding to a first database table of said at least one database table to determine circumstances under which a page of data temporarily stored in memory of said at least one computer system and containing data of the first database table of said computerized database is paged out of said memory.

2. The computer program product of claim 1, wherein the instruction further cause the at least one computer system to perform:

monitoring at least one respective parameter of each said database table of said computerized database over at least one time interval and saving monitored parameter data with respect to the respective database table; and

determining a respective volatility state of each said database table using the saved monitored parameter data to automatically generate said at least one respective database table volatility attribute for each said database table.

3. The computer program product of claim 1, wherein the instructions further cause the at least one computer system to perform:

computing a respective figure of merit representing volatility of the at least a portion of the respective database table for each of said at least one time interval.

4. The computer program product of claim 1, wherein said computerized database supports the SQL database language and said at least one database table volatility attribute comprises an SQL VOLATILE attribute.

5. The computer program product of claim 1, wherein said determining circumstances under which a page of data temporarily stored in memory of said at least one computer system and containing data of a first database table of said computerized database is paged out of said memory comprises preferentially retaining a page of data in memory if the at least one database table volatility attribute corresponding to the first database table indicates that at least a portion of the first database table containing data stored in the first page is volatile.

6. The computer program product of claim 5, wherein said preferentially retaining a page of data in memory comprises pinning the page of data to prevent eviction from memory.

7. The computer program product of claim 5, wherein the instruction further cause the at least one computer system to perform:

determining a respective memory retention priority for each of a plurality of database tables or portions of a database table, wherein pages of data from each database table of portion of a database table are preferentially retained according to the respective memory retention priority.

8. The computer program product of claim 1, wherein said database table volatility attribute comprises a quantitative value.

13. The computer program product of claim 7, wherein the memory retention priority is determined by computing a respective figure of merit for each of the plurality of database tables or portions of a database table, each figure of merit being computed using at least one of:

(a) a size of the corresponding database table or portion of a database table, and (b) a quantitative value expressing volatility of the corresponding database table or portion of a database table.

9. A computer system supporting a database, comprising:

a memory;

at least one processor executing instructions storable in said memory;

a database table of said database storable in said memory; and

a database manager embodied as instructions storable in said memory and executable on said at least one processor, said database manager managing said database and providing access to data in said database table;

said database manager including a page retention function which causes said computer system to use a respective database table volatility attribute associated with each of one or more pages of data to selectively determine whether to preferentially retain the associated page of data in said memory, each database table volatility attribute being storable in said memory and expressing volatility of at least a portion of a respective database table of said database having data contained in the associated page of data, said volatility of at least a portion of a respective database table being a property of the respective database table that is a function of changes to data recorded in said at least a portion of the respective database table with respect to time, said page retention function further causing said computer system to preferentially retain the associated page of data in said memory according to the selective determination made using the respective database table volatility attribute.

10. The computer system of claim 9, wherein the database manager further includes a volatility determining function which:

monitors at least one respective parameter of each of at least one database table of said computerized database over at least one time interval and saves monitored parameter data with respect to the respective database table; and

determines a respective volatility state of each said database table using the saved monitored parameter data to automatically generate said at least one respective database table volatility attribute for each said database table.

11. The computer system of claim 9, wherein said database table volatility attribute comprises a quantitative value.

12. The computer system of claim 9, wherein said page retention function causes said computer system to preferentially retain in said memory one or more pages of data using the respective database table volatility attribute by pinning at least one page containing data from the at least a portion of the database table to prevent eviction from the memory of the computer system providing access to said computerized database if said database table volatility attribute indicates that the at least a portion of the database table is volatile.

14. A computer program product for managing a computerized database, the computer program product comprising a plurality of computer-executable instructions recorded in a non-transitory computer-readable media, wherein said instructions, when executed by at least one computer system, cause the at least one computer system to perform:

determining a volatility state of at least a portion of a database table of the computerized database, said volatility state being a property of the database table that is a function of changes to data recorded in the at least a portion of the database table with respect to time;

recording a database table volatility attribute expressing said volatility state;

using a value of said database table volatility attribute to selectively determine whether to preferentially retain data contained in the at least a portion of the database table in a memory of a first computer system providing access to said computerized database; and

preferentially retaining data contained in the at least a portion of the database table in the memory of the first computer system according to the selective determination made using the value of said database table volatility attribute.

15. The computer program product of claim 14, wherein said preferentially retaining data contained in at least a portion of the database table comprises preferentially retaining at least one page containing data from the at least a portion of the database table in the memory of the computer system providing access to said computerized database if said database table volatility attribute indicates that the at least a portion of the database table is volatile.

16. The computer program product of claim 15, wherein said preferentially retaining at least one page containing data from the at least a portion of the database table comprises pinning the at least one page to prevent eviction from the memory.

17. The computer program product of claim 14, wherein the instructions further cause the at least one computer system to perform:

monitoring at least one parameter of the database table over at least one time interval and saving monitored parameter data with respect to the database table; and

determining the volatility state using the saved monitored parameter data to automatically generate said database table volatility attribute.

18. The computer program product of claim 17, wherein the instructions further cause the at least one computer system to perform:

computing a figure of merit representing volatility of the at least a portion of the database table for each of said at least one time interval.

19. The computer program product of claim 14, wherein said database table volatility attribute comprises a quantitative value.