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
------------------------------
#Informix