pajama

 

A possible cause for slow connection to the database server

A possible cause for slow connection to the database server

www.ibm.com

 

Symptom

DML operations failing with ISAM Error -104

Cause

The 32K File Descriptors (open tables) per thread limit has been reached

Diagnosing The Problem

Use onstat -g opn and look at the ucount column

IBM Informix Dynamic Server Version 11.70.FC2 -- On-Line -- Up 14:23:05 -- 2964472 Kbytes
tid rstcb isfd op_mode op_flags partnum ucount ocount lockmode
671522 0x000000009c9e0a20 0 0x00000400 0x00000397 0x004000da 2 2 0
671522 0x000000009c9e0a20 1 0x00000002 0x00000003 0x004000da 2 2 0
671522 0x000000009c9e0a20 2 0x00000400 0x00000397 0x00100003 2 2 0
671522 0x000000009c9e0a20 3 0x00000002 0x00000003 0x00100003 2 2 0
671522 0x000000009c9e0a20 8 0x01000400 0x00000407 0x0090003c 32692 0 0
671522 0x000000009c9e0a20 9 0x01000440 0x00000797 0x0090003d 32692 0 0

(please note there are 2 partitions - 0x0090003c, 0x0090003d - each of which is opened 32692 times)

Resolving The Problem

*Check the application logic.
*Check that UDRs are not compiled with PDQ set

If you have a user defined routine (UDR) which is called as a
part of many update/delete triggers on different tables
and if the application logic in the update/delete triggers
mimics some kind of hierarchy (so a modification in one table
can cause many changes in many other tables), the UDR can get
called multiple (thousands) times in one session.

If the UDR
1) contains a query which needs to materialize some view
2) was created with PDQPRIORITY > 0 (or the last 'update
statistics low [for procedure]' command was run with PDQPRIORITY
> 0)

each of its invocations materializes the view, leaving the base
tables opened at the RSAM level.

Question

Your read cache rate (onstat -p) rises durning server use to ninety-nine percent. You suspect that you have overallocated buffers to the extent that you are wasting memory. How can you determine how much of your buffer cache memory is used?

Answer

Count the number of buffers in use at regular intervals and compare with the total allocated. You can use the following two methods to count the number of buffer pages in use at a given point in time:

• Execute the onstat -B command. The number of data lines in the output is the number of buffers in use. The onstat -B output includes all the configured buffer pools, grouped by page size. You will have to count the lines in the individual groups if you have more than one buffer pool.
  
  
  
• Open the sysmaster database and execute the following query:
  
  SELECT COUNT(*) AS pages_used
  FROM sysbufhdr
  WHERE bufsize = <buffer_page_size>
  AND bflags != 4;
  
  The buffer_page_size is the page size (in bytes) for the buffer pool you are examining. The buffer page size is listed in the onstat -b/B output. If you only have one buffer pool, you can leave out the buffer page size filter and the query becomes:
  
  SELECT COUNT(*) AS pages_used
  FROM sysbufhdr
  WHERE bflags != 4;

Question

Your IBM Informix server is processing queries more slowly than expected. You want to determine what is slowing the instance. Were do you start your analysis?

Answer

Preconditions

• This performance problem is not limited to one query or a class of queries.
• The database statistics (UPDATE STATISTICS) were collected with appropriate performance considerations and are current.
• The performance is a problem for both network connections to the server and shared memory connections.

Recommended Approach

Informix servers provides a number of commands, the onstats, for reporting system status. You can generally start your analysis using seven of the commands. Examination of the data often leads to further analysis and resolution. The recommended commands are:

• onstat -m
  
  Message log. The message log contains warning and error messages. The onstat -m only reports recent updates to the log file. You may want to look at earlier log entries.
  
  
• onstat -u
  
  User thread status. Look for user threads waiting on a resource. You will see a wait indicator in the first position of the flags field. The address of the resource is in the wait field. The specific wait flag, the type of resource, and cross references follow:
  
  B - wait on buffer - match the wait address to a buffer in onstat -b
  
  C - wait on checkpoint - examine onstat -g ckp and onstat -g con
  
  G - wait on write to log buffer - match the wait address to a log buffer in onstat -l
  
  L - wait on lock - match the wait address to the address of a lock in onstat -k
  
  S - wait on latch - match the wait address to a latch (mutex) in onstat -lmx and onstat -wmx
  
  Y - wait on condition - listed in onstat -g con and do not typically reflect performance problems to the extent that they help with diagnosis
  
  There are several other flags but they are rarely observed.
  
  The first field of the onstat -u output, address, maps to the rstcb field of the onstat -g ath output for thread identification. The sessid field is the session id (SID). You can learn more about resources allocated to a session and its activity with onstat -g ses <SID>.
  
  Collect onstat -u several times in rapid succession. If the waiters persist over a measurable clock time, then the chances are very good that the waits reflect a processing problem that affects performance. Some waiters are normal but they should disappear quickly. Keep in mind that lock waits are programmed.
  
  The last two fields of onstat -u, nreads and nwrites, can be useful indicators of thread activity.
  
  
• onstat -p
  
  Server statistics. The single most important performance statistic in this output is the read cache rate (the first %cached field). Your goal for an OLTP system is to have a read cache rate above 95 percent during normal processing. Try for a rate above 99 percent. Increase the cache rate by adding buffers, which are configured using the BUFFERPOOL configuration parameter. Make sure that you have plenty of system memory (onstat -g osi) when increasing the Informix server memory allocation. Increasing the server memory footprint can indirectly slow the instance by increasing paging/swapping at the OS side. You can use a sysmaster query (see Related Information below) to help determine if you have configured too many buffers.
  
  Other statistics, like bufwaits (waiting for buffer), seqscans (sequential scans), and the read aheads should be considered. In the case of read aheads, the sum of ixda-RA, idx-RA, and da-RA should be close to the value of RA-pgsused as an indicator of effective read-ahead configuration.
  
  Many of the same statistics are viewed on a partnum level with onstat -g ppf.
  
  Consider collecting and retaining onstat -p outputs at regular intervals for comparison. Note that cumulative statistics are zeroed with onstat -z if you want to observe statistics over a limited time interval.
  
  
• onstat -g rea
  
  Ready queue. Reports threads ready to execute at one moment in time. These threads are prepared for execution but lack cpu resource. If the number remains above the number of cpu virtual processors (onstat -g glo) through repetitions of onstat -g rea, then your system is likely limited by processing power. Look for inefficient queries and non-server processing on the machine. See onstat -g glo output for cpu use integrated over time and onstat -g osi for system resources.
  
  
• onstat -g act
  
  Active threads. You will usually see poll threads and listen threads in this queue. Look for threads doing query-related work, like sqlexec and I/O threads. If none show up or they are rare, then work is not getting to the processors.
  
  
• onstat -g ioq
  
  I/O request queues. The statistic to monitor is the maxlen column. This is the maximum length of a queue after the engine was brought online or onstat -z was executed. If the number is too large, then at some point the I/O requests were not serviced fast enough. Try executing onstat -z and checking to see how long it takes for large maxlen values to return.
  
  For informix aio monitor the gfd (global file descriptor) queues. The maxlen should not be greater than 25. The system uses gfd 0, 1, and 2 (stdin, stdout, stderr), so the informix chunks start with gfd 3 (chunk 1).
  
  If you are using kaio monitor the kio queues. The maxlen values should not exceed 32. There will be one kio queue for each cpu virtual processor.
  
  Recommendations for maxlen with DIRECT_IO enabled have not been determined, but are not expected to be larger than 32.
  
  
• onstat -g seg
  
  Shared memory allocations. The shared memory configuration parameters should be tuned so that the server dynamically allocates at most two virtual segments during normal processing.

Question

This document describes a few methods that can be used to find files opened by a process. This information can be useful when debugging processes that open too many files, or have a file leak.

Answer

Introduction

A system administrator might find it necessary to obtain information about all files that are currently opened by a process. A process might have a defect that causes it to continuously create files without closing them, or it might open files, read and write to those files, but fail to close the files afterwards. In such cases it is useful to obtain as much information as possible about the files that have been opened by a process to help pinpoint the cause of the problem. AIX has a virtual file system mounted at /proc that provides information about running processes, including the files opened by those processes. AIX also includes a number of commands that can be used to obtain information about files opened by processes. Open Source commands such as lsof can also be used.

/proc File System

The /proc file system is a virtual file system, meaning it does not contain actual files residing on a disk or in RAM. But the /proc file system contains virtual files that can be manipulated just like real files. These virtual files provide information about processes currently running on a system, using standard UNIX commands and methods for accessing files. Under /proc there are virtual directories named with the process IDs (PIDs) of all processes currently running on the system. Inside of each of these directories are more subdirectories. These subdirectories organize all of the available information about running processes. One of the subdirectories is named fd, an abbreviation for file descriptor. Inside fd is a list of virtual files with numbers for file names. These numbers are the file descriptor numbers assigned by the operating system to the real files that have been opened by the process. In the following example, we find that the process with PID 184422 has only one opened file with file descriptor 4.

# cd /proc/184422/fd
# ls -l total 16 -r--r--r--   1 root system   4811 Jul 12 2004  4

procfiles Command

The AIX procfiles command lists all files opened by a process. For each file the command also provides the inode number for the file, and additional information such as the file size, and uid and gid. Here is an example of procfiles output for the same process with PID 184422 that we found in the /proc file system above.

# procfiles 184422

184422 : /usr/sbin/hostmibd
  Current rlimit: 2147483647 file descriptors
   4: S_IFREG mode:0444 dev:10,5 ino:13407 uid:0 gid:0 rdev:0,0
      O_RDONLY size:4811

Again we see that process 184422 has one opened file with file descriptor 4. File descriptor 4 has major,minor numbers of 10,5 and an inode number of 13407. We can use the following procedure to find the device where the file is located.

# cd /dev
# ls -l | grep "10, *5"
brw-rw----   1 root     system       10,  5 Oct 10 2005  hd2
crw-rw----   1 root     system       10,  5 Oct 10 2005  rhd2

So the device or logical volume that contains the file system in this example is /dev/hd2.

# lsfs | grep hd2
/dev/hd2        --         /usr           jfs2 3801088 yes no

This filesystem is mounted at /usr.

We can use the following command to obtain information about the file with file descriptor 4 and inode 13407.

# istat 13407 /usr
Inode 13407 on device 10/5
File Protection: rw-r--r--
Owner: 2(bin)           Group: 2(bin)
Link count:   1         Length 4811 bytes
Last updated:   Tue Aug 24 16:14:48 CDT 2004
Last modified:  Mon Jul 12 11:33:31 CDT 2004
Last accessed:  Wed Aug  9 09:16:28 CDT 2006
Block pointers (hexadecimal): 1892c

We can use find command to find all file names in the filesystem /usr with an inode of 13407.

# cd /usr
# find . -inum 13407 -exec ls -l {} \;
-rw-r--r--   1 bin      bin            4811 Jul 12 2004
./lib/nls/msg/en_US/hostmibd.cat

Notice the 1 just before the first bin. This indicates that there is only 1 hard link, meaning the file name hostmibd.cat is the only file name associated with this inode.

pstat Command

The AIX pstat command can be used to list all files opened by a process. Here is an example that finds all files currently opened by the cron process.

# ps -ef | grep cron
    root 323762      1   0   Oct 06      -  0:07 /usr/sbin/cron

The PID for cron is 323762, which is 0x4F0B2 in hex.

# pstat -a | grep -i 4F0B2
SLT ST    PID   PPID   PGRP   UID  EUID  TCNT  NAME
 79 a   4f0b2      1  4f0b2     0     0     1  cron

We can use the slot number to display the file system state info and the file descriptor table. In this example we see that cron has 13 opened files, numbered from 0 to 12.

# pstat -u 79 | grep FILE
FILE SYSTEM STATE
FILE DESCRIPTOR TABLE

# pstat -u 79 | grep -p "FILE DESCRIPTOR TABLE"
FILE DESCRIPTOR TABLE
    *ufd: 0xf00000002ff49e20
    fd 0:  fp = 0xf1000714500080e0   flags = 0x0080  count = 0x0000
    fd 1:  fp = 0xf100071450007fa0   flags = 0x0080  count = 0x0000
    fd 2:  fp = 0xf100071450007fa0   flags = 0x0080  count = 0x0000
    fd 3:  fp = 0xf100071450007780   flags = 0x0080  count = 0x0000
    fd 4:  fp = 0xf100071450007af0   flags = 0x0080  count = 0x0000
    fd 5:  fp = 0xf1000714500079b0   flags = 0x0080  count = 0x0000
    fd 6:  fp = 0xf1000714500066a0   flags = 0x0080  count = 0x0000
    fd 7:  fp = 0xf100071450008270   flags = 0x0080  count = 0x0000
    fd 8:  fp = 0xf1000714500081d0   flags = 0x0080  count = 0x0000
    fd 9:  fp = 0xf100071450008220   flags = 0x0080  count = 0x0000
    fd 10:  fp = 0xf100071450008180   flags = 0x0080  count = 0x0000
    fd 11:  fp = 0xf1000714500082c0   flags = 0x0080  count = 0x0001
    fd 12:  fp = 0xf100071450008130   flags = 0x0081  count = 0x0000

lsof Command

The lsof command is an open source command available for free on the internet. lsof is a very powerful command with many options so we only list a few uses for lsof in this document.

# lsof -u account | wc -l
Displays the total number of open file handles in the specified account.

# lsof -u account | grep pid | wc -l
or
# lsof -p pid
Displays the total number of open files in the specified account name for the specified pid.

lsof indicates if the file descriptor is associated with an open socket or an open file.

Conclusion

The /proc virtual file system and the AIX commands procfiles and pstat can be used to list information about files that are currently opened by a process. This information can be used to investigate processes that are having certain types of problems with files. The open source lsof command is also useful for providing information about files opened by processes, and files opened under specific user accounts.