WO2002019168A1 - Heuristic automated method for ideal bufferpool tuning in a computer database - Google Patents

Abstract

The present invention is a method for automating database bufferpool tuning for optimized performance that employs certain heuristic algorithms to achieve its goals. Over a period of time, memory (bufferpool) performance is measured and accumulated in a repository. The repository becomes a knowledge base that is accessed by the algorithms and the ideal memory (bufferpool) configurations, which optimize database performance, are learned and implemented. The sampling of performance continues at regular intervals and the knowledge base continues to grow. As knowledge continues to accumulate, the algorithms are forbidden from becoming complacent. The ideal bufferpool memory configurations are regularly reevaluated to ensure they continue to be optimal given potential changes in the database's use or access patterns.

Description

HEURISTIC AUTOMATED METHOD FOR IDEAL BUFFERPOOL TUNING IN A

COMPUTER DATABASE

TECHNICAL FIELD

This invention relates generally to the field of computer databases, and more particularly to a process and method for a heuristic automated approach for ideal bufferpool tuning in a computer database, providing a means of automated and semi-automated adjustments, visual feedback, and historical data retention.

COPYRIGHT NOTICE

A portion of the disclosure of this patent document contains material that is subject to copyright protection. The copyright owner has no objection to the facsimile reproduction by anyone of the patent document or the patent disclosure, as it appears in the Patent and Trademark Office patent file or records, but otherwise reserves all copyright rights whatsoever.

BACKGROUND OF THE INVENTION Historically database administrators have had to use guesswork and trial and error to identify the optimal bufferpool settings in a database, and to try and determine the best mix between memory allocation and disk thrashing. A bufferpool setting that works well for one or more jobs may perform sub optimally on other jobs and settings. It is a common practice of database administrators to set higher memory allocations to facilitate best-perceived performance. Moreover, there have not been effective automated methods of auto tuning buffer pool sizes, nor of using the computer itself to record, analyze, visualize and adjust the complex mix of users, databases, related access profiles, and performance.

In many situations, once settings are applied that appear to work reasonably well, even though there may be an over allocation of system resources, the settings may be left alone for lack of an effective means for tuning the bufferspace. Then, if systems resources are constrained, these settings may be cut back, possibly resulting in suboptimal performance conditions. Without an ongoing means of monitoring usage, and the effects of bufferpool modifications on overall system performance across a wide range of users and jobs, it is difficult for today's system administrator to optimize either the system or the system settings. One possible outcome of the lack of automated bufferpool tuning methods is over allocation of system memory and disk drives, resulting in an impact on system and operating costs. Another outcome of the present situation is poor performance given a system's resource allocation compared to what might be achieved if there were a means of optimization.

Databases today make extensive use of machine memory to improve performance. Sometimes these memory areas are called caches, heaps, and sometimes pools or bufferpools. Most relational database engines make use of all three memory types for the purpose of minimizing input/output (I/O) to disk, which is generally regarded as orders of magnitude slower than accessing data already in memory.

Caches typically store information about data definitions in memory to help the database engine improve performance. Other caches may store information about programs and configuration values. Heaps are typically areas of memory dedicated to resolving temporary or transient program tasks, such as sorting data, joining tables together, materializing views, and other memory work areas required by the database engine to complete its tasks. Bufferpools are areas of memory dedicated to storing user or application data. When a user or application queries the database, the bufferpools are first checked by the database engine to determine if the required data is already resident in memory. This is often referred to as a Logical Read. If the required data is not available in the bufferpools, then the database engine must issue I/O requests to disk to get the required data. This is often referred to as a Physical Read. Physical reads to disk can occur synchronously or asynchronously. If the physical reads are synchronous, then the user application must wait for the data to be retrieved from disk prior to continuing. Some database engines, like IBM's DB2, also implement asynchronous reads. When the database engine reads data asynchronously, data is pre-fetched into bufferpools typically in advance of the user application's requirement for the data. In this way, when the data is pre-fetched, the user application generally does not experience any I/O delay - even though physical I/O is taking place on its behalf.

Whether to pre-fetch data asynchronously or to read it synchronously is a decision that is made by the database engine optimizer. Most relational database engines, including IBM's DB2, implement advanced optimizer technology that considers the data request, configuration values, and statistical values, and determines the quickest way, or least cost method (in terms of I/O and CPU), of accessing the data. The distinction between synchronous and asynchronous read operations is important for the purpose of understanding the tuning techniques described herein, and an appreciation of the advantages of the present invention. Hence, the following discussion will elaborate further using examples. Synchronous Reads Consider the telephone operator who is taking a call from a customer. The customer provides its customer number. The operator accesses the database record containing the customer's name and other attributes. A SINGLE, or a very few, record or records is returned from the database to the operator. This type of precise read is typical of Online Transaction Processing (OLTP) applications. It is also typical and expected that the optimizer would choose to perform a SYNCHRONOUS read of the required data (physical read) if it was not already available in the bufferpool (logical read). Asynchronous Reads Consider the marketing analyst who wants to gain a better understanding of the locations of his company's customers. A report showing a breakdown of customers by U.S. State may be useful in determining where to best spend advertising dollars. This is a Decision Support Services (DSS) query. This type of query typically needs to access the entire database (versus a single or limited set of records), and it is generally expected that the optimizer would choose to pre- fetch the required data via asynchronous reads into the bufferpools. Assuming the data is distributed across multiple disks/storage devices, the database engine will generally invoke multiple asynchronous pre-fetch tasks, in parallel, to bring the data into memory as fast as possible where it can be tabulated by the database engine. The distinction between synchronous versus asynchronous is important because the tuning method for bufferpools must vary based on the predominant type of data access. Appropriately sized bufferpools can substantially improve the performance of database queries by eliminating or reducing physical reads to disk. The more frequently data requests can be satisfied from memory instead of via disk access, the better performance will generally be for applications that read data precisely (OLTP type) and, to a lesser extent, for applications that read large volumes of data (DSS type).

OLTP type applications which request specific or small sets of records from the database tend to benefit the most from larger bufferpools because the most frequently accessed data has the opportunity to remain resident in the pool, and thus be satisfied by logical reads only. When the application requests data, and the data is already resident in a bufferpool (logical read), this is called a "hit". When a logical read results in a physical read, this may be thought of as a "miss". The proportion of times that physical reads occurred to satisfy logical reads represents the "miss ratio". The proportion of times that data requests were satisfied entirely by logical reads represents the "hit ratio". Because physical I/O is costly to elapsed time, it follows that the higher the "hit ratio" the better expected performance of the database system. This principle is widely accepted within the database community.

Miss Ratio = Number physical reads/Number Logical Reads Eq. 1

Hit Ratio = 100 — (Number physical reads/Number Logical Reads) Eq. 2

A Bufferpool's hit ratio is, therefore, a very important measurement of its effectiveness in terms of its contribution to benefiting database performance. Furthermore, relational databases often implement index data structures to speed access to data stored therein. These index structures commonly contain key values and pointers to data containing those key values. Indices are typically much smaller in size than the data from which they relate, and they are often frequently accessed. Hence, the hit ratio can be, and should be, further broken down into measurements of INDEX hits and OVERALL hits.

BTREE index structures employ a top most "root" page of index entries that reference one or more tiers of non-leaf pages. Non-leaf pages provide pointers to leaf pages, and index leaf pages ultimately provide pointers to the actual database data pages containing the requested data. It is common for BTREE indexes to have two to five levels. It is important to measure the Index Hit Ratio separately from the Overall Hit Ratio of a bufferpool because indexes generally provide substantial performance improvement to record access. By keeping as many levels of index pages in bufferpool memory as possible, access to underlying database records will be greatly improved.

Consider that the average time, to read a page of information from disk (physical read) is, for example, four milliseconds. If an index has four levels (one root page, two non-leaf levels, and a leaf page level), it could take up to five physical I/O operations to traverse the entire structure (four l/Os to the index and an I/O to the data). Thus access to a database could require approximately 20 milliseconds (5 x 4 milliseconds). If all four of the index pages were resident in the bufferpool and could be satisfied by logical reads (100% Index Hit Ratio), it might take only a single physical I/O, or four milliseconds, to access the required data. Thus, by maximizing bufferpool hit ratios, elapsed time to execute user application data requests can be significantly reduced.

DSS type database queries that request large volumes of records from the database do not benefit from ever increasingly larger bufferpools. First, the likelihood that the data will be re-read is minimal. Second, the large numbers of data records retrieved from disk and placed into the pools tends to rapidly displace previously read data. It is for this reason (data displacement effect), that database administrators will commonly separate OLTP and DSS functions into separate bufferpools, physical space allocations, or databases (OLTP is then isolated from the effects of DSS I/O). Third and finally, because the data is rarely re-read (negating the value of high hit ratios), the most important factor in tuning DSS (predominantly asynchronously read) bufferpools tends to be providing sufficient pool size to accommodate the rate of asynchronous data delivery. It is also important to note that indexes are generally less frequently utilized in a DSS database, or, if they are employed and used by the optimizer, then the indexes tend to be pre-fetched (also called "scanned") as with the actual data.

In terms of bufferpool tuning then, characteristics of OLTP versus DSS type access are quite different and have different tuning objectives. A good relational database engine will provide extensive reporting as to the types and numbers of I/O operations that are being performed. To effectively tune bufferpools, it is imperative to know: the following:

• the number of logical data reads;

^• the number of logical index reads; • the number of physical data reads (total);

• the number of physical index reads (total);

• the number of asynchronous data reads; and

• the number of asynchronous index reads.

Based upon the performance counters above, it is possible to compute: • Index Hit Ratio;

^• Overall Hit Ratio;

^• Percentage of Asynchronous Reads; and

• Percentage of Synchronous Reeds.

If the percentage of Synchronous reads is high, the nature of the data access is typically representative of OLTP application environments - highly random, and selective in nature. For these types of applications, it is important to optimize for the Index Hit Ratio as this will yield the best performance by eliminating the most I/O.

On the other hand, if the percentage of Synchronous reads is low to modest, then access to the data in the bufferpool is more sequential and intensive in nature. In a highly asynchronous application environment, it is important to optimize the overall hit ratio for the bufferpool. The reason, of course, is that index pages will tend to be frequently flushed out of the buffers; that is, if they are even utilized at all. In summary, bufferpool tuning is critically important to achieving optimized database performance, but must be done with knowledge of the access type that is prevalent. In other words, the nature of data access must determine the tuning approach. Generally, the better the bufferpool hit ratios, the better the application performance. In response, it may be posited that if ever-increasing bufferpool sizes generally result in better hit ratios and better performance, why not increase bufferpool sizes infinitely? The answer is that, because bufferpools must be substantiated by real memory (RAM) available to the processor, increasing the bufferpool beyond a reasonable size is cost prohibited. When the demand for memory on the machine exceeds the available real memory, the operating system begins a process of paging, which manages the contents of real memory. Most operating systems implement some form of paging process. When a system pages, the contents of real memory are moved back and forth between memory and disk. When paging is excessive (resulting in many disk l/Os), performance of the entire machine typically degrades substantially. Therefore, when tuning bufferpools, it is important to constrain growth to sizes that can be accommodated^" by the real memory available on the machine. The presence of paging must act as a bufferpool growth inhibitor, or any benefits of reduced bufferpool disk I/O will be diminished due to paging disk I/O. In general, of all forms of I/O in most operating systems, paging I/O is widely considered to be the worst type since the degradation will be indiscriminate across all machine processes.

Heretofore, U.S. Patent No. 5,440,692 taught methods of dynamically expanding and contracting the size of DB2 Bufferpools for the IBM MVS Operating System. However, the patent fails to provide for or suggest determining the ideal sizes of these pools for achieving the optimal performance and hardware resource utilization. Moreover, the patent is directed to the IBM mainframe DB2 environment, whereas the current invention is broadly applicable to database management systems in general.

DISCLOSURE OF INVENTION

An object of the present invention, therefore, is to automate the optimization of bufferpool tuning in a computer database. Another object of the invention is the optimization over time, taking into account performance heuristics and load variances. Another object is to provide automated, ongoing re-tuning to continue the optimization and to account for performance changes and anomalies. A further object of the invention is to enable optimization where the optimization further ensures that the optimization algorithms do not become complacent. Yet a further object of the invention is to require minimal intervention from administrative staff. Still yet another object of the, invention is to remove a barrier to optimal database performance by deploying a self-learning, automated process. Another object of the invention is to optimize usage between synchronous read, asynchronous reads, and combinations of usage. Yet another object of the invention is to provide reporting on the types and numbers of I/O being performed and the use of such information over time (history) for optimization. A further object of the invention is to adjust the bufferpool size setting based on the Index Hit Ratio to yield the best performance by eliminating the most I/O.

In accordance with the present invention, there is provided a heuristic method for the automated tuning of bufferpool size in a computer database, comprising the steps of: automatically characterizing the types of database access; automatically collecting data base access and usage data based upon a predefined schedule; and using the types of database access characterized and database access and usage data, optimizing the allocation of bufferpool memory so as to ensure optimal access time and to minimize disk thrashing.

In accordance with another aspect of the present invention, there is provided a heuristic method for automated bufferpool tuning in a computer, comprising the steps of: automatically identifying types of access; automatically recalibrating the bufferpool sized based upon historical data, including time of day and day of week usage patterns stored in a historical usage database; and optimizing the allocation of computer memory to ensure optimal access time.

In accordance with yet another aspect of the present invention, there is provided in a database, a bufferpool tuning process, comprising the steps of: deploying self-executing program to monitor and collect data relating to the performance of a database bufferpool, including access data and tablespace size; combining the tablespace size and access attributes, to track historical performance; and tuning the bufferpool in response to the historical performance. One aspect of the invention deals with a basic problem in bufferpool optimization - determining optimized sizes for database bufferpools. The present invention addresses such a problem and uses learned performance experiences and data access characteristics (sequential vs. random) so as to dynamically monitor and adjust bufferpool sizes. This aspect is further based on the discovery of a technique that alleviates this problem. The technique includes the steps of: automatic identification of types of database access to enable optimal bufferpool settings; automatic recalibration to ensure optimization for varying usage patterns and users so as to ensure optimal time and minimal disk thrashing; determining the optimal settings for INDEX hits and OVERALL hits; adjusting for variances between characteristics of OLTP and DSS type access; and determining which objects to put into which bufferpools. The techniques described herein are advantageous because they optimize the bufferpools for specific jobs and users, and/or at specific times and usage patterns. In addition, the method employs an algorithm that combines a tablespace size and performance attributes, charts historical performance, and tunes the bufferpools. In addition, it can be used to analyze optimal bufferpooling for the specific users, jobs, and data elements being accessed at any finite point in time. The techniques of the invention are advantageous because they overcome the limitation of human administrator adjustments which may miscalculate a situation and apply suboptimized settings and then not notice and readjust due to lack time, shift changes, etc. By using an iterative cybernetic algorithm, optimization of both bufferpool and performance can be-continual measured, tuned and refined

BRIEF DESCRIPTION OF THE DRAWINGS

Figures 1 - 4 are sections of a flowchart depicting a series of steps performed in accordance with an embodiment of the present invention.

The present invention will be described in connection with a preferred embodiment, however, it will be understood that there is no intent to limit the invention to the embodiment described. Specific details disclosed herein are not to be interpreted as limiting, but rather as a basis for the claims and as a representative basis for teaching one skilled in the art to employ the present invention in virtually any appropriately detailed system, structure or manner. On the contrary, the intent is to cover all alternatives, modifications, and equivalents as may be included within the spirit and scope of the invention as defined by the appended claims.

MODES FOR CARRYING OUT THE INVENTION

For a general understanding of the present invention, reference is made to the drawings. In the drawings, like reference numerals have been used throughout to designate identical elements. In describing the present invention, the following term(s) have been used in the description. "Memory" is any circuitry that can store data, and may include local and remote memory and input/output devices. Examples include semiconductor

ROMs, RAMs, and storage medium access devices with data storage media that they can access. A "memory cell" is memory circuitry that can store a single unit of data, such as a bit or other n-ary digit or an analog value.

A "cache" is an area of memory (e.g., in a database) storing attribute information about the database, its objects, and potentially programs. A "heap" is an area of database memory typically dedicated to temporary work space. Sorts, joins, and other transient database operations are performed in heaps, then heaps are re-used on a subsequent transient request. A "bufferpool" is an area of memory that stores user application data such as names and addresses. Bufferpools are used to improve performance by reducing I/O requests to disk devices.

A "logical read" occurs when the database engine accesses its bufferpool(s) to retrieve certain data. The requested data may or may not already be present in the bufferpool(s). A "physical read" results when a logical read request to a bufferpool determines that the requested data was not already in the bufferpool. A physical read, or a disk I/O, returns data to the bufferpool so that logical reads can be satisfied. "Synchronous reads" are physical reads that cause the user application to wait until they have completed. Generally, synchronous reads are issued by the database engine to retrieve relatively small numbers of records. "Asynchronous reads" are physical reads that allow user applications to process large volumes of data rapidly without having to wait for their completion.

When a database engine asynchronously reads data, it is said to "pre-fetch" it. The term pre-fetch implies that the data is to arrive in the bufferpools in advance of the user-application's need for it; thus, there is no I/O delay incurred by the user-application.

A measure of a bufferpool's ability to successfully avoid disk I/O is the "Hit Ratio". The higher the Hit Ratio, generally the better the performance yielded by the database. The "Index Hit Ratio" is a measure of a bufferpool's ability to successfully access Index data without having to issue disk I/O requests. An Overall Index Hit Ratio is a measure of a bufferpool's ability to successfully access Index and Data without having to issue disk I/O requests. The "Percentage of Synchronous Reads" is the proportion of bufferpool read requests that were synchronous, whereas the "Percentage of Asynchronous Reads" is the proportion of bufferpool read requests that were asynchronous.

With a goal of optimizing bufferpool performance with the constraint of avoiding system paging, the following procedure is preferably employed. In describing the procedure, three distinct phases will be described, including:

Phase 1 - Monitoring Current Bufferpool Performance;

Phase 2 - Archiving current performance results along with configuration values to historical data stores; and Phase 3 - Analyzing the historical data stores and making bufferpool tuning changes based on algorithm results.

Phase 1: Monitoring Current Bufferpool Performance.

On any given day, during a specified period of time, database bufferpool I/O activity must be monitored for each defined bufferpool in the database. For IBM's

DB2 Universal Database, the following command can be used to retrieve information on bufferpool performance from the database engine: db2 "get snapshot for bufferpools on DBNAME," where DBNAME is the name of the database containing the bufferpools to be monitored. Alternatively, DB2 provides other call level interfaces or APIs that can be used to retrieve the same data. An example of the output from this command is provided in Table A.

TABLE A

Bufferpool name = IBMDEFAULTBP

Database name = DBNAME

Database path = /dbO/dbmsil/dbmsil/NODEOQOtj,/5QL00002/

Input database alias = DBNAME

Buffer pool data logical reads = 7361

Buffer pool data physical reads = 3914

Buffer pool data writes = 1885

Buffer pool index logical reads = 50

Buffer pool index physical reads = 38

Total buffer pool read time (ms) = 17458

Total buffer pool write time (ms) = 654

Asynchronous pool data page reads = 3901

Asynchronous pool data page writes = 1885

Buffer pool index writes = 0

Asynchronous pool index page reads = 21 19

Asynchronous pool index page writes = 0

Total elapsed asynchronous read time = 15709

Total elapsed asynchronous write time = 654

Asynchronous read requests = 498

Direct reads = 66

Direct writes = 0

Direct read requests = 4

Direct write requests = 0

Direct reads elapsed time (ms) = 31

Direct write elapsed time (ms) = 0

Database files closed = 0

Data pages copied to extended storage = 0

Index pages copied to extended = 0 storage

Data pages copied from extended = 0 storage

Index pages copied from extended = 0 storage

Using the results from the performance snapshot, values for the following performance measurements must be calculated: 1. Bufferpool Index Hit Ratio (IHR) 2. Bufferpool Overall Hit Ratio (OHR)

3. Synchronous Read Percent (SRP) The formula for IHR is:

IHR= (D*100) / (D+E) Eq. 3 where: D= Buffer pooi index logical reads; and

E= Buffer pool index physical reads Substituting the sample values found in Table A, the IHR for bufferpool IBMDEFAULTBP is:

IHR= 50*100 / ( 50+38) = 5000 / 88= 57 % Eq. 4 The formula for OHR is:

OH R= ((A+D)*1 OO)/(B+E+A+D) Eq. 5 where:

A= Buffer pool data logical reads; B= Buffer pool data physical reads; D= Buffer pool index logical reads; and E= Buffer pool index physical reads. Substituting the sample values found in Figure 1 , the OHR for bufferpool IBMDEFAULTBP is:

OHR=((7361 +50)*100)/(39 4+38+7361 +50)=65% Eq. 6 The formula for SRP is:

SRP = 100— ((H+K) / ((B+E) / 100) Eq. 7

Where:

H is equal to the Asynchronous Pool Data Page Reads K is the Asynchronous Pool Index Page Reads B is the Bufferpool Data Physical Reads, and

E is the Bufferpool Index Physical Reads.

Phase 2: Archiving current performance results along with configuration values to historical data stores. Phase 2 determines the optimal size of the bufferpool, given actual usage history, as defined in more detail in Phase 3. In Phase 2 on a regular, periodic basis, all bufferpool performance data (including all values in Table A) plus all computed performance metrics (for example, Index Hit Ratio, Synch Read Percentage), and current Bufferpool configuration (for example, size and related attributes), must all be saved in a different file or database table for later analysis.

Phase 3: Analyzing the historical data stores and making bufferpool tuning changes based on algorithm results.

Phase III provides for continual iteration, over time, to compute the optimal bufferpool size given recent history and to augment or replace the previous bufferpool size/setting, and identify if the system was paging memory, in which case any bufferpool increase can be modified or canceled.

In one embodiment the present invention may be implemented as a method embodied in the form of a software program or code to be performed on computer. An implementation of such a program could be executable on an IBM mainframe or mini-computer machine or equivalent running IBM MVS/ESA, OS/390, AIX, AS/400, OS/2, and IBM Database 2, or an Oracle database, running on any of these aforementioned computers, or Sun Solaris, etc., or on Microsoft Access, or Borland Paradox running on a personal computer, or on any other type of computer systems handling database transactions.

For purposes of further illustration, the. present application includes an Appendix having selected source code extracts from an actual software program performing bufferpool management in accordance with the present invention. In the interest of clarity, only selected features of an actual program are included so as to teach a person skilled in the art of computer programming and database management how to create and utilize this function. It should be appreciated that in the development of the embodiment described (as in any software development project), numerous programming decisions must be made to achieve specific goals, and that such goals may vary from one implementation to another. It should be further appreciated that such a development effort might be complex and time- consuming, but would nevertheless be a routine undertaking of system program development for those of ordinary skill having the benefit of this disclosure. In working' with database tools such as Oracle, IBM DB2, and others, the system administrator sets up the size of the bufferpool based on their best guess for an allocation that provides an efficient use of the current, and comfortably available, RAM and their "wag" (i.e., wild assumed guess) relevant to system optimization. The devoted administrator will then apply his or her skills of observation and experimentation to achieve reasonable performance, and then check the system's performance from time to time to validate or modify the current setting.

Although this approached has seemingly served the industry well for many years, many factors can result in sub-optimization using the standard approach described. For example, certain jobs may run at night, or .on weekends, to minimize contention with normal business day users. These jobs may have very different memory allocation optimization parameters than those viewed by the administrator during his or her availability.

Furthermore, even during the course of a normal business day, the nature of the jobs and users on the system at any given time may vary. There may or may not be a pattern to these usage actualities. And, it may or may not be the case that the bufferpool tuning parameters established by the system administrator at any given point of time are, in fact, going to be the ones that are optimal for that job.

Hence, as the flowchart of the heuristic automated bufferpool informed tuning algorithm is now described in detail, the reader will appreciate that the system is designed to store usage data, test for optimization, reset the data, and monitor ongoing operations using cybernetic principles and iteration to ensure maintenance of an optimal bufferpool size. The system further resets itself occasionally to ensure that the bufferpool for the given moment in time is in place, and that the system stores usage patterns by time slices during the day; noting variances from day to day and time to time, so as to establish and take advantage of changing parameters. Accordingly the system preferably learns, over time, the high-probability, optimal settings that might be deployed on any given day, at any given time of day. Moreover, the system may also be employed to learn, over time, the optimal settings for any given user, and any given type of job that is run, enabling, over time, a historical database of usage patterns to be acquired. Once acquired, the system may repeatedly access such data and to refresh and recalibrate in order to ensure that the dynamic bufferpool allocation parameters do not grow stale or inefficient on any dated usage characteristic.

Turning now to the Figures, Figures 1 - 4, depicted therein is a flowchart for an embodiment of the present invention. Figure 1 depicts the initial steps of a bufferpool tuning algorithm, based on the next higher size pool, if available, within the past thirty days, based on actual usage statistics, and adjusts the bufferpool accordingly. More specifically, beginning with step 110, the process captures bufferpool data for the current date and processes the data to determine heuristics (e.g., Current IHR (CIHR)). Similarly, as represented by Step 112, data and heuristics are determined for prior dates and higher or larger-sized bufferpools - preferably from a historical database. Next, at step 114, a test is performed to determine if bufferpool performance data is available for a next higher bufferpool size. If not, variables are set in step 116 to predetermined levels and processing continues.

Considering Figure 2, there are depicted process steps for capturing data for next lower or smaller-sized buffer pools, beginning with step 120. In particular, data is captured for the bufferpool tuning algorithm, based on the next lower size pool, if available, within the past thirty days. The data is preferably based on actual usage statistics, and adjusts the bufferpool accordingly as will be described with respect to step 126. At step 122, as in step 114, a test is performed to determine if performance history data exists for the next lower sized bufferpool. If not, variables are set in step 124 to predetermined levels and processing continues at step 126.

At step 126, the buffer pool tuning increment (BPINCR) is set in response to the synchronous read percentage previously calculated. For example, the more random (synchronous) the I/O access patterns are, the higher the BPINCR should be set so that random access benefits the most from larger bufferpool settings.

Turning to Figure 3, the optimal size of the bufferpool is determined using the steps depicted, and is preferably based upon actual usage history. Overall, the tuning operates as described in block 134, where if there is a high CSRP, then the adjustments are in response to the IHR. Otherwise, adjustments are made on OHR. More specifically, in one embodiment, step 130 tests to determine if CSRP is greater than a predetermined threshold (e.g., 60%). Alternatively, the testing may employ multiple thresholds so as to allow bufferpools to have their sizes changed by larger increments of memory, as represented by the following code segment: if [[ $csrp -gt 75 ]] then bpincr=2000 elif [[ $csrp -gt 50 && $csrp -It 76 ]] then bpincr=1000 elif [[ $csrp -gt 5 && $csrp -It 51 ]] then bpincr=1000 else bpincr=0 fi where a CSRP greater than seventy-five results in a larger increase in bufferpool size than a CSRP greater than five but less than seventy five percent.

If so, processing continues at step 132, where the Current IHR is compared to the Lower and Higher (historical) IHR values from the historical database. An affirmative result from test step 132 will result in processing continuing at step 136, where the recommended bufferpool size is calculated based upon the current size, plus an incremental adjustment equal to the BPINCR number of additional pages and processing is essentially complete. Otherwise, based upon a negative response in step 132, processing continues at step 140 where further testing is conducted on the value of the Current IHR. Again, an affirmative response results in processing being continued at step 142, where the recommended size is computed using the current bufferpool size and taking away BPINCR pages. A negative response to the tests in steps 132 and 140 results in a determination, as indicated by step 144, that the bufferpool is presently optimized for random access.

Lastly, turning to Figure 4, the optimal bufferpool size is calculated for a bufferpool where access is not predominantly random (lower CSRP level). At step 146, the Current OHR is compared to the lower and higher (historical) levels. An affirmative test result causes processing to continue at step 150, where the bufferpool size is set as the current size plus BPINCR pages. Otherwise, the process continues at step 152, where the need for a smaller page number is analyzed, again testing for Current OHR and a variable (LBPSZ) set in step 124. An affirmative result continues processing at step 154, where the bufferpool size is decreased by a number of pages equal to BPINCR. Negative results to the test in steps 146 and 152 results in a determination, as represented by step 156, that the bufferpool size is optimized.

Ultimately, processing continues, for all of the various paths through the flowchart, at step 160, where a final test is employed to determine whether a bufferpool size increase will likely result in additional memory paging (an unacceptable result). If the test in step 160 results in an affirmative, step 162 cancels the size increase and restores the size to the present size, before completing the optimization process as indicated by step 164.

It should be further appreciated that numerous programming techniques may be employed to accomplish the above-described operations, some of which may be dependent upon the operating system and computer hardware. The following section of source code serves as an exemplary teaching of aspects of the present invention, and particularly includes an embodiment of the previously described process for bufferpool tuning. # Pool-GUY(TM) Buffer Pool Tuner. V1.2

# Copyright 1998, 1999. Database-GUYS Inc. All Rights Reserved.

if [[ -z $USER ]] then if [[ -n $LOGNAME ]] then

USER=$LOGNAME else

USER="DBGUYS-USER" fi fi #

# Initialize fields ceil_pages=100000 floor_pages=250

### DEFINE INTERNAL FUNCTIONS ###

# DGPGJJSAGE - Display Usage Messages function dgpg_usage { pπ nt "Usage: dgpg_bptune -mdb monitored_dbname -pdb performance_dbname" pri nt " -o generated. command. output.file" pri nt " [-nodisplay or -n] [-verbose or -v]" pr nt " [-htmtune generated. html. report.file]" pri nt " [-prompt [ON]]OFF ]" pri nt " " pri nt "This is an internal Pool-GUY command and is not intended" pri nt "to be executed directly by the user." pri nt " " exit 1

}

# DGPG_GETBUFF function dgpg_getbuff { # V1.3 Start

# In case the bufferpool number of pages is -1 , get the BUFFPAGE size from the DB CFG to determine BP sizes. db2 "get db cfg for $dbname" | grep BUFFPAGE | cut -f2 -d '=' | read cfg_buffpage # V1.3 End db2 "connect to $dbname" > $tempfile 2>&1 if [[ $? -ne 0 ]] then print "Unable to Connect to Monitored Database $dbname." dgpg_usage print "Message from DB2..." cat $tempfile rm $tempfile exit 1 else if [[ $verbose -eq 1 ]] then print "Connect to Monitored database $dbname Successful..." fi rm $tempfile fi # typeset -i bsub=0 echo "select 'DGPG', bufferpoolid, bpname, " > $tempfile echo " case npages when -1 then $cfg_buffpage else npages end as npages, " »

$tempfile echo " pagesize," » $tempfile echo " cast((((case npages when -1 then $cfg_buffpage else npages end)*pagesize)/1048576.0) as decimal(8,1))," » $tempfile echo " ((((case npages when -1 then $cfg_buffpage else npages end)^*pagesize)/1048576)+ 1) " » $tempfile echo " from syscat. bufferpools" » $tempfile echo " where ngname is null or ngname in " » $tempfile echo " (select ngname from syscat.nodegroupdef " » $tempfile echo " where nodenum = current node and in_use = 'Y');" » $tempfile db2 -tf $tempfile | grep DGPG | while read lit bpid bpname npages pagesize dbpmb ibpmb do let bsub=$bsub+1 # Store bufferpool data in array beginning at pos 1 bp_id[$bsub]=$bpid bp_name[$bsub]=$bpname bp_pagesize[$bsub]=$pagesize #

# Since it is possible that a bufferpool may not have a "standard" size as # found in syscat. bufferpools, we have to check bufferpoolnodes to see if

# a pool has a special size on the current node. If not, then we can use

# the value from syscat. bufferpools. echo "select 'NPGS', " > $tempfile echo " case npages when -1 then $cfg_buffpage else npages end as npages, " » $tempfile echo " cast((((case npages when -1 then $cfg_buffpage else npages end)*$pagesize)/1048576.0) as decimal(8,1)), " » $tempfile echo " ((((case npages when -1 then $cfg_buffpage else npages end)*$pagesize)/1048576)+1 ) " » $tempfile echo " from syscat. bufferpoolnodes where nodenum = current node " » $tempfile echo " and bufferpoolid = $bpid ; " » $tempfile unset nnpages ndbpmb nibpmb db2 -tf $tempfile | grep NPGS | read lit nnpages ndbpmb nibpmb if [[ -n $nnpages ]] then bp_npages[$bsub]=$nnpages bp_dbpmb[$bsub]=$ndbpmb dbbpsize_mb=$dbbpsize_mb+$nibpmb else bp_npages[$bsub]=$npages bp_dbpmb[$bsub]=$dbpmb dbbpsize_mb=$dbbpsize_mb+$ibpmb fi done # numbps=$bsub # db2 "terminate" > /dev/null 2>&1

}

# DGPG_DB2INIT function dgpg_db2init { if [[ -z $1 ]] then print "Missing Required Parameters." dgpg_usage fi # unset outfile verbose=0 display=1 # while true do if [[ -z $1 ]] then break fi case $1 in

"-mdb") dbname=$2;shift 2;; "-pdb") perfdb=$2;shift 2;;

"-o") outfile=$2;shift 2;; "-htmtune") htmfile=$2;shift 2;; "-v") verbose=1 ;shift 1 ;; "-verbose") verbose=1 ;shift 1 ;; "-n") display=0;shift 1 ;;

"-nodisplay") display=0;shift 1 ;; "-prompt") pmptval=$2;shift 2;; *) shift 1 ;; esac done #

# Setup Defaults based on parameters supplied if [[ -z $dbname ]] then print "Required monitored database parameter is missing (-mdb DBNAME)." dgpg_usage else db2 "connect to $dbname" > /dev/null 2>&1 if [[ $? -eq 0 ]] then db2 "values ('XXX', current timestamp, current node)" | grep XXX | read lit inittime curmode db2 terminate > /dev/null 2>&1 else echo "Error: Cannot connect to $dbname, exiting." dgpg_usage fi fi # typeset -I dbname_lc=$dbname dgpgini=$DGHOME/bin/$dbname_lc.dgpg.ini

# If the current node is > 0, then append node number to dgpgini file for

# EEE so that BPs at different nodes can have different ceilings and floors.

# If the node suffixed file does not exist, then stick with the base file name # presuming that ceilings and floors set therein apply to all nodes. if [[ $curmode -gt 0 ]] then if [[ -r $DGHOME/bin/$dbname_lc.dgpg.ini.$curmode ]] then # use node specific file dgpgini=$DGHOME/bin/$dbnameJc.dgpg.ini.$currnode echo "FYI: Using $dgpgini for node: $currnode" sleep 3 fi # # If the HTML report file is not null (we need to gen html report), and the

# current node is > 0, then pre-pend the html file name with nodeN so that generated HTML files do not trip over each other. if [[ -n $htmfile ]] then htmfilesfx=${htmfile##*/} htmfiledir=${htmfile%/*} htmnode="node$currnode" htmfile="$htmfiledir/$htmnode.$htmfilesfx" echo "HTML file changed to: $htmfile" sleep 3 fi fi # if [[ -z $perfdb ]] then print "Required performance database parameter is missing (-pdb DBNAME)." dgpg_usage fi # typeset -u pmptval_uc=$pmptval if [[ $pmptval_uc = OFF ]] then prompts=0 else prompts=1 fi # if [[ $display -eq 0 ]] then prompts=0 fi

# V1.3 Start # Set up tempfile and outfile if null if [[ -z $TMPDIR ]] then

TMPDIR=/tmp fi tempfile=$TMPDIR/$dbname.dgpgtune.$$ if [[ -z $outfile ]] then outfile=$tempfile.alters # V1.3 End else if [[ $curmode -gt 0 ]] then # add node number to output file name for uniqueness outfile=$outfile.$currnode fi fi # If DBGUYS_SCHEMA is set, use that value, otherwise use $USER if [[ -z $DBGUYS_SCHEMA ]] then export DBGUYS_SCHEMA=$USER fi

# Build array of current bufferpool names and sizes dgpg_getbuff

# Connect to the Performance Database # typeset -u dbname=$dbname db2 "connect to $perfdb" > $tempfile 2>&1 if [[ $? -ne 0 ]] then print "Unable to Connect to Performance Database $perfdb." dgpg_usage print "Message from DB2..." cat $tempfile rm $tempfile exit 1 else if [[ $verbose -eq 1 ]] then print "Connect to performance database $perfdb Successful..." fi rm $tempfile fi

} # DGPG_DB2END function dgpg_db2end { # print " " print "Performing Termination Activities..." print " " db2 "terminate" > $tempfile 2>&1

# if [[ $verbose = 1 ]] then cat $tempfile fi

# If display is true and alter BP commands have been written

# to the output file, then propose executing them now against the monitored database. if [[ $display -eq 1 && $altercnt -gt 0 ]] then print " " print "$altercnt ALTER BUFFERPOOL statement(s) have been queued" print "in file $outfile." print " " if [[ $prompts -eq 1 ]] then print -n "Execute them now for database $dbname? [Y|[N]]: " read -r ans typeset -u ans_uc=$ans if [[ $ans_uc = Y ]] then db2 -v "connect to $dbname" db2 -tvf $outfile db2 -v "terminate" fi fi fi # if [[ -f $tempfile ]] then rm $tempfile fi # if [[ -n $htmfile ]] then echo "<A HREF=\"$thispg\" name=\"notes\"><H2>Notes</H2></A>" » $htmfile echo "<TABLE BORDER>" » $htmfile echo "<TR><TH>Code</TH><TH>Explanation</TH></TR>" » $htmfile echo "<TR><TD ALIGN=CENTER>F</TD><TD>While tuning downward, the <B>F</B>loor size was reached.</TD></TR>" » $htmfile echo "<TR><TD ALIGN=CENTER>C</TD><TD>While tuning upward, the <B>C</B>eiling size was reached.</TD></TR>" » $htmfile echo "<TR><TD ALIGN=CENTER>«/TD><TD>Decrease Bufferpool Size</TD></TR>" » $htmfile echo "<TR><TD ALIGN=CENTER»</TD><TD>lncrease Bufferpool Size</TD></TR>" » $htmfile echo "<TR><TD ALIGN=CENTER>!</TD><TD>System is Paging, " » $htmfile echo "Increase Bufferpool Size Cancelled. <BR>" » $htmfile echo "Paging will only be detected if Wise-GUY<SUP>TM</SUP> " » $htmfile echo "is installed and has been active during " » $htmfile echo "the last seven days.</TD></TR>" » $htmfile echo "</TABLE>" » $htmfile echo "<P><HR>" » $htmfile echo "<H2>Key Links</H2><CENTER>" » $htmfile echo "<A HREF="http://www.database-guys.com/">Database-GUYS lnc.</A>" » $htmfile echo "&#160;|&#160;" » $htmfile echo "<A HREF="http://www.idug.org/">lntemational DB2 Users Group</A>" » $htmfile echo "&#160;|&#160;" » $htmfile echo "<A HREF="http://www.software.ibm.com/data/">IBM DB2 Home</A>" » $htmfile echo "</CENTER><P><HR>" » $htmfile echo "Copyright &#169 2000 by Database-GUYS Inc." » $htmfile echo "Spencerport, New York, 14559 USA. <B>All Rights Reserved. </B>" » $htmfile echo "</BODY></HTML> " » $htmfile chmod g+w $htmfile print "HTML file $htmfile is ready." fi # #

} #

#— BEGIN MAIN PROCESSING —

#

# Load the database guys profile file # if [[ -z $DGHOME ]] then

DGHOME=$HOME/dbguys fi # if [[ -r $DGHOME/.dbguys_profile ]] then . $DGHOME/.dbguys_profile else print "ERROR: Cannot load .dbguys_profile file to set env vars" sleep 10 exit 3 fi #

# Set Trap to catch CTRL+C # trap dgpg_db2end 2 # #

# Perform initialization & Get parameters

# dgpg_db2init $@ #

# For each bufferpool in monitored database, as found in performance

# database, determine trends and tuning recommendation. # tick="^,M typeset -u dbuc=$dbname

# db2 "select 'MAXDT', max(snapshot_dt) from $DBGUYS_SCHEMA.DGPG_BPHIST_TB where dbname = $tick$dbuc$tick" | grep MAXDT | read lit maxdate

# typeset -i bpsub=1

# # Is the system paging? We can find out if Wise-GUY is installed by

# checking the database performance history table. # if [[ $DBGUYS_DGWG_INSTALLED = Y ]] then echo "select 'SYSPG', coalesce(avg(avg_paging_pi),0) " > $tempfile echo " from $DBGUYS_SCHEMA.DGWG_DB_STAT_TB" » $tempfile echo " where dbname = $tick$dbuc$tick " » $tempfile echo " and days(current date) - days(snapshot_dt) < 7; " » $tempfile db2 -tf $tempfile | grep SYSPG | read lit syspage else # assume not paging syspage=0 fi # typeset -ix altercnt=0 if [[ -n $htmfile ]] then echo "<HTML><HEAD> " > $htmfile echo "<TITLE>Pool-GUY(TM) Bufferpool Analysis for $dbuc</TITLE>" » $htmfile echo "<meta name=\"abstract\" content=\"Bufferpool Analysis for $dbuc\">" » $htmfile echo "<meta name=\"owner\" content=\"$USER\">" » $htmfile date +"%Y%m%d" | read revdt echo "<meta name=\"review\" content=\"$revdt\">" » $htmfile echo "<body bgcolor=\"#FFFFFF\"> " » $htmfile echo "<H1>Pool-GUY<SUP>TM</SUP> Bufferpool Analysis for $dbuc</H1>" » $htmfile echo "HTML Prepared: $(date) <BR>" » $htmfile echo "Server: $(hostname), Node: $curmode <BR>" » $htmfile echo "<H2>Bufferpool Summary</H2>" » $htmfile echo "<TABLE BORDER CELLPADDING=4 CELLSPACING=4>" » $htmfile echo "<TR>" » $htmfile echo " <TH>Bufferpool Name</TH><TH>Size MB</TH><TH>Pages</TH><TH>Page Size</TH>" » $htmfile echo "</TR>" » $htmfile typeset -i x=1 while true do echo "<TR>" » $htmfile echo " <TD>" » $htmfile echo " <A HREF=\"#${bp_name[$x]}\">${bp_name[$x]}</A>" » $htmfile echo " </TD>" » $htmfile echo " <TD ALlGN=RIGHT>" » $htmfile echo " ${bp_dbpmb[$x]}" » $htmfile echo " </TD>" » $htmfile echo " <TD ALIGN=RIGHT>" » $htmfile echo " ${bp__npages[$x]}" » $htmfile echo " </TD>" » $htmfile echo " <TD ALIGN=RIGHT>" » $htmfile echo " ${bp_pagesize[$x]}" » $htmfile echo " </TD>" » $htmfile echo "</TR>" » $htmfile let x=$x+1 if [[ -z ${bp_name[$x]} ]] then break fi done echo "</TABLE>" » $htmfile echo "<H2>Bufferpool Details</H2>" » $htmfile fi # while true do #

# Clear the variables # unset Ipgs lihr lohr Isrp Iprpm unset hpgs hihr hohr hsrp hprpm unset cihr cohr csrp cprpm unset ihrl ihr2 ihr3 ihr4 ohrl ohr2 ohr3 ohr4 unset srpl srp2 srp3 srp4 prpml prpm2 prpm3 prpm4 unset floor_pages ceil_pages #

# Get floor and ceiling values for the bufferpool, if available. # if [[ -r $dgpgini ]] then grep -v '#' $dgpgini | grep ${bp_name[$bpsub]} | read name floor_pages ceil_pages fi if [[ -z $floor_pages ]] then floor_pages=250 ceil_pages= 100000 fi #

# Get the average index hit ratio, average overall hit ratio, average # synchronous read percentage, and average pages read per minute

# for the bufferpool for the current maximum date in bp history table. # if [[ $display -eq 1 ]] then if [[ $prompts -eq 1 ]] then clear else echo " " fi echo "Analyzing Bufferpool: ${bp_name[$bpsub]}, ${bp_dbpmb[$bpsub]}MB, Floor: $floor_pages, Ceiling: $ceil_pages" fi if [[ -n $htmfile ]] then print $htmfile | grep V > /dev/null 2>&1 if [[ $? -eq 0 ]] then print ${htmfile##*/} | read thispg else print $htmfile | read thispg fi echo "<A HREF=\"$thispg\" name=\"${bp_name[$bpsub]}\"><H3>${bp_name[$bpsub]}</H3></A>" » $htmfile echo "<H4>Trend Report</H4>" » $htmfile echo "<TABLE BORDER CELLPADDING=4 CELLSPACING=4>" » $htmfile echo "<CAPTION>${bp_name[$bpsub]} Performance Trends</CAPTION>" » $htmfile fi echo "select 'CURR', coalesce(avg(ihr),0), coalesce(avg(ohr),0), " > $tempfile echo " coalesce(avg(srp),0), coalesce(avg(prpm),0) " » $tempfile echo " from $DBGUYS_SCHEMA.DGPG_BPHIST_TB " » $tempfile echo " where dbname = $tick$dbuc$tic " » $tempfile echo " and nodenum = current node " » $tempfile echo " and bpname = $tick${bp_name[$bpsub]}$tick " » $tempfile echo " and snapshot_dt = $tick$maxdate$tick;" » $tempfile db2 -tf $tempfile | grep CURR | read lit cihr cohr csrp cprpm #

# Get same information for past 7 days (1 week) # echo "select '1AVG', coalesce(avg(ihr),-1), " > $tempfile echo " coalesce(avg(ohr),-1 ), " » $tempfile echo " coalesce(avg(srp),-1 ), coalesce(avg(prpm),-1 ) " » $tempfile echo " from $DBGUYS_SCHEMA.DGPG_BPHIST_TB " » $tempfile echo " where dbname = $tick$dbuc$tick " » $tempfile echo " and nodenum = current node " » $tempfile echo " and bpname = $tick${bp_name[$bpsub]}$tick " » $tempfile echo " and days(current date) - days(snapshot_dt) < 8" » $tempfile echo " and snapshot_dt != $tick$maxdate$tick;" » $tempfile db2 -tf $tempfile | grep 1AVG | read lit ihrl ohrl srpl prpml #

# Get same information for 7-14 days ago (2 weeks) # echo "select '2AVG', coalesce(avg(ihr),-1 ), " > $tempfile echo " coalesce(avg(ohr),-1), " » $tempfile echo " coalesce(avg(srp),-1 ), coalesce(avg(prpm),-1 ) " » $tempfile echo " from $DBGUYS_SCHEMA.DGPG_BPHIST_TB " » $tempfile echo " where dbname = $tick$dbuc$tick " » $tempfile echo " and nodenum = current node " » $tempfile echo " and bpname = $tick${bp_name[$bpsub]}$tick " » $tempfile echo " and days(current date) - days(snapshot_dt) < 15" » $tempfile echo " and days(current date) - days(snapshot_dt) > 7;" » $tempfile db2 -tf $tempfile | grep 2AVG | read lit ihr2 ohr2 srp2 prρm2 #

# Get same information for 15-21 days ago (3 weeks) # echo "select '3AVG', coalesce(avg(ihr),-1 ), " > $tempfile echo " coalesce(avg(ohr),-1 ), " » $tempfile echo " coalesce(avg(srp),-1 ), coalesce(avg(prpm),-1 ) " » $tempfile echo " from $DBGUYS_SCHEMA.DGPG_BPHIST_TB " » $tempfile echo " where dbname = $tick$dbuc$tick " » $tempfile echo " and nodenum = current node " » $tempfile echo " and bpname = $tick${bp_name[$bpsub]}$tick " » $tempfile echo " and days(current date) - days(snapshot_dt) < 22" » $tempfile echo " and days(current date) - days(snapshot_dt) > 14;" » $tempfile db2 -tf $tempfile | grep 3AVG | read lit ihr3 ohr3 srp3 prpm3 #

# Get same information for 22-28 days ago (4 weeks) # echo "select '4AVG', coalesce(avg(ihr),-1), " > $tempfile echo " coalesce(avg(ohr),-1 ), " » $tempfile echo " coalesce(avg(srp),-1), coalesce(avg(prpm),-1) " » $tempfile echo " from $DBGUYS_SCHEMA.DGPG_BPHIST_TB " » $tempfile echo " where dbname = $tick$dbuc$tick " » $tempfile echo " and nodenum = current node " » $tempfile echo " and bpname = $tick${bp_name[$bpsub]}$tick " » $tempfile echo " and days(current date) - days(snapshot_dt) < 29" » $tempfile echo " and days(current date) - days(snapshot_dt) > 21 ;" » $tempfile db2 -tf $tempfile | grep 4AVG | read lit ihr4 ohr4 srp4 prpm4 #

# Get the average index hit ratio, average overall hit ratio, average

# sync read percent, and average pages read per minute for the next

# highest historical bufferpool size. Constrain search to last 30 days. # Object is to learn performance results of having a larger BP size from

# history, if available. If no historical experience is available, we

# will assume a larger size will yield better performance. # echo "select ΗGHR', coalesce(npages,-1), avg(ihr), avg(ohr), avg(srp), avg(prpm)" > $tempfile echo " from $DBGUYS_SCHEMA.DGPG_BPHIST_TB " » $tempfile echo " where dbname = $tick$dbuc$tick " » $tempfile echo " and bpname = $tick${bp_name[$bpsub]}$tick " » $tempfile echo " and nodenum = current node " » $tempfile echo " and days(current date) - days(snapshot_dt) < 31" » $tempfile echo " and npages in " » $tempfile echo " (select min(npages) from $DBGUYS_SCHEMA.DGPG_BPHIST_TB " » $tempfile echo " where dbname = $tick$dbuc$tick " » $tempfile echo " and nodenum = current node " » $tempfile echo " and bpname = $tick${bp_name[$bpsub]}$tick " » $tempfile echo " and days(current date) - days(snapshot_dt) < 31" » $tempfile echo " and npages > ${bp_npages[$bpsub]} )" » $tempfile echo " group by 1 , npages; " » $tempfile db2 -tf $tempfile | grep HGHR | read lit hpgs hihr hohr hsrp hprpm #

# Get the average index hit ratio, average overall hit ratio, average # sync read percent, and average pages read per minute for the next

# LOWEST historical bufferpool size. Constrain search to last 30 days.

# Object is to learn performance results of having a smaller BP size from

# history, if available. If no historical experience is available, we

# will assume a smaller size will yield better performance. # echo "select 'LOWR', coalesce(npages,-1), avg(ihr), avg(ohr), avg(srp), avg(prpm)" > $tempfile echo " from $DBGUYS_SCHEMA.DGPG_BPHIST_TB " » $tempfile echo " where dbname = $tick$dbuc$tick " » $tempfile echo " and bpname = $tick${bp_name[$bpsub]}$tick " » $tempfile echo " and nodenum = current node " » $tempfile echo " and days(current date) - days(snapshot_dt) < 31" » $tempfile echo " and npages in " » $tempfile echo " (select max(npages) from $DBGUYS_SCHEMA.DGPG_BPHIST_TB " » $tempfile echo " where dbname = $tick$dbuc$tick " » $tempfile echo " and nodenum = current node " » $tempfile echo " and bpname = $tick${bp_name[$bpsub]}$tick " » $tempfile echo " and days(current date) - days(snapshot_dt) < 31 " » $tempfile echo " and npages < ${bp_npages[$bpsub]} )" » $tempfile echo " group by 1 , npages; " » $tempfile db2 -tf $tempfile | grep LOWR | read lit Ipgs lihr lohr Isrp Iprpm #

# V1.3 Start

# bpincr set to 2000 pages when sync read % > 75

# Use bpincr of 1000 when SRP > 5 < 76 # if [[ $csrp -gt 75 ]] then bpincr=2000 elif [[ $csrp -gt 50 && $csrp -It 76 ]] then bpincr=1000 elif [[ $csrp -gt 5 && $csrp -It 51 ]] then bpincr=1000 else bpincr=0 fi # # V1.3 End #

# Cancel any bp adjustments if both the current ohr and ihr are zero # if [[ $cihr -eq 0 && $cohr -eq 0 ]] then bpincr=0 fi #

# Set up defaults for unknown higher and lower values # if [[ -z $lpgs ]] then Ipgs='N/A' lihr=0 lohr=0 lsrp=0 lprpm=0 fi if [[ -z $hpgs ]] then hpgs='N/A' hihr=100 hohr=100 hsrp=0 hprpm=0 fi #

# If the pool is being randomly read (csrp > 60%), then base tuning rule

# on the index hit ratio. If the pool is being async read, then base tuning

# on the overall hit ratio. # typeset -i rec_pgs=0 unset oind iind if [[ $csrp -gt 60 ]] then # the bufferpool is predominantly randomly read if [[ $cihr -gt $lihr && $cihr -It $hihr ]] then let rec_pgs=${bp_npages[$bpsub]}+$bpincr iind- >' if [[ $syspage -gt 0 ]] then let rec_pgs=1 iind=T fi if [[ $rec_pgs -gt $ceil_pages ]] then rec_pgs=$ceil_pages iind='C' fi else if [[ $cihr -le $lihr || $lpgs = 'N/A' ]] then let rec_pgs=${bp_npages[$bpsub]}-$bpincr iind='<' if [[ $rec_pgs -It $floor_pages ]] then rec_pgs=$floor_pages iind^F fi fi fi else # the pool is predominantly sequentially read if [[ $cohr -gt $lohr && $cohr -It $hohr ]] then let rec_pgs=${bp_npages[$bpsub]}+$bpincr oind='>' if [[ $syspage -gt 0 ]] then let rec_pgs=1 oind= fi if [[ $rec_pgs -gt $ceil_pages ]] then rec_pgs=$ceil_pages oind='C fi else if [[ $cohr -le $lohr || $lpgs = 'N/A' ]] then let rec_pgs=${bp_npages[$bpsub]}-$bpincr oind='<' if [[ $rec_pgs -It $floor_pages ]] then rec_pgs=$floor_pages oind='F' fi fi fi fi

if [[ $bpincr -eq 0 ]] then # clear the indicators iind=' ' oind=' ' fi

# if [[ $rec_pgs -eq ${bp_npages[$bpsub]} ]] then rec_pgs=0 fi #

# At this point, if rec_pgs is 0, then BP is at ideal size and no changes required.

# If rec_pgs is 1 , then the recommended increase was cancelled due to paging.

# If rec_pgs is any other value, it represents the proposed new size for the # pool. Ensure that the recommended size is not less than 250 pages nor greater

# than 100000 (appx 400MB @ 4k pages or 800MB @ 8k pages). This ceiling is a bit

# arbitrary... the customer may wish to change this to a number representing

# 25-50% of the real memory available on the machine. # if [[ $rec_pgs -eq 0 ]] then rectxt- 'Bufferpool size is optimized, no changes required." elif [[ $rec_pgs -eq 1 ]] then rectxt- 'lncrease in Bufferpool size cancelled due to system paging." else if [[ $rec_pgs -It $floor_pages ]] then rec_pgs=$floor_pages fi if [[ $rec_pgs -gt $ceil_pages ]] then rec_pgs=$ceil_pages fi rectxt- 'Recommended size for ${bp_name[$bpsub]} is $rec_pgs pages, alter generated." # # If no display, then write all alter bp statements to output file # if [[ $display -eq 0 ]] then echo "alter bufferpool ${bp_name[$bpsub]} size $rec_pgs ;" » $outfile let altercnt=$altercnt+1 fi fi

if [[ $ihr1 -It 0 ]] then ihr1='N/A' fi if [[ $ihr2 -It 0 ]] then ihr2='N/A' fi if [[ $ihr3 -It 0 ]] then ihr3=^,N/A^I fi if [[ $ihr4 -It 0 ]] then ihr4='N/A' fi if [[ $ohr1 -It 0 ]] then ohr1='N/A' fi if [[ $ohr2 -It 0 ]] then ohr2='N/A" fi if [[ $ohr3 -It 0 ]] then ohr3=^,N/A fi if [[ $ohr4 -It 0 ]] then ohr4='N/A' fi if [[ $srp1 -It 0 ]] then srp1='N/A' fi if [[ $srp2 -It 0 ]] then srp2='N/A' fi if [[ $srp3 -It 0 ]] then srp3='N/A' fi if [[ $srp4 -It 0 ]] then srp4='N/A' fi if [[ $prpm1 -It 0 ]] then prpm1='N/A' fi if [[ $prpm2 -It 0 ]] then prpm2='N/A' fi if [[ $prpm3 -It 0 ]] then prpm3='N/A' fi if [[ $prpm4 -It 0 ]] then prpm4='N/A' fi let sz=${bp_pagesize[$bpsub]}/1024 # if [[ -n $htmfile ]] then echo "<TR>" » $htmfile echo " <TH>Performance<BR>Variable</TH>" » $htmfile echo " <TH>Four<BR>Weeks Ago</TH>" » $htmfile echo " <TH>Three<BR>Weeks Ago</TH>" » $htmfile echo " <TH>Two<BR>Weeks Ago</TH>" » $htmfile echo " <TH>Previous<BR>Seven Days</TH>" » $htmfile echo " <TH>Today's<BR>Results</TH>" » $htmfile echo "</TR>" » $htmfile echo "<TR>" » $htmfile echo " <TD>lndex Hit Ratio</TD>" » $htmfile echo " <TD ALIGN=RIGHT>$ihr4 %</TD>" » $htmfile echo " <TD ALIGN=RIGHT>$ihr3 %</TD>" » $htmfile echo " <TD ALIGN=RIGHT>$ihr2 %</TD>" » $htmfile echo " <TD ALIGN=RIGHT>$ihr1 %</TD>" » $htmfile echo " <TD ALIGN=RIGHT>$cihr %</TD>" » $htmfile echo "</TR>" » $htmfile echo "<TR>" » Shtmfile echo " <TD>Overall Hit Ratio</TD>" » $htmfile echo " <TD ALIGN=RIGHT>$ohr4 %</TD>" » $htmfile echo " <TD ALIGN=RIGHT>$ohr3 %</TD>" » $htmfile echo " <TD ALIGN=RIGHT>$ohr2 %</TD>" » $htmfile echo " <TD ALIGN=RIGHT>$ohr1 %</TD>" » $htmfile echo " <TD ALIGN=RIGHT>$cohr %</TD>" » $htmfile echo "</TR>" » $htmfile echo "<TR>" » $htmfile echo " <TD>Synchronous Reads</TD>" » $htmfile echo " <TD ALIGN=RIGHT>$srp4 %</TD>" » $htmfile echo " <TD ALIGN=RIGHT>$srp3 %</TD>" » $htmfile echo " <TD ALIGN=RIGHT>$srp2 %</TD>" » $htmfile echo " <TD ALIGN=RIGHT>$srp1 %</TD>" » $htmfile echo " <TD ALIGN=RIGHT>$csrp %</TD>" » $htmfile echo "</TR>" » Shtmfile echo "<TR>" » Shtmfile echo " <TD>Pages Read/Minute</TD>" » Shtmfile echo " <TD ALIGN=RIGHT>$prpm4</TD>" » Shtmfile echo " <TD ALIGN=RIGHT>$prpm3</TD>" » Shtmfile echo " <TD ALIGN=RIGHT>$prpm2</TD>" » Shtmfile echo " <TD ALIGN=RIGHT>$prpm1</TD>" » Shtmfile echo " <TD ALIGN=RIGHT>$cprpm</TD>" » Shtmfile echo "</TR>" » Shtmfile echo "</TABLE>" » Shtmfile echo "<H4>Comparative Tuning Analysis</H4>" » Shtmfile echo "<TABLE BORDER CELLPADDING=4 CELLSPACING=4>" » Shtmfile echo "<CAPTION>${bp_name[$bpsub]} Tuning Analysis</CAPTION>" » Shtmfile echo '<TR>" » Shtmfile echo ¹ <TH>&nbsp<BR>Performance<BR>Variable</TH>" » Shtmfile echo <TH>&nbsp<BR>&nbsp<BR><A HREF=\"#notes\">Notes</A></TH>"

» Shtmfile echo ' <TH>Next<BR><l>Lowest</|xBR>Historical Size</TH>" » Shtmfile echo <TH>&nbsp<BR><B>Current</B><BR>Size</TH>" » Shtmfile echo <TH>Next<BRxl>Highest</l><BR>Historical Size</TH>" » Shtmfile echo '</TR>" » Shtmfile echo '<TR>" » Shtmfile echo <TD ALIGN=LEFT>Number of $sz"K" Pages</TD>" » Shtmfile echo <TD ALIGN=CENTER>&nbsp</TD>" » Shtmfile echo <TD ALIGN=CENTER>$lpgs</TD>" » Shtmfile echo <TD ALIGN=CENTER>${bp_npages[$bpsub]}</TD>" » Shtmfile echo <TD ALIGN=CENTER>$hpgs</TD>" » Shtmfile echo "</TR>" » Shtmfile echo ^■<TR>" » Shtmfile echo <TD ALIGN=LEFT>lndex Hit Ratio</TD>" » Shtmfile echo <TD ALIGN=CENTER>$iind &nbsp</TD>" » Shtmfile echo <TD ALIGN=CENTER>$lihr %</TD>" » Shtmfile echo <TD ALIGN=CENTER>$cihr %</TD>" » Shtmfile echo <TD ALIGN=CENTER>$hihr %</TD>" » Shtmfile echo ^■</TR>" » Shtmfile echo ^■<TR>" » Shtmfile echo <TD ALIGN=LEFT>Overall Hit Ratio</TD>" » Shtmfile echo <TD ALIGN=CENTER>$oind &nbsp</TD>" » Shtmfile echo <TD ALIGN=CENTER>$lohr %</TD>" » Shtmfile echo <TD ALIGN=CENTER>$cohr %</TD>" » Shtmfile echo <TD ALIGN=CENTER>$hohr %</TD>" » Shtmfile echo ^■</TR>" » Shtmfile echo '<TR>" » Shtmfile echo " <TD ALIGN=LEFT>Synchronous Reads</TD>" » Shtmfile echo " <TD ALIGN=CENTER>&nbsp</TD>" » Shtmfile echo " <TD ALIGN=CENTER>$lsrp %</TD>" » Shtmfile echo " <TD ALIGN=CENTER>$csrp %</TD>" » Shtmfile echo " <TD ALIGN=CENTER>$hsrp %</TD>" » Shtmfile echo "</TR>" » Shtmfile echo "<TR>" » Shtmfile echo " <TD ALIGN=LEFT>Pages Read/Minute</TD>" » Shtmfile echo " <TD ALIGN=CENTER>&nbsp</TD>" » Shtmfile echo " <TD ALIGN=CENTER>$lprpm</TD>" » Shtmfile echo " <TD ALIGN=CENTER>$cprpm</TD>" » Shtmfile echo " <TD ALIGN=CENTER>$hprpm</TD>" » Shtmfile echo "</TR>" » Shtmfile echo "</TABLE>" » Shtmfile echo "<P>" » Shtmfile echo "<H4>Analysis Notes</H4>" » Shtmfile echo "<UL>" » Shtmfile echo " <LI>The configured pool <l>floor</l> size is $floor_pages pages." » Shtmfile echo " <LI>The configured pool <l>ceiling</l> size is $ceil_pages pages." » Shtmfile if [[ Scsrp -gt 60 ]] then echo " <LI>The pool is predominantly randomly read, " » Shtmfile echo " index hit ratios will be evaluated." » Shtmfile else echo " <LI>The pool is largely asynchronously read, " » Shtmfile echo " overall hit ratios will be evaluated." » Shtmfile fi echo "</UL>" » Shtmfile echo "<P>" » Shtmfile echo "<H4>Recommendation</H4>" » Shtmfile echo "Srectxt" » Shtmfile echo "<P><HR>" » Shtmfile fi if [[ Sdisplay -eq 1 ]] then echo Trend Report:" echo II echo Variable 4 Wks Ago\t3 Wks Ago\t2 Wks Ago\tLast Week Today" echo ========= =========\t=========\t=========\t=========\t== echo Indx Hit% $ihr4\t\t $ihr3\t\t $ihr2\t\t $ihr1\t\t Scihr" echo Ovrl Hit% $ohr4\t\t $ohr3\t\t $ohr2\t\t $ohr1\t\t Scohr" echo SyncRead% $srp4\t\t $srp3\t\t $srp2\t\t $srp1\t\t Scsrp" echo Pgs / Min $prpm4\t\t $prpm3\t\t $prpm2\t\t $prpm1\t\t Scprpm" echo II echo Tuning Analysis Report:" echo echo Variable Next LowesfΛtCurrent \tNext Highest" echo ============ ===========\t=========\t============" echo Pages($sz"k") \t$lpgs\t\t${bp_npages[$bpsub]}\t\t$hpgs " echo Indx Hit% $iind \t$Iihr\t\t$cihr\t\t$hihr" echo Ovrl Hit% Soind \t$lohr\t\t$cohr\t\t$hohr" echo SyncRead% \t$lsrp\t\t$csrp\t\t$hsrp" echo Pgs / Min \t$lprpm\t\t$cprpm\t\t$hprpm" echo Srectxt if [[ $rec_pgs -ge $floor_pages ]] then print "Proposed: alter bufferpool ${bp_name[$bpsub]} size $rec_pgs;" if [[ Sprompts -eq 1 ]] then print -n "Write proposed statement to Soutfile ? [Y|[N]]: " read -r ans typeset -u ans_uc=$ans if [[ $ans_uc = Y ]] then echo "alter bufferpool ${bp_name[$bpsub]} size $rec_pgs;" » Soutfile let altercnt=$altercnt+1 fi else echo "alter bufferpool ${bp_name[$bpsub]} size $rec_pgs;" » Soutfile let altercnt=$altercnt+1 fi else if [[ Sprompts = 1 ]] then print -n "Press [Enter] to continue..." read -r ack fi fi fi # let bpsub=$bpsub+1 if [[ -z ${bp_name[$bpsub]} ]] then break fi done #

# Perform End of Script Tasks dgpg_db2end

# end script dgpg_bptune

In recapitulation, the present invention is a method for automating database bufferpool tuning for optimized performance that employs certain heuristic algorithms to achieve its goals. Over a period of time, memory (bufferpool) performance is measured and accumulated in a repository. The repository becomes a knowledge base that is accessed by the algorithms and the ideal memory (bufferpool) configurations, which optimize database performance, are learned and implemented. The sampling of performance continues at regular intervals and the knowledge base continues to grow. As knowledge continues to accumulate, the algorithms are forbidden from becoming complacent. The ideal bufferpool memory configurations are regularly reevaluated to ensure they continue to be optimal given potential changes in the database's use or access patterns. The entire method can be coded in a machine language and automated; minimal intervention from administration staff is required thus freeing these valuable resources for other business objectives. Most significantly, the techniques employed would ordinarily be very time consuming and prone to error if not automated, but the performance and productivity gains to be realized are potentially substantial. This method then seeks to remove a barrier to optimal database performance by deploying a self-learning, automated process. . It is, therefore, apparent that there has been provided, in accordance with the present invention, a method and apparatus for automated bufferpool tuning. While this invention has been described in conjunction with preferred embodiments thereof, it is evident that many alternatives, modifications, and variations will be apparent to those skilled in the art. Accordingly, it is intended to embrace all such alternatives, modifications and variations that fall within the spirit and broad scope of the appended claims.

Claims

1. A heuristic method for the automated tuning of bufferpool size in a computer database, comprising the steps of: automatically characterizing the types of database access; automatically collecting data base access and usage data based upon a predefined schedule; and using the types of database access characterized and database access and usage data, optimizing the allocation of bufferpool memory so as to ensure optimal access time and to minimize disk thrashing.

2. The method of claim 1 , wherein said step of automatically collecting data base access and usage data includes collecting data relating to database access and usage, wherein the data is selected from at least one of the group consisting of: time of day; day of week; usage patterns; users; and jobs.

3. The method of claim 1 , wherein said step of optimizing the allocation of bufferpool memory further determines the optimal settings for a hit index and overall hits and adjusts for variances between characteristics of a predominant access type.

4. The method of claim 1 , further including the step of repeating the steps of claim 1 on a regular basis.

5. The method of claim 3, further including the step of storing data collected during a plurality of cycles of executing the steps of claim 1 in a historical database, and wherein the step of optimizing the allocation of bufferpool memory includes access to the historical database.

6. A heuristic method for automated bufferpool tuning in a computer, comprising the steps of: automatically identifying types of access; automatically recalibrating the bufferpool sized based upon historical data, including time of day and day of week usage patterns stored in a historical usage database; and optimizing the allocation of computer memory to ensure optimal access time.

7. The method of claim 6, wherein the step of optimizing the allocation of computer memory further includes determining the optimal settings for INDEX hits and OVERALL hits, and incorporating said variables in a determination of the optimal memory allocation.

8. The method of claim 6, wherein the step of optimizing the allocation of computer memory further includes adjusting the allocation for variances between different access types.

9. The method of claim 6, wherein the historical data includes data relative to specific jobs, users, specific times, and usage patterns, and where the step of optimizing the allocation of computer memory^" includes optimizing a bufferpool for specific jobs and users in accordance with a predicted usage pattern.

10. In a database, a bufferpool tuning process, comprising the steps of: deploying self-executing program to monitor and collect data relating to the performance of a database bufferpool, including access data and tablespace size; combining the tablespace size and access attributes, to track historical performance; and tuning the bufferpool in response to the historical performance.