After the 14.10.xC4 embargo, finally, Informix Dynamic Server 14.10.xC4W1 has been released.
This new version includes tons of bug fixes as usual. You can check the list of the customer reported bugs fixed at: https://www.ibm.com/support/pages/fix-list-informix-server-1410xc4w1-releases
But this blog is not about bug fixes. Let me talk about a new great feature introduced in xC4W1: The TOP command
As you already know, Informix Dynamic Server is a great complex software with lots of technology and multi-threading capabilities. Also, it works in multiple and different architectures like Windows, Unix or Linux and implements parallel procesing on its own.
All this beauty make also DBA’s live hard when trying to understand how all this internal sessions, threads, system processes, scheduler tasks, memory management, etc. is working. So you can imagine Informix Dynamic Server as a small Operating System on its own. Having internal information is possible using onstat commands or exploring sysmaster database, but this is a hard and difficult work.
We realize how hard is for a DBA to understand what’s going on when you have thousands of sessions or threads using your system and then somebody is eating your system resources. Senior trained DBAs can try to analize what’s going on using onstat commands, but all DBAs know how hard this can be.
Talking with Informix Engineers about this problem, we realized that there are utilities that help to solve this type of problem at the operating system level. Why not to emulate top or htop utils but using informix processes?
So they listened to their users and has released this new onstat -g top utility to understand easily what’s happening in your server.
You can get basic information about onstat -g top at: https://www.ibm.com/support/knowledgecenter/SSGU8G_14.1.0/com.ibm.adref.doc/ids_adr_onstatgtop.htm
But I’ve already installed Informix 14.10.xC4 and trust me when I say, just install or upgrade a test system and try this option. You will not regret it!
Just to finish, let me show you all the info you can get from this command in their basic form:
$ onstat -g top
IBM Informix Dynamic Server Version 14.10.FC4W1AEE -- On-Line (Prim) -- Up 02:05:36 -- 97567816 Kbytes
Top Resource Usage (Max lines 10, Time interval 5 seconds):
Top Threads (CPU usage)
tid name sid CPU_time #scheds status
9 sm_poll 0 4.3115 171264 yield forever
8 sm_poll 0 4.2943 319889 yield forever
2923 ontape 2715 2.2244 14918256 ready
2184 sqlexec 2053 1.1304 4359 cond wait netnorm
331 sqlexec 298 0.9766 7579 cond wait netnorm
2185 sqlexec 2054 0.6138 2089 running
431 sqlexec 396 0.3553 1027 cond wait netnorm
44 kaio 0 0.3227 3935 IO Idle
95 kaio 0 0.2963 4639 IO Idle
2925 arcbackup1 2715 0.2209 7361 yield time
Top pools (memory growth)
name increase(b) total_size(b)
2054 1628200 2103048
2054_SORT_0 474408 474408
sscpool26 200984 866696
4478 177680 190184
391 80872 557304
4480 12504 12504
4481 12504 12504
sscpool27 10960 122576
sscpool15 4144 83128
4478*O0 3352 3352
Top partitions (disk reads)
partnum disk_rds buff_rds buff_wrts rhitratio name
0x3400007 22 189 63 89 informix.f_greserva3
0x2000855 11 1705 16 100 informix.gvenpedl_dtll
0x2b0000b 9 31243 374 100 informix.gvenpedl
0x3400002 8 2195 0 100 informix.i_galmstkc
0x20003de 7 45 13 85 informix.gartclie
0x3400014 6 253 0 98 informix.p_galmstkg
0x2f00006 4 126 66 97 informix.greserva
0x3400004 4 252 0 99 informix.p_galmstkc
0x2700359 3 231 0 99 informix.p_gartclie
0x2000858 2 28995 0 100 informix.gventarl
Top DBspaces (page I/Os (reads + writes))
dbsnum page_ios page_Rd page_Wr name
15 154473 154473 0 s_icon_1
2 1030 0 1030 l_phys
32 184 184 0 d_cusiges_1
52 176 176 0 i_cusalm
3 132 0 132 l_log1
43 88 88 0 d_cusven
39 64 64 0 i_cusiges_1
47 32 32 0 d_cusalm
48 16 16 0 i_cusven
Top threads (physical log usage)
tid rstcb plog_pages name
331 0xb30b1148 904 sqlexec
389 0xb30d0b28 8 sqlexec
Top threads (logical log usage)
tid rstcb llog_bytes name
331 0xb30b1148 165996 sqlexec
2547 0xbbd5b6a8 368 srvinfx
389 0xb30d0b28 340 sqlexec
223 0x53a18408 192 CDRACK_1
Awesome! So, I’ll just let you try it!
Vicente Salvador
Do you know, if licence jar file for Innovator Edition is already available. Because i only found for xC3 on IBM site.
Do you know, if licence jar file for Innovator Edition is already available?
Because i only found for xC3 on IBM site.
Great article Vicente, but you only scratched the surface. One can specify any one of the reports available under the TOP option and then have it run every N seconds and just like UNIX’s top command, you can specify the number of lines to report each time. Here is an extract from thde onstat help:
top [ [ [ [ ]]]]
Print top consumers of various resources over specified interval
Valid combinations:
thread cpu (CPU usage)
thread drd (disk reads)
thread bfr (buffer reads)
thread bfw (buffer writes)
thread plg (physical log usage)
thread llg (logical log usage)
session cpu (CPU usage)
session drd (disk reads)
session bfr (buffer reads)
session bfw (buffer writes)
session plg (physical log usage)
session llg (logical log usage)
chunk ios (page reads/writes)
chunk art (average read times)
chunk awt (average write times)
space ios (page reads/writes)
space art (average read times)
space awt (average write times)
mempool gro (memory growth)
sessmem gro (memory growth)
partition drd (disk reads)
table drd (disk reads)