Cost/benefit based checkpointing while maintaining a logical standby database
A method, system, and computer program product for generating checkpoint information in a database management system that determines the frequency of checkpointing of transactions based on a cost/benefit tradeoff analysis. A method for generating checkpointing information in a database management system comprises the steps of generating redo information based on transactions processed by the database management system, determining a cost of recovery using the redo information, determining a cost of taking a checkpoint, receiving a user specification of acceptable recovery delay and acceptable checkpoint overhead, and taking a checkpoint when cost effective to do so based on the cost of recovery using the redo information, the cost of taking the checkpoint and the user-specified acceptable delay and overhead.
1. A method for generating checkpointing information in a database management system comprising the steps of:
generating redo information based on transactions processed by the database management system; and
in response to generation of a block of redo information, determining whether to take a checkpoint by:
determining a cost of recovery using the redo information comprising the steps of:
determining a cost of recovery using the redo information from a first checkpoint to a point in time with no checkpoint between the first checkpoint and the point in time, and
determining a cost of recovery using the redo information from a first checkpoint to the point in time with a second checkpoint between the first checkpoint and the point in time;
determining a cost of taking a checkpoint by:
generating an inadvisable interval list over which a checkpoint is not to be taken by:
determining an inadvisable checkpoint interval by keeping track of a size of uncheckpointed data contained in a transaction and comparing the size of uncheckpointed data contained in the transaction with a threshold value computed from the user-supplied acceptable delay and acceptable overhead parameters, and
inserting an interval relating to the transaction in the inadvisable interval list if the size of uncheckpointed data contained in the transaction exceeds the threshold value, and
maintaining the inadvisable interval list as redo records are processed and consumed;
receiving a user specification of acceptable recovery delay and acceptable checkpoint overhead; and
taking a checkpoint when cost effective to do so based on the cost of recovery using the redo information, the cost of taking the checkpoint and the user-specified acceptable delay and overhead.
2. The method of claim 1 , wherein step of maintaining the inadvisable interval list as redo records are processed and consumed comprises the steps of:
by maintaining a sorted list of all inadvisable checkpoint interval;
removing an interval from the inadvisable list of intervals when a transaction that contributed to the interval is consumed.
3. The method of claim 2 , wherein the threshold equals the product of the acceptable recovery delay, the acceptable checkpoint overhead, and a rate of processing of the redo information.
4. The method of claim 3 , wherein the step of taking a checkpoint based on the cost of recovery using the redo information and on the cost of taking a checkpoint comprises the step of:
taking the second checkpoint if the cost of recovery using the redo information from a first checkpoint to the point in time with a second checkpoint between the first checkpoint and the point in time is less than the cost of recovery using the redo information from a first checkpoint to the point in time with no checkpoint between the first checkpoint and the point in time.
5. The method of claim 4 , further comprising the step of determining the cost-effectiveness of a third checkpoint, comprising the steps of:
checking presence of a timestamp of the third checkpoint in the inadvisable interval list; and
determining that the third checkpoint is not cost-effective, if the timestamp of the third checkpoint is present in the inadvisable interval list.
6. A system for generating checkpointing information in a database management system comprising:
a processor operable to execute computer program instructions;
a memory operable to store computer program instructions executable by the processor; and
computer program instructions stored in the memory and executable to perform the steps of:
generating redo information based on transactions processed by the database management system; and
in response to generation of a block of redo information, determining whether to take a checkpoint by:
determining a cost of recovery using the redo information comprising the steps of:
determining a cost of recovery using the redo information from a first checkpoint to a point in time with no checkpoint between the first checkpoint and the point in time, and
determining a cost of recovery using the redo information from a first checkpoint to the point in time with a second checkpoint between the first checkpoint and the point in time;
determining a cost of taking a checkpoint by:
generating an inadvisable interval list over which a checkpoint is not to be taken by:
determining an inadvisable checkpoint interval by keeping track of a size of uncheckpointed data contained in a transaction and comparing the size of uncheckpointed data contained in the transaction with a threshold value computed from the user-supplied acceptable delay and acceptable overhead parameters, and
inserting an interval relating to the transaction in the inadvisable interval list if the size of uncheckpointed data contained in the transaction exceeds the threshold value, and
maintaining the inadvisable interval list as redo records are processed and consumed;
receiving a user specification of acceptable recovery delay and acceptable checkpoint overhead; and
taking a checkpoint when cost effective to do so based on the cost of recovery using the redo information, the cost of taking the checkpoint and the user-specified acceptable delay and overhead.
7. The system of claim 6 , wherein step of maintaining the inadvisable interval list as redo records are processed and consumed comprises the steps of:
by maintaining a sorted list of all inadvisable checkpoint interval;
removing an interval from the inadvisable list of intervals when a transaction that contributed to the interval is consumed.
8. The system of claim 7 , wherein the threshold equals the product of the acceptable recovery delay, the acceptable checkpoint overhead, and a rate of processing of the redo information.
9. The system of claim 8 , wherein the step of taking a checkpoint based on the cost of recovery using the redo information and on the cost of taking a checkpoint comprises the step of:
taking the second checkpoint if the cost of recovery using the redo information from a first checkpoint to the point in time with a second checkpoint between the first checkpoint and the point in time is less than the cost of recovery using the redo information from a first checkpoint to the point in time with no checkpoint between the first checkpoint and the point in time.
10. The system of claim 9 , further comprising the step of determining the cost-effectiveness of a third checkpoint, comprising the steps of:
checking presence of a timestamp of the third checkpoint in the inadvisable interval list; and
determining that the third checkpoint is not cost-effective, if the timestamp of the third checkpoint is present in the inadvisable interval list.
11. A computer program product for generating checkpointing information in a database management system comprising:
a computer readable storage medium;
computer program instructions, recorded on the computer readable storage medium, executable by a processor, for performing the steps of
generating redo information based on transactions processed by the database management system; and
in response to generation of a block of redo information, determining whether to take a checkpoint by:
determining a cost of recovery using the redo information comprising the steps of:
determining a cost of recovery using the redo information from a first checkpoint to a point in time with no checkpoint between the first checkpoint and the point in time, and
determining a cost of recovery using the redo information from a first checkpoint to the point in time with a second checkpoint between the first checkpoint and the point in time;
determining a cost of taking a checkpoint by:
generating an inadvisable interval list over which a checkpoint is not to be taken by:
determining an inadvisable checkpoint interval by keeping track of a size of uncheckpointed data contained in a transaction and comparing the size of uncheckpointed data contained in the transaction with a threshold value computed from the user-supplied acceptable delay and acceptable overhead parameters, and
inserting an interval relating to the transaction in the inadvisable interval list if the size of uncheckpointed data contained in the transaction exceeds the threshold value, and
maintaining the inadvisable interval list as redo records are processed and consumed;
receiving a user specification of acceptable recovery delay and acceptable checkpoint overhead; and
taking a checkpoint when cost effective to do so based on the cost of recovery using the redo information, the cost of taking the checkpoint and the user-specified acceptable delay and overhead.
12. The computer program product of claim 11 , wherein step of maintaining the inadvisable interval list as redo records are processed and consumed comprises the steps of:
by maintaining a sorted list of all inadvisable checkpoint interval;
removing an interval from the inadvisable list of intervals when a transaction that contributed to the interval is consumed.
13. The computer program product of claim 12 , wherein the threshold equals the product of the acceptable recovery delay, the acceptable checkpoint overhead, and a rate of processing of the redo information.
14. The computer program product of claim 13 , wherein the step of taking a checkpoint based on the cost of recovery using the redo information and on the cost of taking a checkpoint comprises the step of:
taking the second checkpoint if the cost of recovery using the redo information from a first checkpoint to the point in time with a second checkpoint between the first checkpoint and the point in time is less than the cost of recovery using the redo information from a first checkpoint to the point in time with no checkpoint between the first checkpoint and the point in time.
15. The computer program product of claim 14 , further comprising the step of determining the cost-effectiveness of a third checkpoint, comprising the steps of:
checking presence of a timestamp of the third checkpoint in the inadvisable interval list; and
determining that the third checkpoint is not cost-effective, if the timestamp of the third checkpoint is present in the inadvisable interval list.