System and method for gathering and analyzing database performance statistics

• US 6,804,627 B1
• Filed: 12/31/2002
• Issued: 10/12/2004
• Est. Priority Date: 12/31/2002
• Status: Expired due to Term

• Current Assignee: Emc IP Holding Company LLC (Dell Technologies Inc.)
• Original Assignee: EMC Corporation (Dell Technologies Inc.)
• Inventors: Marokhovsky, Serge G., Chen, Shuzi, Ward, Anthony, Prathab, Sadasiva K
• Primary Examiner(s): Barlow, John
• Assistant Examiner(s): LE, JOHN H
• Application Number:
  US10/335,329
• Time in Patent Office: 651 Days
• Field of Search: 702/182, 702/185, 702/186, 700/174-178, 707/103, 707/200, 707/201, 709/224, 455/427, 342/41
• US Class Current: 702/182
• CPC Class Codes: G06F 11/3409 for performance assessment
• CPC Class Codes: G06F 11/3419 by assessing time
• CPC Class Codes: G06F 11/3452 Performance evaluation by statistical analysis
• CPC Class Codes: G06F 2201/87 Monitoring of transactions
• CPC Class Codes: G06F 2201/88 Monitoring involving counting
• CPC Class Codes: Y10S 707/99944 Object-oriented database structure

Abstract

Database performance assessment typically entails logging and capturing utilities which tend to gather large quantities of unwieldy data leading to a cumbersome and indefinite analysis. A system and method for database performance analysis includes periodic sampling of pending database requests, rather than exhaustive monitoring and capturing all database access traffic, to identify areas of contention. A database access queue is periodically sampled, or scanned, to gather a snapshot of pending requests of database transactions. Pending requests are aggregated by an aggregating process which aggregates, the samples with previous samples corresponding to the same transaction. Correlating the aggregated samples identifies transactions which have been pending the longest and identifies tables and segments in the database which have a relatively high number of pending transactions. By periodically sampling, rather than exhaustively logging all requests, CPU intrusiveness is minimized, trivial, benign transactions are eliminated from the output, and the most burdened tables and segments are identified to enable a database administrator to make informed decisions about remedial actions.

Claims

1. A method for processing database performance statistics comprising:

sampling, at a predetermined sampling interval, a set of database performance statistics indicative of pending database requests for access to respective database objects;

building, for each of the pending database requests, a scope structure including the sampled database performance statistics in the pending database requests to generate a set of snapshots of database requests; and

aggregating, in a global accumulator having transaction entries indicative of database transactions for the respective database objects, the scope structure of database requests to correlate current pending database requests with transaction entries in the global accumulator corresponding to previous pending database requests for the same transaction for the respective database objects.

2. The method of claim 1 wherein the sampling further comprises

scanning a queue for the pending database requests; and

reading the database performance statistics for the entries in the scanned queue.

3. The method of claim 1 wherein the aggregating further comprises:

indexing the global accumulator with a transaction identifier indicative of the transaction corresponding to the pending database request;

determining, via the transaction identifier, if a transaction entry in the global accumulator corresponds to the pending transaction; and

creating, if a corresponding transaction entry is not found in the global accumulator, a new transaction entry corresponding to the pending transaction for a respective database object.

4. The method of claim 1 wherein the aggregating further comprises:

indexing the global accumulator with a transaction identifier indicative of the pending transaction;

comparing the database performance statistics corresponding to the same transaction; and

computing, for each of the database performance statistics corresponding to the same transaction, aggregate parameters indicative of transaction performance for the same transaction.

5. The method of claim 1 further comprising

analyzing, at a predetermined report interval, the accumulated transactions in the global accumulator to identify transaction performance for transactions related to the respective database objects;

and

generating a report indicative of the analysis of performance for transactions related to the respective database objects.

6. The method of claim 5 wherein the predetermined report interval is 30 minutes.

7. The method of claim 1 wherein the sampling further comprises sampling a subset of selected queues from a plurality of queues corresponding to database instances.

8. The method of claim 1 wherein the sampling further comprises sampling according to a predetermined sampling frequency cycle, the sampling frequency cycle indicative of a periodic window in which to retrieve the database performance statistics.

9. The method of claim 1 further comprising a sleep time interval and wherein sampling further comprises:

retrieving, for a predetermined sampling time, the database performance statistics; and

idling for the duration of the sleep time interval such that database performance statistics are not retrieved during the idling.

10. The method of claim 1 wherein the sampling, building, and the aggregating occur during predetermined intervals according to a data collection policy, the data collection policy specifying the predetermined intervals for mitigating intrusiveness of the sampling.

11. The method of claim 10 wherein the data collection policy is indicative of the predetermined intervals such that transactions having substantial impact on database throughput are identified in the sampling and transactions having insubstantial impact on database throughput are omitted from the sampling.

12. The method of claim 1 comprising:

repeating the steps of sampling, building and aggregating according to an instance sampling sequence, the instance sampling sequence corresponding to a respective database instance, such the global accumulator indicates, for the instance sampling sequence for that respective database instance, transaction performance for transactions occurring during the instance sampling sequence on that respective database instance.

13. The method of claim 12 further comprising:

repeating the instance sampling sequence for multiple database instances during a database sampling frequency cycle, the database sampling frequency cycle allowing collection of database transaction performance for the multiple database instances for respective database objects, the database sampling frequency cycle occurring repetitively over a database collection report interval.

14. The method of claim 13 further comprising:

coalescing aggregate parameters for multiple transactions of a similar transaction type for respective database objects from the global accumulator to produce an aggregate result of transaction performance by transaction type for each of the respective database object for each database instance; and

presenting the aggregate result of transaction performance by transaction type for each respective database object for each database instance to a user such that the user can identify performance characteristics of the database transactions by transaction type for the respective database objects.

15. A workload analysis toolkit for gathering, analyzing and reporting database performance statistics comprising:

a collection agent operable to sample, at a predetermined sampling time interval, a queue of pending database requests for access to respective database objects, each of the pending database requests indicative of database performance statistics;

a gathering component in the collection agent operable to build, for each of the pending database requests, a scope structure including the database performance statistics in the pending database requests to generate a scope structure snapshot of database requests; and

an aggregating component responsive to the collection agent and operable to aggregate, in a global accumulator having transaction entries indicative of database transactions for the respective database objects, the scope structure snapshot of database requests to correlate current pending database requests with transaction entries in the global accumulator corresponding to previous pending database requests for the same transaction for the respective database objects.

16. The toolkit of claim 15 wherein the gathering component is further operable to:

scan the queue for the pending database requests; and

read the database performance statistics for the entries in the scanned queue to generate the scope structure.

17. The toolkit of claim 15 further comprising a transaction identifier, wherein the aggregating component is further operable to:

index the global accumulator with a transaction identifier indicative of the transaction corresponding to the pending database request;

determine, via the transaction identifier, if a transaction entry in the global accumulator corresponds to the pending transaction; and

create, in the global accumulator, a new transaction entry corresponding to the pending transaction for a respective database object.

18. The system of claim 15 further comprising a transaction identifier, wherein the aggregating component is further operable to:

index the global accumulator with a transaction identifier indicative of the pending transaction;

compare the database performance statistics corresponding to the same transaction; and

compute, for each of the database performance statistics corresponding to the same transaction, aggregate parameters indicative of transaction performance for the same transaction.

19. The toolkit of claim 15 further comprising a report writer and a predetermined report interval, the report writer operable to:

analyze, at the predetermined report interval, the accumulated transactions in the global accumulator to identify transaction performance for transactions related to the respective database objects; and

generate a report indicative of the analysis of transaction performance for transactions related to the respective database objects.

20. The toolkit of claim 15 wherein the queue further comprises a plurality of DB instance queues, and the collection agent is further operable to sample a subset of selected queues from the plurality of DB instance queues corresponding to database instances.

21. The toolkit of claim 15 further comprising a predetermined sampling frequency cycle indicative of a periodic window in which to retrieve the database performance statistics, wherein the collection agent is further operable to sample according to the predetermined sampling frequency cycle.

22. The method of claim 15 further comprising a sleep time interval and wherein the collection agent is further operable to

retrieve, for a predetermined sampling time, the database performance statistics;

and

idle for the duration of the sleep time interval such that database performance statistics are not retrieved during the idling.

23. The toolkit of claim 15 further comprising a data collection policy having predetermined intervals corresponding to sampling, building, and the aggregating of the database performance statistics such that the predetermined intervals mitigate intrusiveness of the sampling.

24. The toolkit of claim 22 wherein the data collection policy is indicative of the predetermined intervals such that transactions having substantial impact on database throughput are identified in the scope structure snapshot and transactions having insubstantial impact on database throughput are omitted from the scope structure snapshot.

25. The toolkit of claim 15 wherein the aggregating component is further operable to:

repeat the steps of sampling, building and aggregating according to an instance sampling sequence, the instance sampling sequence corresponding to a respective database instance, such the global accumulator indicates, for the instance sampling sequence for that respective database instance, transaction performance for transactions occurring during the instance sampling sequence on that respective database instance.

26. The toolkit of claim 25 wherein the aggregating component is further operable to:

repeat the instance sampling sequence for multiple database instances during a database sampling frequency cycle, the database sampling frequency cycle allowing collection of database transaction performance for the multiple database instances for respective database objects, the database sampling frequency cycle occurring repetitively over a database collection report interval.

27. The toolkit of claim 25 wherein the report output writer is further operable to:

coalesce aggregate parameters for multiple transactions of a similar transaction type for respective database objects from the global accumulator to produce an aggregate result of transaction performance by transaction type for each of the respective database object for each database instance; and

present the aggregate result of transaction performance by transaction type for each respective database object for each database instance to a user such that the user can identify performance characteristics of the database transactions by transaction type for respective database objects.

28. A method for analyzing database performance comprising:

identifying a set of database instances for analysis, each of the database instances having a set of queues corresponding to pending requests;

sampling, during a sampling interval, pending requests in at least some of the queues according to a data collection policy (DCP);

gathering performance parameters indicative of database performance for each of a set of the pending requests;

storing, in a scope structure, the performance parameters indicative of snapshots of pending requests in the sampled queue;

waiting, for the duration of a sleep time interval, a predetermined period prior to subsequent sampling;

aggregating, in a global accumulator, the scope structure snapshots based on a transaction identifier such that subsequent snapshots are correlated with previous scope structure snapshots corresponding to the same pending request, the aggregating further comprising;

creating, if a corresponding transaction identifier is not found for previous scope structure snapshots for the pending request, a global accumulator entry indicative of the transaction; and

updating, if a corresponding transaction identifier is found indicative of previous samples for the pending request, the global accumulator entry corresponding to the pending request;

repeating, according to a sampling frequency, the gathering of the performance parameters; and

generating, according to a predetermined archive interval, a report indicative of contention for resources in the database.

29. A computer program product having computer program code for processing database performance statistics comprising:

computer program code for sampling, at a predetermined sampling interval, a set of database performance statistics indicative of pending database requests for access to respective database objects;

computer program code for building, for each of the pending database requests, a scope structure including the sampled database performance statistics in the pending database requests to generate a set of snapshots of database requests; and

computer program code for aggregating, in a global accumulator having transaction entries indicative of database transactions for the respective database objects, the scope structure of database requests to correlate current pending database requests with transaction entries in the global accumulator corresponding to previous pending database requests for the same transaction for the respective database objects.

30. A computer data signal having program code for processing database performance statistics comprising:

program code for sampling, at a predetermined sampling interval, a set of database performance statistics indicative of pending database requests for access to respective database objects;

program code for building, for each of the pending database requests, a scope structure including the sampled database performance statistics in the pending database requests to generate a set of snapshots of database requests; and

program code for aggregating, in a global accumulator having transaction entries indicative of database transactions for the respective database objects, the scope structure of database requests to correlate current pending database requests with transaction entries in the global accumulator corresponding to previous pending database requests for the same transaction for the respective database objects.

31. A system for processing database performance statistics comprising:

means for sampling, at a predetermined sampling interval, a set of database performance statistics indicative of pending database requests for access to respective database objects;

means for building, for each of the pending database requests, a scope structure including the sampled database performance statistics in the pending database requests to generate a set of snapshots of database requests; and

means for aggregating, in a global accumulator having transaction entries indicative of database transactions for the respective database objects, the scope structure of database requests to correlate current pending database requests with transaction entries in the global accumulator corresponding to previous pending database requests for the same transaction for the respective database objects.