Resource and Maintenance Problems

Good performance requires planning and regular maintenance. Make sure your operating system is configured with sufficient resources for Ingres. Insufficient system resources cause poor performance or prevent Ingres from starting.

Tools for Identifying Operating System Problems

The following tools can help you identify operating system resource problems:

• Review the minimum requirements for a basic Ingres installation given in the Readme file. If your particular environment requires more resources, use the Ingres utilities to verify that there are enough resources.
• System resources can be monitored by some operating system utilities. Syntax details are described in Unix Operating System Utilities.

  UNIX:

  BSD:

  pstat utility—to display the status of UNIX system tables and system swap space

  vmstat utility—to display virtual memory status

  System V:

  • sar utility—to display activity of various system resources such as CPU utilization, swapping activity, and disk activity.
  • show memory—displays the system memory resources and the amount of non-paged dynamic memory (total, free and in use).
  • show process/id=pid/continuous—displays the amount of page faulting, working set, buffered I/O, and direct I/O the server is doing.
  • show device—indicates if a particular disk drive is out of disk space.
  • show device /files—if there is a problem starting an installation, this command can be used to make sure that an Ingres process is not holding on to a mailbox.

    The installation utility allows the examination of all Ingres installed images, showing the amount of global pages and sections available and used.

    VMS:

    The following VMS tools are very useful for tracing script problems:

  • set verify—allows you to see the commands used in the installation script or any other VMS command procedure.
  • set watch file/class=major (requires CMEXEC VMS privilege)—allows all the files that are being accessed to be displayed. This assists in diagnosing if a location of a file or library is being incorrectly referenced. Use this command when debugging Ingres processes interactively.
  • The displays can be turned off with the following command entered at the operating system prompt:

    Important! This is an unsupported VMS command. Use at your own risk.

    set watch file/class=none.

    Syntax details are described in VMS Operating System Utilities.

Diagnose Poor Performance Due to Insufficient System Resources

If Ingres seems slow or unresponsive for no apparent reason, follow these steps to diagnose the problem. Write down any error messages you receive when performing these steps:

1. Connect to your DBMS Server through Ingres monitors:
   1. First display the server_number of your DBMS Server using the iinamu utility:

      iinamu

      IINAMU> show ingres

   2. Connect to the DBMS Server monitor by typing the command:

      iimonitor server_number

   3. To see the DBMS Server sessions, at the iimonitor prompt type:

      IIMONITOR> show sessions

   4. Check the status of the sessions to determine which one is making excessive use of the server. (You can use VDBA to check session status.)

      For syntax details, see the sections iimonitor and iinamu in the Command Reference Guide.

2. If repeated "show sessions" commands in iimonitor show that the query session is continually in a CS_EVENT_WAIT (LOCK) state, the problem involves concurrency and locking.

   Alternatively, you can use the VDBA Performance Monitor to check for this problem.

   1. Select Servers in the left pane of the Performance Monitor.
   2. Select INGRES.
   3. Select Sessions in the Servers.
3. If the session alternates between CS_EVENT_WAIT and CS_COMPUTABLE, this indicates that the query is processing. However, if the query is taking an excessive amount of time, set up a trace on it, as described in Trace Utilities.
   1. Interrupt the query that is running:
      • Interactively, use Ctrl+C and wait.
      • In batch or background mode, use the following command to terminate:

        Windows: In the Task Manager, highlight iidbmst and click on End Process

        UNIX:

        kill pid

        VMS:

        stop process/id = pid

        where pid is the process ID of the query.

        Important! The command format "stop proc" must be used only as a last resort. Use of this option can cause more problems than it solves.

   2. Issue the command set gep.
      • Rerun the query. This outputs a query execution plan.
      • Alternatively, start an SQL window on the database in VDBA and click the Display Query Execution Plan button to graphically display the query plan.
      • It is important to note that interrupting a query requires some time because Ingres is optimized to commit rather than back out of transactions. It takes at least as long to back out of a transaction as to process the transaction normally. The transaction must be fully backed out before sessions can resume and locks are freed.
4. It is useful to note whether the query runs differently when called from other Ingres tools. For example, try issuing the same query from Interactive SQL, and Embedded SQL
5. Determine if you can access all of the data in the tables in all components of the query.
   1. From the Terminal Monitor type:

      select count(*) from tablename

      This verifies that Ingres can sequentially access every row in the table and indicates that other access paths (secondary indexes, hash pointers, B-Tree page pointers, and so on) can cause the problem. Queries using restrictive where clauses probably are using these secondary access methods.

   2. Check for permits that apply to this data by typing the following command from the Terminal Monitor:

      help permit tablename

   3. Check for restrictions that you have placed on this data through the Knowledge Management Extension features, such as database rules. From the Terminal Monitor enter the following query to determine if a table is subject to restrictions imposed by a database rule:

      select * from iirules where table_name = 'tablename'

      Alternatively, in the right pane of VDBA's Database Object Manager (DOM) window:

      • Select the Databases branch and the desired database
      • Select Tables and the desired table_name
      • Select Rules