onstat -p

Dear forumers, Attached is the "onstat -p" command : Do you think my bufwaits is high ?? Or is there anything significant from this information that need attention for performance reasons ? -------------------------------------------------------------------------------- ----------------------------------------------------- Profile dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 6905193182 22105447024 107725253166 93.59 94190106 252268094 482840071 80.49 isamtot open start read write rewrite delete commit rollbk 89519595895 201770353 1361832701 68726735250 67103836 18122350 44929225 8373651 19462 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 3560050.03 445002.72 2037 4074 bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans 369129515 55322 73916466654 4 0 6659 8748508 1216642 ixda-RA idx-RA da-RA RA-pgsused lchwaits 5539472315 167505538 240290966 5928754001 37407090 Best Regards, Dominic Seah 64616826

How long has this database been up? 7 days? (Assuming 5 min Checkpoint interval and all triggered by time) I'd say you have some issues. Read cache and write cache are not great and should be looked into bufwaits is relatively high as are lock waits Very large number of lock requests - at a guess you are in lock mode row - might want to check if you need that on the tables involved. 8 million compresses/44 million deletes - high traffic on wide rows. Data Model/application issue. 19K rollbacks? Seems a little high, too many for people to be doing it manually (unless you have thousands of users) 4 deadlocks, but no timeouts - interesting. checkpoint waits don't bother me with this volume of traffic. sequential scans are interesting and may indicate statistics that are out of date. high latch waits. What are: The OS and version uname -a (assuming some flavour of unix or even cygwin) How much memory does it have (vmstat) The database and version (onstat -) onconfig settings (send $INFORMIXDIR/etc/$ONCONFIG) The last 100 lines of your logfile (onstat -m, logfile is listed on the top line, tail -100l <that_file>) How are you currently managing statistics? j. (going back to bed) -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On Behalf Of Dominic Seah Chin Teck Sent: Thursday, March 22, 2007 9:29 PM To: ids@iiug.org Subject: onstat -p [8713] Dear forumers, Attached is the "onstat -p" command : Do you think my bufwaits is high ?? Or is there anything significant from this information that need attention for performance reasons ? ---------------------------------------------------------------------------- --------------------------------------------------------- Profile dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 6905193182 22105447024 107725253166 93.59 94190106 252268094 482840071 80.49 isamtot open start read write rewrite delete commit rollbk 89519595895 201770353 1361832701 68726735250 67103836 18122350 44929225 8373651 19462 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 3560050.03 445002.72 2037 4074 bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans 369129515 55322 73916466654 4 0 6659 8748508 1216642 ixda-RA idx-RA da-RA RA-pgsused lchwaits 5539472315 167505538 240290966 5928754001 37407090 Best Regards, Dominic Seah 64616826 **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

Bufwaits ratio (BR) is only 1.63 which is fine. Raw bufwaits is high, but in comparison to the number of operations that may need to latch an LRU or a buffer it' within normal performance metric tolerances. What's high is your BTR (Buffer Turnover Ratio). Calculated using an estimated 7days 4 hours of operation since that stats were cleared and a guess of 200,000 buffers I get a BTR of 656.6 so in order for that to be reasonable you'd have to have 100 times the number of buffers (20,000,000) or been in operation for 100 times as long (or more like 700 days) since startup or zeroing stats. Given that neither is likely, your problem is that either you need more buffers or you're thrashing the existing buffer cache unneccessarily. The onstat output shows that you had 1216642 seq scans over 201770353 SQL requests (ISAM opens) or 1 out of every 166 queries generated a sequential scan. That's too high and the likely culprit for the high BTR figure. You may have to add indexes or update statistics and you should certainly review all major SQLs on the system. Art S. Kagel ----- Original Message ----- From: Dominic Seah Chin Teck <ids@iiug.org> At: 3/22 22:29:25 Dear forumers, Attached is the "onstat -p" command : Do you think my bufwaits is high ?? Or is there anything significant from this information that need attention for performance reasons ? -------------------------------------------------------------------------------- ----------------------------------------------------- Profile dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 6905193182 22105447024 107725253166 93.59 94190106 252268094 482840071 80.49 isamtot open start read write rewrite delete commit rollbk 89519595895 201770353 1361832701 68726735250 67103836 18122350 44929225 8373651 19462 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 3560050.03 445002.72 2037 4074 bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans 369129515 55322 73916466654 4 0 6659 8748508 1216642 ixda-RA idx-RA da-RA RA-pgsused lchwaits 5539472315 167505538 240290966 5928754001 37407090 Best Regards, Dominic Seah 64616826 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.