Performing a non-invasive runtime simulation for a database system

• US 7,805,434 B2
• Filed: 01/12/2006
• Issued: 09/28/2010
• Est. Priority Date: 01/12/2006
• Status: Expired due to Fees

• Current Assignee: LinkedIn Corporation (Microsoft Corporation)
• Original Assignee: International Business Machines Corporation
• Inventors: Alcorn, Michael Harold, Chitnis, Upendra D.
• Primary Examiner(s): Truong; Cam Y T
• Application Number:
  US11/331,323
• Publication Number:
  US 20070162418A1
• Time in Patent Office: 1,720 Days
• Field of Search: 707/2
• US Class Current: 707/713
• CPC Class Codes: G06F 16/22 Indexing; Data structures therefor; Storage structures

Abstract

Mechanisms for evaluating at least one setting for a database system are disclosed. The mechanisms include providing at least one configuration derivative including the at least one setting. The configuration derivative is uncommitted. The mechanisms also include running the database system for a period of time. The database system is run using a committed configuration including a plurality of settings for the database system. The mechanisms also include collecting data on performance of the database system based on the at least one configuration derivative during the running of the database.

Claims

1. A method for evaluating at least one desired setting for a database system comprising:

providing at least one configuration derivative including the at least one desired setting, the configuration derivative being uncommitted such that the at least one desired setting is not used in actual operation of the database system;

running the database system for a period of time in standard and actual operation of the database system, the period of time greater than a point in time and being sufficient time to allow the database system to operate under a plurality of different conditions of the standard and actual operation, wherein the database system is run using a committed configuration, the committed configuration including a plurality of settings for the database system, at least one of the plurality of settings being different than the at least one desired setting in the configuration derivative;

collecting and storing data on performance of the database system based on the at least one configuration derivative during the running of the database system for the period of time, the data related to the at least one configuration derivative, wherein the data is collected based on the at least one configuration derivative as a background process during the running of the database system in the actual operation, the actual operation using the committed configuration, the collecting the data being performed during an operation under the plurality of different conditions of the standard and actual operation; and

analyzing the data to evaluate a performance of the configuration derivative as an alternate configuration to the committed configuration for the running of the database system in the actual operation, the evaluation occurring prior to committing the configuration derivative, the analyzing the data including using the data collected during the different conditions of the standard and actual operation and indicating how the configuration derivative performs under the different conditions of the standard and actual operation, wherein the at least one desired setting includes at least one materialized query table for the database system or at least one index and wherein the collecting further includes at least one of determining whether the at least one materialized query table is utilized during the actual operation of the database system over the period of time or determining a number of times the at least one index is used during the actual operation of the database system.

2. The method of claim 1 wherein the at least one desired setting includes at least one materialized query table for the database system.

3. The method of claim 2 wherein the collecting data further includes:

determining whether the at least one materialized query table is utilized during the actual operation of the database system over the period of time.

4. The method of claim 1 wherein the database system further includes an optimizer and wherein the at least one desired setting is provided by the optimizer.

5. The method of claim 1 wherein the database system further includes an optimizer and wherein the data collecting is performed using the optimizer.

6. The method of claim 1 further comprising:

returning the data to an authorized user; and

allowing the authorized user to commit at least a portion of the at least one setting.

7. The method of claim 1 further comprising providing at least one recommended setting based on the analyzing of the data.

8. The method of claim 7 further comprising:

providing the at least one recommended setting to an authorized user.

9. The method of claim 1 wherein the at least one configuration derivative includes a plurality of different configuration derivatives and wherein the method further includes:

performing a comparison of the plurality of configuration derivatives based on the collected data; and

allowing a user to implement one of the plurality of configuration derivatives based on the comparison.

10. The method of claim 1 wherein the at least one desired setting further includes at least one index and wherein the collecting further includes:

determining whether the at least one index is used during the actual operation of the database system.

11. The method of claim 10 wherein the index determining further includes:

determining a number of times the at least one index is used during the actual operation of the database system.

12. The method of claim 1 wherein the analyzing the data includes providing one or more performance metric scores indicative of the performance of the configuration derivative during actual operation and providing one more performance metric scores indicative of the performance of the committed configuration during actual operation, and wherein the analyzing the data includes comparing the performance metric scores for the configuration derivative to the performance metric scores for the committed configuration.

13. The method of claim 12 further comprising committing the configuration derivative when the comparison of performance metric scores indicates that there is an expectation that the configuration derivative improves the performance of the database system relative to the committed configuration.

14. The method of claim 1 wherein the collecting the data is performed for a sufficient time to allow the database system to operate under a full range of expected conditions.

15. The method of claim 1 wherein the collecting the data includes collecting whether and how many times indices are used by the configuration derivative during the actual operation, whether and how many times materialized query tables are used by the configuration derivative during the actual operation, and whether and how objects are used by the configuration derivative during the actual operation.

16. A method for evaluating at least one desired setting for a database system comprising:

providing at least one configuration derivative including the at least one desired setting, the configuration derivative being uncommitted such that the at least one desired setting is not used in actual operation of the database system;

running the database system for a period of time in standard and actual operation of the database system, the period of time greater than a point in time and being sufficient time to allow the database system to operate under a plurality of different conditions of the standard and actual operation, wherein the database system is run using a committed configuration, the committed configuration including a plurality of settings for the database system, at least one of the plurality of settings being different than the at least one desired setting in the actual operation configuration derivative;

collecting and storing data on performance of the database system based on the at least one configuration derivative during the running of the database system for the period of time, the data related to the at least one configuration derivative, wherein the data is collected based on the at least one configuration derivative as a background process during the running of the database system in the actual operation, the actual operation using the committed configuration, the collecting the data being performed during the under the plurality of different conditions of the standard and actual operation; and

analyzing the data to evaluate a performance of the configuration derivative as an alternate configuration to the committed configuration for the running of the database system in the actual operation, the evaluation occurring prior to committing the configuration derivative, wherein the data is analyzed as a background process during the running of the database system in the actual operation, the analyzing the data including using the data collected during the different conditions of the standard and actual operation and indicating how the configuration derivative performs under the different conditions of the standard and actual operation, wherein the analyzing the data includes providing one or more performance metric scores indicative of the performance of the configuration derivative during the actual operation and providing one more performance metric scores indicative of the performance of the committed configuration during the actual operation, and wherein the analyzing the data includes comparing the performance metric scores for the configuration derivative to the performance metric scores for the committed configuration, wherein the at least one desired setting includes at least one materialized query table for the database system or at least one index and wherein the collecting further includes at least one of determining whether the at least one materialized query table is utilized during the actual operation of the database system over the period of time or determining a number of times the at least one index is used during the actual operation of the database system.

17. A method for evaluating at least one desired setting for a database system comprising:

providing at least one configuration derivative including the at least one desired setting, the configuration derivative being uncommitted such that the at least one desired setting is not used in actual operation of the database system, wherein the least one desired setting includes at least one materialized query table for the database system and includes at least one index;

running the database system for a period of time in standard and actual operation, the period of time greater than a point in time and being sufficient time to allow the database system to operate under a plurality of different conditions of the standard and actual operation, wherein the database system is run using a committed configuration, the committed configuration including a plurality of settings for the database system, at least one of the plurality of settings being different than the at least one desired setting in the configuration derivative;

collecting and storing data on performance of the database system based on the at least one configuration derivative during the running of the database system for the period of time, the data related to the at least one configuration derivative, wherein the data is collected based on the at least one configuration derivative as a background process during the running of the database system in actual operation, the actual operation using the committed configuration, wherein the collecting the data is performed during an operation under the plurality of different conditions of the standard and actual operation, and wherein the collected data includes whether the at least one materialized query table is utilized during the actual operation of the database system, whether the at least one index is used during the actual operation of the database system, and a number of times the at least one index is used during the actual operation of the database system;

analyzing the data to evaluate a performance of the configuration derivative as an alternate configuration to the committed configuration for the running of the database system in the actual operation, the evaluation occurring prior to committing the configuration derivative, wherein the data is analyzed as a background process during the running of the database system in actual operation, wherein the analyzing the data includes using the data collected during the different conditions of the standard and actual operation and indicating how the configuration derivative performs under the different conditions of the standard and actual operation, wherein the analyzing the data includes providing one or more performance metric scores indicative of the performance of the configuration derivative during the actual operation and providing one more performance metric scores indicative of the performance of the committed configuration during the actual operation, and wherein the analyzing the data includes comparing the performance metric scores for the configuration derivative to the performance metric scores for the committed configuration; and

committing the configuration derivative when the comparison of performance metric scores indicates that there is an expectation that the configuration derivative improves the performance of the database system relative to the committed configuration, wherein the collecting further includes at least one of determining whether the at least one materialized query table is utilized during the actual operation of the database system over the period of time or determining a number of times the at least one index is used during the actual operation of the database system.