Performance Problems

Most performance problems stem from multiple causes. A complete performance analysis must include each item in the Diagnostic Hierarchy section below. But if you need to resolve a specific performance problem quickly, focus your attention on that area. The information in this troubleshooting section is designed to assist you in resolving a performance issue after first isolating which factors are influencing performance.

Use the procedures in this section if an Ingres tool "hangs," that is, your program seems to start but nothing happens. Other programs such as SQL display header information, but when you issue a command, nothing happens.

Flow Diagram for Troubleshooting Performance Problems

Use the following flow chart to identify and isolate a system performance problem:

Diagnose DBMS Server Due to Logging System Problem

Use the following procedure to diagnose a DBMS server that is not responding due to logging system problems.

Check the logging system by issuing the following command at the operating system prompt to invoke the logstat utility:
Windows:

logstat | more

UNIX:

logstat | more

VMS:

define sys$output filename

logstat

deassign sys$output
If you are unable to start up logstat, the recovery process has probably taken an exclusive lock and is in recovery.
If logstat starts up, check the status field, as described in Logstat Status Fields.

Logstat Status Fields

The problem states and resolutions of logstat fields are described in the following table:

Status Field	Description	Action
ONLINE, ECPDONE	A consistency point is completed. The system is fully functional and online.	The logging system has finished a consistency point. This status flag is present most of the time while the logging system is functioning normally. If this is your status, stop this procedure and review Operating System Resources.
LOGFULL	The log file is full.	A status of LOGFULL means that the system is suspended from processing new requests because there is no more room in the transaction log file. It remains so until the problem is corrected. Examine the other status field entries. If ARCHIVE is also indicated, the Archiver is actively processing the log file to free up space. The LOGFULL condition is removed when the Archiver is finished.
CPNEEDED	A consistency point is needed.	CPNEEDED means the logging system is about to take a consistency point.
ARCHIVE	The Archiver is processing.	The status ARCHIVE means the Archiver process is archiving.
START_ARCHIVER	The Archiver has stopped.	A status of START_ARCHIVER means that the archiver process stopped. Restart the Archiver.
FORCE_ABORT	The force-abort- limit has been reached.	If the status is FORCE_ABORT, a transaction came too close to the end of the transaction log file before the oldest was committed to disk, and reached the force-abort-limit. The oldest open transaction is aborted to free space for the new one. You can use logstat (or ipm) to monitor the force abort. The system frees the FORCE_ABORT status when the abort operation is completed.
RECOVER	The recovery process is performing recovery.	If the status is RECOVER, the logging system is in recovery, a normal mode. Recovery requires some time because Ingres is optimized to commit rather than back out of transactions.

Important! If the server is recovering a lengthy transaction, be aware that shutting down the server with iimonitor's "stop server" (or by killing processes at the operating system level) results in slower recovery. Allow the recovery to proceed normally. You can monitor the progress of the recovery with logstat and iimonitor or the ipm utility.

Many other normal states appear on the status line. For additional information on reading logstat output, see the Logstat section in the Command Reference Guide.

How to Avoid Logfull Abort

To avoid logfull aborts, examine the transaction processing strategy errors that caused the log file to fill. Consider the following points:

If transactions do many updates or are left open for a long time, you must either commit them more frequently or increase the size of the log file. If the log file size is increased, check the "percentage of logfile written" before a consistency point is taken. For details, see the ipm, logstat, or VDBA output.
Avoid continuous transaction errors by:
- Setting autocommit on where applicable
- Avoiding application errors that are caused by beginning transactions before they get all the user input required or by failing to use Ingres application timeout features. (Timeout features are discussed in the SQL Reference Guide and Forms-based Application Development Tools User Guide. Also, for a discussion of transaction processing, see the SQL Reference Guide.
If you have taken the above precautions but still get log full/abort, the transaction log is sized too small for the types of transactions you need to process. Read the tips in the next section on choosing the correct transaction log file size.
Use set log_trace to examine the size and type of log records being written to the log file.

Process of Resizing Transaction Logs

Resizing transaction logs involves these steps:

Determine whether the log requires resizing
Resize the transaction log file
Reestablish Dual Logging

Determine Whether a Log Requires Resizing

To determine whether a log requires resizing, use one of the following procedures.

Using VDBA's Performance Monitor, start a monitoring session on the desired node:

Select the node in the left pane and click the Monitor toolbar button.
Select Log Information in the branch.
Click the Header tab in the right pane to display log usage.

Start the Interactive Performance Monitor utility (ipm).

Highlight the Log_info menu item and select the Select option.
Highlight the Header menu item and select the Select option.
The Log file Diagram shows the percentage use of the Transaction Log.
Exit the ipm program.

Find the current peak transaction load by using the logstat utility to monitor the amount of log file that is in use during your peak hours. This allows you to choose the correct size for your log file. Because requirements change, you must monitor the log file regularly.

Enter the logstat command.
Find the value under % of log file in use.
If the log file needs to be resized, select the new size.
UNIX:

Use mkrawlog to reconfigure a raw log file.
Shut down the Ingres installation by entering the ingstop command.

Resize the Transaction Log File

To resize the transaction file, follow these steps:

Shut down the Ingres installation by entering the ingstop command.
Start the Configuration-By-Forms (cbf) or Configuration Manager (vcbf) utility.
1. Choose Transaction Log from the menu.
  The file name is displayed along with the current size of the Transaction Log.
2. Choose Destroy.
  When destroyed, the Transaction Log information table is emptied.
3. Select the Create option.
4. Enter the required Transaction Log file size in megabytes.
  The transaction log is created.
Exit cbf or vcbf.
Restart the Ingres installation by entering the command ingstart.

Important! Reconfiguring your log file destroys the current contents of the file and leaves an empty, reinitialized log file after the reconfiguration is complete. When Ingres is shut down, all transactions are written to disk; therefore, to prevent inconsistent database problems, reconfigure the log file only after a successful Ingres installation shutdown procedure.

Reestablish Dual Logging

A failure in one of the two dual log files is most often symptomatic of hardware problems. In the event of such a failure, you must reestablish dual logging at the first opportunity.

To re-establish dual logging after a failure of one of the log files, follow these steps:

Shut down the installation.
Create a new log file in the location of the failed log.
Access the Configuration-By-Forms utiltity
Windows: Click Start on the taskbar, and chose Programs, Ingres, Configuration Manager.

UNIX: Type cbf or vcbf on the command line to access the Configuration-By-Forms and Configuration Manager Utility.

VMS: Type cbf or vcbf on the command line to access the Configuration-By-Forms and Configuration Manager Utility.
Select dual transaction log.
Select Reformat to reestablish dual logging.
This automatically determines which of the log files is valid and copies the contents of the valid log to the newly created log file. After the copy is complete, both log files are marked valid.
Restart the installation with the ingstart command.
Check that both log files are enabled. You can check the enabled status using either the Configuration-By-Forms (cbf) or Configuration Manager (vcbf) utility.