Top Ten onstat Commands

onstat -D

INFORMIX-OnLine Version 7.12.UC2   -- On-Line -- Up 122 days 20:48:40 -- 72616 s

Dbspaces

address  number   flags    fchunk   nchunks  flags    owner    name

c3fa80e8 1        1        1        1        N        informix rootdbs

c3fa84b0 2        2001     2        1        N T      informix tempdbs

c3fa8518 3        1        3        1        N        informix db1

c3fa8580 4        1        4        1        N        informix db2

 4 active, 2047 maximum

Chunks

address  chk/dbs offset   page Rd  page Wr  pathname

c3fa8150 1   1   0        1259     289      /home/informix/ROOTDBS

c3fa8228 2   2   0        11       11       /home/informix/TEMPDBS

c3fa8300 3   3   0        6        0        /home/informix/db1

c3fa83d8 4   4   0        6        0        /home/informix/db2

 4 active, 2047 maximum

Monitor the number of page reads and page writes in the “page Rd” and “page Wr” columns.  This can be used to determine how even the I/O access to each chunk is.  Remember that there may be multiple chunks on a single device.
onstat -F

INFORMIX-OnLine Version 7.12.UC2   -- On-Line -- Up 122 days 20:51:45 -- 72616 s

Fg Writes     LRU Writes    Chunk Writes

0             103           311

address  flusher  state    data

c3faa444 0        I        0        = 0X0

      states: Exit Idle Chunk Lru

Monitor the types of writes occurring in you system.  Foreground (Fg Writes) should be eliminated.  LRU Writes and Chunk Writes will vary depending on the type of system you have.  An OLTP system should maximize LRU Writes.  There will always be some Chunk Writes, but LRU Writes will speed up checkpoint duration.  In a DSS system, Chunk Writes should be maximized.  Some LRU Writes may still occur in an effort to eliminate foreground writes(Fg Writes).

Also monitor page cleaners (flushers) at checkpoint time.  Make sure they are all busy doing Chunk Writes.

onstat -l

INFORMIX-OnLine Version 7.12.UC2   -- On-Line -- Up 122 days 20:56:26 -- 72616 s

Physical Logging

Buffer bufused  bufsize  numpages numwrits pages/io

  P-2  0        16       274      22       12.45

      phybegin physize  phypos   phyused  %used

      10003f   500      433      0        0.00

Logical Logging

Buffer bufused  bufsize  numrecs  numpages numwrits recs/pages pages/io

  L-2  0        16       2437     113      31       21.6       3.6

address  number   flags    uniqid   begin        size     used    %used

c3ecc55c 1        U-B----  7        100233        250      250   100.00

c3ecc578 2        U---C-L  8        10032d        250      106    42.40

c3ecc594 3        F------  0        100427        250        0     0.00

c3ecc5b0 4        F------  0        100521        250        0     0.00

c3ecc5cc 5        U-B----  5        10061b        250      250   100.00

c3ecc5e8 6        U-B----  6        100715        250      250   100.00

Monitor the physical log buffer usage.  Observe the bufsize and the pages/io columns of the first line of the output.  If (pages/io) / (bufsize) is roughly 75% then the buffer is being utilized efficiently.  If it is less then 75% then the physical log buffer is probably too large.  If the ratio is greater than 90% then the physical log buffer is potentially too small.

Monitor the logical log buffer usage the same way as the physical log buffer.  However, if unbuffered logging is being used, the flushing of the buffer will depend on the size of transactions, not the utilization of the buffer.  This rule may not apply.  If most transactions are smaller than a page of the logical log buffer, this ratio may always be low.  Just keep the logical log buffers at there defaults.

The physical log file should be monitored near checkpoint time to determine if the percent used is near 75%.  A well tuned physical log file will be nearly full at checkpoint time.  If the physical log file is not being filled up, then it is wasting disk space.

Also, the logical log files should be monitored to be sure that they are being backed up.  By using the sysmasters database, it can be determined which logical log files have been freed.  Look at the systrans table for min(tx_loguniq>0) to find the last log file containing an open transaction.

onstat -m

INFORMIX-OnLine Version 7.12.UC2   -- On-Line -- Up 122 days 20:57:47 -- 72616 s

Message Log File: /home/informix/online.log

13:09:47  Dataskip is now OFF for all dbspaces

13:09:47  On-Line Mode

13:09:47  Checkpoint Completed:  duration was 0 seconds.

14:04:50  Checkpoint Completed:  duration was 0 seconds.

14:14:51  Checkpoint Completed:  duration was 0 seconds.

14:24:54  Checkpoint Completed:  duration was 0 seconds.

14:29:54  Checkpoint Completed:  duration was 0 seconds.

Mon Nov  4 11:23:51 1996

11:23:51  Logical Log 7 Complete.

11:27:10  Checkpoint Completed:  duration was 0 seconds.

Tue Dec 31 11:16:01 1996

11:16:01  Checkpoint Completed:  duration was 0 seconds.

11:21:00  Checkpoint Completed:  duration was 0 seconds.

11:26:01  Checkpoint Completed:  duration was 0 seconds.

11:36:01  Checkpoint Completed:  duration was 0 seconds.

Monitor the online message log file for unusual events which may occur.  Also keep tabs on the frequency and duration of checkpoints.

onstat -p

INFORMIX-OnLine Version 7.12.UC2   -- On-Line -- Up 122 days 20:58:17 -- 72616 s

Profile

dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached

1535     1548     13734    88.82   484      818      3280     85.24

isamtot  open     start    read     write    rewrite  delete   commit   rollbk

10135    1520     1284     3057     875      6        7        20       0

ovlock   ovuserthread ovbuff   usercpu  syscpu   numckpts flushes

0        0            0        39.04    28.34    10       70772

bufwaits lokwaits lockreqs deadlks  dltouts  ckpwaits compress seqscans

241      1        11196    0        0        0        174      101

ixda-RA  idx-RA   da-RA    RA-pgsused lchwaits

31       0        329      360        40

The profile information has many things that can be monitored. 

The read percent cached is important for an OLTP system.  The read percent cached should be 95% or higher.  This is not always possible because of the applications use of the data.  But it is a starting point.  Adding more buffers generally will increase the read percent cache.

The write percent cached can also be monitored.  But it is much harder to tune.  As buffers increase, so should the write percent cache.  85% or higher is a beginning target.  However, depending on the application, that percentage may not be achieved. 

Lock contention can be monitored by looking at the lokwaits and lockreqs columns.  If lokwaits are 1% or higher that of lockreqs, you may have lock contention.  Changing the way application use isolation levels and locks will help improve lock contention.

Deadlocks within applications can be detected by the deadlks and dltouts columns.  Deadlks is a count of the number of dead locks detected and aborted by online.  Dltouts is a count of the number of queries which have had the deadlock timeout time expire.  Dead lock timeouts only occur for distributed queries.  If deadlocks are occurring, changes will be required in the application.

Read ahead efficiency can be monitored.  Add up the values in the ixda-RA, idx-RA, and da-RA columns and compare the sum to RA-pgsused.  If the sum of pages read ahead is not nearly equal to the number of pages used, then too many pages are being read ahead.  Reduce the RA_PAGES parameter.  An additional effect of this will be a reduction of the read percent cache.

Additionally, the RA_THRESHOLD needs to be set close to RA_PAGES or the bufwaits column will increase as the database engine is waiting for the read aheads to complete before it can use the pages.

onstat -R

# of dirty buffers in each LRU queue

INFORMIX-OnLine Version 7.12.UC2   -- On-Line -- Up 122 days 20:59:53 -- 72616 s

8 buffer LRU queue pairs

# f/m   length   % of   pair total

 0 F        25   100.0%      25

 1 m         0     0.0%

 2 f        25   100.0%      25

 3 m         0     0.0%

 4 f        25   100.0%      25

 5 m         0     0.0%

 6 f        25   100.0%      25

 7 m         0     0.0%

 8 f        25   100.0%      25

 9 m         0     0.0%

10 f        25   100.0%      25

11 m         0     0.0%

12 f        25   100.0%      25

13 m         0     0.0%

14 f        25   100.0%      25

15 m         0     0.0%

0 dirty, 200 queued, 200 total, 256 hash buckets, 2048 buffer size

</s