Optimizing Database Definitions in an Existing Database

• US 20150317361A1
• Filed: 05/02/2014
• Published: 11/05/2015
• Est. Priority Date: 05/02/2014
• Status: Active Grant

• Current Assignee: AirBNB Incorporated
• Original Assignee: International Business Machines Corporation
• Inventors: Battaglia, Michael, Smith, Heather A.
• Granted Patent: US 9,582,526 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/217 Database tuning G06F16/2282 takes precedence; database performance monitoring G06F11/3409
• CPC Class Codes: G06F 16/2455 Query execution
• CPC Class Codes: G06F 16/951 Indexing; Web crawling techniques

Abstract

A mechanism is provided in a data processing system for optimizing a database definition of an existing database. The mechanism receives usage data for the existing database. The mechanism queries a knowledgebase for information about database objects of the existing database. The mechanism executes optimization logic to generate a set of database commands based on the usage data and information about database objects. The set of database commands, when executed against a database definition of the existing database, optimize one or more database objects of the existing database. The mechanism returns the set of optimization commands.

Claims

1. A method, in a data processing system, for optimizing a database definition of an existing database, the method comprising:

receiving usage data for the existing database;

querying a knowledgebase for information about database objects of the existing database;

executing optimization logic to generate a set of database commands based on the usage data and information about database objects, wherein the set of database commands, when executed against a database definition of the existing database, optimize one or more database objects of the existing database; and

returning the set of optimization commands.

2. The method of claim 1, wherein generating the set of database commands comprises generating a data description language file comprising the set of database commands.

3. The method of claim 1, wherein the set of database commands alter and create database object.

4. The method of claim 1, wherein receiving usage data for the existing database comprises receiving the usage data from a repository of usage metrics stored by a usage monitor in a database.

5. The method of claim 4, wherein the repository of usage metrics stores at least one of:

an average number of updates made within a specific time unit;

a specific number of updates made within a specific time unit;

a minimum number of updates made within a specific time unit;

a maximum number of updates made within a specific time unit;

an average number of reads made within a specific time unit;

a specific number of reads made within a specific time unit;

a minimum number of reads made within a specific time unit;

a maximum number of reads made within a specific time unit;

an average number of inserts made within a specific time unit;

a specific number of inserts made within a specific time unit;

a minimum number of inserts made within a specific time unit;

a maximum number of inserts made within a specific time unit;

an average column size and the related size unit;

a range or list of values for a given column;

a frequency percentage of how often a column is used in where clauses for statements against a given table;

or

a data insertion pattern for each column observed as random, ascending, or descending.

6. The method of claim 1, wherein the optimization logic comprises decision trees having end nodes, wherein each end node identifies a command to optimize a given database object.

7. The method of claim 1, further comprising receiving an optimization command that requests optimization of the one or more database objects in the existing database, wherein the optimization command identifies an object type to be optimized and an object name of the one or more database objects to be optimized.

8. The method of claim 7, wherein the optimization command identifies a method type that identifies whether the optimization is to be executed automatically or manually.

9. The method of claim 8, further comprising automatically executing the set of database commands against the database definition of the existing database responsive to the method type identifying that optimization is to be executed automatically.

10. A computer program product comprising a computer readable storage medium having a computer readable program stored therein, wherein the computer readable program, when executed on a computing device, causes the computing device to:

receive usage data for an existing database;

query a knowledgebase for information about database objects of the existing database;

execute optimization logic to generate a set of database commands based on the usage data and information about database objects, wherein the set of database commands, when executed against a database definition of the existing database, optimize one or more database objects of the existing database; and

return the set of optimization commands.

11. The computer program product of claim 10, wherein generating the set of database commands comprises generating a data description language file comprising the set of database commands.

12. The computer program product of claim 10, wherein the set of database commands alter and create database objects.

13. The computer program product of claim 10, wherein receiving usage data for the existing database comprises receiving the usage data from a repository of usage metrics stored by a usage monitor in a database.

14. The computer program product of claim 10, wherein the optimization logic comprises decision trees having end nodes, wherein each end node identifies a command to optimize a given database object.

15. The computer program product of claim 10, wherein the computer readable program, when executed on a computing device, causes the computing device to:

receive an optimization command that requests optimization of the one or more database objects in the existing database, wherein the optimization command identifies an object type to be optimized, an object name of the one or more database objects to be optimized, and a method type that identifies whether the optimization is to be executed automatically or manually; and

automatically execute the set of database commands against the database definition of the existing database responsive to the method type identifying that optimization is to be executed automatically.

16. An apparatus comprising:

a processor; and

a memory coupled to the processor, wherein the memory comprises instructions which, when executed by the processor, cause the processor to;

receive usage data for an existing database;

query a knowledgebase for information about database objects of the existing database;

execute optimization logic to generate a set of database commands based on the usage data and information about database objects, wherein the set of database commands, when executed against a database definition of the existing database, optimize one or more database objects of the existing database; and

return the set of optimization commands.

17. The apparatus of claim 16, wherein generating the set of database commands comprises generating a data description language file comprising the set of database commands.

18. The apparatus of claim 16, wherein receiving usage data for the existing database comprises receiving the usage data from a repository of usage metrics stored by a usage monitor in a database.

19. The apparatus of claim 16, wherein the optimization logic comprises decision trees having end nodes, wherein each end node identifies a command to optimize a given database object.

20. The apparatus of claim 16, wherein the computer readable program, when executed on a computing device, causes the computing device to:

receive an optimization command that requests optimization of the one or more database objects in the existing database, wherein the optimization command identifies an object type to be optimized, an object name of the one or more database objects to be optimized, and a method type that identifies whether the optimization is to be executed automatically or manually; and

automatically execute the set of database commands against the database definition of the existing database responsive to the method type identifying that optimization is to be executed automatically.