Apparatus and method to schedule and perform database maintenance

• US 7,085,786 B2
• Filed: 05/20/2002
• Issued: 08/01/2006
• Est. Priority Date: 05/20/2002
• Status: Expired due to Fees

• Current Assignee: LinkedIn Corporation (Microsoft Corporation)
• Original Assignee: International Business Machines Corporation
• Inventors: Carlson, Wayne Charles, Fisher, James Arthur, Shotwell, Kerri Renee
• Primary Examiner(s): Gaffin, Jeffrey
• Assistant Examiner(s): Wu, Yicun
• Application Number:
  US10/151,372
• Publication Number:
  US 20030217078A1
• Time in Patent Office: 1,534 Days
• Field of Search: 707/1, 707/2, 707/3, 707/4, 707/8, 707/9, 707/10, 707/100, 707/101, 707/102, 707/104.1, 707/202, 707/203, 707/204, 707/200, 707/201, 706/45, 702/1, 702/182
• US Class Current: 707/609
• CPC Class Codes: G06F 11/3419 by assessing time
• CPC Class Codes: G06F 11/3442 for planning or managing the needed capacity
• CPC Class Codes: G06F 11/3452 Performance evaluation by statistical analysis
• CPC Class Codes: G06F 16/217 Database tuning G06F16/2282 takes precedence; database performance monitoring G06F11/3409
• CPC Class Codes: G06F 2201/80 Database-specific techniques
• CPC Class Codes: G06F 2201/81 Threshold
• CPC Class Codes: Y10S 707/951 Calendar or scheduling
• CPC Class Codes: Y10S 707/99953 Recoverability
• CPC Class Codes: Y10S 707/99955 Archiving or backup

Abstract

A method to schedule and perform database maintenance, where the database includes information regarding one or more information storage media moveably disposed in a data storage and retrieval system, which includes selecting a first time period, commencing that first time period at a first time and ending that first time period at a third time, performing one or more database queries during that first time period, recording the number of database queries performed during each of the two or more first time intervals, determining the least utilized first time interval where that least utilized first time interval commenced at a second time, calculating a first time difference between the first time and the second time, and scheduling database maintenance at a fourth time. A data storage and retrieval system comprising a computer useable medium having computer readable program code disposed therein to implement Applicants'"'"' method to schedule and perform database maintenance. A computer program product usable with a programmable computer processor having computer readable program code embodied therein to implement Applicants'"'"' method to schedule and perform database maintenance.

Claims

1. A method to schedule database maintenance, comprising the steps of:

selecting a first time period, wherein said first time period equals twenty-four hours, wherein said first time period comprises 48 first time intervals;

commencing said first time period at a first time and ending said first time period at a third time;

performing one or more database queries during said first time period;

recording the number of database queries performed during each of said two or more first time intervals;

determining the least utilized first time interval, wherein said least utilized first time interval comprises the one of said two or more first time intervals during which the fewest database queries were performed, wherein said least utilized first time interval commenced at a second time;

calculating a first time difference between said first time and said second time;

scheduling said database maintenance at a fourth time, wherein said fourth time equals said third time plus said first time difference.

2. The method of claim 1, wherein said database comprises the identity, location, and category of one or more information storage media moveably disposed in a data storage and retrieval system.

3. The method of claim 2, wherein said data storage and retrieval system comprises one or more data storage devices, and wherein said data storage and retrieval system is capable of communicating with one or more host computers.

4. The method of claim 3, further comprising the steps of:

receiving a request from said one or more host computers to mount a first information storage medium in one of said one or more data storage devices, wherein said first information storage medium comprises one of said one or more information storage media; and

identifying said first information storage medium by performing one of said one or more database queries.

5. The method of claim 4, wherein said request comprises category information specific to said first information storage medium.

6. The method of claim 5, further comprising the step of forming a prediction of future database usage based upon said number of database inquiries performed during said first time period.

7. The method of claim 1, further comprising the steps of:

commencing a second time period at said third time, wherein said second time period comprises two or more second time intervals, and wherein said second time period ends at a sixth time;

performing one or more second database queries during said second time period;

recording the number of said second database queries performed during each of said two or more second time intervals;

determining the least utilized second time interval, wherein said least utilized second time interval comprises the one of said two or more second time intervals during which the fewest second database inquiries were performed, wherein said least utilized second time interval commenced at a fifth time;

calculating a second time difference between said third time and said fifth time;

scheduling second database maintenance at a seventh time, wherein said seventh time equals said sixth time plus said second time difference.

8. The method of claim 7, further comprising the steps of:

forming a first prediction of future database usage based upon said number of database inquiries performed during said first time period; and

revising said prediction based upon said number of second database inquiries performed during said second time period.

9. A method to schedule database maintenance, wherein said database comprises information relating to one or more information storage media disposed in a data storage and retrieval system, wherein said data storage and retrieval system is capable of communication with one or more host computers, said method comprising the following steps in the following order:

(1) selecting a time interval Δ

T, wherein Δ

T equals 24 hours divided by (N),

wherein (N) is greater than or equal to 2;

(2) setting (j)=1;

(3) commencing time period P(j) at time T(1)(j), wherein time period P(j) comprises a plurality of time intervals Δ

T(i)(j), wherein (i) is greater than or equal to 1 and less than or equal to N;

(4) performing a plurality of database queries during said time period P(j), and recording the number of database queries R(i)(j) performed during each of said time intervals Δ

T(i)(j);

(5) establishing the maintenance time interval Δ

T(_MAIN)(2), wherein the value of (_MAIN) is set to the value of (i) for the one of said plurality of time intervals Δ

T(i)(1) during which the fewest number of database inquiries R(i)(1) were performed;

(6) setting R_REF equal to R(_MIN−

1)(1), wherein R(_MIN−

1)(1) comprises the number of database inquiries performed during the time interval Δ

T(_MIN−

1)(1), wherein Δ

T(_MIN−

1)(1) comprises the time interval immediately preceding the one of said plurality of time intervals Δ

T(i)(1) during which the fewest number of database inquiries R(i)(1) were performed;

(7) setting (j)=(j)+1;

(8) commencing time period P(j) at time T(1)(j), wherein time period P(j) comprises a plurality of time intervals Δ

T(i)(j), wherein (i) is greater than or equal to 1 and less than or equal to N;

(9) recording the number of database queries R(i)(j) performed during each of said time intervals Δ

T(i)(j); and

(10) determining if R(_MIN−

1)(j) is greater than about 1.1*R_REF;

(11) operative if R(_MIN−

1)(j) is not greater than about 1.1*R_REF;

performing database maintenance during time interval Δ

T(_MAIN)(j); and

repeating steps (7), (8), (9), (10)(11), and (12);

(12) operative if R(_MIN−

1)(j) is greater than about 1.1*R_REF;

determining Δ

T(_MIN)(j), wherein Δ

T(_MIN)(j) comprises the one of said plurality of time intervals Δ

T(i)(j) during which the fewest number of database inquiries R(i)(j) were performed;

setting maintenance time interval Δ

T(_MAIN)(j+1), wherein the value of (_MAIN) is set to the value of (i) for the one of said plurality of time intervals Δ

T(i)(j) during which the fewest number of database inquiries R(i)(j) were performed;

setting R_REF equal to R(_MIN−

1)(j), wherein R(_MIN−

1)(j) comprises the number of database inquiries performed during the time interval Δ

T(_MIN−

1)(j), wherein Δ

T(_MIN−

1)(j) comprises the time interval immediately preceding the one of said plurality of time intervals Δ

T(i)(j) during which the fewest number of database inquiries R(i)(j) were performed repeating steps (7), (8), (9), (10), (11), and (12).

10. A data storage and retrieval system comprising a computer useable medium having computer readable program code disposed therein to schedule database maintenance, wherein said database comprises information relating to one or more information storage media disposed in said data storage and retrieval system, the computer readable program code comprising a series of computer readable program steps to effect:

selecting a first time period, wherein said first time period equals twenty-four hours, wherein said first time period comprises 48 first time intervals;

commencing said first time period at a first time and ending said first time period at a third time;

performing one or more database queries during said first time period;

recording the number of database queries performed during each of said two or more first time intervals;

determining the least utilized first time interval, wherein said least utilized first time interval comprises the one of said two or more first time intervals during which the fewest database queries were performed, wherein said least utilized first time interval commenced at a second time;

calculating the a first time difference between said first time and said second time;

scheduling said database maintenance at a fourth time, wherein said fourth time equals said third time plus said first time difference.

11. The data storage and retrieval system of claim 10, wherein said database comprises the identity, location, and category of one or more information storage media moveably disposed in said data storage and retrieval system.

12. The data storage and retrieval system of claim 11, wherein said data storage and retrieval system comprises one or more data storage devices, and wherein said data storage and retrieval system is capable of communicating with one or more host computers, said computer readable program code comprising a series of computer readable program steps to effect:

receiving a request from said one or more host computers to mount a first information storage medium in one or said one or more data storage devices, wherein said first information storage medium comprises one of said one or more information storage media; and

identifying said first information storage medium by performing one of said one or more database queries.

13. The data storage and retrieval system of claim 12, wherein said request comprises category information specific to said first information storage medium.

14. The data storage and retrieval system of claim 13, wherein said computer readable program code further comprises a series of computer readable program steps to effect forming a prediction of future database usage based upon said number of database inquiries performed during said first time period.

15. The data storage and retrieval system of claim 10, wherein said computer readable program code further comprises a series of computer readable program steps to effect:

commencing a second time period at said third time, wherein said second time period comprises two or more second time intervals, and wherein said second time period ends at a sixth time;

performing one or more second database queries during said second time period;

recording the number of said second database queries performed during each of said two or more second time intervals;

determining the least utilized second time interval, wherein said least utilized second time interval comprises the one of said two or more second time intervals during which the fewest second database inquiries were performed, wherein said least utilized second time interval commenced at a fifth time;

calculating the a second time difference between said third time and said fifth time;

scheduling second database maintenance at a seventh time, wherein said seventh time equals said sixth time plus said second time difference.

16. The data storage and retrieval system of claim 15, wherein said computer readable program code further comprises a series of computer readable program steps to effect:

forming a first prediction of future database usage based upon said number of database inquiries performed during said first time period; and

revising said prediction based upon said number of second database inquiries performed during said second time period.

17. A data storage and retrieval system comprising a computer useable medium having computer readable program code disposed therein to schedule database maintenance, wherein said database comprises information relating to one or more information storage media disposed in said data storage and retrieval system, the computer readable program code comprising a series of computer readable program steps to effect:

(1) selecting a time interval Δ

T, wherein Δ

T equals 24 hours divided by (N), wherein (N) is greater than or equal to 2;

(2) setting (j)=1;

(3) commencing time period P(j) at time T(1)(j), wherein time period P(j) comprises a plurality of time intervals Δ

T(i)(j), wherein (i) is greater than or equal to 1 and less than or equal to (N);

(4) performing a plurality of database queries during said time period P(j), and recording the number of database queries R(i)(j) performed during each of said time intervals Δ

T(i)(j);

(5) establishing the maintenance time interval Δ

T(_MAIN)(2), wherein (_MAIN) is set to the value of (i) for the one of said plurality of time intervals Δ

T(i)(1) during which the fewest number of database inquiries R(i)(1) were performed;

(6) setting R_REF equal to R(_MIN−

1)(1), wherein R(_MIN−

1)(1) comprises the number of database inquiries performed during the time interval Δ

T(_MIN−

1)(1), wherein Δ

T(_MIN−

1)(1) comprises the time interval immediately preceding the one of said plurality of time intervals Δ

T(i)(1) during which the fewest number of database inquiries R(i)(1) were performed;

(7) setting (j)=(j)+1;

(8) commencing time period P(j) at time T(1)(j), wherein time period P(j) comprises a plurality of time intervals Δ

T(i)(j), wherein (i) is greater than or equal to 1 and less than or equal to (N);

(9) recording the number of database queries R(i)(j) performed during each of said time intervals Δ

T(i)(j); and

(10) determining if R(_MIN−

1)(j) is greater than about 1.1*R_REF;

(11) operative if R(_MIN−

1)(j) is not greater than about 1.1*R_REF.

performing database maintenance during time interval Δ

T(_MAIN)(j); and

repeating steps (7), (8), (9), (10)(11), and (12);

(12) operative if R(_MIN−

1)(j) is greater than about 1.1*R_REF;

determining Δ

T(_MIN)(j), wherein Δ

T(_MIN)(j) comprises the one of said plurality of time intervals Δ

T(i)(j) during which the fewest number of database inquiries R(i)(j) were performed;

setting maintenance time interval Δ

T(_MAIN)(j+1), wherein the value of (_MAIN) is set to the value of (i) for the one of said plurality of time intervals Δ

T(i)(j) during which the fewest number of database inquiries R(i)(j) were performed;

setting R_REF equal to R(_MIN−

1)(j), wherein R(_MIN−

1)(j) comprises the number of database inquiries performed during the time interval Δ

T(_MIN−

1)(j), wherein Δ

T(_MIN−

1)(j) comprises the time interval immediately preceding the one of said plurality of time intervals Δ

T(i)(j) during which the fewest number of database inquiries R(i)(j) were performed repeating steps (7), (8), (9), (10), (11), and (12).

18. A computer program product usable with a programmable computer processor having computer readable program code embodied therein to schedule database maintenance, wherein said database comprises information relating to one or more information storage media disposed in a data storage and retrieval system, comprising:

computer readable program code which causes said programmable computer processor to select a first time period, wherein said first time period comprises two or more first time intervals;

computer readable program code which causes said programmable computer processor to commence said first time period at a first time and ending said first time period at a third time;

computer readable program code which causes said programmable computer processor to perform one or more database queries during said first time period;

computer readable program code which causes said programmable computer processor to record the number of database queries performed during each of said two or more first time intervals;

computer readable program code which causes said programmable computer processor to determine the least utilized first time interval, wherein said least utilized first time interval comprises the one of said two or more first time intervals during which the fewest database queries were performed, wherein said least utilized first time interval commenced at a second time;

computer readable program code which causes said programmable computer processor to calculate the a first time difference between said first time and said second time;

computer readable program code which causes said programmable computer processor to schedule said database maintenance at a fourth time, wherein said fourth time equals said third time plus said first time difference;

computer readable program code which causes said programmable computer processor to receive a request from said one or more host computers to mount a first information storage medium in one of said one or more data storage devices, wherein said first information storage medium comprises one of said one or more information storage media;

computer readable program code which causes said programmable computer processor to identify said first information storage medium by performing one of said one or more database queries;

computer readable program code which causes said programmable computer processor to form a prediction of future database usage based upon said number of database inquiries performed during said first time period.

19. The computer code product of claim 18, further comprising computer readable program code which causes said programmable computer processor to commence a second time period at said third time, wherein said second time period comprises two or more second time intervals, and wherein said second time period ends at a sixth time;

computer readable program code which causes said programmable computer processor to perform one or more second database queries during said second time period;

computer readable program code which causes said programmable computer processor to record the number of said second database queries performed during each of said two or more second time intervals;

computer readable program code which causes said programmable computer processor to determine the least utilized second time interval, wherein said least utilized second time interval comprises the one of said two or more second time intervals during which the fewest second database inquiries were performed, wherein said least utilized second time interval commenced at a fifth time;

computer readable program code which causes said programmable computer processor to calculate a second time difference between said third time and said fifth time;

computer readable program code which causes said programmable computer processor to schedule second database maintenance at a seventh time, wherein said seventh time equals said fifth time plus said second time difference.

20. The computer code product of claim 19, further comprising computer readable program code which causes said programmable computer processor to revise said prediction based upon said number of second database inquiries performed during said second time period.