Database Monitoring and Tuning

#### Search Results

Search

# Database Monitoring and Tuning

This document describes the data base administrator's tasks in the area of monitoring and tuning. It covers the following topics:

- Monitoring Resource Use

- Reporting on Resource Use

- Monitoring Database Controls

- Performance Management, Statistics, and Tuning

- Adabas Session Statistics

- Command Logging

## Monitoring Resource Use

The DBA is responsible for monitoring the database environment on a continuing basis to ensure that an efficient level of service is provided while maintaining database integrity.

The DBA should implement a set of procedures designed to foresee degradation before the event and to adjust the operation or design of the database in an orderly and controlled way. This set of procedures includes

- identifying potential sources of degradation;

- establishing tools for monitoring database performance; and

- controlling the implementation of adjustments.

## Reporting on Resource Use

The DBA should report regularly on database use and performance to both data processing and user management. The reports should be factual, but should also include recommendations for tuning the database environment. It should be remembered that tuning, while benefitting the organization as a whole, may adversely affect the service received by one or more users. Any decision on tuning should, therefore, be made by all affected users.

## Monitoring Database Controls

The DBA should establish appropriate controls and monitor them to ensure the integrity of the database.

Computer-generated control totals can be checked and cross-footed between computer processing runs or generated reports. Batch responses (or inquiries) may include such information as the exact run time, search parameters, time of last update of data, and the primary parameter controls. This increases the confidence level and helps to ensure the integrity of the database.

The problem of control totals takes different forms at different installations. Although hard and fast rules are not possible in this area, some general guidelines can be given.

The DBA needs to ensure that proper consideration is given to the following areas in the design of each application system that will use the database:

- What controls can be checked on every batch update run? For example, record counts, additions, deletions, updates.

- What controls require a full file pass to check them? For example, value field hash totals.

- What input transactions, Adabas logs, etc., should be retained in order to be able to recover when control totals are found to be wrong at the end of a given period?

- Are localized control totals (that is, by branch, product group) of any use in identifying the areas affected by a file control total error?

## Performance Management, Statistics, and Tuning

The following table illustrates some of the monitoring statistics that may be used and what adjustments to (or tuning of) the database environment may result.

Changes in.... | May require tuning of ....
--- | ---
database structure | access method used | hardware or software configuration | processing priority | disk storage allocation
terminal and line traffic |  | Y | Y | Y | Y
response times (application performance) | Y | Y | Y | Y | Y
access totals by user and descriptor | Y | Y |  |  | Y
database size | Y | Y | Y |  | Y
database growth rate | Y | Y | Y |  | Y

When any alteration is made to a production database, care must be taken to ensure a continued high level of reliability and integrity. Whatever the change, the DBA must make sure that the decision is the right one and that it is properly and accurately implemented. He should retain absolute control over the tuning process and ensure that it follows the formal acceptance procedures.

The DBA must be careful not to overreact to changes in the items listed in the table. A sudden change in line traffic, response times, etc., may only be temporary. It is important to determine whether the change represents a permanent trend or a temporary disturbance to the normal way of operating.

The table can be used to determine what tuning may be necessary when a new project will cause a significant change in terminal and line traffic, response times, etc. The DBA can then act in advance to minimize these effects before the new application system is implemented.

## Adabas Session Statistics

The statistics printed at the end of each Adabas session may be used to monitor Adabas performance. Specifically, the session statistics include input/output (I/O statistics, command statistics, buffer/queue usage statistics, highest usage session statistics, and various additional session statistics. This section covers the following topics:

- Input/Output Statistics

- Command Statistics

- Buffer and Queue Statistics

- High Usage Session Statistics

- Additional Session Statistics

### Input/Output Statistics

The following I/O statistics are provided:

- I/O Counts (Including Initialization)
- WORK1 and PLOG write I/O counts (if ADARUN LFIOP>0)
- Distribution of ASSO/DATA I/Os by VOLSER Number (Excluding Initialization)

#### I/O Counts (Including Initialization)

| Reads | Writes
--- | --- | ---
ASSO | 50 | 21
DATA | 2388 | 2184
WORK | 9 | 1385
PLOG | 9 | 1603
CLOG | 0 | 0
TOTAL: | 2456 | 5193
LOG. READS | 33899 |
BUFFER EFF. | 13.9 |

The input/output (I/O) counts represent the number of physical I/Os executed during the session to the Associator (ASSO), Data Storage (DATA), Work (WORK), the data protection log (PLOG), and the command log (CLOG).

Also provided are the number of logical reads issued for the buffer pool (LOG. READS) and the buffer efficiency (BUFFER EFF.) which is the number of logical reads divided by the number of Associator and Data Storage reads. The higher the value for buffer efficiency, the more efficient is buffer pool usage. If the value is less than 10, the DBA may wish to increase the size of the Adabas buffer pool (see the Adabas Operations documentation, the ADARUN LBP parameter description).

#### WORK1 and PLOG write I/O counts (if ADARUN LFIOP>0)

Statistic | Count
--- | ---
PLOG protection blocks | 1603
PLOG different blocks | 1086
PLOG protection I/Os | 1015
WORK1 protection blocks | 1596
WORK1 different blocks | 1023
WORK1 protection I/Os | 995

Write I/O statistics for the database protection areas, PLOG and WORK1 (Work part 1), are provided when the asynchronous buffer flush is enabled (ADARUN LFIOP>0). The total count of protection blocks written, the count of different protection blocks written, and the count of protection write I/Os are provided. The total count includes blocks that were written more than once (with increasing amounts of data). The count of different blocks includes each block only once. The I/O count is lower than the total block count when some I/Os write out multiple blocks in one operation.

For example, if WORK RABN 101 is half full and written out (to make a transaction persistent during an ET command), then filled to completion and subsequently written out together with RABN 102, statistics would be calculated as follows:

- the count of WORK1 protection blocks submitted for write-out would be 3 (101 twice and 102 once);

- the count of different WORK1 protection blocks submitted would be 2 (101 and 102); and

- the count of protection I/Os would be 2 (first 101 alone, then 101 and 102).

These counts can assist with the tuning of database update processing, particularly for update-intensive applications, as well as indicate the efficiency of the settings for ADARUN parameters NWORK1BUFFERS and NPLOGBUFFERS. A large gap between the total count of protection blocks written and the count of different blocks written indicates that many blocks were written out before they were full, usually in order to complete ET commands. In addition to these counts, the high watermarks for NWORK1BUFFERS and NPLOGBUFFERS, if at or close to 100%, may indicate potential for reducing the protection write I/Os by increasing these parameters.

#### Distribution of ASSO/DATA I/Os by VOLSER Number (Excluding Initialization)

VOLSER | HIGH RABN | COUNT
--- | --- | ---
ADA003 | (ASSO: 894) | 38
ADA003 | (ASSO: 2544) | 6
ADA003 | (DATA: 894) | 0
ADA003 | (DATA: 1344) | 4572
TOTAL: |  | 4616

The distribution of I/Os for the Associator and Data Storage per physical volume is also provided. The data provided are the highest RABN accessed/updated (HIGH RABN) and the number of I/Os (COUNT). The DBA can use this data to determine if any adjustments are necessary to the buffer pool parameters and/or to the physical allocation of the database.

### Command Statistics

In the following example, command statistics are provided for a session in which Adabas executed 12687 calls in five threads.

- Distribution of Commands by Source
- Distribution of Commands by Thread
- Distribution of Commands by File
- Distribution of Commands by Type

#### Distribution of Commands by Source

The following table shows the source of commands for the session: either from the same environment (local) or from a remote environment across a network:

Source | Number
--- | ---
REMOTE | 0
LOCAL | 0
INTERNAL | 0
OPERATOR | 12686

#### Distribution of Commands by Thread

The following table shows the thread activity for the session:

Thread | Number
--- | ---
1 | 7328
2 | 2728
3 | 1240
4 | 814
5 | 541
TOTAL: | 12651

If the thread with the highest number has an activity count greater than zero it can be assumed that the Adabas nucleus would be able to process a larger number of commands if the number of threads were increased. Increasing the number of threads would prevent commands from waiting in the command queue for selection.

#### Distribution of Commands by File

The following table shows the distribution of commands by file:

File | Number
--- | ---
0 | 4247
1 | 8404
TOTAL: | 12651

Commands that are not file-related (e.g. BT, ET) are counted against file 0.

#### Distribution of Commands by Type

The following table shows the distribution of commands by command type:

Command Type | Number
--- | ---
A1/4 | 4198
ET | 4191
L1/4 | 4242
OP | 56
TOTAL: | 12687

The command type UC indicates a privileged call issued by Adabas utilities.

Note
The command type REST indicates commands such as C2 and RS.

### Buffer and Queue Statistics

Session statistics include the maximum buffer and queue use during the session. These statistics are presented for all buffers and queues (except the buffer pool) for which high-water marks can be computed. The following table shows high-water marks for a sample session:

Pool Area | ADARUN Parameter | High-Water Mark(1) | %
--- | --- | --- | ---
AB | NAB = 10 | 12032 | 29
CQ | NC = 20 | 3648 | 95
DUQ | LDEUQP = 5000 | 500 | 10
FI | LFP= 12000 | 1760 | 14
HQ | NH = 100 | 552 | 23
PLOG-IO | NPLOGB=1 | 1 | 100
SC | LCP= 10000 | 0 | 0
TBI | LI = 10000 | 0 | 0
TBS | LQ = 10000 | 0 | 0
UQ | NU = 20 | 4880 | 86
UQF(2) | NU = 20 |  |
Wrkl-IO | NWORK1=1 | 1 | 100
WORK | LWP = 14000 | 70464 | 50
Work Part 1 | LP=1500 | 59 | 3
Work Part 2 | LWKP2=372 | 0 | 0
Work Part 3 | No parameter(3)=818 | 0 | 0
XID | XID = 0 | 0 | 0

The high-water marks are provided together with the applicable ADARUN parameter setting that was in effect for the session. The DBA should monitor each high-water mark and, if necessary, make adjustments to the appropriate ADARUN parameters.

Notes:

1. The High-Water Mark values represent the used bytes of the pool. The pool sizes depend on the setting of the appropriate ADARUN parameter. The ADARUN parameter either specifies the size of the pool in bytes (for example, LDEUQP, LFP, LCP, and LI) or the number of elements (for example, NAB, NC, NH, and NU). When the ADARUN parameter specifies the number of elements, the calculation of the pool size determined by multiplying the number of elements by the specific element size. The element size is described in the specific description of the ADARUN parameter.
2. The UQF is the user queue extension that holds the file list. The size of its pool is computed using the UQ pool size.
3. The Work Part 3 value represents the remaining size of the Work data set. It is calculated by subtracting the LP and LWKP2 settings from the total Work data set size (`Work Part 3 = WorkDSsize – LP - LWKP2`).

### High Usage Session Statistics

The following high usage session statistics are produced for a nucleus session:

    There were     nnn  users    participating

    Most calls (       nnn)    initiated by: User   'userid'
                                             Job    jobname
                                             SECUID secuid

    Most I/O-s (       nnn)    initiated by: User   userid
                                             Job    jobname
                                             SECUID secuid

    Most thread time (  hh:mm:ss)   used by: User   'userid'
                                             Job    jobname
                                             SECUID secuid

The following substitutions occur in these statistics:

Substitution | Description
--- | ---
hh:mm:ss | The highest thread time used by a user during the session.
jobname | The name of the job that initiated the maximum number of calls or I/Os during the session or the highest thread time during the session.
nnn | The maximum number of calls or I/Os performed by a user during the session or the number of users participating in the session.
secuid | If known, the security system IDs of the users who initiated the maximum number of calls or I/Os during the session or the highest thread time during the session.
userid | The user IDs (last eight bytes of the communication IDs) of the users who initiated the maximum number of calls or I/Os during the session or the highest thread time during the session. User IDs are printed in text format when they are printable; otherwise, they appear in hexadecimal format.

Note
The high usage session statistics include only users who ended their session with a CL command. Users whose sessions were terminated abnormally (for example, through a timeout) are not included.

### Additional Session Statistics

The following additional session statistics are produced for a nucleus session:

nnn | Formats had to be translated
nnn | Formats had to be overwritten
nnn | Autorestarts were done
nnn | Throw-backs due to ISN problem
nnn | Throw-backs due to space problem
nnn | Buffer-flushes were done

These are described in more detail in this section:

- Formats Translated/Overwritten
- Autorestarts
- Command Throwbacks
- Buffer Flushes

#### Formats Translated/Overwritten

Adabas read and update commands require a format buffer that specifies the fields to be read or updated. This format buffer is interpreted and converted into an internal format buffer by Adabas, which enters each resulting internal format buffer into the internal format buffer pool. Each internal format buffer is identified by a combination of user and command IDs.

For each new read/update command, Adabas looks to see if a user ID/command ID entry is already present in the format buffer pool. If not, Adabas translates the command's new format buffer and enters it into the pool. Once the format buffer pool becomes full, an existing entry must be overwritten to accommodate a new entry.

The format translation process is CPU intensive. Therefore, the DBA should ensure that an excessive number of format overwrites are not occurring by doing the following:

1. Ensure that user programs are making correct use of command IDs; that is, using non-blank command IDs when appropriate and releasing command IDs when no longer needed. For further information on command ID use, refer to the Adabas Command Reference documentation.

2. Consider increasing the size of the internal format buffer pool (with the ADARUN LFP parameter, described in the Adabas Operations documentation).

The Adabas nucleus produces statistics on format translations and format overwrites at the conclusion of each session. The Adabas operator command DSTAT may also be used to obtain this information.

#### Autorestarts

The number of Autorestarts performed during the session.

#### Command Throwbacks

The number of times a command could not be executed because the Adabas nucleus was waiting for

- an available ISN; or

- Adabas work pool space.

In such an event, the command is thrown back into the command queue for processing at a later point in time.

If either of these numbers is greater than zero:

1. adjust the ratio between the ADARUN LWP (work pool size) and LS (sort work area) parameters;

2. increase the size of the Adabas work pool (ADARUN LWP parameter);

3. evaluate ADARUN TT (transaction time limit) parameter;

4. check application program hold logic;

5. increase the Adabas hold queue size (ADARUN NH parameter); and

6. use superdescriptors to reduce complexity of search commands.

The ADARUN parameters are described in the Adabas Operations documentation.

#### Buffer Flushes

The number of buffer flushes performed during the session.

The Adabas buffer pool represents a virtual database that is shared by all active users. It contains the most frequently used Associator and Data Storage blocks, and its purpose is to minimize physical I/O activity.

The size of the buffer pool is determined by the ADARUN LBP parameter. LBP should be set as large as possible with the restriction that setting too large a value may cause excessive paging by the operating system.

## Command Logging

Adabas command logging may be used to generate information on all the commands issued by users to Adabas. Some of the information provided is

- user identification;

- time of day;

- the command used;

- the file accessed;

- the record accessed;

- the Adabas response code received;

- the time required for the command to perform.

Command logging is controlled by the ADARUN parameter LOGGING.

### On this page

- Monitoring Resource Use
- Reporting on Resource Use
- Monitoring Database Controls
- Performance Management, Statistics, and Tuning
- Adabas Session Statistics
- Command Logging