Monitoring, diagnosing, and repairing a management database in a data storage management system

• US 10,248,494 B2
• Filed: 10/25/2016
• Issued: 04/02/2019
• Est. Priority Date: 10/29/2015
• Status: Active Grant

• Current Assignee: CommVault Systems Incorporated
• Original Assignee: CommVault Systems Incorporated
• Inventors: Haridas, Ganesh, Narasimman, Sundar, Suriyanarayanan, Karthik, A S, Gunassekaran, Klose, Michael Frank
• Primary Examiner(s): McCarthy, Christopher S
• Application Number:
  US15/334,036
• Publication Number:
  US 20170123890A1
• Time in Patent Office: 889 Days
• CPC Class Codes: G06F 11/0706 the processing taking place on a specific hardware platform or in a specific software environment
• CPC Class Codes: G06F 11/073 in a memory management context, e.g. virtual memory or cache management memory management G06F12/00; testing of static memory units G11C29/00
• CPC Class Codes: G06F 11/0751 Error or fault detection not based on redundancy power supply failures G06F1/30; network fault management H04L41/06
• CPC Class Codes: G06F 11/0769 Readable error formats, e.g. cross-platform generic formats, human understandable formats
• CPC Class Codes: G06F 11/0772 Means for error signaling, e.g. using interrupts, exception flags, dedicated error registers
• CPC Class Codes: G06F 11/079 Root cause analysis, i.e. error or fault diagnosis in a hardware test environment G06F11/22; in a software test environment G06F11/36
• CPC Class Codes: G06F 11/0793 Remedial or corrective actions recovery from an exception in an instruction pipeline G06F9/3861; by retry G06F11/1402; for recovering from a failure of a protocol instance or entity H04L69/40
• CPC Class Codes: G06F 11/302 where the computing system component is a software system
• CPC Class Codes: G06F 11/3055 Monitoring arrangements for monitoring the status of the computing system or of the computing system component, e.g. monitoring if the computing system is on, off, available, not available error or fault processing without redundancy G06F11/0703; error detection or correction by redundancy in data representation G06F11/08; error detection or correction by redundancy in operation G06F11/14; error detection or correction by redundancy in hardware G06F11/16
• CPC Class Codes: G06F 11/3409 for performance assessment
• CPC Class Codes: G06F 11/3452 Performance evaluation by statistical analysis
• CPC Class Codes: G06F 11/3495 for systems
• CPC Class Codes: G06F 16/10 File systems; File servers
• CPC Class Codes: G06F 16/11 File system administration, e.g. details of archiving or snapshots file system backup G06F11/14
• CPC Class Codes: G06F 16/16 File or folder operations, e.g. details of user interfaces specifically adapted to file systems
• CPC Class Codes: G06F 16/21 Design, administration or maintenance of databases
• CPC Class Codes: G06F 16/2228 Indexing structures
• CPC Class Codes: G06F 16/2282 Tablespace storage structures; Management thereof
• CPC Class Codes: G06F 2201/80 Database-specific techniques
• CPC Class Codes: G06F 2201/81 Threshold

Abstract

A lightweight always-on monitoring, collecting, diagnosing, and correcting utility operates in an enhanced storage manager that manages a data storage managements system. The always-on utility provides a comprehensive and pro-active approach, which is intended to reduce, if not altogether eliminate, the need for after-the-fact diagnostics. The always-on utility also enforces so-called best practices and other heuristics, which include pro-actively activating certain database settings that are not enabled by default; manipulating certain aspects of the database to improve performance; and reporting aspects that are outside best-practice parameters to the trouble report system so that system administrators and/or developers may intervene before a catastrophic failure occurs. In some cases, the best-practice parameters represent heuristics designed by the present inventors to improve the performance and general health of the management database.

Claims

1. A method comprising:

monitoring a database, by a storage manager, during active operation of a data storage management system,

wherein the database stores information used by the storage manager to manage the data storage management system, and

wherein the storage manager executes on a computing device comprising one or more processors and non-transitory computer-readable memory;

collecting, by the storage manager, information about the database,

wherein the collected information includes information about the structure of the database and information about the operation of the database;

diagnosing, by the storage manager, a problem associated with the database,

based on analyzing at least some of the collected information about the database; and

correcting the problem associated with the database, at least in part, by causing

at least a threshold number of temporary-database data structures to be instantiated in the database.

2. The method of claim 1 wherein the database is an SQL database.

3. The method of claim 1, wherein a database management system associated with the storage manager also executes on the computing device, and wherein the database management system comprises the database;

periodically scanning database resources that are locked in query execution during active operation of the data storage management system; and

identifying one or more database resources that remain locked longer than a pre-defined threshold,

wherein the pre-defined threshold is a query timeout period defined in the database management system.

4. The method of claim 3 wherein the one or more database resources that remain locked longer than the pre-defined threshold cause further queries to time out.

5. The method of claim 3 further comprising:

reporting, by the storage manager, the identified one or more database resources that remain locked longer than the pre-defined threshold as a trouble report.

6. The method of claim 3 further comprising:

reporting, by the storage manager, the identified one or more database resources that remain locked longer than the pre-defined threshold to a trouble report system in communication with the storage manager

wherein the reporting includes one or more queries that caused the respective identified one or more database resources to lock.

7. The method of claim 1 wherein the information about the database that is collected by the storage manager comprises one or more of:

(a) CPU usage by queries serviced by the database;

(b) suspended queries;

(c) recommendations for missing indexes;

(d) deadlocked operations;

(e) corruption detected in the database;

(f) native database-health reports generated by a database management system that controls the database; and

(g) native alarms generated by the database management system.

8. The method of claim 1, wherein the diagnosing is further based on diagnostic parameters comprising one or more of:

(a) a first threshold representing a minimum number of temporary-database data structures that should be instantiated in the database;

(b) a second threshold representing a maximum number of rows in any heap data structure in the database;

(c) a third threshold for classification of one or more indexes in the database that are busier, according to the third threshold, than other indexes in the database;

(d) a fourth threshold representing a maximum measure of fragmentation of each table in the database allowed before re-indexing the respective table;

(e) a fifth threshold, lower than the fourth threshold, representing a maximum measure of fragmentation of each table in the database allowed before re-organizing the respective table;

(f) a sixth threshold representing a maximum size for any temporary-database data structure in the database;

(g) a seventh threshold for measuring corruption detected in database indexes;

and

(h) an eighth threshold for measuring corruption detected in database data.

9. A method comprising:

monitoring a database, by a storage manager, during active operation of a data storage management system,

wherein the database stores information used by the storage manager to manage the data storage management system, and

wherein the storage manager executes on a computing device comprising one or more processors and non-transitory computer-readable memory;

collecting, by the storage manager, information about the database,

wherein the collected information includes information about the structure of the database and information about the operation of the database;

diagnosing, by the storage manager, a problem associated with the database,

based on analyzing at least some of the collected information about the database; and

correcting the problem associated with the database, at least in part, by

storing one or more indexes that are identified as busier relative to other indexes in the database, to a second mass storage device that is distinct from a first mass storage device storing the database.

10. A method comprising:

monitoring a database, by a storage manager, during active operation of a data storage management system,

wherein the database stores information used by the storage manager to manage the data storage management system, and

wherein the storage manager executes on a computing device comprising one or more processors and non-transitory computer-readable memory;

collecting, by the storage manager, information about the database,

wherein the collected information includes information about the structure of the database and information about the operation of the database;

diagnosing, by the storage manager, a problem associated with the database,

based on analyzing at least some of the collected information about the database; and

correcting the problem associated with the database, at least in part, by causing;

(1) re-indexing of a portion of the database if the portion of the database exceeds a first threshold of fragmentation, and (2) reorganizing the portion of the database if the portion of the database does not exceed the first threshold and exceeds a second and lower threshold of fragmentation.

11. The method of claim 10 wherein the correcting further causes:

(3) re-indexing of an index of the database if corruption is detected in the index, and (4) initiating a restore of the database if corruption is detected in database data.

12. A method comprising:

collecting, by a storage manager, during active operation of a data storage management system, information about a relational database,

wherein the storage manager executes on a computing device comprising one or more processors and non-transitory computer-readable memory,

wherein the database stores information used by the storage manager to manage the data storage management system, and

wherein the collected information is obtained from a relational database management system that controls the database;

analyzing, by the storage manager, at least some of the collected information about the database relative to a plurality of diagnostic parameters in the storage manager; and

diagnosing, by the storage manager, one or more issues wherein one or more aspects of the database deviate from one or more of the plurality of diagnostic parameters; and

correcting a diagnosed issue with the database by instructing the relational database management system to

instantiate more temporary-database data structures sufficient to reach a first threshold representing a minimum number of temporary-database data structures that should be instantiated in the database.

13. The method of claim 12 wherein the plurality of diagnostic parameters comprises one or more of:

(a) a first threshold representing a minimum number of temporary-database data structures that should be instantiated in the database;

(b) a second threshold representing a maximum number of rows in any heap data structure in the database;

(c) a third threshold for classification of one or more indexes in the database that are busier, according to the third threshold, than other indexes in the database;

(d) a fourth threshold representing a maximum measure of fragmentation of each table in the database allowed before re-indexing the respective table;

(e) a fifth threshold, lower than the fourth threshold, representing a maximum measure of fragmentation of each table in the database allowed before re-organizing the respective table;

(f) a sixth threshold representing a maximum size for any temporary-database data structure in the database;

(g) a seventh threshold for measuring corruption detected in database indexes; and

(h) an eighth threshold for measuring corruption detected in database data.

14. The method of claim 12 further comprising:

correcting a diagnosed issue with the database

wherein if the relational database management system reports that an index for a table in the database is corrupted, the storage manager instructs the relational database management system to re-index the table.

15. The method of claim 12 further comprising:

correcting a diagnosed issue with the database,

wherein if the relational database management system reports that an index for a table in the database is corrupted, the storage manager instructs the relational database management system to re-index the table, and

wherein if the relational database management system reports that data in the database is corrupted, the storage manager initiates a database restore operation based on a secondary copy of the database maintained by the data storage management system.

16. The method of claim 12 wherein the collected information about the database is provided in response to requests submitted by the storage manager to the relational database management system;

and

wherein the collected information about the database comprises one or more of;

(a) CPU usage by queries serviced by the database;

(b) suspended queries;

(c) recommendations for missing indexes;

(d) deadlocked operations;

(e) corruption detected in the database;

(f) native database-health reports generated by the relational database management system; and

(g) native alarms generated by the relational database management system.

17. The method of claim 12 further comprising:

correcting a diagnosed issue with the database

wherein if the relational database management system reports that data in the database is corrupted, the storage manager initiates a database restore based on a secondary copy of the database maintained by the data storage management system.

18. The method of claim 12 further comprising:

reporting, by the storage manager, the diagnosed one or more issues as trouble reports to a trouble reporting system in communication with the storage manager.

19. A method comprising:

collecting, by a storage manager, during active operation of a data storage management system, information about a relational database,

wherein the storage manager executes on a computing device comprising one or more processors and non-transitory computer-readable memory,

wherein the database stores information used by the storage manager to manage the data storage management system, and

wherein the collected information is obtained from a relational database management system that controls the database;

analyzing, by the storage manager, at least some of the collected information about the database,

relative to a plurality of diagnostic parameters in the storage manager; and

diagnosing, by the storage manager, one or more issues wherein one or more aspects of the database deviate from one or more of the plurality of diagnostic parameters;

correcting a diagnosed issue with the database

wherein;

if a first table in the database exceeds a first threshold representing a maximum measure of fragmentation of each table in the database allowed before re-indexing the respective table, the storage manager instructs the relational database management system to re-index the first table, and

if the first table in the database does not exceed the first threshold and exceeds a second threshold, which is lower than the first threshold, representing a maximum measure of fragmentation of each table in the database allowed before re-organizing the respective table, the storage manager instructs the relational database management system to re-organize the first table, and

if the first table in the database does not exceed the first threshold and does not exceed the second threshold, the storage manager deems the first table to be in compliance with fragmentation-related diagnostic parameters.