Automatic buffer pool page fix prioritization for database tuning

1. A computer-implemented method for identifying a buffer pool for page fixing, the method comprising:

for each database subsystem in a plurality of database subsystems running in a logical partition (LPAR):

collecting data from a plurality of buffer pools on a periodic interval;

monitoring real storage availability on the LPAR;

calculating an input/output (I/O) intensity score for each buffer pool from the plurality of buffer pools for the periodic interval;

ranking the plurality of buffer pools based on the I/O intensity score;

calculating a cumulative I/O intensity score for each buffer pool from the plurality of buffer pools across a cycle of multiple periodic intervals;

ranking the plurality of buffer pools based on the cumulative I/O intensity score;

selecting a buffer pool candidate for page fixing, wherein the buffer pool candidate for page fixing is a highest-ranked buffer pool from the plurality of buffer pools during the cycle based on the cumulative I/O intensity score that satisfies the real storage availability on the LPAR; and

page fixing the buffer pool candidate selected for page fixing.

2. The computer-implemented method as in claim 1 , wherein calculating the cumulative I/O intensity score for each buffer pool from the plurality of buffer pools is based on a number of times the buffer pool was ranked in multiple different top rankings based on the I/O intensity score.

3. The computer-implemented method as in claim 1 , further comprising:

monitoring an availability of frame sizes on the LPAR;

selecting an optimal frame size based on the availability of frame sizes on the LPAR; and

page fixing the buffer pool candidate selected for page fixing using the optimal frame size.

4. The computer-implemented method as in claim 1 , further comprising:

receiving user-configurable thresholds for LPAR parameters; and

calculating default thresholds for the LPAR parameters when the user-configurable thresholds are not received, wherein the buffer pool candidate for page fixing is the highest-ranked buffer pool from the plurality of buffer pools during the cycle based on the cumulative I/O intensity score that satisfies the real storage availability on the LPAR, the user-configurable thresholds for the LPAR parameters, and the default thresholds for the LPAR parameters.

5. The computer-implemented method as in claim 1 , further comprising:

receiving a query for an analysis of a specific buffer pool from the plurality of buffer pools; and

in response to the query, outputting a ranking and an I/O intensity score for the specific buffer pool, wherein the ranking is based on the I/O intensity score for the specific buffer pool.

6. The computer-implemented method as in claim 1 , further comprising:

receiving a query for a ranking of top candidates for page fixing from a specific database subsystem; and

in response to the query, outputting the ranking of the top candidates for page fixing from the specific database subsystem.

7. A computer program product for identifying a buffer pool for page fixing, the computer program product being tangibly embodied on a non-transitory computer-readable medium and including executable code that, when executed, causes a computing device, for each database subsystem in a plurality of database subsystems running in a logical partition (LPAR), to:

collect data from a plurality of buffer pools on a periodic interval;

monitor real storage availability on the LPAR;

calculate an input/output (I/O) intensity score for each buffer pool from the plurality of buffer pools for the periodic interval;

rank the plurality of buffer pools based on the I/O intensity score;

calculate a cumulative I/O intensity score for each buffer pool from the plurality of buffer pools across a cycle of multiple periodic intervals;

rank the plurality of buffer pools based on the cumulative I/O intensity score;

select a buffer pool candidate for page fixing, wherein the buffer pool candidate for page fixing is a highest-ranked buffer pool from the plurality of buffer pools during the cycle based on the cumulative I/O intensity score that satisfies the real storage availability on the LPAR; and

page fix the buffer pool candidate selected for page fixing.

8. The computer program product of claim 7 , wherein calculating the cumulative I/O intensity score for each buffer pool from the plurality of buffer pools is based on a number of times the buffer pool was ranked in multiple different top rankings based on the I/O intensity score.

9. The computer program product of claim 7 , further comprising executable code that, when executed, causes the computing device to:

monitor an availability of frame sizes on the LPAR;

select an optimal frame size based on the availability of frame sizes on the LPAR; and

page fix the buffer pool candidate selected for page fixing using the optimal frame size.

10. The computer program product of claim 7 , further comprising executable code that, when executed, causes the computing device to:

receive user-configurable thresholds for LPAR parameters; and

calculate default thresholds for the LPAR parameters when the user-configurable thresholds are not received, wherein the buffer pool candidate for page fixing is the highest-ranked buffer pool from the plurality of buffer pools during the cycle based on the cumulative I/O intensity score that satisfies the real storage availability on the LPAR, the user-configurable thresholds for the LPAR parameters, and the default thresholds for the LPAR parameters.

11. The computer program product of claim 7 , further comprising executable code that, when executed, causes the computing device to:

receive a query for an analysis of a specific buffer pool from the plurality of buffer pools; and

in response to the query, output a ranking and an I/O intensity score for the specific buffer pool, wherein the ranking is based on the I/O intensity score for the specific buffer pool.

12. The computer program product of claim 7 , further comprising executable code that, when executed, causes the computing device to:

receive a query for a ranking of top candidates for page fixing from a specific database subsystem; and

in response to the query, output the ranking of the top candidates for page fixing from the specific database subsystem.

13. A system for identifying a buffer pool for page fixing, the system comprising:

at least one processor; and

a non-transitory computer-readable medium comprising instructions that, when executed by the at least one processor, cause the system to:

for each database subsystem in a plurality of database subsystems running in a logical partition (LPAR):

collect data from a plurality of buffer pools on a periodic interval;

monitor real storage availability on the LPAR;

calculate an input/output (I/O) intensity score for each buffer pool from the plurality of buffer pools for the periodic interval;

rank the plurality of buffer pools based on the I/O intensity score;

calculate a cumulative I/O intensity score for each buffer pool from the plurality of buffer pools across a cycle of multiple periodic intervals;

rank the plurality of buffer pools based on the cumulative I/O intensity score;

select a buffer pool candidate for page fixing, wherein the buffer pool candidate for page fixing is a highest-ranked buffer pool from the plurality of buffer pools during the cycle based on the cumulative I/O intensity score that satisfies the real storage availability on the LPAR; and

page fix the buffer pool candidate selected for page fixing.

14. The system of claim 13 , wherein calculating the cumulative I/O intensity score for each buffer pool from the plurality of buffer pools is based on a number of times the buffer pool was ranked in multiple different top rankings based on the I/O intensity score.

15. The system of claim 13 , further comprising instructions that, when executed by the at least one processor, cause the system to:

monitor an availability of frame sizes on the LPAR;

select an optimal frame size based on the availability of frame sizes on the LPAR; and

page fix the buffer pool candidate selected for page fixing using the optimal frame size.

16. The system of claim 13 , further comprising instructions that, when executed by the at least one processor, cause the system to:

receive user-configurable thresholds for LPAR parameters; and

calculate default thresholds for the LPAR parameters when the user-configurable thresholds are not received, wherein the buffer pool candidate for page fixing is the highest-ranked buffer pool from the plurality of buffer pools during the cycle based on the cumulative I/O intensity score that satisfies the real storage availability on the LPAR, the user-configurable thresholds for the LPAR parameters, and the default thresholds for the LPAR parameters.

17. The system of claim 13 , further comprising instructions that, when executed by the at least one processor, cause the system to:

receive a query for an analysis of a specific buffer pool from the plurality of buffer pools; and

in response to the query, output a ranking and an I/O intensity score for the specific buffer pool, wherein the ranking is based on the I/O intensity score for the specific buffer pool.

18. The system of claim 13 , further comprising instructions that, when executed by the at least one processor, cause the system to:

receive a query for a ranking of top candidates for page fixing from a specific database subsystem; and

in response to the query, output the ranking of the top candidates for page fixing from the specific database subsystem.