need help interpreting onstat -p before and after some changes!

[WiccaChic]

Hi forum. I have pasted a before and after copy of "onstat -p" and I
am hoping to get your take on what you think seems to have gotten
better/worse based on just that info. I made some changes to my
buffers, cleaners, lrus and check point intervals - largely based on
input from you folks!

It is not a mistake for you to assume I have very little Informix dba
type experience. But I am trying to read my way to it as well as I
can!

___| BEFORE |_________________________________________________ _______
IBM Informix Dynamic Server Version 9.40.FC2 -- On-Line -- Up 17 days
07:04:06 -- 1706560 Kbytes

Profile
dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached
900881808 1877905516 32921980794 97.26 65487771 101306873 1371660039
95.23

isamtot open start read write rewrite delete commit
rollbk
26826182414 777860503 1129038992 18919136928 513690577 76895220
21650089 110788
136

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 1365099.75 263254.84 1279 3547

bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress
seqscans
51043463 12 15084951321 0 0 4442 11836393
22801537

ixda-RA idx-RA da-RA RA-pgsused lchwaits
93004239 26278520 637128593 753040097 38850158


___| AFTER |_________________________________________________ __________
IBM Informix Dynamic Server Version 9.40.FC2 -- On-Line -- Up 17
days 02:05:52 -- 3387376 Kbytes

Profile
dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached
450404183 1066467881 26798253905 98.32 68805670 117683468 998172729
93.11

isamtot open start read write rewrite delete commit
rollbk
21845481388 505190720 1169318304 14887625300 240575222 101593739
50604236 96415
14

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 905473.40 160867.72 4488 13024

bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress
seqscans
28954227 6 16212210789 0 0 18081 6319320
4991755

ixda-RA idx-RA da-RA RA-pgsused lchwaits
42829593 17202291 297607117 356203620 5794405
__________________________________________________ _______________
I have seen some signifigant performance boosts on some of the OLTP
type work we do in the evening. I also have noticed longer checkpoints
(even though I lowered the interval and raised cleaners and lrus
aggresively) but the users have not really noticed or complained.

Thanks for helping.

 

[vpshriyan]

Hi Wic,

It's bit difficult to do a thorough statistical analysis of these accumulated information. Anyway, in my view the changes have been positive and because of this factor you and your end-users are experiencing better performance than before. Below are my technical analysis:

1. Cached reads & writes are above 90%, a positive sign.

2. Bufer waits shows the number of times a thread had to wait to get a lock on a buffer. This is linked with the readahead values RA_PAGES & RA_THRESHOLD. If these are set very high, bufer waits can grow tremendously. It can also be caused by small value of BUFFERS.

3. Lock waits status of pretty good. Ideally be less than 1% of lock requests.

4. Checkpoint waits figure can climb-up in a high transaction volume system. When a trasaction thread enter into a critical state, database server prevents the checkpoints from being executed.

6. The sequential scans field (seqscans) need to be monitored in an OLTP system, as it's success depends on efficient use of index scannings.

-- tables having 20,000 rows and no indexes, a possible source of sequential scans.
select b.tabname, ti_nrows
from sysmaster:systabinfo a, sysmaster:systabnames b,
systables c
where ti_partnum = b.partnum and ti_partnum = c.partnum
and c.tabid > 99 and ti_nrows > 20000
and tabid not in ( select tabid from sysindexes );

7. Ideally, (ixda-RA + idx-RA + da-RA) should almost equal RA-pgsused, if not reduce RA_PAGES & RA_THRESHOLD values.

Regards,
Shriyan

"Life is a test, if this had been a real life you would have been given instructions on where to go."