Informix

nested-group-icon.png

DB2

Expand all | Collapse all

a script to monitor Informix sessions

  • 1.  a script to monitor Informix sessions

    Posted 25 days ago
      |   view attached
    Hello
    I would like to share a script that I have created and used since some years to have some consolidated performance information on Informix sessions.
    I already tried to share it via iiug toolbox a long time ago but I never have been able to understand how to do, I would like to propose it for sharing with the Informix community so is there someone here who has an idea how to do ?
    By the way, the recent versions of Informix 14.10 now include an 'onstat -g top' feature, but I have not yet tried it, so I don't know if my script is still useful or not with the latest IDS versions.The script will provide output similar to below :
    [informix@MYSRVBDD01 ~]$ ./ifxmon.sh -s 10 -n 20
    choix_tri=cpu
    20191001-110740
    tid rstcb sessid user tty pid hostname vp Last Run CPU Time nreads nwrites #scheds status
    247916 826b59b8 245645 uadm - 112669 MYSRVBDD01 22cpu 10/01 11:07:40 4226.02 764861 1218044 1333450 running
    255168 148149098 252869 appluser - 52658 MYSRVAPP01.cus.windom.loc 25cpu 10/01 11:07:40 2244.30 5542599 64 5610553 running
    228575 e0a62ab8 226920 webapp - -1 crmapplicati.cus.xx 12cpu 10/01 11:07:36 1020.35 69649323 16080 70258298 cond wait netnorm
    243318 19e2da3a8 241059 webapp - -1 crmapplicati.cus.xx 18cpu 10/01 11:07:40 601.72 32712294 7936 33823310 running
    259845 8d905848 257511 informix - -1 MYSRVAPP01.cus.windom.loc 12cpu 10/01 11:07:40 448.53 0 0 13086 running
    259843 15c617eb8 257509 ronlyusr - -1 MYSRVAPP01.cus.windom.loc 1cpu 10/01 11:07:40 448.52 0 0 13110 running
    260091 1361da5f8 257752 informix - -1 MYSRVAPP01.cus.windom.loc 14cpu 10/01 11:07:39 356.36 0 0 10911 running
    258919 b64e3c08 256639 webapp - -1 crmapplicati.cus.xx 19cpu 10/01 11:07:40 310.23 10879853 8368 10526724 running
    260702 a6db7928 258359 informix - -1 prjobs02.cus.windom.loc 15cpu 10/01 11:07:40 165.14 0 0 345713 running
    260720 58507568 258371 ronlyusr - -1 MYSRVAPP01.cus.windom.loc 24cpu 10/01 11:07:40 154.34 0 0 4672 running
    260721 15c61fbd8 258372 informix - -1 MYSRVAPP01.cus.windom.loc 21cpu 10/01 11:07:40 154.31 0 0 4633 running
    203224 1361d31c8 201778 uadm 3 15327 MYSRVBDD01 18cpu 10/01 11:07:39 135.10 1087 512 675124217 cond wait sm_read
    259183 12f4c6158 256898 cgi - 36214 MYSRVIFW01.cus.windom.loc 12cpu 10/01 11:07:39 55.70 28 128 19858 cond wait netnorm
    217284 112db4b78 215912 eoduser - 70371 MYSRVAPP01.cus.windom.loc 16cpu 10/01 11:07:39 53.83 20324 30907 400744 cond wait netnorm
    217283 97b57918 215911 eoduser - 70372 MYSRVAPP01.cus.windom.loc 23cpu 10/01 11:07:38 53.66 20545 28476 398084 cond wait netnorm
    217282 1361d28d8 215910 eoduser - 70370 MYSRVAPP01.cus.windom.loc 13cpu 10/01 11:07:38 52.63 20525 32965 396489 cond wait netnorm
    217280 a6d8c008 215908 eoduser - 70357 MYSRVAPP01.cus.windom.loc 15cpu 10/01 11:07:39 43.32 41368 59693 134586 cond wait netnorm
    203175 15c652038 201735 appluser - 102158 MYSRVAPP01.cus.windom.loc 9cpu 10/01 11:07:36 42.64 33 0 387206 cond wait netnorm
    261028 14815c968 258674 informix - -1 prjobs03.cus.windom.loc 17cpu 10/01 11:07:40 21.67 0 0 7527 running
    259690 1903010a8 257367 webapp - -1 crmapplicati.cus.xx 11cpu 10/01 11:07:40 9.16 2359665 0 2155215 running
    20191001-110750
    tid rstcb sessid user tty pid hostname vp Last Run CPU% nreads nwrites #scheds status
    260702 a6db7928 258359 informix - -1 prjobs02.cus.windom.loc 22cpu 10/01 11:07:50 100.46% 0 0 360310 running
    261058 82691db8 258704 informix - -1 prjobs02.cus.windom.loc 19cpu 10/01 11:07:50 100.45% 0 0 5083 running
    255168 148149098 252869 appluser - 52658 MYSRVAPP01.cus.windom.loc 20cpu 10/01 11:07:50 99.71% 39697 0 5650380 running
    259843 15c617eb8 257509 ronlyusr - -1 MYSRVAPP01.cus.windom.loc 25cpu 10/01 11:07:50 99.60% 0 0 13369 running
    260091 1361da5f8 257752 informix - -1 MYSRVAPP01.cus.windom.loc 21cpu 10/01 11:07:50 99.56% 0 0 11156 running
    260720 58507568 258371 ronlyusr - -1 MYSRVAPP01.cus.windom.loc 10cpu 10/01 11:07:50 99.48% 0 0 4969 cond wait netnorm
    259845 8d905848 257511 informix - -1 MYSRVAPP01.cus.windom.loc 17cpu 10/01 11:07:50 99.02% 0 0 13328 running
    260721 15c61fbd8 258372 informix - -1 MYSRVAPP01.cus.windom.loc 16cpu 10/01 11:07:50 98.87% 0 0 4929 running
    247916 826b59b8 245645 uadm - 112669 MYSRVBDD01 18cpu 10/01 11:07:50 94.90% 512 1856 1334778 running
    243318 19e2da3a8 241059 webapp - -1 crmapplicati.cus.xx 9cpu 10/01 11:07:50 94.67% 9727 0 33837017 running
    258919 b64e3c08 256639 webapp - -1 crmapplicati.cus.xx 24cpu 10/01 11:07:49 75.28% 7149 0 10534615 cond wait netnorm
    261073 14815c968 258714 informix - -1 prjobs03.cus.windom.loc 15cpu 10/01 11:07:50 55.27% 0 0 1885 running
    259690 1903010a8 257367 webapp - -1 crmapplicati.cus.xx 11cpu 10/01 11:07:50 50.26% 1393326 0 3420677 running
    260788 15c653218 258440 cgi - 36666 MYSRVIFW01.cus.windom.loc 16cpu 10/01 11:07:45 3.17% 3 64 7583 cond wait netnorm
    217280 a6d8c008 215908 eoduser - 70357 MYSRVAPP01.cus.windom.loc 15cpu 10/01 11:07:49 1.32% 34 64 134708 cond wait netnorm
    217283 97b57918 215911 eoduser - 70372 MYSRVAPP01.cus.windom.loc 9cpu 10/01 11:07:49 1.13% 19 64 398521 cond wait netnorm
    217284 112db4b78 215912 eoduser - 70371 MYSRVAPP01.cus.windom.loc 12cpu 10/01 11:07:49 1.08% 10 64 401116 cond wait netnorm
    217282 1361d28d8 215910 eoduser - 70370 MYSRVAPP01.cus.windom.loc 24cpu 10/01 11:07:49 0.83% 19 64 396794 cond wait netnorm
    203224 1361d31c8 201778 uadm 3 15327 MYSRVBDD01 24cpu 10/01 11:07:50 0.17% 0 0 675182050 cond wait sm_read
    261080 e0a7fb78 258721 appluser - 84797 MYSRVIBC01.cus.windom.loc 12cpu 10/01 11:07:50 0.17% -598535 0 41 cond wait netnorm
    20191001-110800
    tid rstcb sessid user tty pid hostname vp Last Run CPU% nreads nwrites #scheds status
    261073 14815c968 258714 informix - -1 prjobs03.cus.windom.loc 16cpu 10/01 11:08:00 100.42% 0 0 5478 running
    261058 82691db8 258704 informix - -1 prjobs02.cus.windom.loc 9cpu 10/01 11:08:00 100.42% 0 0 8695 running
    260702 a6db7928 258359 informix - -1 prjobs02.cus.windom.loc 18cpu 10/01 11:08:00 100.40% 0 0 375069 running
    243318 19e2da3a8 241059 webapp - -1 crmapplicati.cus.xx 15cpu 10/01 11:08:00 100.36% 10752 0 33847722 running
    259845 8d905848 257511 informix - -1 MYSRVAPP01.cus.windom.loc 13cpu 10/01 11:08:00 99.10% 0 0 13652 running
    260091 1361da5f8 257752 informix - -1 MYSRVAPP01.cus.windom.loc 19cpu 10/01 11:08:00 98.86% 0 0 11458 running
    260721 15c61fbd8 258372 informix - -1 MYSRVAPP01.cus.windom.loc 11cpu 10/01 11:08:00 98.70% 0 0 5227 running
    260720 58507568 258371 ronlyusr - -1 MYSRVAPP01.cus.windom.loc 14cpu 10/01 11:08:00 98.69% 0 0 5264 running
    259843 15c617eb8 257509 ronlyusr - -1 MYSRVAPP01.cus.windom.loc 23cpu 10/01 11:08:00 98.35% 0 0 13675 running
    247916 826b59b8 245645 uadm - 112669 MYSRVBDD01 21cpu 10/01 11:08:00 98.10% 1024 1860 1336185 running
    255168 148149098 252869 appluser - 52658 MYSRVAPP01.cus.windom.loc 22cpu 10/01 11:08:00 97.38% 40209 0 5690518 running
    259690 1903010a8 257367 webapp - -1 crmapplicati.cus.xx 10cpu 10/01 11:08:00 44.31% 1355438 0 4662045 IO Wait
    261094 e0a7fb78 258735 appluser - 84910 MYSRVIBC01.cus.windom.loc 12cpu 10/01 11:08:00 5.27% 161792 256 144879 IO Wait
    260788 15c653218 258440 cgi - 36666 MYSRVIFW01.cus.windom.loc 11cpu 10/01 11:07:56 3.12% 3 0 8120 cond wait netnorm
    217280 a6d8c008 215908 eoduser - 70357 MYSRVAPP01.cus.windom.loc 17cpu 10/01 11:07:59 0.70% 21 0 134773 cond wait netnorm
    217284 112db4b78 215912 eoduser - 70371 MYSRVAPP01.cus.windom.loc 11cpu 10/01 11:07:59 0.52% 11 64 401312 cond wait netnorm
    217282 1361d28d8 215910 eoduser - 70370 MYSRVAPP01.cus.windom.loc 19cpu 10/01 11:07:59 0.29% 8 0 396896 cond wait netnorm
    217283 97b57918 215911 eoduser - 70372 MYSRVAPP01.cus.windom.loc 11cpu 10/01 11:07:56 0.26% 8 0 398625 cond wait netnorm
    203224 1361d31c8 201778 uadm 3 15327 MYSRVBDD01 17cpu 10/01 11:07:55 0.07% 0 0 675209632 cond wait sm_read
    203175 15c652038 201735 appluser - 102158 MYSRVAPP01.cus.windom.loc 22cpu 10/01 11:07:57 0.07% 0 0 387353 cond wait netnorm
    The script is not perfect, it will only process information for sqlexec threads and I think it doesn't work well with PDQ operations but it was very useful for me and I still use it to quickly identify users sessions cpu or I/O usage without needing to execute manually all these onstat commands and perform all cross-checking and computation.
    It uses awk to process output from various onstat commands (onstat -g cpu, onstat -g ath, onstat -u and onstat -g ses) to consolidate informations from these commands and to show the top sessions either by CPU or by I/O activity. When started it shows cumulated CPU and I/O reads & write since the sessions started, then it shows the delta between each interval.
    The output might not be perfectly formatted especially when hostnames are too long, I don't really care as long as I can understand the output.
    The script can run on Linux and AIX as it doesn't use other specific tools except onstat and standard ksh or bash with awk, I have used it on Informix 11.x and 12.x, I don't know how it performs on other versions.
    You can use the script at your own risk, I take no responsibility about it but I just hope that other admins will find it useful, you can take it, adapt it to your own needs and share it back with others, however please keep original credits inside the script.
    Short explanation :
    Execute the script on the database server by user with sufficient permissions to execute the onstat command.
    By default the tool will display the top 10 sessions ordered by CPU consumption, first it will show sessions stats since their beginning,
    then it will display real-time statistics by computing delta on the CPU and I/O consumption.

    Usage : ifxmon.sh [-d] [-n nbsessions] [-s delay]
    -d => sort sessions by disk I/Os instead of by CPU.

    By default nbsessions=10 and delay=30s.


    Fabrice

    ------------------------------
    Fabrice PLATEL
    ------------------------------

    Attachment(s)

    sh
    ifxmon.sh   6K 1 version


  • 2.  RE: a script to monitor Informix sessions

    Posted 25 days ago
    Fabrice:

    Pretty cool script! If you can package it with a usage or man-like description page that includes your contact info (in case someone wants to send you a fix or suggested improvement) and send it to me privately I will post it to the IIUg Software Repository pages for you.

    Art

    Art S. Kagel, President and Principal Consultant
    ASK Database Management


    Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference.  Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.








  • 3.  RE: a script to monitor Informix sessions

    Posted 24 days ago

    Art was faster than me, I was going to propose you the same.

     

    We have move the 'old' software repository to sourceforge a couple of years ago.

    You will find bunches of very interesting stuff there

     

    https://sourceforge.net/projects/iiug-software-repository/

     

    Have a great day

     

    Eric

     

     

     

    Eric Vercelletto
    Data Management Architect and Owner / Begooden IT Consulting
    Board of Directors, International Informix Users group
    IBM Champion 2013,2014,2015,2016,2017,2018,2019,2020
    ibm-champion-rgb-130px

    Tel:     +33(0) 298 51 3210
    Mob : +33(0)626 52 50 68
    skype: begooden-it
    Google Hangout: eric.vercelletto@begooden-it.com
    Email:
    eric.vercelletto@begooden-it.com
    www :
    http://www.vercelletto.com
    www  https://kandooerp.org

     

     






  • 4.  RE: a script to monitor Informix sessions

    Posted 24 days ago

    Hello Eric
    I just have answered Art privately as he proposed to upload the script to the IIUG repository.
    Regards
    Fabrice



    ------------------------------
    Fabrice PLATEL
    ------------------------------