Using IPM to Monitor and Troubleshoot

This chapter provides procedures for using IPM in specific situations. These procedures show how to use IPM as a general purpose and problem-solving tool.

This chapter describes procedures for:

• Monitoring
  • Determining which servers are running
  • Viewing a list of sessions in a DBMS server
  • Viewing the amount of logging and locking resources being consumed
  • Using IPM when you have an unresponsive server
• Analyzing performance
  • Checking on slow system response time during heavy transaction activity
  • Finding concurrency problems by monitoring the locking system
  • Determining the efficiency of group commit in your installation
• Managing the system
  • Determining how close to capacity the log file is
  • Locating a transaction that is preventing reclamation of log file space
• Troubleshooting
  • Using IPM utilities for troubleshooting an Ingres installation

View Running Servers

To determine which servers are running in an installation, use IPM to display either the Server List or the Log Process Display.

To display the Server List screen

Select Server_List from the Main Menu.

Only servers registered with the Name server are shown.

To display the Log Process Display screen

1. Select Log_Info from the Main Menu.

   The Log_Info Menu is displayed.

2. Select Processes.

   The Log Process screen is displayed. All servers, and the archiver and recovery processes in the current installation are shown.

View Sessions

To view a list of sessions in a DBMS server

1. Select Server_List from the Main Menu.

   The Server List screen is displayed.

2. Select a server and then select the Sessions menu item.

   The Session List screen is displayed. If desired, you can view session detail. Privileged users can remove unwanted sessions.

Monitor Resource Usage

If you are monitoring a busy system and want to view the amount of locking and logging resources being consumed for a certain period, use the Interval option with autorefresh on.

Both the Locking System Summary and Logging System Summary screens allow statistics to be collected over an interval whose starting point is determined by selecting the Interval menu item.

To start the interval

1. Invoke IPM with autorefresh on. To do this, either use the -r flag on the ipm command, or set the autorefresh option on the Option Selection screen (select the Options menu item from the Main Menu).
2. Select either Lock_Info or Log_Info from the Main Menu.

   Either the Lock Info Menu or Log Info Menu is displayed.

3. Select Summary from either the Lock Info Menu screen or the Log Info Menu screen.

   The System Summary screen is displayed.

4. Select the Interval menu item.

   The Interval submenu is displayed.

5. Select the Begin_Now menu item to enable the Interval option.

   A starting and current time is displayed. The starting times and statistics are saved until reset. This allows you to use other parts of IPM and view these screens to see the latest cumulative locking and logging statistics.

Operate With an Unresponsive Server

If a server becomes unresponsive, you can still monitor the state of the system with IPM. There are two methods:

• Restart IPM with the -s (stand-alone) option.

  In this case, IPM cannot print database or table names because it cannot connect to any server in stand-alone mode. However, this is the simplest method.

• Start up a second server.

  Delete the first (problem) server from the Name Server's list using the iinamu utility. You must be a privileged user to invoke this utility. For more information on starting a server and using the iinamu utility, see the System Administrator Guide.

  After IPM connects to the new server, you can use IPM to determine the state of the system. Use the Log and Lock Information areas (Log Info and Lock Info from the Main Menu) to view the state of the system.

Check for Logging Delays

Certain logging conditions may result in user complaints of slow response time. The Logging System Header screen in IPM can help you determine if a sluggish system is due to logging activities.

To check for logging delay

1. Select Log_Info from the Main Menu.

   The Log Info Menu is displayed.

2. Select Header.

   The Logging System Header screen is displayed.

3. Check the Status field.

   Any of the following states indicates that the delay is due to the logging system:

   • LOGFULL,CPFLUSH

     The log file is overly full while executing a consistency point (CP). The CP must complete before log space can be released. The status LOGFULL, ARCHIVE may also be displayed.

   • LOGFULL,ARCHIVE

     The log file becomes full and journaled transactions must be archived to free up log space. When the archiver is done, log space is released.

   • LOG_FULL

     The log file has reached a limit where all database updates/deletes/inserts are stalled until enough space is reclaimed. The Warning field is also visible.

   • FORCE_ABORT

     The logging system is backing out the oldest transaction. Doing this does not block any other transactions, but it does take CPU cycles. The amount of CPU cycles used depends upon what the transaction was doing when it was singled out for rollback.

Locate Concurrency Problems

If a session is stalled, it may be waiting for a lock.

To check for concurrency problems

1. Select Lock_Info from the Main Menu.

   The Lock Info Menu is displayed.

2. Select Locks.

   The Lock List Display screen is displayed.

3. Tab to the Status column and select the Find menu item.

   The String to search for prompt appears.

4. Type WAIT and press the Return key.

   If there are lock lists with blocked locks, the cursor is placed on that lock list. Any lock lists with a status of WAIT are blocked from proceeding.

5. Select the Block_Info menu item to determine what lock is preventing the transaction from proceeding. (The cursor must be on the blocked lock list.)

   The Blocking Lock Display screen is displayed.

6. Look in the scrolling area. The blocking lock is at the top, and all other waiting locks are below. Note the session name so that you can track down the user.
7. Select End.

   You are returned to the Lock List Display screen.

8. Select a lock list and than select the Examine menu item to view the individual locks for that lock list.

Determine Group Commit Efficiency

You can use IPM to determine how efficient group commit is in your installation. The ratio of Log group count to Log group commit indicates how many waiting transactions are satisfied for each group commit operation.

To determine the efficiency of group commit in your installation

1. Select Log_Info from the Main Menu.

   The Log Info Menu is displayed.

2. Select Summary.

   The Logging System Summary screen is displayed.

3. Look at the ratio of Log group count to Log group commit.

   A ratio of 10:1 indicates that 10 commits were satisfied with one group commit write. You can use the Interval menu item to observe this over a short period of time.

   If the ratio approaches 1:1, potentially empty (or near empty) log buffers are being written out, possibly wasting space in the log file. To avoid this, decrease the size and number of the log buffers.

Check Log File Capacity

The Logging System Header screen in IPM lets you determine how full and how close to FORCE_ABORT the log file is.

Note: This procedure is most useful when the autorefresh option is set.

To check the capacity of the log file

1. Select Log_Info from the Main Menu.

   The Log Info Menu is displayed.

2. Select Header.

   The Logging System Header screen is displayed.

3. Check the following values:
   • The Log file diagram, which shows how full the log file is.
   • The Blocks in use field.
4. Compare the Block in use value to Abort interval and Logfull interval to determine how close to FORCE_ABORT and LOG_FILE_FULL the logging system is.

Locate a Problem Transaction

To see which transaction is preventing reclamation of log file space

1. Select Log_Info from the Main Menu.

   The Log Info Menu is displayed.

2. Select Header.

   The Logging System Header screen is displayed.

3. Examine the Log File diagram.

   The diagram depicts the log file as it logically appears. The field boundaries represent the physical begin and end of file. The logical begin and end are marked by > symbols.

4. Select the Examine menu item.

   A submenu and additional transaction information on the Logging System Header screen is displayed.

   On this transaction display, you can view the transactions that span the portion of the log file represented by the ^ symbol on the Log file diagram field. All transactions found for the current portion of the log file appear in the scrolling area on the bottom right of the screen. The Next and Previous menu items move the caret symbol to any area of the Log file Diagram covered by the arrow (">----->").

5. Select Next to logically step through the log file (starting at the BOF--where the caret rests) until a user transaction appears in the scrolling area.

   This transaction is the oldest transaction in the log file that is preventing log file space from being reclaimed.

6. Take steps to remove or commit the offending transaction.

IPM as a Troubleshooting Tool

You can use IPM as an efficient troubleshooting tool to locate the source of various operating or functional problems by using IPM utilities. IPM includes the following utilities, often used in troubleshooting an Ingres installation:

• iimonitor

  This utility is used to monitor and administer DBMS servers. With iimonitor or IPM, you can examine the status of a DBMS server or shut down a server or particular server session.

• lockstat

  The lockstat utility allows you to display locking status information.

• logstat

  The logstat utility allows you to display logging status information.

• iinamu

  This utility is used to monitor and administer the Name Server. You can display DBMS server information, register servers, and delete servers from the registered list.

For detailed troubleshooting procedures, see the System Administrator Guide.