Identifying Operating System Resource Problems
The following tools can help you identify operating system resource problems:
• Review the minimum requirements for a basic Actian X installation given in the Readme file. If your particular environment requires more resources, use the Actian X utilities to verify that there are enough resources.
Linux:
BSD:
pstat utility—to display the status of Linux 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 Actian X process is not holding on to a mailbox.
The installation utility allows the examination of all Actian X installed images, showing the amount of global pages and sections available and used.
Check System Resources
If Actian X 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 Actian X monitors:
a. First display the server_number of your DBMS Server using the iinamu utility:
iinamu
IINAMU> show ingres
b. Connect to the DBMS Server monitor by typing the command:
iimonitor server_number
c. To see the DBMS Server sessions, at the iimonitor prompt type:
IIMONITOR> show sessions
d. 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.
a. Select Servers in the left pane of the Performance Monitor.
b. Select INGRES.
c. 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 on page 15.
a. 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
Linux:
kill pid
where pid is the process ID of the query.
The command format “stop proc” must be used only as a last resort. Use of this option can cause more problems than it solves.
b. 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.
• Interrupting a query requires some time because Actian X 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 Actian X 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.
a. From the Terminal Monitor type:
select count(*) from tablename
This verifies that Actian X 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.
b. Check for permits that apply to this data by typing the following command from the Terminal Monitor:
help permit tablename