Tuning advice. 
Author Message
 Tuning advice.

HPUX 10.20

Dual processor K200 with 512Mb Ram

IDS 7.30.UC7

OLTP system running apps and instance on same box using shared memeory
connection, approx. 100 users.  The top output can show oninit running as
high as 90% when one of our "poor" applications is running (3rd party
software s%&t) but generally this is <50%

Earlier this year I increased the number of buffers allocated, this appears
to have resulted in an increase in checkpoint times from an average of 0
seconds to an average of 1 second with frequent occurances of 2,3,4 and
higher.  There are some very high peaks of 15+ but these are down to
problems with some applications and users running big updates during the
day.

As it happens the latest Tech Notes has an article on tuning checkpoints and
this mentions using onstat -g ioq to monitor the AIO VPS (output below)  I
always thought I needed approx one per chunk of these and I'm already
running with more than that, should I look at increasing this?

I also think I may be running into some disk related bottlenecks.

Some onstat info and the onconfig follow.

onstat -p

Informix Dynamic Server Version 7.30.UC7   -- On-Line -- Up 4 days
19:53:17 -- 298184 Kbytes

Profile
dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached
11361140 6648673  259256709 95.62   490697   4030618  6108539  91.97

isamtot  open     start    read     write    rewrite  delete   commit
rollbk
177806221 11056284 17144992 108726109 993605   80792    22199    59751    8

gp_read  gp_write gp_rewrt gp_del   gp_alloc gp_free  gp_curs
0        0        0        0        0        0        0

ovlock   ovuserthread ovbuff   usercpu  syscpu   numckpts flushes
0        0            0        41874.73 5360.16  687      2640

bufwaits lokwaits lockreqs deadlks  dltouts  ckpwaits compress seqscans
409271   78       131714614 5        0        465      111473   37517

ixda-RA  idx-RA   da-RA    RA-pgsused lchwaits
5346646  12818    4201810  9469045    34900

onstat -g ioq

Informix Dynamic Server Version 7.30.UC7   -- On-Line -- Up 4 days
19:46:38 -- 298184 Kbytes

AIO I/O queues:
q name/id    len maxlen totalops  dskread dskwrite  dskcopy
  adt   0      0      0        0        0        0        0
  msc   0      0      1    21627        0        0        0
  aio   0      0      1     1234        2     1229        0
  pio   0      0      2     8130        0     8130        0
  lio   0      0      2    13308        0    13308        0
  gfd   3      0      7     6149     4142     2007        0
  gfd   4      0      1     3755     1748     2007        0
  gfd   5      0      1      350      350        0        0
  gfd   6      0     17   183672    65046   118626        0
  gfd   7      0     32    49739    48778      961        0
  gfd   8      0      1        5        5        0        0
  gfd   9      0     32     9952     7125     2827        0
  gfd  10      0     16   184376    65134   119242        0
  gfd  11      0      6      824      815        9        0
  gfd  12      0    368  1092188  1032091    60097        0
  gfd  13      0     32   108814    98913     9901        0
  gfd  14      0     16     9861     7059     2802        0
  gfd  15      0     64  6124061  6118215     5846        0
  gfd  16      0     32   114991   114424      567        0
  gfd  17      0     32     4144     3941      203        0
  gfd  18      0     32    65341    65341        0        0
  gfd  19      0      1       12       12        0        0
  gfd  20      0     35  1450984  1435438    15546        0
  gfd  21      0     32     7325     7136      189        0
  gfd  22      0     33   488831   487321     1510        0
  gfd  23      0     17   185220    66007   119213        0
  gfd  24      0     32    10221     9923      298        0
  gfd  25      0     32     2449     2326      123        0
  gfd  26      0     32    56746    56620      126        0
  gfd  27      0     32     5835     5835        0        0
  gfd  28      0     65  1657706  1651573     6133        0
  gfd  29      0      1     2775     2775        0        0
  gfd  30      0      1        7        7        0        0

onstat -g iof

Informix Dynamic Server Version 7.30.UC7   -- On-Line -- Up 4 days
19:54:05 -- 298184 Kbytes

AIO global files:
gfd pathname         totalops  dskread dskwrite  io/s
  3 /dev/online_root     6143     4130     2013   0.0
  4 ol_ch_root_mirror     3764     1751     2013   0.0
  5 /dev/online_llog     7018      350     6668   0.0
  6 ol_ch_temp1        158541    65204    93337   0.6
  7 ol_ch_m_acti        20014    19472      542   0.1
  8 ol_ch_plog           4075        5     4070   0.0
  9 ol_ch_temp2          7424     7090      334   0.0
 10 ol_ch_temp3        159525    65292    94233   0.6
 11 ol_ch_m_multi         815      810        5   0.0
 12 ol_ch_mars_b       385241   331959    53282   1.4
 13 ol_ch_trafic       106089    97620     8469   0.4
 14 ol_ch_temp4          7378     7059      319   0.0
 15 ol_ch_mars_a      3520555  3515915     4640  12.6
 16 ol_ch_m_pent        35775    35655      120   0.1
 17 ol_ch_stuff          3442     3295      147   0.0
 18 ol_ch_m_test        63306    63306        0   0.2
 19 ol_ch_m_mentor         12       12        0   0.0
 20 ol_ch_m_mfsl       835539   822590    12949   3.0
 21 ol_ch_m_tech         4787     4725       62   0.0
 22 ol_ch_mitre2       156147   154917     1230   0.6
 23 ol_ch_temp5        160393    66151    94242   0.6
 24 ol_ch_m_misi         4201     4142       59   0.0
 25 ol_ch_m_eire         1543     1516       27   0.0
 26 ol_ch_icon          16252    16209       43   0.1
 27 ol_ch_trafic2        5748     5748        0   0.0
 28 ol_ch_mars_tmp    1113025  1107581     5444   4.0
 29 ol_ch_llog_mirror     9443     2775     6668   0.0
 30 ol_ch_plog_mirror     4077        7     4070   0.0

hpk200:/> onstat -g iov

Informix Dynamic Server Version 7.30.UC7   -- On-Line -- Up 4 days
20:06:22 -- 298184 Kbytes

AIO I/O vps:
class/vp s  io/s totalops  dskread dskwrite  dskcopy  wakeups  io/wup
errors
  msc  0 i   0.1    21843        0        0        0    21091   1.0       0
  aio  0 i   4.4  1239476  1139252   100221        0  1190947   1.0       0
  aio  1 i   3.1   864188   782324    81864        0   846349   1.0       0
  aio  2 i   1.7   480361   437566    42795        0   467930   1.0       0
  aio  3 i   1.1   318999   300384    18615        0   308691   1.0       0
  aio  4 i   0.9   244676   233249    11427        0   234027   1.0       0
  aio  5 i   0.8   211325   202716     8609        0   200525   1.1       0
  aio  6 i   0.7   192514   185111     7403        0   181906   1.1       0
  aio  7 i   0.6   178799   172094     6705        0   168673   1.1       0
  aio  8 i   0.6   169075   162745     6330        0   159492   1.1       0
  aio  9 i   0.6   162142   156190     5952        0   152790   1.1       0
  aio 10 i   0.6   157315   151717     5598        0   148486   1.1       0
  aio 11 i   0.5   153949   148651     5298        0   145410   1.1       0
  aio 12 i   0.5   150959   145933     5026        0   142582   1.1       0
  aio 13 i   0.5   147858   143255     4603        0   140197   1.1       0
  aio 14 i   0.5   145228   140964     4264        0   137954   1.1       0
  aio 15 i   0.5   142219   138341     3878        0   135741   1.0       0
  aio 16 i   0.5   139399   135974     3425        0   133258   1.0       0
  aio 17 i   0.5   136719   133575     3144        0   131119   1.0       0
  aio 18 i   0.5   134047   131132     2915        0   129093   1.0       0
  aio 19 i   0.5   131805   128940     2865        0   127100   1.0       0
  aio 20 i   0.5   129414   126538     2876        0   125053   1.0       0
  aio 21 i   0.4   126403   123674     2729        0   122893   1.0       0
  aio 22 i   0.4   123547   120912     2635        0   120182   1.0       0
  aio 23 i   0.4   119394   116779     2615        0   116663   1.0       0
  aio 24 i   0.4   113783   111223     2560        0   112064   1.0       0
  aio 25 i   0.4   106866   104474     2392        0   105278   1.0       0
  aio 26 i   0.3    97118    94693     2425        0    95982   1.0       0
  aio 27 i   0.3    83989    81557     2432        0    83459   1.0       0
  aio 28 i   0.2    68946    66583     2363        0    68700   1.0       0
  aio 29 i   0.2    54349    51821     2528        0    53312   1.0       0
  aio 30 i   0.1    41763    39310     2453        0    39815   1.0       0
  aio 31 i   0.1    32964    30626     2338        0    30230   1.1       0
  aio 32 i   0.1    28238    26000     2238        0    24906   1.1       0
  aio 33 i   0.1    25754    23722     2032        0    22062   1.2       0
  aio 34 i   0.1    24255    22330     1925        0    20445   1.2       0
  aio 35 i   0.1    23072    21231     1841        0    19347   1.2       0
  aio 36 i   0.1    22148    20339     1809        0    18417   1.2       0
  aio 37 i   0.1    21442    19653     1789        0    17616   1.2       0
  aio 38 i   0.1    20661    18953     1708        0    16988   1.2       0
  aio 39 i   0.1    20101    18367     1734        0    16396   1.2       0
  pio  0 i   0.0     4099        0     4099        0     4097   1.0       0
  pio  1 i   0.0     4093        0     4093        0     4094   1.0       0
  lio  0 i   0.0     6723        0     6723        0     6706   1.0       0
  lio  1 i   0.0     6685        0     6685        0     6700   1.0       0

onstat -d

Informix Dynamic Server Version 7.30.UC7   -- On-Line -- Up 4 days
19:54:33 -- 298184 Kbytes

Dbspaces
address  number   flags    fchunk   nchunks  flags    owner    name
dd3c9158 1        2        1        1        M        informix rootdbs
dd3fcef0 2        2        2        1        M        informix dbs_llog
ddcdb018 3        2001     3        1        N T      informix dbs_temp1
ddcdb0d8 4        1        4        1        N        informix dbs_m_acti
ddcdb198 5        2        5        1        M      
...

read more »



Wed, 18 Jun 1902 08:00:00 GMT
 Tuning advice.

Well, you do need more buffers.  Generally the read cache rate needs to be
pretty close to 99%.  You might be able to reduce your checkpoint times even
more by setting LRU_MAX_DIRTY to 2.

You also have skewed usage of the chunks.  From your sample ol_ch_mars_a is
accounting for a rather large percentage of your activity. You might want to
consider moving some of the tables in there to othere chunks.  This might help
your checkpoint times also.

I would consider setting DD_HASHSIZE to a much larger value.  I don't know where
all of your IO is coming from, but if it is dictionary information, this would
help.  If oninit is only taking 50% of the system when it is overloaded, that
implies that you are blocking quite a bit on IO completion.  Increasing buffers
would help.

I'm sure that some other folks that frequent the news group will have some other
ideas and/or suggestions.  I'll be interested to see what they suggest.

Quote:

> HPUX 10.20

> Dual processor K200 with 512Mb Ram

> IDS 7.30.UC7

> OLTP system running apps and instance on same box using shared memeory
> connection, approx. 100 users.  The top output can show oninit running as
> high as 90% when one of our "poor" applications is running (3rd party
> software s%&t) but generally this is <50%

> Earlier this year I increased the number of buffers allocated, this appears
> to have resulted in an increase in checkpoint times from an average of 0
> seconds to an average of 1 second with frequent occurances of 2,3,4 and
> higher.  There are some very high peaks of 15+ but these are down to
> problems with some applications and users running big updates during the
> day.

> As it happens the latest Tech Notes has an article on tuning checkpoints and
> this mentions using onstat -g ioq to monitor the AIO VPS (output below)  I
> always thought I needed approx one per chunk of these and I'm already
> running with more than that, should I look at increasing this?

> I also think I may be running into some disk related bottlenecks.

> Some onstat info and the onconfig follow.

> onstat -p

> Informix Dynamic Server Version 7.30.UC7   -- On-Line -- Up 4 days
> 19:53:17 -- 298184 Kbytes

> Profile
> dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached
> 11361140 6648673  259256709 95.62   490697   4030618  6108539  91.97

> isamtot  open     start    read     write    rewrite  delete   commit
> rollbk
> 177806221 11056284 17144992 108726109 993605   80792    22199    59751    8

> gp_read  gp_write gp_rewrt gp_del   gp_alloc gp_free  gp_curs
> 0        0        0        0        0        0        0

> ovlock   ovuserthread ovbuff   usercpu  syscpu   numckpts flushes
> 0        0            0        41874.73 5360.16  687      2640

> bufwaits lokwaits lockreqs deadlks  dltouts  ckpwaits compress seqscans
> 409271   78       131714614 5        0        465      111473   37517

> ixda-RA  idx-RA   da-RA    RA-pgsused lchwaits
> 5346646  12818    4201810  9469045    34900

> onstat -g ioq

> Informix Dynamic Server Version 7.30.UC7   -- On-Line -- Up 4 days
> 19:46:38 -- 298184 Kbytes

> AIO I/O queues:
> q name/id    len maxlen totalops  dskread dskwrite  dskcopy
>   adt   0      0      0        0        0        0        0
>   msc   0      0      1    21627        0        0        0
>   aio   0      0      1     1234        2     1229        0
>   pio   0      0      2     8130        0     8130        0
>   lio   0      0      2    13308        0    13308        0
>   gfd   3      0      7     6149     4142     2007        0
>   gfd   4      0      1     3755     1748     2007        0
>   gfd   5      0      1      350      350        0        0
>   gfd   6      0     17   183672    65046   118626        0
>   gfd   7      0     32    49739    48778      961        0
>   gfd   8      0      1        5        5        0        0
>   gfd   9      0     32     9952     7125     2827        0
>   gfd  10      0     16   184376    65134   119242        0
>   gfd  11      0      6      824      815        9        0
>   gfd  12      0    368  1092188  1032091    60097        0
>   gfd  13      0     32   108814    98913     9901        0
>   gfd  14      0     16     9861     7059     2802        0
>   gfd  15      0     64  6124061  6118215     5846        0
>   gfd  16      0     32   114991   114424      567        0
>   gfd  17      0     32     4144     3941      203        0
>   gfd  18      0     32    65341    65341        0        0
>   gfd  19      0      1       12       12        0        0
>   gfd  20      0     35  1450984  1435438    15546        0
>   gfd  21      0     32     7325     7136      189        0
>   gfd  22      0     33   488831   487321     1510        0
>   gfd  23      0     17   185220    66007   119213        0
>   gfd  24      0     32    10221     9923      298        0
>   gfd  25      0     32     2449     2326      123        0
>   gfd  26      0     32    56746    56620      126        0
>   gfd  27      0     32     5835     5835        0        0
>   gfd  28      0     65  1657706  1651573     6133        0
>   gfd  29      0      1     2775     2775        0        0
>   gfd  30      0      1        7        7        0        0

> onstat -g iof

> Informix Dynamic Server Version 7.30.UC7   -- On-Line -- Up 4 days
> 19:54:05 -- 298184 Kbytes

> AIO global files:
> gfd pathname         totalops  dskread dskwrite  io/s
>   3 /dev/online_root     6143     4130     2013   0.0
>   4 ol_ch_root_mirror     3764     1751     2013   0.0
>   5 /dev/online_llog     7018      350     6668   0.0
>   6 ol_ch_temp1        158541    65204    93337   0.6
>   7 ol_ch_m_acti        20014    19472      542   0.1
>   8 ol_ch_plog           4075        5     4070   0.0
>   9 ol_ch_temp2          7424     7090      334   0.0
>  10 ol_ch_temp3        159525    65292    94233   0.6
>  11 ol_ch_m_multi         815      810        5   0.0
>  12 ol_ch_mars_b       385241   331959    53282   1.4
>  13 ol_ch_trafic       106089    97620     8469   0.4
>  14 ol_ch_temp4          7378     7059      319   0.0
>  15 ol_ch_mars_a      3520555  3515915     4640  12.6
>  16 ol_ch_m_pent        35775    35655      120   0.1
>  17 ol_ch_stuff          3442     3295      147   0.0
>  18 ol_ch_m_test        63306    63306        0   0.2
>  19 ol_ch_m_mentor         12       12        0   0.0
>  20 ol_ch_m_mfsl       835539   822590    12949   3.0
>  21 ol_ch_m_tech         4787     4725       62   0.0
>  22 ol_ch_mitre2       156147   154917     1230   0.6
>  23 ol_ch_temp5        160393    66151    94242   0.6
>  24 ol_ch_m_misi         4201     4142       59   0.0
>  25 ol_ch_m_eire         1543     1516       27   0.0
>  26 ol_ch_icon          16252    16209       43   0.1
>  27 ol_ch_trafic2        5748     5748        0   0.0
>  28 ol_ch_mars_tmp    1113025  1107581     5444   4.0
>  29 ol_ch_llog_mirror     9443     2775     6668   0.0
>  30 ol_ch_plog_mirror     4077        7     4070   0.0

> hpk200:/> onstat -g iov

> Informix Dynamic Server Version 7.30.UC7   -- On-Line -- Up 4 days
> 20:06:22 -- 298184 Kbytes

> AIO I/O vps:
> class/vp s  io/s totalops  dskread dskwrite  dskcopy  wakeups  io/wup
> errors
>   msc  0 i   0.1    21843        0        0        0    21091   1.0       0
>   aio  0 i   4.4  1239476  1139252   100221        0  1190947   1.0       0
>   aio  1 i   3.1   864188   782324    81864        0   846349   1.0       0
>   aio  2 i   1.7   480361   437566    42795        0   467930   1.0       0
>   aio  3 i   1.1   318999   300384    18615        0   308691   1.0       0
>   aio  4 i   0.9   244676   233249    11427        0   234027   1.0       0
>   aio  5 i   0.8   211325   202716     8609        0   200525   1.1       0
>   aio  6 i   0.7   192514   185111     7403        0   181906   1.1       0
>   aio  7 i   0.6   178799   172094     6705        0   168673   1.1       0
>   aio  8 i   0.6   169075   162745     6330        0   159492   1.1       0
>   aio  9 i   0.6   162142   156190     5952        0   152790   1.1       0
>   aio 10 i   0.6   157315   151717     5598        0   148486   1.1       0
>   aio 11 i   0.5   153949   148651     5298        0   145410   1.1       0
>   aio 12 i   0.5   150959   145933     5026        0   142582   1.1       0
>   aio 13 i   0.5   147858   143255     4603        0   140197   1.1       0
>   aio 14 i   0.5   145228   140964     4264        0   137954   1.1       0
>   aio 15 i   0.5   142219   138341     3878        0   135741   1.0       0
>   aio 16 i   0.5   139399   135974     3425        0   133258   1.0       0
>   aio 17 i   0.5   136719   133575     3144        0   131119   1.0       0
>   aio 18 i   0.5   134047   131132     2915        0   129093   1.0       0
>   aio 19 i   0.5   131805   128940     2865        0   127100   1.0       0
>   aio 20 i   0.5   129414   126538     2876        0   125053   1.0       0
>   aio 21 i   0.4   126403   123674     2729        0   122893   1.0       0
>   aio 22 i   0.4   123547   120912     2635        0   120182   1.0       0
>   aio 23 i   0.4   119394   116779     2615        0   116663   1.0       0
>   aio 24 i   0.4   113783   111223     2560        0   112064   1.0       0
>   aio 25 i   0.4   106866   104474     2392        0   105278   1.0       0
>   aio 26 i   0.3    97118    94693     2425        0    95982   1.0       0
>   aio 27 i   0.3    83989    81557     2432        0    83459   1.0       0
>   aio 28 i   0.2    68946    66583     2363        0    68700   1.0       0
>   aio 29 i   0.2    54349    51821     2528        0    53312   1.0       0
>   aio 30 i   0.1    41763    39310     2453        0    39815   1.0       0
>   aio 31 i   0.1    32964    30626     2338        0    30230   1.1       0
>   aio 32 i   0.1    28238    26000     2238        0    24906   1.1       0
>   aio 33 i   0.1    25754    23722     2032        0    22062   1.2       0
>   aio 34 i

...

read more »



Wed, 18 Jun 1902 08:00:00 GMT
 Tuning advice.
Yeh, I know about ol_ch_mars_a, Oh for more disk, or some free weekends to
attempt a re-org!

Looking at the onstat -g dic most lists are full but with unreferenced
tables, I'll try upping this!

I'll try the LRU_MAX_DIRTY 2 as well, doesn't this tend to reduce cached
writes?

I'm wary of increasing BUFFERS too much because of leaving enough memory for
the applications, this box runs boths Apps and Server.

swapinfo output

                  Kb      Kb      Kb   PCT  START/      Kb
TYPE      AVAIL    USED    FREE  USED   LIMIT RESERVE  PRI  NAME
dev      524288   65168  459120   12%       0       -    1  /dev/vg00/lvol2
dev      524288   66124  458164   13%       0       -    1
/dev/vg04/lv_swap2
reserve       -  602220 -602220
memory   397140  334944   62196   84%

sar -w

HP-UX hpk200 B.10.20 U 9000/819    09/20/00

00:00:00 swpin/s bswin/s swpot/s bswot/s pswch/s
01:00:00    0.00     0.0    0.00     0.0    1051
02:00:00    0.00     0.0    0.00     0.0     661
03:00:00    0.00     0.0    0.00     0.0     432
04:00:00    0.00     0.0    0.00     0.0      76
05:00:00    0.00     0.0    0.00     0.0      72
06:00:00    0.00     0.0    0.00     0.0     408
07:00:00    0.00     0.0    0.00     0.0      74
08:00:01    0.00     0.0    0.00     0.0      78
08:20:01    0.00     0.0    0.00     0.0      79
08:40:01    0.00     0.0    0.00     0.0     735
09:00:00    0.00     0.0    0.00     0.0     427
09:20:01    0.78     0.0    0.81     0.1    1215
09:40:01    0.97     0.0    1.03     0.2    1586
10:00:00    0.95     0.0    0.99     0.4    1223
10:20:00    0.98     0.0    1.01     0.3     999
10:40:00    0.94     0.1    0.96     0.8     767
11:00:00    0.97     0.0    0.99     0.3     407
11:20:00    0.97     0.1    0.97     0.2     519
11:40:00    0.98     0.1    0.99     0.3     700
12:00:00    1.00     0.1    1.00     0.1     440
12:20:00    0.99     0.1    0.99     0.2     501
12:40:00    0.99     0.1    1.00     0.1     409
13:00:00    1.00     0.0    0.98     0.0     344
13:20:00    1.00     0.0    0.99     0.0     333
13:40:01    1.00     0.1    1.01     0.1     263
14:00:01    1.00     0.0    1.02     0.1     243
14:20:01    0.99     0.0    1.01     0.1     317
14:40:01    1.00     0.1    1.01     0.1     352
15:00:00    1.00     0.1    0.98     0.0     778
15:20:01    0.98     0.1    1.01     0.2     502
15:40:00    0.99     0.1    0.98     0.1    1298
16:00:01    1.00     0.1    0.97     0.0     797
16:20:01    1.00     0.0    1.00     0.0     211
16:40:01    1.00     0.0    0.99     0.0     206
17:00:00    0.98     0.1    1.00     0.1     430

Average     0.46     0.0    0.46     0.1     483

onstat -g seg

Informix Dynamic Server Version 7.30.UC7   -- On-Line -- Up 4 days
23:02:22 -- 298184 Kbytes

Segment Summary:
id       key        addr     size       ovhd     class blkused  blkfree
521      1381451778 c0bda000 2711552    652      M     325      6
520      1381451777 d1235000 202932224  5572     R*    24765    7
(shared) 1381451777 dd3bd000 102408192  2172     V     6884     5617
Total:   -          -        308051968  -        -     31974    5630

   (* segment locked in memory)

I need to read up on interpreting this onfo :o(  but this gives an idea of
how the memory is working at present.

Thanks.

--
Tony Flaherty
Snr. A/P, Informix DBA, HpUx Admin, Gimmi a broom!
MFS Ltd.

Quote:

>Well, you do need more buffers.  Generally the read cache rate needs to be
>pretty close to 99%.  You might be able to reduce your checkpoint times
even
>more by setting LRU_MAX_DIRTY to 2.

>You also have skewed usage of the chunks.  From your sample ol_ch_mars_a is
>accounting for a rather large percentage of your activity. You might want
to
>consider moving some of the tables in there to othere chunks.  This might
help
>your checkpoint times also.

>I would consider setting DD_HASHSIZE to a much larger value.  I don't know
where
>all of your IO is coming from, but if it is dictionary information, this
would
>help.  If oninit is only taking 50% of the system when it is overloaded,
that
>implies that you are blocking quite a bit on IO completion.  Increasing
buffers
>would help.

>I'm sure that some other folks that frequent the news group will have some
other
>ideas and/or suggestions.  I'll be interested to see what they suggest.



[snipped my original post]

- Show quoted text -

Quote:
>> --
>> Tony Flaherty
>> Snr. A/P, Informix DBA, HpUx Admin, Gimmi a broom!
>> MFS Ltd.

>--
>Madison Pruet

>===========================================
>Enterprise Replication Product Developement
>Dallas, Texas
>Informix Software
>===========================================



Wed, 18 Jun 1902 08:00:00 GMT
 Tuning advice.
Why aren't you using kaio, Tony?
Also, as Madison Pruet suggested, get the tables on ol_ch_mars_a spread out
a bit.  ALTER FRAGMENT ..INIT is the boy!


Quote:
> HPUX 10.20

> Dual processor K200 with 512Mb Ram

> IDS 7.30.UC7

> OLTP system running apps and instance on same box using shared memeory
> connection, approx. 100 users.  The top output can show oninit running as
> high as 90% when one of our "poor" applications is running (3rd party
> software s%&t) but generally this is <50%

> Earlier this year I increased the number of buffers allocated, this
appears
> to have resulted in an increase in checkpoint times from an average of 0
> seconds to an average of 1 second with frequent occurances of 2,3,4 and
> higher.  There are some very high peaks of 15+ but these are down to
> problems with some applications and users running big updates during the
> day.

> As it happens the latest Tech Notes has an article on tuning checkpoints
and
> this mentions using onstat -g ioq to monitor the AIO VPS (output below)  I
> always thought I needed approx one per chunk of these and I'm already
> running with more than that, should I look at increasing this?

> I also think I may be running into some disk related bottlenecks.

> Some onstat info and the onconfig follow.

> onstat -p

> Informix Dynamic Server Version 7.30.UC7   -- On-Line -- Up 4 days
> 19:53:17 -- 298184 Kbytes

> Profile
> dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached
> 11361140 6648673  259256709 95.62   490697   4030618  6108539  91.97

> isamtot  open     start    read     write    rewrite  delete   commit
> rollbk
> 177806221 11056284 17144992 108726109 993605   80792    22199    59751
8

> gp_read  gp_write gp_rewrt gp_del   gp_alloc gp_free  gp_curs
> 0        0        0        0        0        0        0

> ovlock   ovuserthread ovbuff   usercpu  syscpu   numckpts flushes
> 0        0            0        41874.73 5360.16  687      2640

> bufwaits lokwaits lockreqs deadlks  dltouts  ckpwaits compress seqscans
> 409271   78       131714614 5        0        465      111473   37517

> ixda-RA  idx-RA   da-RA    RA-pgsused lchwaits
> 5346646  12818    4201810  9469045    34900

> onstat -g ioq

> Informix Dynamic Server Version 7.30.UC7   -- On-Line -- Up 4 days
> 19:46:38 -- 298184 Kbytes

> AIO I/O queues:
> q name/id    len maxlen totalops  dskread dskwrite  dskcopy
>   adt   0      0      0        0        0        0        0
>   msc   0      0      1    21627        0        0        0
>   aio   0      0      1     1234        2     1229        0
>   pio   0      0      2     8130        0     8130        0
>   lio   0      0      2    13308        0    13308        0
>   gfd   3      0      7     6149     4142     2007        0
>   gfd   4      0      1     3755     1748     2007        0
>   gfd   5      0      1      350      350        0        0
>   gfd   6      0     17   183672    65046   118626        0
>   gfd   7      0     32    49739    48778      961        0
>   gfd   8      0      1        5        5        0        0
>   gfd   9      0     32     9952     7125     2827        0
>   gfd  10      0     16   184376    65134   119242        0
>   gfd  11      0      6      824      815        9        0
>   gfd  12      0    368  1092188  1032091    60097        0
>   gfd  13      0     32   108814    98913     9901        0
>   gfd  14      0     16     9861     7059     2802        0
>   gfd  15      0     64  6124061  6118215     5846        0
>   gfd  16      0     32   114991   114424      567        0
>   gfd  17      0     32     4144     3941      203        0
>   gfd  18      0     32    65341    65341        0        0
>   gfd  19      0      1       12       12        0        0
>   gfd  20      0     35  1450984  1435438    15546        0
>   gfd  21      0     32     7325     7136      189        0
>   gfd  22      0     33   488831   487321     1510        0
>   gfd  23      0     17   185220    66007   119213        0
>   gfd  24      0     32    10221     9923      298        0
>   gfd  25      0     32     2449     2326      123        0
>   gfd  26      0     32    56746    56620      126        0
>   gfd  27      0     32     5835     5835        0        0
>   gfd  28      0     65  1657706  1651573     6133        0
>   gfd  29      0      1     2775     2775        0        0
>   gfd  30      0      1        7        7        0        0

> onstat -g iof

> Informix Dynamic Server Version 7.30.UC7   -- On-Line -- Up 4 days
> 19:54:05 -- 298184 Kbytes

> AIO global files:
> gfd pathname         totalops  dskread dskwrite  io/s
>   3 /dev/online_root     6143     4130     2013   0.0
>   4 ol_ch_root_mirror     3764     1751     2013   0.0
>   5 /dev/online_llog     7018      350     6668   0.0
>   6 ol_ch_temp1        158541    65204    93337   0.6
>   7 ol_ch_m_acti        20014    19472      542   0.1
>   8 ol_ch_plog           4075        5     4070   0.0
>   9 ol_ch_temp2          7424     7090      334   0.0
>  10 ol_ch_temp3        159525    65292    94233   0.6
>  11 ol_ch_m_multi         815      810        5   0.0
>  12 ol_ch_mars_b       385241   331959    53282   1.4
>  13 ol_ch_trafic       106089    97620     8469   0.4
>  14 ol_ch_temp4          7378     7059      319   0.0
>  15 ol_ch_mars_a      3520555  3515915     4640  12.6
>  16 ol_ch_m_pent        35775    35655      120   0.1
>  17 ol_ch_stuff          3442     3295      147   0.0
>  18 ol_ch_m_test        63306    63306        0   0.2
>  19 ol_ch_m_mentor         12       12        0   0.0
>  20 ol_ch_m_mfsl       835539   822590    12949   3.0
>  21 ol_ch_m_tech         4787     4725       62   0.0
>  22 ol_ch_mitre2       156147   154917     1230   0.6
>  23 ol_ch_temp5        160393    66151    94242   0.6
>  24 ol_ch_m_misi         4201     4142       59   0.0
>  25 ol_ch_m_eire         1543     1516       27   0.0
>  26 ol_ch_icon          16252    16209       43   0.1
>  27 ol_ch_trafic2        5748     5748        0   0.0
>  28 ol_ch_mars_tmp    1113025  1107581     5444   4.0
>  29 ol_ch_llog_mirror     9443     2775     6668   0.0
>  30 ol_ch_plog_mirror     4077        7     4070   0.0

> hpk200:/> onstat -g iov

> Informix Dynamic Server Version 7.30.UC7   -- On-Line -- Up 4 days
> 20:06:22 -- 298184 Kbytes

> AIO I/O vps:
> class/vp s  io/s totalops  dskread dskwrite  dskcopy  wakeups  io/wup
> errors
>   msc  0 i   0.1    21843        0        0        0    21091   1.0
0
>   aio  0 i   4.4  1239476  1139252   100221        0  1190947   1.0
0
>   aio  1 i   3.1   864188   782324    81864        0   846349   1.0
0
>   aio  2 i   1.7   480361   437566    42795        0   467930   1.0
0
>   aio  3 i   1.1   318999   300384    18615        0   308691   1.0
0
>   aio  4 i   0.9   244676   233249    11427        0   234027   1.0
0
>   aio  5 i   0.8   211325   202716     8609        0   200525   1.1
0
>   aio  6 i   0.7   192514   185111     7403        0   181906   1.1
0
>   aio  7 i   0.6   178799   172094     6705        0   168673   1.1
0
>   aio  8 i   0.6   169075   162745     6330        0   159492   1.1
0
>   aio  9 i   0.6   162142   156190     5952        0   152790   1.1
0
>   aio 10 i   0.6   157315   151717     5598        0   148486   1.1
0
>   aio 11 i   0.5   153949   148651     5298        0   145410   1.1
0
>   aio 12 i   0.5   150959   145933     5026        0   142582   1.1
0
>   aio 13 i   0.5   147858   143255     4603        0   140197   1.1
0
>   aio 14 i   0.5   145228   140964     4264        0   137954   1.1
0
>   aio 15 i   0.5   142219   138341     3878        0   135741   1.0
0
>   aio 16 i   0.5   139399   135974     3425        0   133258   1.0
0
>   aio 17 i   0.5   136719   133575     3144        0   131119   1.0
0
>   aio 18 i   0.5   134047   131132     2915        0   129093   1.0
0
>   aio 19 i   0.5   131805   128940     2865        0   127100   1.0
0
>   aio 20 i   0.5   129414   126538     2876        0   125053   1.0
0
>   aio 21 i   0.4   126403   123674     2729        0   122893   1.0
0
>   aio 22 i   0.4   123547   120912     2635        0   120182   1.0
0
>   aio 23 i   0.4   119394   116779     2615        0   116663   1.0
0
>   aio 24 i   0.4   113783   111223     2560        0   112064   1.0
0
>   aio 25 i   0.4   106866   104474     2392        0   105278   1.0
0
>   aio 26 i   0.3    97118    94693     2425        0    95982   1.0
0
>   aio 27 i   0.3    83989    81557     2432        0    83459   1.0
0
>   aio 28 i   0.2    68946    66583     2363        0    68700   1.0
0
>   aio 29 i   0.2    54349    51821     2528        0    53312   1.0
0
>   aio 30 i   0.1    41763    39310     2453        0    39815   1.0
0
>   aio 31 i   0.1    32964    30626     2338        0    30230   1.1
0
>   aio 32 i   0.1    28238    26000     2238        0    24906   1.1
0
>   aio 33 i   0.1    25754    23722     2032        0    22062   1.2
0
>   aio 34 i   0.1    24255    22330     1925        0    20445   1.2
0
>   aio 35 i   0.1    23072    21231     1841        0    19347   1.2
0
>   aio 36 i   0.1    22148    20339     1809        0    18417   1.2
0
>   aio 37 i   0.1    21442    19653     1789        0    17616   1.2
0
>   aio 38 i   0.1    20661    18953     1708        0    16988   1.2
0
>   aio 39 i   0.1    20101    18367     1734        0    16396   1.2
0
>   pio  0 i   0.0     4099        0     4099        0     4097   1.0
0
>   pio  1 i   0.0     4093        0     4093        0     4094   1.0
0
>   lio  0 i   0.0     6723        0     6723        0     6706   1.0
0
>   lio  1 i   0.0     6685        0     6685        0     6700   1.0
0

> onstat -d

> Informix Dynamic Server Version 7.30.UC7   -- On-Line -- Up 4 days
> 19:54:33 -- 298184 Kbytes

> Dbspaces
> address  number   flags    fchunk   nchunks  flags    owner    name

...

read more »



Wed, 18 Jun 1902 08:00:00 GMT
 Tuning advice.
Ok - if the dictionary information tends to be unreferenced, then that implies
that you are not using prepared statements. By not preparing the statements you
can run into dictionary thrashing and not be aware of it.  I'd still consider
increasing the dictionary size, possibably large enough so that you can get an
idea as to how large the dictionary would need to be if all of the statements
were prepared and loaded into memory.

It might be a good stragety to increase the size of the chains until they did
not reach max length, just to get that information.

M.P.

Quote:

> Yeh, I know about ol_ch_mars_a, Oh for more disk, or some free weekends to
> attempt a re-org!

> Looking at the onstat -g dic most lists are full but with unreferenced
> tables, I'll try upping this!

> I'll try the LRU_MAX_DIRTY 2 as well, doesn't this tend to reduce cached
> writes?

> I'm wary of increasing BUFFERS too much because of leaving enough memory for
> the applications, this box runs boths Apps and Server.

> swapinfo output

>                   Kb      Kb      Kb   PCT  START/      Kb
> TYPE      AVAIL    USED    FREE  USED   LIMIT RESERVE  PRI  NAME
> dev      524288   65168  459120   12%       0       -    1  /dev/vg00/lvol2
> dev      524288   66124  458164   13%       0       -    1
> /dev/vg04/lv_swap2
> reserve       -  602220 -602220
> memory   397140  334944   62196   84%

> sar -w

> HP-UX hpk200 B.10.20 U 9000/819    09/20/00

> 00:00:00 swpin/s bswin/s swpot/s bswot/s pswch/s
> 01:00:00    0.00     0.0    0.00     0.0    1051
> 02:00:00    0.00     0.0    0.00     0.0     661
> 03:00:00    0.00     0.0    0.00     0.0     432
> 04:00:00    0.00     0.0    0.00     0.0      76
> 05:00:00    0.00     0.0    0.00     0.0      72
> 06:00:00    0.00     0.0    0.00     0.0     408
> 07:00:00    0.00     0.0    0.00     0.0      74
> 08:00:01    0.00     0.0    0.00     0.0      78
> 08:20:01    0.00     0.0    0.00     0.0      79
> 08:40:01    0.00     0.0    0.00     0.0     735
> 09:00:00    0.00     0.0    0.00     0.0     427
> 09:20:01    0.78     0.0    0.81     0.1    1215
> 09:40:01    0.97     0.0    1.03     0.2    1586
> 10:00:00    0.95     0.0    0.99     0.4    1223
> 10:20:00    0.98     0.0    1.01     0.3     999
> 10:40:00    0.94     0.1    0.96     0.8     767
> 11:00:00    0.97     0.0    0.99     0.3     407
> 11:20:00    0.97     0.1    0.97     0.2     519
> 11:40:00    0.98     0.1    0.99     0.3     700
> 12:00:00    1.00     0.1    1.00     0.1     440
> 12:20:00    0.99     0.1    0.99     0.2     501
> 12:40:00    0.99     0.1    1.00     0.1     409
> 13:00:00    1.00     0.0    0.98     0.0     344
> 13:20:00    1.00     0.0    0.99     0.0     333
> 13:40:01    1.00     0.1    1.01     0.1     263
> 14:00:01    1.00     0.0    1.02     0.1     243
> 14:20:01    0.99     0.0    1.01     0.1     317
> 14:40:01    1.00     0.1    1.01     0.1     352
> 15:00:00    1.00     0.1    0.98     0.0     778
> 15:20:01    0.98     0.1    1.01     0.2     502
> 15:40:00    0.99     0.1    0.98     0.1    1298
> 16:00:01    1.00     0.1    0.97     0.0     797
> 16:20:01    1.00     0.0    1.00     0.0     211
> 16:40:01    1.00     0.0    0.99     0.0     206
> 17:00:00    0.98     0.1    1.00     0.1     430

> Average     0.46     0.0    0.46     0.1     483

> onstat -g seg

> Informix Dynamic Server Version 7.30.UC7   -- On-Line -- Up 4 days
> 23:02:22 -- 298184 Kbytes

> Segment Summary:
> id       key        addr     size       ovhd     class blkused  blkfree
> 521      1381451778 c0bda000 2711552    652      M     325      6
> 520      1381451777 d1235000 202932224  5572     R*    24765    7
> (shared) 1381451777 dd3bd000 102408192  2172     V     6884     5617
> Total:   -          -        308051968  -        -     31974    5630

>    (* segment locked in memory)

> I need to read up on interpreting this onfo :o(  but this gives an idea of
> how the memory is working at present.

> Thanks.

> --
> Tony Flaherty
> Snr. A/P, Informix DBA, HpUx Admin, Gimmi a broom!
> MFS Ltd.


> >Well, you do need more buffers.  Generally the read cache rate needs to be
> >pretty close to 99%.  You might be able to reduce your checkpoint times
> even
> >more by setting LRU_MAX_DIRTY to 2.

> >You also have skewed usage of the chunks.  From your sample ol_ch_mars_a is
> >accounting for a rather large percentage of your activity. You might want
> to
> >consider moving some of the tables in there to othere chunks.  This might
> help
> >your checkpoint times also.

> >I would consider setting DD_HASHSIZE to a much larger value.  I don't know
> where
> >all of your IO is coming from, but if it is dictionary information, this
> would
> >help.  If oninit is only taking 50% of the system when it is overloaded,
> that
> >implies that you are blocking quite a bit on IO completion.  Increasing
> buffers
> >would help.

> >I'm sure that some other folks that frequent the news group will have some
> other
> >ideas and/or suggestions.  I'll be interested to see what they suggest.


> [snipped my original post]

> >> --
> >> Tony Flaherty
> >> Snr. A/P, Informix DBA, HpUx Admin, Gimmi a broom!
> >> MFS Ltd.

> >--
> >Madison Pruet

> >===========================================
> >Enterprise Replication Product Developement
> >Dallas, Texas
> >Informix Software
> >===========================================

--
Madison Pruet

===========================================
Enterprise Replication Product Developement
Dallas, Texas
Informix Software
===========================================



Wed, 18 Jun 1902 08:00:00 GMT
 Tuning advice.

Quote:

> Well, you do need more buffers.  Generally the read cache rate needs to be
> pretty close to 99%.  You might be able to reduce your checkpoint times even
> more by setting LRU_MAX_DIRTY to 2.

> You also have skewed usage of the chunks.  From your sample ol_ch_mars_a is
> accounting for a rather large percentage of your activity. You might want to
> consider moving some of the tables in there to othere chunks.  This might help
> your checkpoint times also.

> I would consider setting DD_HASHSIZE to a much larger value.  I don't know where
> all of your IO is coming from, but if it is dictionary information, this would
> help.  If oninit is only taking 50% of the system when it is overloaded, that
> implies that you are blocking quite a bit on IO completion.  Increasing buffers
> would help.

> I'm sure that some other folks that frequent the news group will have some other
> ideas and/or suggestions.  I'll be interested to see what they suggest.

I like your suggestions myself.

Art S. Kagel

- Show quoted text -

Quote:

> > HPUX 10.20

> > Dual processor K200 with 512Mb Ram

> > IDS 7.30.UC7

> > OLTP system running apps and instance on same box using shared memeory
> > connection, approx. 100 users.  The top output can show oninit running as
> > high as 90% when one of our "poor" applications is running (3rd party
> > software s%&t) but generally this is <50%

> > Earlier this year I increased the number of buffers allocated, this appears
> > to have resulted in an increase in checkpoint times from an average of 0
> > seconds to an average of 1 second with frequent occurances of 2,3,4 and
> > higher.  There are some very high peaks of 15+ but these are down to
> > problems with some applications and users running big updates during the
> > day.

> > As it happens the latest Tech Notes has an article on tuning checkpoints and
> > this mentions using onstat -g ioq to monitor the AIO VPS (output below)  I
> > always thought I needed approx one per chunk of these and I'm already
> > running with more than that, should I look at increasing this?

> > I also think I may be running into some disk related bottlenecks.

> > Some onstat info and the onconfig follow.

[SNIP]


Wed, 18 Jun 1902 08:00:00 GMT
 Tuning advice.

Neil,

I tried KAIO on my development server and I had problems with it, although
the hardware specification is sufficiently different to make me think that
the problems were due to the low spec of the dev machine I still don't want
to risk it on the live machine.  I know from past posts that you use this
with no problems undre hpux, but equally I've seen posts here were people
have had problems.  I'll reconsider this though, perhaps try it over a
weekend.

--
Tony Flaherty
Snr. A/P, Informix DBA, HpUx Admin, Gimmi a broom!
MFS Ltd.

Quote:

>Why aren't you using kaio, Tony?
>Also, as Madison Pruet suggested, get the tables on ol_ch_mars_a spread out
>a bit.  ALTER FRAGMENT ..INIT is the boy!



>> HPUX 10.20

>> Dual processor K200 with 512Mb Ram

>> IDS 7.30.UC7

>> OLTP system running apps and instance on same box using shared memeory
>> connection, approx. 100 users.  The top output can show oninit running as
>> high as 90% when one of our "poor" applications is running (3rd party
>> software s%&t) but generally this is <50%

>> Earlier this year I increased the number of buffers allocated, this
>appears
>> to have resulted in an increase in checkpoint times from an average of 0
>> seconds to an average of 1 second with frequent occurances of 2,3,4 and
>> higher.  There are some very high peaks of 15+ but these are down to
>> problems with some applications and users running big updates during the
>> day.

>> As it happens the latest Tech Notes has an article on tuning checkpoints
>and
>> this mentions using onstat -g ioq to monitor the AIO VPS (output below)
I
>> always thought I needed approx one per chunk of these and I'm already
>> running with more than that, should I look at increasing this?

>> I also think I may be running into some disk related bottlenecks.

>> Some onstat info and the onconfig follow.

>> onstat -p

>> Informix Dynamic Server Version 7.30.UC7   -- On-Line -- Up 4 days
>> 19:53:17 -- 298184 Kbytes

>> Profile
>> dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached
>> 11361140 6648673  259256709 95.62   490697   4030618  6108539  91.97

>> isamtot  open     start    read     write    rewrite  delete   commit
>> rollbk
>> 177806221 11056284 17144992 108726109 993605   80792    22199    59751
>8

>> gp_read  gp_write gp_rewrt gp_del   gp_alloc gp_free  gp_curs
>> 0        0        0        0        0        0        0

>> ovlock   ovuserthread ovbuff   usercpu  syscpu   numckpts flushes
>> 0        0            0        41874.73 5360.16  687      2640

>> bufwaits lokwaits lockreqs deadlks  dltouts  ckpwaits compress seqscans
>> 409271   78       131714614 5        0        465      111473   37517

>> ixda-RA  idx-RA   da-RA    RA-pgsused lchwaits
>> 5346646  12818    4201810  9469045    34900

>> onstat -g ioq

>> Informix Dynamic Server Version 7.30.UC7   -- On-Line -- Up 4 days
>> 19:46:38 -- 298184 Kbytes

>> AIO I/O queues:
>> q name/id    len maxlen totalops  dskread dskwrite  dskcopy
>>   adt   0      0      0        0        0        0        0
>>   msc   0      0      1    21627        0        0        0
>>   aio   0      0      1     1234        2     1229        0
>>   pio   0      0      2     8130        0     8130        0
>>   lio   0      0      2    13308        0    13308        0
>>   gfd   3      0      7     6149     4142     2007        0
>>   gfd   4      0      1     3755     1748     2007        0
>>   gfd   5      0      1      350      350        0        0
>>   gfd   6      0     17   183672    65046   118626        0
>>   gfd   7      0     32    49739    48778      961        0
>>   gfd   8      0      1        5        5        0        0
>>   gfd   9      0     32     9952     7125     2827        0
>>   gfd  10      0     16   184376    65134   119242        0
>>   gfd  11      0      6      824      815        9        0
>>   gfd  12      0    368  1092188  1032091    60097        0
>>   gfd  13      0     32   108814    98913     9901        0
>>   gfd  14      0     16     9861     7059     2802        0
>>   gfd  15      0     64  6124061  6118215     5846        0
>>   gfd  16      0     32   114991   114424      567        0
>>   gfd  17      0     32     4144     3941      203        0
>>   gfd  18      0     32    65341    65341        0        0
>>   gfd  19      0      1       12       12        0        0
>>   gfd  20      0     35  1450984  1435438    15546        0
>>   gfd  21      0     32     7325     7136      189        0
>>   gfd  22      0     33   488831   487321     1510        0
>>   gfd  23      0     17   185220    66007   119213        0
>>   gfd  24      0     32    10221     9923      298        0
>>   gfd  25      0     32     2449     2326      123        0
>>   gfd  26      0     32    56746    56620      126        0
>>   gfd  27      0     32     5835     5835        0        0
>>   gfd  28      0     65  1657706  1651573     6133        0
>>   gfd  29      0      1     2775     2775        0        0
>>   gfd  30      0      1        7        7        0        0

>> onstat -g iof

>> Informix Dynamic Server Version 7.30.UC7   -- On-Line -- Up 4 days
>> 19:54:05 -- 298184 Kbytes

>> AIO global files:
>> gfd pathname         totalops  dskread dskwrite  io/s
>>   3 /dev/online_root     6143     4130     2013   0.0
>>   4 ol_ch_root_mirror     3764     1751     2013   0.0
>>   5 /dev/online_llog     7018      350     6668   0.0
>>   6 ol_ch_temp1        158541    65204    93337   0.6
>>   7 ol_ch_m_acti        20014    19472      542   0.1
>>   8 ol_ch_plog           4075        5     4070   0.0
>>   9 ol_ch_temp2          7424     7090      334   0.0
>>  10 ol_ch_temp3        159525    65292    94233   0.6
>>  11 ol_ch_m_multi         815      810        5   0.0
>>  12 ol_ch_mars_b       385241   331959    53282   1.4
>>  13 ol_ch_trafic       106089    97620     8469   0.4
>>  14 ol_ch_temp4          7378     7059      319   0.0
>>  15 ol_ch_mars_a      3520555  3515915     4640  12.6
>>  16 ol_ch_m_pent        35775    35655      120   0.1
>>  17 ol_ch_stuff          3442     3295      147   0.0
>>  18 ol_ch_m_test        63306    63306        0   0.2
>>  19 ol_ch_m_mentor         12       12        0   0.0
>>  20 ol_ch_m_mfsl       835539   822590    12949   3.0
>>  21 ol_ch_m_tech         4787     4725       62   0.0
>>  22 ol_ch_mitre2       156147   154917     1230   0.6
>>  23 ol_ch_temp5        160393    66151    94242   0.6
>>  24 ol_ch_m_misi         4201     4142       59   0.0
>>  25 ol_ch_m_eire         1543     1516       27   0.0
>>  26 ol_ch_icon          16252    16209       43   0.1
>>  27 ol_ch_trafic2        5748     5748        0   0.0
>>  28 ol_ch_mars_tmp    1113025  1107581     5444   4.0
>>  29 ol_ch_llog_mirror     9443     2775     6668   0.0
>>  30 ol_ch_plog_mirror     4077        7     4070   0.0

>> hpk200:/> onstat -g iov

>> Informix Dynamic Server Version 7.30.UC7   -- On-Line -- Up 4 days
>> 20:06:22 -- 298184 Kbytes

>> AIO I/O vps:
>> class/vp s  io/s totalops  dskread dskwrite  dskcopy  wakeups  io/wup
>> errors
>>   msc  0 i   0.1    21843        0        0        0    21091   1.0
>0
>>   aio  0 i   4.4  1239476  1139252   100221        0  1190947   1.0
>0
>>   aio  1 i   3.1   864188   782324    81864        0   846349   1.0
>0
>>   aio  2 i   1.7   480361   437566    42795        0   467930   1.0
>0
>>   aio  3 i   1.1   318999   300384    18615        0   308691   1.0
>0
>>   aio  4 i   0.9   244676   233249    11427        0   234027   1.0
>0
>>   aio  5 i   0.8   211325   202716     8609        0   200525   1.1
>0
>>   aio  6 i   0.7   192514   185111     7403        0   181906   1.1
>0
>>   aio  7 i   0.6   178799   172094     6705        0   168673   1.1
>0
>>   aio  8 i   0.6   169075   162745     6330        0   159492   1.1
>0
>>   aio  9 i   0.6   162142   156190     5952        0   152790   1.1
>0
>>   aio 10 i   0.6   157315   151717     5598        0   148486   1.1
>0
>>   aio 11 i   0.5   153949   148651     5298        0   145410   1.1
>0
>>   aio 12 i   0.5   150959   145933     5026        0   142582   1.1
>0
>>   aio 13 i   0.5   147858   143255     4603        0   140197   1.1
>0
>>   aio 14 i   0.5   145228   140964     4264        0   137954   1.1
>0
>>   aio 15 i   0.5   142219   138341     3878        0   135741   1.0
>0
>>   aio 16 i   0.5   139399   135974     3425        0   133258   1.0
>0
>>   aio 17 i   0.5   136719   133575     3144        0   131119   1.0
>0
>>   aio 18 i   0.5   134047   131132     2915        0   129093   1.0
>0
>>   aio 19 i   0.5   131805   128940     2865        0   127100   1.0
>0
>>   aio 20 i   0.5   129414   126538     2876        0   125053   1.0
>0
>>   aio 21 i   0.4   126403   123674     2729        0   122893   1.0
>0
>>   aio 22 i   0.4   123547   120912     2635        0   120182   1.0
>0
>>   aio 23 i   0.4   119394   116779     2615        0   116663   1.0
>0
>>   aio 24 i   0.4   113783   111223     2560        0   112064   1.0
>0
>>   aio 25 i   0.4   106866   104474     2392        0   105278   1.0
>0
>>   aio 26 i   0.3    97118    94693     2425        0    95982   1.0
>0
>>   aio 27 i   0.3    83989    81557     2432        0    83459   1.0
>0
>>   aio 28 i   0.2    68946    66583     2363        0    68700   1.0
>0
>>   aio 29 i   0.2    54349    51821     2528        0    53312   1.0
>0
>>   aio 30 i   0.1    41763    39310     2453        0    39815   1.0
>0
>>   aio 31 i   0.1    32964    30626     2338        0    30230   1.1
>0
>>   aio 32 i   0.1    28238    26000     2238        0    24906   1.1
>0
>>   aio 33 i   0.1    25754    23722     2032        0    22062   1.2
>0
>>   aio 34 i   0.1    24255    22330     1925        0    20445   1.2
>0
>>   aio 35 i   0.1  

...

read more »



Wed, 18 Jun 1902 08:00:00 GMT
 Tuning advice.
< French - Sorry For bad english>
Read all the other answers - They seem to be right -
Just 2  things:
Please onstat -R    I thinck that you may have a lot of Foreground write
(Fgwrite)
You can try  to increase  your physical log (too small)- May preserve time
on ontape -r
if you do that.

If you divide seqscans by RA-pgsused, you have a big number (pages/seqscans
: no good !)
identify faulty table (sysmaster:sysptprof) - Add indexes -



Quote:
> HPUX 10.20

> Dual processor K200 with 512Mb Ram

> IDS 7.30.UC7

> OLTP system running apps and instance on same box using shared memeory
> connection, approx. 100 users.  The top output can show oninit running as
> high as 90% when one of our "poor" applications is running (3rd party
> software s%&t) but generally this is <50%

> Earlier this year I increased the number of buffers allocated, this
appears
> to have resulted in an increase in checkpoint times from an average of 0
> seconds to an average of 1 second with frequent occurances of 2,3,4 and
> higher.  There are some very high peaks of 15+ but these are down to
> problems with some applications and users running big updates during the
> day.

> As it happens the latest Tech Notes has an article on tuning checkpoints
and
> this mentions using onstat -g ioq to monitor the AIO VPS (output below)  I
> always thought I needed approx one per chunk of these and I'm already
> running with more than that, should I look at increasing this?

> I also think I may be running into some disk related bottlenecks.

> Some onstat info and the onconfig follow.

> onstat -p

> Informix Dynamic Server Version 7.30.UC7   -- On-Line -- Up 4 days
> 19:53:17 -- 298184 Kbytes

> Profile
> dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached
> 11361140 6648673  259256709 95.62   490697   4030618  6108539  91.97

> isamtot  open     start    read     write    rewrite  delete   commit
> rollbk
> 177806221 11056284 17144992 108726109 993605   80792    22199    59751
8

> gp_read  gp_write gp_rewrt gp_del   gp_alloc gp_free  gp_curs
> 0        0        0        0        0        0        0

> ovlock   ovuserthread ovbuff   usercpu  syscpu   numckpts flushes
> 0        0            0        41874.73 5360.16  687      2640

> bufwaits lokwaits lockreqs deadlks  dltouts  ckpwaits compress seqscans
> 409271   78       131714614 5        0        465      111473   37517

> ixda-RA  idx-RA   da-RA    RA-pgsused lchwaits
> 5346646  12818    4201810  9469045    34900

> onstat -g ioq

> Informix Dynamic Server Version 7.30.UC7   -- On-Line -- Up 4 days
> 19:46:38 -- 298184 Kbytes

> AIO I/O queues:
> q name/id    len maxlen totalops  dskread dskwrite  dskcopy
>   adt   0      0      0        0        0        0        0
>   msc   0      0      1    21627        0        0        0
>   aio   0      0      1     1234        2     1229        0
>   pio   0      0      2     8130        0     8130        0
>   lio   0      0      2    13308        0    13308        0
>   gfd   3      0      7     6149     4142     2007        0
>   gfd   4      0      1     3755     1748     2007        0
>   gfd   5      0      1      350      350        0        0
>   gfd   6      0     17   183672    65046   118626        0
>   gfd   7      0     32    49739    48778      961        0
>   gfd   8      0      1        5        5        0        0
>   gfd   9      0     32     9952     7125     2827        0
>   gfd  10      0     16   184376    65134   119242        0
>   gfd  11      0      6      824      815        9        0
>   gfd  12      0    368  1092188  1032091    60097        0
>   gfd  13      0     32   108814    98913     9901        0
>   gfd  14      0     16     9861     7059     2802        0
>   gfd  15      0     64  6124061  6118215     5846        0
>   gfd  16      0     32   114991   114424      567        0
>   gfd  17      0     32     4144     3941      203        0
>   gfd  18      0     32    65341    65341        0        0
>   gfd  19      0      1       12       12        0        0
>   gfd  20      0     35  1450984  1435438    15546        0
>   gfd  21      0     32     7325     7136      189        0
>   gfd  22      0     33   488831   487321     1510        0
>   gfd  23      0     17   185220    66007   119213        0
>   gfd  24      0     32    10221     9923      298        0
>   gfd  25      0     32     2449     2326      123        0
>   gfd  26      0     32    56746    56620      126        0
>   gfd  27      0     32     5835     5835        0        0
>   gfd  28      0     65  1657706  1651573     6133        0
>   gfd  29      0      1     2775     2775        0        0
>   gfd  30      0      1        7        7        0        0

> onstat -g iof

> Informix Dynamic Server Version 7.30.UC7   -- On-Line -- Up 4 days
> 19:54:05 -- 298184 Kbytes

> AIO global files:
> gfd pathname         totalops  dskread dskwrite  io/s
>   3 /dev/online_root     6143     4130     2013   0.0
>   4 ol_ch_root_mirror     3764     1751     2013   0.0
>   5 /dev/online_llog     7018      350     6668   0.0
>   6 ol_ch_temp1        158541    65204    93337   0.6
>   7 ol_ch_m_acti        20014    19472      542   0.1
>   8 ol_ch_plog           4075        5     4070   0.0
>   9 ol_ch_temp2          7424     7090      334   0.0
>  10 ol_ch_temp3        159525    65292    94233   0.6
>  11 ol_ch_m_multi         815      810        5   0.0
>  12 ol_ch_mars_b       385241   331959    53282   1.4
>  13 ol_ch_trafic       106089    97620     8469   0.4
>  14 ol_ch_temp4          7378     7059      319   0.0
>  15 ol_ch_mars_a      3520555  3515915     4640  12.6
>  16 ol_ch_m_pent        35775    35655      120   0.1
>  17 ol_ch_stuff          3442     3295      147   0.0
>  18 ol_ch_m_test        63306    63306        0   0.2
>  19 ol_ch_m_mentor         12       12        0   0.0
>  20 ol_ch_m_mfsl       835539   822590    12949   3.0
>  21 ol_ch_m_tech         4787     4725       62   0.0
>  22 ol_ch_mitre2       156147   154917     1230   0.6
>  23 ol_ch_temp5        160393    66151    94242   0.6
>  24 ol_ch_m_misi         4201     4142       59   0.0
>  25 ol_ch_m_eire         1543     1516       27   0.0
>  26 ol_ch_icon          16252    16209       43   0.1
>  27 ol_ch_trafic2        5748     5748        0   0.0
>  28 ol_ch_mars_tmp    1113025  1107581     5444   4.0
>  29 ol_ch_llog_mirror     9443     2775     6668   0.0
>  30 ol_ch_plog_mirror     4077        7     4070   0.0

> hpk200:/> onstat -g iov

> Informix Dynamic Server Version 7.30.UC7   -- On-Line -- Up 4 days
> 20:06:22 -- 298184 Kbytes

> AIO I/O vps:
> class/vp s  io/s totalops  dskread dskwrite  dskcopy  wakeups  io/wup
> errors
>   msc  0 i   0.1    21843        0        0        0    21091   1.0
0
>   aio  0 i   4.4  1239476  1139252   100221        0  1190947   1.0
0
>   aio  1 i   3.1   864188   782324    81864        0   846349   1.0
0
>   aio  2 i   1.7   480361   437566    42795        0   467930   1.0
0
>   aio  3 i   1.1   318999   300384    18615        0   308691   1.0
0
>   aio  4 i   0.9   244676   233249    11427        0   234027   1.0
0
>   aio  5 i   0.8   211325   202716     8609        0   200525   1.1
0
>   aio  6 i   0.7   192514   185111     7403        0   181906   1.1
0
>   aio  7 i   0.6   178799   172094     6705        0   168673   1.1
0
>   aio  8 i   0.6   169075   162745     6330        0   159492   1.1
0
>   aio  9 i   0.6   162142   156190     5952        0   152790   1.1
0
>   aio 10 i   0.6   157315   151717     5598        0   148486   1.1
0
>   aio 11 i   0.5   153949   148651     5298        0   145410   1.1
0
>   aio 12 i   0.5   150959   145933     5026        0   142582   1.1
0
>   aio 13 i   0.5   147858   143255     4603        0   140197   1.1
0
>   aio 14 i   0.5   145228   140964     4264        0   137954   1.1
0
>   aio 15 i   0.5   142219   138341     3878        0   135741   1.0
0
>   aio 16 i   0.5   139399   135974     3425        0   133258   1.0
0
>   aio 17 i   0.5   136719   133575     3144        0   131119   1.0
0
>   aio 18 i   0.5   134047   131132     2915        0   129093   1.0
0
>   aio 19 i   0.5   131805   128940     2865        0   127100   1.0
0
>   aio 20 i   0.5   129414   126538     2876        0   125053   1.0
0
>   aio 21 i   0.4   126403   123674     2729        0   122893   1.0
0
>   aio 22 i   0.4   123547   120912     2635        0   120182   1.0
0
>   aio 23 i   0.4   119394   116779     2615        0   116663   1.0
0
>   aio 24 i   0.4   113783   111223     2560        0   112064   1.0
0
>   aio 25 i   0.4   106866   104474     2392        0   105278   1.0
0
>   aio 26 i   0.3    97118    94693     2425        0    95982   1.0
0
>   aio 27 i   0.3    83989    81557     2432        0    83459   1.0
0
>   aio 28 i   0.2    68946    66583     2363        0    68700   1.0
0
>   aio 29 i   0.2    54349    51821     2528        0    53312   1.0
0
>   aio 30 i   0.1    41763    39310     2453        0    39815   1.0
0
>   aio 31 i   0.1    32964    30626     2338        0    30230   1.1
0
>   aio 32 i   0.1    28238    26000     2238        0    24906   1.1
0
>   aio 33 i   0.1    25754    23722     2032        0    22062   1.2
0
>   aio 34 i   0.1    24255    22330     1925        0    20445   1.2
0
>   aio 35 i   0.1    23072    21231     1841        0    19347   1.2
0
>   aio 36 i   0.1    22148    20339     1809        0    18417   1.2
0
>   aio 37 i   0.1    21442    19653     1789        0    17616   1.2
0
>   aio 38 i   0.1    20661    18953     1708        0    16988   1.2
0
>   aio 39 i   0.1    20101    18367     1734        0    16396   1.2
0
>   pio  0 i   0.0     4099        0     4099        0     4097   1.0
0
>   pio  1 i   0.0     4093        0     4093        0     4094   1.0
0
>   lio  0 i   0.0     6723        0     6723        0  

...

read more »



Wed, 18 Jun 1902 08:00:00 GMT
 Tuning advice.
Mmm, I guess I need to turn on TBLSPACE_STATS for this.

--
Tony Flaherty
Snr. A/P, Informix DBA, HpUx Admin, Gimmi a broom!
MFS Ltd.

Quote:

>< French - Sorry For bad english>
>Read all the other answers - They seem to be right -
>Just 2  things:
>Please onstat -R    I thinck that you may have a lot of Foreground write
>(Fgwrite)
>You can try  to increase  your physical log (too small)- May preserve time
>on ontape -r
>if you do that.

>If you divide seqscans by RA-pgsused, you have a big number (pages/seqscans
>: no good !)
>identify faulty table (sysmaster:sysptprof) - Add indexes -



>> HPUX 10.20

>> Dual processor K200 with 512Mb Ram

>> IDS 7.30.UC7

>> OLTP system running apps and instance on same box using shared memeory
>> connection, approx. 100 users.  The top output can show oninit running as
>> high as 90% when one of our "poor" applications is running (3rd party
>> software s%&t) but generally this is <50%

>> Earlier this year I increased the number of buffers allocated, this
>appears
>> to have resulted in an increase in checkpoint times from an average of 0
>> seconds to an average of 1 second with frequent occurances of 2,3,4 and
>> higher.  There are some very high peaks of 15+ but these are down to
>> problems with some applications and users running big updates during the
>> day.

>> As it happens the latest Tech Notes has an article on tuning checkpoints
>and
>> this mentions using onstat -g ioq to monitor the AIO VPS (output below)
I
>> always thought I needed approx one per chunk of these and I'm already
>> running with more than that, should I look at increasing this?

>> I also think I may be running into some disk related bottlenecks.

>> Some onstat info and the onconfig follow.

>> onstat -p

>> Informix Dynamic Server Version 7.30.UC7   -- On-Line -- Up 4 days
>> 19:53:17 -- 298184 Kbytes

>> Profile
>> dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached
>> 11361140 6648673  259256709 95.62   490697   4030618  6108539  91.97

>> isamtot  open     start    read     write    rewrite  delete   commit
>> rollbk
>> 177806221 11056284 17144992 108726109 993605   80792    22199    59751
>8

>> gp_read  gp_write gp_rewrt gp_del   gp_alloc gp_free  gp_curs
>> 0        0        0        0        0        0        0

>> ovlock   ovuserthread ovbuff   usercpu  syscpu   numckpts flushes
>> 0        0            0        41874.73 5360.16  687      2640

>> bufwaits lokwaits lockreqs deadlks  dltouts  ckpwaits compress seqscans
>> 409271   78       131714614 5        0        465      111473   37517

>> ixda-RA  idx-RA   da-RA    RA-pgsused lchwaits
>> 5346646  12818    4201810  9469045    34900

>> onstat -g ioq

>> Informix Dynamic Server Version 7.30.UC7   -- On-Line -- Up 4 days
>> 19:46:38 -- 298184 Kbytes

>> AIO I/O queues:
>> q name/id    len maxlen totalops  dskread dskwrite  dskcopy
>>   adt   0      0      0        0        0        0        0
>>   msc   0      0      1    21627        0        0        0
>>   aio   0      0      1     1234        2     1229        0
>>   pio   0      0      2     8130        0     8130        0
>>   lio   0      0      2    13308        0    13308        0
>>   gfd   3      0      7     6149     4142     2007        0
>>   gfd   4      0      1     3755     1748     2007        0
>>   gfd   5      0      1      350      350        0        0
>>   gfd   6      0     17   183672    65046   118626        0
>>   gfd   7      0     32    49739    48778      961        0
>>   gfd   8      0      1        5        5        0        0
>>   gfd   9      0     32     9952     7125     2827        0
>>   gfd  10      0     16   184376    65134   119242        0
>>   gfd  11      0      6      824      815        9        0
>>   gfd  12      0    368  1092188  1032091    60097        0
>>   gfd  13      0     32   108814    98913     9901        0
>>   gfd  14      0     16     9861     7059     2802        0
>>   gfd  15      0     64  6124061  6118215     5846        0
>>   gfd  16      0     32   114991   114424      567        0
>>   gfd  17      0     32     4144     3941      203        0
>>   gfd  18      0     32    65341    65341        0        0
>>   gfd  19      0      1       12       12        0        0
>>   gfd  20      0     35  1450984  1435438    15546        0
>>   gfd  21      0     32     7325     7136      189        0
>>   gfd  22      0     33   488831   487321     1510        0
>>   gfd  23      0     17   185220    66007   119213        0
>>   gfd  24      0     32    10221     9923      298        0
>>   gfd  25      0     32     2449     2326      123        0
>>   gfd  26      0     32    56746    56620      126        0
>>   gfd  27      0     32     5835     5835        0        0
>>   gfd  28      0     65  1657706  1651573     6133        0
>>   gfd  29      0      1     2775     2775        0        0
>>   gfd  30      0      1        7        7        0        0

>> onstat -g iof

>> Informix Dynamic Server Version 7.30.UC7   -- On-Line -- Up 4 days
>> 19:54:05 -- 298184 Kbytes

>> AIO global files:
>> gfd pathname         totalops  dskread dskwrite  io/s
>>   3 /dev/online_root     6143     4130     2013   0.0
>>   4 ol_ch_root_mirror     3764     1751     2013   0.0
>>   5 /dev/online_llog     7018      350     6668   0.0
>>   6 ol_ch_temp1        158541    65204    93337   0.6
>>   7 ol_ch_m_acti        20014    19472      542   0.1
>>   8 ol_ch_plog           4075        5     4070   0.0
>>   9 ol_ch_temp2          7424     7090      334   0.0
>>  10 ol_ch_temp3        159525    65292    94233   0.6
>>  11 ol_ch_m_multi         815      810        5   0.0
>>  12 ol_ch_mars_b       385241   331959    53282   1.4
>>  13 ol_ch_trafic       106089    97620     8469   0.4
>>  14 ol_ch_temp4          7378     7059      319   0.0
>>  15 ol_ch_mars_a      3520555  3515915     4640  12.6
>>  16 ol_ch_m_pent        35775    35655      120   0.1
>>  17 ol_ch_stuff          3442     3295      147   0.0
>>  18 ol_ch_m_test        63306    63306        0   0.2
>>  19 ol_ch_m_mentor         12       12        0   0.0
>>  20 ol_ch_m_mfsl       835539   822590    12949   3.0
>>  21 ol_ch_m_tech         4787     4725       62   0.0
>>  22 ol_ch_mitre2       156147   154917     1230   0.6
>>  23 ol_ch_temp5        160393    66151    94242   0.6
>>  24 ol_ch_m_misi         4201     4142       59   0.0
>>  25 ol_ch_m_eire         1543     1516       27   0.0
>>  26 ol_ch_icon          16252    16209       43   0.1
>>  27 ol_ch_trafic2        5748     5748        0   0.0
>>  28 ol_ch_mars_tmp    1113025  1107581     5444   4.0
>>  29 ol_ch_llog_mirror     9443     2775     6668   0.0
>>  30 ol_ch_plog_mirror     4077        7     4070   0.0

>> hpk200:/> onstat -g iov

>> Informix Dynamic Server Version 7.30.UC7   -- On-Line -- Up 4 days
>> 20:06:22 -- 298184 Kbytes

>> AIO I/O vps:
>> class/vp s  io/s totalops  dskread dskwrite  dskcopy  wakeups  io/wup
>> errors
>>   msc  0 i   0.1    21843        0        0        0    21091   1.0
>0
>>   aio  0 i   4.4  1239476  1139252   100221        0  1190947   1.0
>0
>>   aio  1 i   3.1   864188   782324    81864        0   846349   1.0
>0
>>   aio  2 i   1.7   480361   437566    42795        0   467930   1.0
>0
>>   aio  3 i   1.1   318999   300384    18615        0   308691   1.0
>0
>>   aio  4 i   0.9   244676   233249    11427        0   234027   1.0
>0
>>   aio  5 i   0.8   211325   202716     8609        0   200525   1.1
>0
>>   aio  6 i   0.7   192514   185111     7403        0   181906   1.1
>0
>>   aio  7 i   0.6   178799   172094     6705        0   168673   1.1
>0
>>   aio  8 i   0.6   169075   162745     6330        0   159492   1.1
>0
>>   aio  9 i   0.6   162142   156190     5952        0   152790   1.1
>0
>>   aio 10 i   0.6   157315   151717     5598        0   148486   1.1
>0
>>   aio 11 i   0.5   153949   148651     5298        0   145410   1.1
>0
>>   aio 12 i   0.5   150959   145933     5026        0   142582   1.1
>0
>>   aio 13 i   0.5   147858   143255     4603        0   140197   1.1
>0
>>   aio 14 i   0.5   145228   140964     4264        0   137954   1.1
>0
>>   aio 15 i   0.5   142219   138341     3878        0   135741   1.0
>0
>>   aio 16 i   0.5   139399   135974     3425        0   133258   1.0
>0
>>   aio 17 i   0.5   136719   133575     3144        0   131119   1.0
>0
>>   aio 18 i   0.5   134047   131132     2915        0   129093   1.0
>0
>>   aio 19 i   0.5   131805   128940     2865        0   127100   1.0
>0
>>   aio 20 i   0.5   129414   126538     2876        0   125053   1.0
>0
>>   aio 21 i   0.4   126403   123674     2729        0   122893   1.0
>0
>>   aio 22 i   0.4   123547   120912     2635        0   120182   1.0
>0
>>   aio 23 i   0.4   119394   116779     2615        0   116663   1.0
>0
>>   aio 24 i   0.4   113783   111223     2560        0   112064   1.0
>0
>>   aio 25 i   0.4   106866   104474     2392        0   105278   1.0
>0
>>   aio 26 i   0.3    97118    94693     2425        0    95982   1.0
>0
>>   aio 27 i   0.3    83989    81557     2432        0    83459   1.0
>0
>>   aio 28 i   0.2    68946    66583     2363        0    68700   1.0
>0
>>   aio 29 i   0.2    54349    51821     2528        0    53312   1.0
>0
>>   aio 30 i   0.1    41763    39310     2453        0    39815   1.0
>0
>>   aio 31 i   0.1    32964    30626     2338        0    30230   1.1
>0
>>   aio 32 i   0.1    28238    26000     2238        0    24906   1.1
>0
>>   aio 33 i   0.1    25754    23722     2032        0    22062   1.2
>0
>>   aio 34 i   0.1    24255    22330     1925        0    20445   1.2
>0
>>   aio 35 i   0.1    23072    21231     1841        0    19347   1.2
>0
>>   aio 36 i   0.1    22148    20339     1809        0    18417   1.2
>0
>>   aio 37 i  

...

read more »



Wed, 18 Jun 1902 08:00:00 GMT
 Tuning advice.
Sigh,

Ok, after my thread about memory usage and this thread I thought I'd run for
a day or two with my proposed changes;-

Value                   From           To
BUFFERS            80000         130000
CKPTINTVL          200           400
LRUS                    67            127
CLEANERS           67            127

My checkpoints are worse as is my write cache percentage, read cache is no
better.  Now I'm confused ;o/

Onstat -p

Informix Dynamic Server Version 7.30.UC7   -- On-Line -- Up 1 days
19:02:10 -- 4
02288 Kbytes

Profile
dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached
2588092  3606720  80714163 96.79   142549   1030517  1279771  88.86

isamtot  open     start    read     write    rewrite  delete   commit
rollbk
65657799 3473262  5041188  46246483 231204   35468    17582    20672    3

gp_read  gp_write gp_rewrt gp_del   gp_alloc gp_free  gp_curs
0        0        0        0        0        0        0

ovlock   ovuserthread ovbuff   usercpu  syscpu   numckpts flushes
0        0            0        12028.12 1275.18  58       480

bufwaits lokwaits lockreqs deadlks  dltouts  ckpwaits compress seqscans
73869    25       52342368 1        0        60       19620    17180

ixda-RA  idx-RA   da-RA    RA-pgsused lchwaits
531611   3065     1410791  1868333    5290

I've been trying to improve this instance for several months, slowly adding
buffers, each time I've done this the write cache appears to have
deteriated, I thought this was because I needed to tune other values also
but now I've just confused myself totally ;o(  Any ideas...Please.
--
Tony Flaherty
Snr. A/P, Informix DBA, HpUx Admin, Gimmi a broom!
MFS Ltd.

Quote:

>HPUX 10.20

>Dual processor K200 with 512Mb Ram

>IDS 7.30.UC7

>OLTP system running apps and instance on same box using shared memeory
>connection, approx. 100 users.  The top output can show oninit running as
>high as 90% when one of our "poor" applications is running (3rd party
>software s%&t) but generally this is <50%

>Earlier this year I increased the number of buffers allocated, this appears
>to have resulted in an increase in checkpoint times from an average of 0
>seconds to an average of 1 second with frequent occurances of 2,3,4 and
>higher.  There are some very high peaks of 15+ but these are down to
>problems with some applications and users running big updates during the
>day.

>As it happens the latest Tech Notes has an article on tuning checkpoints
and
>this mentions using onstat -g ioq to monitor the AIO VPS (output below)  I
>always thought I needed approx one per chunk of these and I'm already
>running with more than that, should I look at increasing this?

>I also think I may be running into some disk related bottlenecks.

>Some onstat info and the onconfig follow.

>onstat -p

>Informix Dynamic Server Version 7.30.UC7   -- On-Line -- Up 4 days
>19:53:17 -- 298184 Kbytes

>Profile
>dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached
>11361140 6648673  259256709 95.62   490697   4030618  6108539  91.97

>isamtot  open     start    read     write    rewrite  delete   commit
>rollbk
>177806221 11056284 17144992 108726109 993605   80792    22199    59751    8

>gp_read  gp_write gp_rewrt gp_del   gp_alloc gp_free  gp_curs
>0        0        0        0        0        0        0

>ovlock   ovuserthread ovbuff   usercpu  syscpu   numckpts flushes
>0        0            0        41874.73 5360.16  687      2640

>bufwaits lokwaits lockreqs deadlks  dltouts  ckpwaits compress seqscans
>409271   78       131714614 5        0        465      111473   37517

>ixda-RA  idx-RA   da-RA    RA-pgsused lchwaits
>5346646  12818    4201810  9469045    34900

>onstat -g ioq

>Informix Dynamic Server Version 7.30.UC7   -- On-Line -- Up 4 days
>19:46:38 -- 298184 Kbytes

>AIO I/O queues:
>q name/id    len maxlen totalops  dskread dskwrite  dskcopy
>  adt   0      0      0        0        0        0        0
>  msc   0      0      1    21627        0        0        0
>  aio   0      0      1     1234        2     1229        0
>  pio   0      0      2     8130        0     8130        0
>  lio   0      0      2    13308        0    13308        0
>  gfd   3      0      7     6149     4142     2007        0
>  gfd   4      0      1     3755     1748     2007        0
>  gfd   5      0      1      350      350        0        0
>  gfd   6      0     17   183672    65046   118626        0
>  gfd   7      0     32    49739    48778      961        0
>  gfd   8      0      1        5        5        0        0
>  gfd   9      0     32     9952     7125     2827        0
>  gfd  10      0     16   184376    65134   119242        0
>  gfd  11      0      6      824      815        9        0
>  gfd  12      0    368  1092188  1032091    60097        0
>  gfd  13      0     32   108814    98913     9901        0
>  gfd  14      0     16     9861     7059     2802        0
>  gfd  15      0     64  6124061  6118215     5846        0
>  gfd  16      0     32   114991   114424      567        0
>  gfd  17      0     32     4144     3941      203        0
>  gfd  18      0     32    65341    65341        0        0
>  gfd  19      0      1       12       12        0        0
>  gfd  20      0     35  1450984  1435438    15546        0
>  gfd  21      0     32     7325     7136      189        0
>  gfd  22      0     33   488831   487321     1510        0
>  gfd  23      0     17   185220    66007   119213        0
>  gfd  24      0     32    10221     9923      298        0
>  gfd  25      0     32     2449     2326      123        0
>  gfd  26      0     32    56746    56620      126        0
>  gfd  27      0     32     5835     5835        0        0
>  gfd  28      0     65  1657706  1651573     6133        0
>  gfd  29      0      1     2775     2775        0        0
>  gfd  30      0      1        7        7        0        0

>onstat -g iof

>Informix Dynamic Server Version 7.30.UC7   -- On-Line -- Up 4 days
>19:54:05 -- 298184 Kbytes

>AIO global files:
>gfd pathname         totalops  dskread dskwrite  io/s
>  3 /dev/online_root     6143     4130     2013   0.0
>  4 ol_ch_root_mirror     3764     1751     2013   0.0
>  5 /dev/online_llog     7018      350     6668   0.0
>  6 ol_ch_temp1        158541    65204    93337   0.6
>  7 ol_ch_m_acti        20014    19472      542   0.1
>  8 ol_ch_plog           4075        5     4070   0.0
>  9 ol_ch_temp2          7424     7090      334   0.0
> 10 ol_ch_temp3        159525    65292    94233   0.6
> 11 ol_ch_m_multi         815      810        5   0.0
> 12 ol_ch_mars_b       385241   331959    53282   1.4
> 13 ol_ch_trafic       106089    97620     8469   0.4
> 14 ol_ch_temp4          7378     7059      319   0.0
> 15 ol_ch_mars_a      3520555  3515915     4640  12.6
> 16 ol_ch_m_pent        35775    35655      120   0.1
> 17 ol_ch_stuff          3442     3295      147   0.0
> 18 ol_ch_m_test        63306    63306        0   0.2
> 19 ol_ch_m_mentor         12       12        0   0.0
> 20 ol_ch_m_mfsl       835539   822590    12949   3.0
> 21 ol_ch_m_tech         4787     4725       62   0.0
> 22 ol_ch_mitre2       156147   154917     1230   0.6
> 23 ol_ch_temp5        160393    66151    94242   0.6
> 24 ol_ch_m_misi         4201     4142       59   0.0
> 25 ol_ch_m_eire         1543     1516       27   0.0
> 26 ol_ch_icon          16252    16209       43   0.1
> 27 ol_ch_trafic2        5748     5748        0   0.0
> 28 ol_ch_mars_tmp    1113025  1107581     5444   4.0
> 29 ol_ch_llog_mirror     9443     2775     6668   0.0
> 30 ol_ch_plog_mirror     4077        7     4070   0.0

>hpk200:/> onstat -g iov

>Informix Dynamic Server Version 7.30.UC7   -- On-Line -- Up 4 days
>20:06:22 -- 298184 Kbytes

>AIO I/O vps:
>class/vp s  io/s totalops  dskread dskwrite  dskcopy  wakeups  io/wup
>errors
>  msc  0 i   0.1    21843        0        0        0    21091   1.0       0
>  aio  0 i   4.4  1239476  1139252   100221        0  1190947   1.0       0
>  aio  1 i   3.1   864188   782324    81864        0   846349   1.0       0
>  aio  2 i   1.7   480361   437566    42795        0   467930   1.0       0
>  aio  3 i   1.1   318999   300384    18615        0   308691   1.0       0
>  aio  4 i   0.9   244676   233249    11427        0   234027   1.0       0
>  aio  5 i   0.8   211325   202716     8609        0   200525   1.1       0
>  aio  6 i   0.7   192514   185111     7403        0   181906   1.1       0
>  aio  7 i   0.6   178799   172094     6705        0   168673   1.1       0
>  aio  8 i   0.6   169075   162745     6330        0   159492   1.1       0
>  aio  9 i   0.6   162142   156190     5952        0   152790   1.1       0
>  aio 10 i   0.6   157315   151717     5598        0   148486   1.1       0
>  aio 11 i   0.5   153949   148651     5298        0   145410   1.1       0
>  aio 12 i   0.5   150959   145933     5026        0   142582   1.1       0
>  aio 13 i   0.5   147858   143255     4603        0   140197   1.1       0
>  aio 14 i   0.5   145228   140964     4264        0   137954   1.1       0
>  aio 15 i   0.5   142219   138341     3878        0   135741   1.0       0
>  aio 16 i   0.5   139399   135974     3425        0   133258   1.0       0
>  aio 17 i   0.5   136719   133575     3144        0   131119   1.0       0
>  aio 18 i   0.5   134047   131132     2915        0   129093   1.0       0
>  aio 19 i   0.5   131805   128940     2865        0   127100   1.0       0
>  aio 20 i   0.5   129414   126538     2876        0   125053   1.0       0
>  aio 21 i   0.4   126403   123674     2729        0   122893   1.0       0
>  aio 22 i   0.4   123547   120912     2635        0   120182   1.0       0
>  aio 23 i   0.4   119394   116779     2615        0   116663   1.0       0
>  aio 24 i   0.4   113783   111223     2560        0   112064   1.0       0
>  aio 25 i   0.4   106866   104474     2392        0

...

read more »



Wed, 18 Jun 1902 08:00:00 GMT
 
 [ 10 post ] 

 Relevant Pages 

1. Tuning Advice

2. oracle 8.0.6 running aweful!!!!! need tuning advice!

3. Advice in tuning performance

4. Tuning Nightmare - Advice Needed

5. Advice on data cache tuning ...

6. Oracle tuning and SQL tuning

7. Tuning scripts FREE/Online from tuning book

8. Oracle tuning and SQL tuning

9. Net8 Performance Tuning (client/server tuning)

10. Your Advice: SQL datetime?? :Your Advice

11. Query tuning experts: Any hope for tuning query with 5+ joins and a sort?

12. empower! - free Oracle performance tuning software and tuning guide


 
Powered by phpBB® Forum Software