本文是SinoDB数据库中常用的 onstat 监控命令的说明。onstat 实用程序读取共享内存结构，并在命令执行时提供有关数据库服务器的统计信息，显示 onstat 输出时，共享内存的内容有可能改变了。onstat实用程序不会在共享内存上放置任何锁，因此执行该实用程序不会影响性能。
1. 常用onstat监控命令
2. 具体说明
onstat -
打印数据库当前运行模式
[informix@vm84145 ~]$ onstat -
Sinoregal SinoDB Dynamic Server Version 12.10.FC8 -- On-Line -- Up 01:42:32 -- 2374324 Kbytes
onstat -c
打印onconfig文件内容。在输出中包含了配置文件的路径，可以通过该命令快速找到配置文件。
[informix@vm84145 ~]$ onstat -c
Sinoregal SinoDB Dynamic Server Version 12.10.FC8 -- On-Line -- Up 01:48:29 -- 2374324 Kbytes
Configuration File: /home/informix/sinodb/etc/onconfig.sinodb
##################################################################
# Licensed Material - Property of Sinoregal Corporation
#
# "Restricted Materials of Sinoregal Corporation"
#
# Sinoregal SinoDB Dynamic Server
# Copyright Sinoregal Corporation 0000, 2017. All rights reserved.
#
# Title: onconfig.std
# Description: Sinoregal SinoDB Dynamic Server Configuration Parameters
#
# Important: $INFORMIXDIR now resolves to the environment
# variable INFORMIXDIR. Replace the value of the INFORMIXDIR
# environment variable only if the path you want is not under
# $INFORMIXDIR.
.....
onstat -d
打印数据库空间和Chunk文件使用信息
[informix@vm84145 ~]$ onstat -d
Sinoregal SinoDB Dynamic Server Version 12.10.FC8 -- On-Line -- Up 02:05:30 -- 2374324 Kbytes
Dbspaces
address number flags fchunk nchunks pgsize flags owner name
47481028 1 0x1 1 1 2048 N BA informix rootdbs
48a9e0d8 2 0x1 2 1 2048 N BA informix llogdbs
48a9e318 3 0x1 3 1 2048 N BA informix plogdbs
48a9e558 4 0x2001 4 1 8192 N TBA informix tempdbs1
48a9e798 5 0x2001 5 1 8192 N TBA informix tempdbs2
48a9e9d8 6 0x2001 6 1 8192 N TBA informix tempdbs3
48a9ec18 7 0x11 7 1 8192 N BBA informix bindbs1
48a9f028 8 0x8001 8 1 2048 N SBA informix sbdbs1
48a9f268 9 0x1 9 1 2048 N BA informix datadbs1
48a9f4a8 10 0x1 10 1 2048 N BA informix datadbs2
48a9f6e8 11 0x1 11 1 2048 N BA informix datadbs3
48a9f928 12 0x1 12 1 2048 N BA informix datadbs4
48a9fb68 13 0x1 13 1 2048 N BA informix datadbs5
13 active, 2047 maximum
Note: For BLOB chunks, the number of free pages shown is out of date.
Run 'onstat -d update' for current stats.
Chunks
address chunk/dbs offset size free bpages flags pathname
47481268 1 1 0 1024000 1009415 PO-B-D /home/informix/dbs/rootdbs
48aa1028 2 2 0 1024000 47 PO-B-D /home/informix/dbs/llogdbs
48aa2028 3 3 0 1024000 2 PO-B-D /home/informix/dbs/plogdbs
48aa3028 4 4 0 64000 63947 PO-B-- /home/informix/dbs/tempdbs1
48aa4028 5 5 0 64000 63947 PO-B-- /home/informix/dbs/tempdbs2
48aa5028 6 6 0 64000 63947 PO-B-- /home/informix/dbs/tempdbs3
48aa6028 7 7 0 512000 ~127872 128000 POBB-D /home/informix/dbs/bindbs1
48aa7028 8 8 0 512000 477454 477465 POSB-D /home/informix/dbs/sbdbs1
Metadata 34482 25659 34482
48aa8028 9 9 0 1024000 1019026 PO-B-D /home/informix/dbs/datadbs1-1
48aa9028 10 10 0 1024000 1023947 PO-B-D /home/informix/dbs/datadbs2-1
48aaa028 11 11 0 1024000 1023947 PO-B-D /home/informix/dbs/datadbs3-1
48aab028 12 12 0 1024000 1023947 PO-B-D /home/informix/dbs/datadbs4-1
48aac028 13 13 0 1024000 1023947 PO-B-D /home/informix/dbs/datadbs5-1
13 active, 32766 maximum
NOTE: The values in the "size" and "free" columns for DBspace chunks are
displayed in terms of "pgsize" of the DBspace to which they belong.
Expanded chunk capacity mode: always
[informix@vm84145 ~]$
标记值2(flags)
chunks字段信息
chunk 状态信息标志说明
onstat -D
查询已经创建的数据库空间，及每个数据库空间的文件编号，文件数量，文件路径，页大小，每个文件的IO情况等信息。
[informix@vm84145 ~]$ onstat -D
Sinoregal SinoDB Dynamic Server Version 12.10.FC8 -- On-Line -- Up 02:07:41 -- 2374324 Kbytes
Dbspaces
address number flags fchunk nchunks pgsize flags owner name
47481028 1 0x1 1 1 2048 N BA informix rootdbs
48a9e0d8 2 0x1 2 1 2048 N BA informix llogdbs
48a9e318 3 0x1 3 1 2048 N BA informix plogdbs
48a9e558 4 0x2001 4 1 8192 N TBA informix tempdbs1
48a9e798 5 0x2001 5 1 8192 N TBA informix tempdbs2
48a9e9d8 6 0x2001 6 1 8192 N TBA informix tempdbs3
48a9ec18 7 0x11 7 1 8192 N BBA informix bindbs1
48a9f028 8 0x8001 8 1 2048 N SBA informix sbdbs1
48a9f268 9 0x1 9 1 2048 N BA informix datadbs1
48a9f4a8 10 0x1 10 1 2048 N BA informix datadbs2
48a9f6e8 11 0x1 11 1 2048 N BA informix datadbs3
48a9f928 12 0x1 12 1 2048 N BA informix datadbs4
48a9fb68 13 0x1 13 1 2048 N BA informix datadbs5
13 active, 2047 maximum
Chunks
address chunk/dbs offset page Rd page Wr pathname
47481268 1 1 0 2037 836 /home/informix/dbs/rootdbs
48aa1028 2 2 0 515 246 /home/informix/dbs/llogdbs
48aa2028 3 3 0 11 831 /home/informix/dbs/plogdbs
48aa3028 4 4 0 12 84 /home/informix/dbs/tempdbs1
48aa4028 5 5 0 12 88 /home/informix/dbs/tempdbs2
48aa5028 6 6 0 12 88 /home/informix/dbs/tempdbs3
48aa6028 7 7 0 509 0 /home/informix/dbs/bindbs1
48aa7028 8 8 0 14 1 /home/informix/dbs/sbdbs1
48aa8028 9 9 0 103 0 /home/informix/dbs/datadbs1-1
48aa9028 10 10 0 3 1 /home/informix/dbs/datadbs2-1
48aaa028 11 11 0 3 1 /home/informix/dbs/datadbs3-1
48aab028 12 12 0 3 1 /home/informix/dbs/datadbs4-1
48aac028 13 13 0 3 1 /home/informix/dbs/datadbs5-1
13 active, 32766 maximum
NOTE: The values in the "page Rd" and "page Wr" columns for DBspace chunks
are displayed in terms of system base page size.
Expanded chunk capacity mode: always
onstat -F
显示将页清空到磁盘上的每种类型的写操作的计数。
[informix@vm84145 ~]$ onstat -F
Sinoregal SinoDB Dynamic Server Version 12.10.FC8 -- On-Line -- Up 12 days 23:15:43 -- 2757528 Kbytes
Fg Writes LRU Writes Chunk Writes
0 0 172307
address flusher state data # LRU Chunk Wakeups Idle Tim
475ef8f0 0 I 0 0 2057 1122508 1120526.498
475f01b8 1 I 0 0 361 1120812 1120532.745
475f0a80 2 I 0 0 44 1120495 1120532.967
475f1348 3 I 0 0 5 1120456 1120532.996
475f1c10 4 I 0 0 1 1120452 1120533.005
475f24d8 5 I 0 0 1 1120452 1120533.026
475f2da0 6 I 0 0 1 1120452 1120533.010
475f3668 7 I 0 0 0 1120451 1120532.909
475f3f30 8 I 0 0 0 1120451 1120533.029
475f47f8 9 I 0 0 0 1120451 1120533.032
475f50c0 10 I 0 0 0 1120451 1120533.042
475f5988 11 I 0 0 0 1120451 1120533.049
475f6250 12 I 0 0 0 1120451 1120533.044
475f6b18 13 I 0 0 0 1120451 1120533.059
475f73e0 14 I 0 0 0 1120451 1120533.034
475f7ca8 15 I 0 0 0 1120451 1120533.048
475f8570 16 I 0 0 0 1120451 1120533.039
475f8e38 17 I 0 0 0 1120451 1120533.032
475f9700 18 I 0 0 0 1120451 1120533.025
475f9fc8 19 I 0 0 0 1120451 1120533.060
475fa890 20 I 0 0 0 1120451 1120533.046
475fb158 21 I 0 0 0 1120451 1120533.052
475fba20 22 I 0 0 0 1120451 1120533.073
475fc2e8 23 I 0 0 0 1120451 1120533.028
475fcbb0 24 I 0 0 0 1120451 1120533.032
475fd478 25 I 0 0 0 1120451 1120533.046
475fdd40 26 I 0 0 0 1120451 1120533.039
475fe608 27 I 0 0 0 1120451 1120533.053
475feed0 28 I 0 0 0 1120451 1120533.049
475ff798 29 I 0 0 0 1120451 1120533.051
47600060 30 I 0 0 0 1120451 1120533.033
47600928 31 I 0 0 0 1120451 1120533.058
states: Exit Idle Chunk Lru
onstat -k
打印锁信息
[informix@vm84145 ~]$ onstat -k
Sinoregal SinoDB Dynamic Server Version 12.10.FC8 -- On-Line -- Up 02:12:45 -- 2374324 Kbytes
Locks
address wtlist owner lklist type tblsnum rowid key#/bsiz
4424d0b0 0 475b09c0 0 S 100002 204 0
4424d138 0 475b1b50 0 HDR+S 100002 204 0
4424d2d0 0 475b1288 448c9550 HDR+S 100002 201 0
4424d710 0 475b2ce0 0 S 100002 204 0
448c9550 0 475b1288 0 S 100002 204 0
5 active, 100000 total, 32768 hash buckets, 0 lock table overflows
[informix@vm84145 ~]$
锁类型
onstat -p
打印系统运行状态统计信息。
[informix@vm84145 ~]$ onstat -p
Sinoregal SinoDB Dynamic Server Version 12.10.FC8 -- On-Line -- Up 02:14:11 -- 2374324 Kbytes
Profile
dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached
2167 2728 38492 94.37 1062 2178 4184 74.62
isamtot open start read write rewrite delete commit rollbk
33839 1640 1907 10654 1439 271 32 116 0
gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs
2 0 0 0 0 0 2
ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes
0 0 0 2.71 1.81 11 24
bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans
47 0 17290 0 0 2 27 125
ixda-RA idx-RA da-RA logrec-RA RA-pgsused lchwaits
601 144 71 2 206 43
onstat -l
打印物理日志缓存区，逻辑日志缓冲区，逻辑日志的使用情况。
[informix@vm84145 ~]$ onstat -l
Sinoregal SinoDB Dynamic Server Version 12.10.FC8 -- On-Line -- Up 02:03:10 -- 2374324 Kbytes
Physical Logging
Buffer bufused bufsize numpages numwrits pages/io
P-1 0 1024 830 17 48.82
phybegin physize phypos phyused %used
3:53 1023945 84936 0 0.00
Logical Logging
Buffer bufused bufsize numrecs numpages numwrits recs/pages pages/io
L-3 0 512 2542 245 137 10.4 1.8
Subsystem numrecs Log Space used
OLDRSAM 2531 326868
HA 11 484
address number flags uniqid begin size used %used
475dffa8 7 F------ 0 2:53 102390 0 0.00
475fbf88 8 A------ 0 2:102443 102390 0 0.00
4886fd90 9 A------ 0 2:204833 102390 0 0.00
4886fdf8 10 A------ 0 2:307223 102390 0 0.00
4886fe60 11 U-B---- 11 2:409613 102390 51 0.05
4886fec8 12 U-B---- 12 2:512003 102390 10 0.01
4886ff30 13 U------ 13 2:614393 102390 40589 39.64
4886ff98 14 U------ 14 2:716783 102390 8 0.01
473abf68 15 U---C-L 15 2:819173 102390 153 0.15
47481f40 16 A------ 0 2:921563 102390 0 0.00
10 active, 10 total
[informix@vm84145 ~]$
物理日志标题
逻辑日志标题
flags
onstat -m
查看online日志的最新信息。在输出信息中，也包含了online日志的路径信息，可以根据该日志路径，查看online日志的完整内容。
[informix@vm84145 ~]$ onstat -m
Sinoregal SinoDB Dynamic Server Version 12.10.FC8 -- On-Line -- Up 01:53:10 -- 2374324 Kbytes
Message Log File: /home/informix/sinodb/tmp/online.log
19:07:48 sid 59 - informix@vm84145 - pid 20442 terminated by onmode -z.
.
19:10:01 requested number of KAIO events (131072) exceeds limit (16384). using 16384.
19:11:09 sid 61 - informix@vm84145 - pid 20485 terminated by onmode -z.
.
19:11:58 Checkpoint Completed: duration was 0 seconds.
19:11:58 Tue Mar 1 - loguniq 15, logpos 0x95018, timestamp: 0x22ce08 Interval: 1373
19:11:58 Maximum server connections 1
19:11:58 Checkpoint Statistics - Avg. Txn Block Time 0.000, # Txns blocked 0, Plog used 332, Llog used 72
19:11:58 Increasing # of AIO VPs to 4
19:16:59 Checkpoint Completed: duration was 0 seconds.
19:16:59 Tue Mar 1 - loguniq 15, logpos 0x97018, timestamp: 0x22ce15 Interval: 1374
19:16:59 Maximum server connections 1
19:16:59 Checkpoint Statistics - Avg. Txn Block Time 0.000, # Txns blocked 0, Plog used 19, Llog used 2
[informix@vm84145 ~]$
onstat -r
可以使用-r参数，每隔指定秒数，执行一次命令。
[informix@vm84145 ~]$ onstat -r 2
Sinoregal SinoDB Dynamic Server Version 12.10.FC8 -- On-Line -- Up 02:19:44 -- 2374324 Kbytes
Sinoregal SinoDB Dynamic Server Version 12.10.FC8 -- On-Line -- Up 02:19:46 -- 2374324 Kbytes
Sinoregal SinoDB Dynamic Server Version 12.10.FC8 -- On-Line -- Up 02:19:48 -- 2374324 Kbytes
......
onstat -x
打印事务
[informix@vm84145 ~]$ onstat -x
Sinoregal SinoDB Dynamic Server Version 12.10.FC8 -- On-Line -- Up 1 days 01:54:53 -- 2374324 Kbytes
Transactions
est.
address flags userthread locks begin_logpos current logpos isol rb_time retrys coord
475e0028 A---- 47599028 0 - - COMMIT - 0
475e0398 A---- 475998f0 0 - - COMMIT - 0
475e0708 A---- 4759a1b8 0 - - COMMIT - 0
475e0a78 A---- 4759aa80 0 - - COMMIT - 0
475e0de8 A---- 4759b348 0 - - COMMIT - 0
475e1158 A---- 4759bc10 0 - - COMMIT - 0
475e14c8 A---- 4759c4d8 0 - - COMMIT - 0
475e1838 A---- 4759cda0 0 - - COMMIT - 0
475e1ba8 A---- 4759d668 0 - - COMMIT - 0
475e1f18 A---- 4759df30 0 - - COMMIT - 0
475e2288 A---- 4759e7f8 0 - - COMMIT - 0
475e25f8 A---- 4759f0c0 0 - - COMMIT - 0
475e2968 A---- 4759f988 0 - - COMMIT - 0
475e2cd8 A---- 475a0250 0 - - COMMIT - 0
475e3048 A---- 475a0b18 0 - - COMMIT - 0
475e33b8 A---- 475a13e0 0 - - COMMIT - 0
475e3728 A---- 475a1ca8 0 - - COMMIT - 0
475e3a98 A---- 475a2570 0 - - COMMIT - 0
475e3e08 A---- 475a2e38 0 - - COMMIT - 0
475e4178 A---- 475a3700 0 - - COMMIT - 0
475e44e8 A---- 475a3fc8 0 - - COMMIT - 0
475e4858 A---- 475a4890 0 - - COMMIT - 0
475e4bc8 A---- 475a5158 0 - - COMMIT - 0
475e4f38 A---- 475a5a20 0 - - COMMIT - 0
475e52a8 A---- 475a62e8 0 - - COMMIT - 0
475e5618 A---- 475a6bb0 0 - - COMMIT - 0
475e5988 A---- 475a7478 0 - - COMMIT - 0
475e5cf8 A---- 475a7d40 0 - - COMMIT - 0
475e6068 A---- 475a8608 0 - - COMMIT - 0
475e63d8 A---- 475a8ed0 0 - - COMMIT - 0
475e6748 A---- 475a9798 0 - - COMMIT - 0
475e6ab8 A---- 475aa060 0 - - COMMIT - 0
475e6e28 A---- 475aa928 0 - - COMMIT - 0
475e7198 A---- 475ab1f0 0 - - COMMIT - 0
475e7508 A---- 475abab8 0 - - COMMIT - 0
475e7878 A---- 475ac380 0 - - DIRTY - 0
475e7be8 A---- 475acc48 0 - - COMMIT - 0
475e7f58 A---- 475b2ce0 0 - - NOTRANS - 0
475e82c8 A---- 475b09c0 0 - - NOTRANS - 0
475e8638 A---- 475addd8 0 - - COMMIT - 0
475e89a8 A---- 475aef68 0 - - COMMIT - 0
475e8d18 A---- 475af830 0 - - COMMIT - 0
475e9088 A---- 475b00f8 0 - - COMMIT - 0
475e93f8 A---- 475b1288 0 - - NOTRANS - 0
475e9768 A---- 475b2ce0 1 - - DIRTY - 0
475e9ad8 A---- 475b1288 2 - - DIRTY - 0
475e9e48 A---- 475b09c0 1 - - DIRTY - 0
475ea1b8 A---- 475b1b50 1 - - COMMIT - 0
475ea528 A---- 475b2418 0 - - COMMIT - 0
49 active, 128 total, 51 maximum concurrent
flags标识说明
onstat -u
打印用户线程
address flags sessid user tty wait tout locks nreads nwrites
a120018 ---P--D 1 informix - 0 0 0 50 33
a1204bc ---P--F 0 informix - 0 0 0 0 278
a120e04 ---P--B 7 informix - 0 0 0 1 0
a1212a8 ---P--D 9 informix - 0 0 0 0 0
a12174c L--PR-- 26 joeg ttyp2 a01f8d4 -1 1 0 0
a121bf0 Y-BP--- 23 lizg ttyp1 2fb050 0 6 0 0
onstat -u 命令很重要，因为它可以帮助确定会话状态或它的一个线程的状态。例如，可以查看一个会话是否在等待锁、回滚事务或等待检查点完成。
如果有地址被列在 wait 列上，那么该线程在等待特定的条目。条目类型可以由 flags 列的第一个位置中的标识确定。对于很多线程而言，有地址在 wait 列上是很常见的；例如，它在等待一个条件。
想要查看更多关于会话的信息，指定一个会话 ID 并运行onstat -g ses session-id 命令。
flags 列列出了线程的状态。在 flags 列中标识的位置对于识别标识所代表的意义很重要。下面展示了标识位置以及在每个位置中可以使用的标识的列表。
onstat -g ath
显示所有线程的信息
[informix@vm84145 ~]$ onstat -g ath
Sinoregal SinoDB Dynamic Server Version 12.10.FC8 -- On-Line -- Up 13 days 02:39:02 -- 2757528 Kbytes
Threads:
tid tcb rstcb prty status vp-class name
2 4839b6e8 0 1 IO Idle 3lio* lio vp 0
3 483bc368 0 1 IO Idle 4pio* pio vp 0
4 483dd368 0 1 IO Idle 5aio* aio vp 0
5 483fe368 1d39d00 1 IO Idle 6msc* msc vp 0
6 4842f368 0 1 IO Idle 7fifo* fifo vp 0
7 484782f0 475ef028 3 sleeping secs: 1 1cpu main_loop()
8 484d6220 0 1 running 9soc* soctcppoll
9 484d6aa0 0 1 running 10soc* soctcppoll
10 48513370 0 2 sleeping forever 1cpu* soctcplst
11 48513c80 475ef8f0 1 sleeping secs: 1 8cpu flush_sub(0)
12 485b8028 475f01b8 1 sleeping secs: 1 1cpu flush_sub(1)
13 485b8368 475f0a80 1 sleeping secs: 1 1cpu flush_sub(2)
onstat -g buf
打印缓冲池的概要文件信息
[informix@vm84145 ~]$ onstat -g buf
Sinoregal SinoDB Dynamic Server Version 12.10.FC8 -- On-Line -- Up 13 days 02:47:09 -- 2757528 Kbytes
Profile
Buffer pool page size: 2048
dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached
147735 744878 29157135 99.49 373400 586410 1775617 78.97
bufwrits_sinceckpt bufwaits ovbuff flushes
0 968 0 1738
Fg Writes LRU Writes Avg. LRU Time Chunk Writes Total Mem
0 0 -nan 169655 431Mb
Bufferpool Segments
id segment size # buffs
0 0xc2653000 431Mb 200000
----------------------------------
Buffer pool page size: 8192
dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached
229 5536 1270884 99.98 3981 20544 910598 99.56
bufwrits_sinceckpt bufwaits ovbuff flushes
0 1 0 341
Fg Writes LRU Writes Avg. LRU Time Chunk Writes Total Mem
0 0 -nan 3816 80Mb
Bufferpool Segments
id segment size # buffs
0 0xdd5b8000 80Mb 10000
----------------------------------
Buffer pool page size: 16384
dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached
10 104 86 88.37 1 8 2 50.00
bufwrits_sinceckpt bufwaits ovbuff flushes
0 0 0 1
Fg Writes LRU Writes Avg. LRU Time Chunk Writes Total Mem
0 0 -nan 1 158Mb
Bufferpool Segments
id segment size # buffs
0 0xe263f000 158Mb 10000
----------------------------------
Fast Cache Stats
gets hits %hits puts
6035204 5901352 97.78 9189856
onstat -g ses
打印Session连接信息。信息包括进程 ID、登录名和主机名。
[informix@vm84145 ~]$ onstat -g ses
Sinoregal SinoDB Dynamic Server Version 12.10.FC8 -- On-Line -- Up 9 days 04:05:38 -- 2757528 Kbytes
session #RSAM total used dynamic
id user tty pid hostname threads memory memory explain
216 informix - 0 - 0 16384 12560 off
215 sinocx 2 31305 vm84145 1 131072 91920 off
213 informix 0 30048 vm84145 1 86016 73976 off
210 informix - 358728 192.168. 1 94208 73744 off
209 informix - 358728 bogon 1 94208 72248 off
208 informix - 358728 bogon 1 139264 98576 off
207 informix - 358728 bogon 1 110592 83008 off
206 informix - 358728 bogon 1 94208 72248 off
29 informix - 0 - 1 708608 544728 off
28 informix - 0 - 1 716800 535728 off
27 informix - 0 - 1 643072 486272 off
26 informix - 0 - 1 102400 86960 off
4 informix - 0 - 0 16384 14176 off
3 informix - 0 - 0 16384 12560 off
2 informix - 0 - 0 16384 12560 off
informix 拥有的且进程且ID 为 0 的会话不与用户对应。它们供数据库服务器内部使用。
onstat -g ses 215
指定SessionID，可以显示更详细的会话信息。
[informix@vm84145 ~]$ onstat -g ses 215
Sinoregal SinoDB Dynamic Server Version 12.10.FC8 -- On-Line -- Up 9 days 04:32:13 -- 2757528 Kbytes
session effective #RSAM total used dynamic
id user user tty pid hostname threads memory memory explain
215 sinocx - 2 31305 vm84145 1 131072 91920 off
Program :
/home/informix/sinodb/bin/dbaccess
tid name rstcb flags curstk status
2030 sqlexec 47613c80 Y--P--- 6448 cond wait netnorm -
Memory pools count 2
name class addr totalsize freesize #allocfrag #freefrag
215 V 4b78c040 126976 38384 94 21
215*O0 V 4a109040 4096 768 1 1
name free used name free used
overhead 0 6656 scb 0 144
opentable 0 5952 filetable 0 1064
log 0 16536 temprec 0 21664
keys 0 640 gentcb 0 1624
ostcb 0 2992 sqscb 0 23016
hashfiletab 0 552 osenv 0 2776
sqtcb 0 7824 fragman 0 336
sapi 0 144
sqscb info
scb sqscb optofc pdqpriority optcompind directives
4808c1c0 4a228028 0 0 2 1
Sess SQL Current Iso Lock SQL ISAM F.E.
Id Stmt type Database Lvl Mode ERR ERR Vers Explain
215 - testdb CR Not Wait -272 0 9.24 Off
Last parsed SQL statement :
select * from t_employee
如上述示例所示，如果包含一个会话 ID，onstat -g ses 命令可以列出关于该会话的更多特定信息。
一般情况下，对于每个会话只能看到一个线程即sqlexec 线程。但是，排序或索引构建期间，可能会展示多个线程。
onstat -g sql
[informix@vm84145 ~]$ onstat -g sql
Sinoregal SinoDB Dynamic Server Version 12.10.FC8 -- On-Line -- Up 9 days 04:45:37 -- 2757528 Kbytes
Sess SQL Current Iso Lock SQL ISAM F.E.
Id Stmt type Database Lvl Mode ERR ERR Vers Explain
215 - testdb CR Not Wait -272 0 9.24 Off
213 - testdb CR Not Wait 0 0 9.24 Off
210 - testdb CR Not Wait 0 0 9.28 Off
209 - testdb CR Not Wait 0 0 9.28 Off
208 - testdb CR Not Wait -201 0 9.28 Off
207 - testdb CR Not Wait 0 0 9.28 Off
206 - testdb CR Not Wait 0 0 9.28 Off
29 sysadmin DR Wait 5 0 0 - Off
28 sysadmin DR Wait 5 0 0 - Off
27 sysadmin DR Wait 5 0 0 - Off
26 sysadmin CR Not Wait 0 0 - Off
onstat -g sql 命令提供每个会话执行的最后一个SQL 语句的概述信息。onstat -g sql 命令运行结果中包含的字段如下：
| 列名 | 说明 |
|---|---|
| Sess Id | 用户执行 SQL 语句的会话 ID。您可以通过执行 onstat -g ses 命令查找相应的会话 ID 来查找用户名。 |
| SQL Stmt type | 语句类型，例如 SELECT、UPDATE、DELETE 或 INSERT |
| Current Database | 会话的当前数据库的名称 |
| Iso Lvl | 当前隔离级别 (CR = committed read,RR = repeatable read, CS = cursor stability,DR = dirty read, NL =no logging) |
| Lock mode | 当前锁模式 (Not Wait 或 Wait x , 其中x 为等待的秒数) |
| SQL ERR | 最后一个 SQL 错误 |
| ISAM ERR | 最后一个 ISAM 错误 |
| F.E.Version | 客户端应用程序的星瑞格数据库版本 |
onstat -g seg
打印共享内存段的统计信息，显示当前有哪些内存段，及每个内存段的使用情况。
[informix@vm84145 ~]$ onstat -g seg
Sinoregal SinoDB Dynamic Server Version 12.10.FC8 -- On-Line -- Up 1 days 07:07:37 -- 2374324 Kbytes
Segment Summary:
id key addr size ovhd class blkused blkfree
3637252 52564801 44000000 23408640 712648 R 5715 0
3670021 52564802 45653000 2097152000 24577800 V 16137 495863
3702790 52564803 c2653000 226308096 1 B 55251 0
3735559 52564804 cfe26000 84439040 1 B 20615 0
Total: - - 2431307776 - - 97718 495863
(* segment locked in memory)
No reserve memory is allocated
** onstat -g con
显示条件和正在等待这些条件的线程的信息。
[informix@vm84145 ~]$ onstat -g con
Sinoregal SinoDB Dynamic Server Version 12.10.FC8 -- On-Line -- Up 13 days 03:03:41 -- 2757528 Kbytes
Conditions with waiters:
cid addr name waiter waittime
255 48b25ae0 ReadAhead 48 86
421 4413fd40 bp_cond 89 109
1923 4a024608 netnorm 2801 1679
1948 49b53c88 netnorm 2802 1643
1968 4aa00f80 netnorm 2804 1543
onstat -g cpu
打印运行时间统计信息
[informix@vm84145 ~]$ onstat -g cpu
Sinoregal SinoDB Dynamic Server Version 12.10.FC8 -- On-Line -- Up 1 days 05:45:57 -- 2374324 Kbytes
Thread CPU Info:
tid name vp Last Run CPU Time #scheds status
2 lio vp 0 3lio* 03/01 17:31:18 0.0000 1 IO Idle
3 pio vp 0 4pio* 03/01 17:31:20 0.1003 2 IO Idle
4 aio vp 0 5aio* 03/02 23:12:07 0.1714 193 IO Idle
5 msc vp 0 6msc* 03/01 19:01:45 0.0115 8 IO Idle
6 fifo vp 0 7fifo* 03/01 17:31:23 0.1005 2 IO Idle
7 main_loop() 8cpu 03/02 23:17:14 0.6485 107851 sleeping secs: 1
8 soctcppoll 9soc* 03/02 23:17:14 107149.3314 428965 running
9 soctcppoll 10soc* 03/02 23:17:14 103582.4168 414765 running
11 flush_sub(0) 8cpu 03/02 23:17:14 0.1898 108003 sleeping secs: 1
12 flush_sub(1) 1cpu 03/02 23:17:14 0.1614 107212 sleeping secs: 1
.....
onstat -g rea
显示准备就绪的虚拟处理器线程信息。
[informix@vm84145 ~]$ onstat -g rea
Sinoregal SinoDB Dynamic Server Version 12.10.FC8 -- On-Line -- Up 13 days 03:18:25 -- 2757528 Kbytes
tid tcb rstcb prty status vp-class name
6 536a38 406464 4 ready 3cpu main_loop()
28 60cfe8 40a124 4 ready 1cpu onmode_mon
33 672a20 409dc4 2 ready 3cpu sqlexec
onstat -g glo
打印全局多线程信息。
[informix@vm84145 ~]$ onstat -g glo
Sinoregal SinoDB Dynamic Server Version 12.10.FC8 -- On-Line -- Up 1 days 05:57:14 -- 2374324 Kbytes
MT global info:
sessions threads vps lngspins time
0 61 15 0 107833
sched calls thread switches yield 0 yield n yield forever
total: 5458482 4561161 897200 4102486 35138
per sec: 97 97 0 88 0
Virtual processor summary:
class vps usercpu syscpu total
cpu 4 21.11 6.01 27.12
aio 4 1.06 3.15 4.21
lio 1 0.30 0.78 1.08
pio 1 0.27 0.78 1.05
adm 1 2.76 4.52 7.28
soc 2 10.44 9.80 20.24
msc 1 0.00 0.00 0.00
fifo 1 0.27 0.77 1.04
total 15 36.21 25.81 62.02
Individual virtual processors:
vp pid class usercpu syscpu total Thread Eff
1 19098 cpu 6.46 1.72 8.18 9.25 88%
2 19099 adm 2.76 4.52 7.28 0.00 0%
3 19100 lio 0.30 0.78 1.08 1.08 100%
4 19101 pio 0.27 0.78 1.05 1.05 100%
5 19102 aio 0.30 0.86 1.16 1.16 100%
6 19103 msc 0.00 0.00 0.00 0.00 0%
7 19104 fifo 0.27 0.77 1.04 1.04 100%
8 19106 cpu 5.74 1.47 7.21 7.21 100%
9 19107 soc 5.41 5.07 10.48 NA NA
10 19108 soc 5.03 4.73 9.76 NA NA
11 19109 aio 0.24 0.76 1.00 1.00 100%
12 19110 aio 0.27 0.82 1.09 1.09 100%
13 19710 cpu 4.66 1.39 6.05 6.05 100%
14 19711 cpu 4.25 1.43 5.68 5.68 100%
18 20496 aio 0.25 0.71 0.96 0.96 100%
tot 36.21 25.81 62.02
虚拟处理器摘要
Virtual Processor Summary
Individual virtual processors
onstat -g ckp
打印 checkpoint 历史记录和配置建议
[informix@vm84145 ~]$ onstat -g ckp
Sinoregal SinoDB Dynamic Server Version 12.10.FC8 -- On-Line -- Up 1 days 05:21:56 -- 2374324 Kbytes
AUTO_CKPTS=On RTO_SERVER_RESTART=Off
Critical Sections Physical Log Logical Log
Clock Total Flush Block # Ckpt Wait Long # Dirty Dskflu Total Avg Total Avg
Interval Time Trigger LSN Time Time Time Waits Time Time Time Buffers /Sec Pages /Sec Pages /Sec
1420 17:12:05 CKPTINTVL 15:0x18c6018 0.0 0.0 0.0 0 0.0 0.0 0.0 336 336 329 0 66 0
1421 18:12:05 CKPTINTVL 15:0x1902018 0.0 0.0 0.0 0 0.0 0.0 0.0 326 326 321 0 60 0
1422 19:12:05 CKPTINTVL 15:0x1952018 0.0 0.0 0.0 0 0.0 0.0 0.0 351 351 343 0 80 0
1423 20:07:06 CKPTINTVL 15:0x195a018 0.0 0.0 0.0 0 0.0 0.0 0.0 9 9 26 0 8 0
1424 20:12:06 CKPTINTVL 15:0x1990018 0.0 0.0 0.0 0 0.0 0.0 0.0 315 315 314 1 54 0
1425 21:07:06 CKPTINTVL 15:0x1998018 0.0 0.0 0.0 0 0.0 0.0 0.0 9 9 27 0 8 0
1426 21:12:06 CKPTINTVL 15:0x19d3018 0.0 0.0 0.0 0 0.0 0.0 0.0 326 326 322 1 59 0
1427 22:02:06 CKPTINTVL 15:0x19db018 0.0 0.0 0.0 0 0.0 0.0 0.0 10 10 28 0 8 0
1428 22:12:06 CKPTINTVL 15:0x1a11018 0.0 0.0 0.0 0 0.0 0.0 0.0 316 316 313 0 54 0
Max Plog Max Llog Max Dskflush Avg Dskflush Avg Dirty Blocked
pages/sec pages/sec Time pages/sec pages/sec Time
10240 1280 0 146 0 0
onsat -g cfg
打印配置参数的当前值
[informix@vm84145 ~]$ onstat -g cfg
Sinoregal SinoDB Dynamic Server Version 12.10.FC8 -- On-Line -- Up 1 days 04:44:30 -- 2374324 Kbytes
Configuration Parameter List
name current value
ROOTNAME rootdbs
ROOTPATH /home/informix/dbs/rootdbs
ROOTOFFSET 0
ROOTSIZE 2048000
MIRROR 0
MIRRORPATH /home/informix/sinodb/tmp/demo_on.root_mirror
MIRROROFFSET 0
DBSERVERNAME sinodb
DBSERVERALIASES
SERVERNUM 0
MSGPATH /home/informix/sinodb/tmp/online.log
CONSOLE /home/informix/sinodb/tmp/online.con
TAPEDEV /home/informix/backup
TAPESIZE 0
TAPEBLK 32
LTAPEDEV /home/informix/backup
......
onstat -g ppf
打印所有分区概要文件。使用 onstat -g ppf partition_number 命令显示指定分区号的分区概要文件。如果 TBLSPACE_STATS 配置参数设置为 0，那么 onstat -g ppf 命令显示：Partition profiles disabled。
[informix@vm84145 ~]$ onstat -g ppf |more
Sinoregal SinoDB Dynamic Server Version 12.10.FC8 -- On-Line -- Up 13 days 03:29:33 -- 2757528 Kbytes
Partition profiles
partnum lkrqs lkwts dlks touts isrd iswrt isrwt isdel bfrd bfwrt seqsc rhitratio
0x6 0 0 0 0 14 0 0 0 0 0 0 0
0xa 0 0 0 0 15053 0 0 0 12996 0 0 88
0xe 0 0 0 0 2667634 0 0 0 4082 0 0 100
0xf 0 0 0 0 1199 0 0 0 1185 0 0 65
0x10 0 0 0 0 13 0 0 0 0 0 0 0
......
- onstat -g lmx
打印所有锁定的互斥。
bash: onstst: command not found...
[informix@vm84145 ~]$ onstat -g lmx
Sinoregal SinoDB Dynamic Server Version 12.10.FC8 -- On-Line -- Up 13 days 03:38:51 -- 2757528 Kbytes
Locked mutexes:
mid addr name holder lkcnt waiter waittime
134825 7000002043a9148 free_lock 11009 0 200 22921
11010 22918
587817 70000022ddb3268 sync_lock1 200 0
593614 700000239ce7b68 SB_LTH_LATCH 875 0
Number of mutexes on VP free lists: 49
onstat -g mem
打印池内存统计信息
[informix@vm84145 ~]$ onstat -g mem
Sinoregal SinoDB Dynamic Server Version 12.10.FC8 -- On-Line -- Up 1 days 06:01:54 -- 2374324 Kbytes
Pool Summary:
name class addr totalsize freesize #allocfrag #freefrag
aqtpool V 48206040 8192 1128 6 2
afpool V 46de5040 12288 5104 5 4
tpcpool V 481cb040 40960 2168 36 3
seqpool V 481fb040 4096 696 2 1
pnlpool V 481ce040 53248 3688 46 3
sbtlist V 472d0040 20480 7160 4 3
dstpool V 481ca040 970752 24944 606 35
tenprp_cach V 48448040 8192 4008 8 1
ampool V 481f3040 4096 264 7 1
srvnm_cache V 481ba040 8192 3920 9 1
main_loop() V 4842c040 679936 21976 112 40
51*O0 V 48cd8040 4096 768 1 1
RA_req_106_ V 48bfe040 4096 64 2 1
sb_delundoq V 47308040 49152 8712 4 3
XTF_mem V 48268040 724992 4968 4 3
52*O0 V 48bae040 4096 768 1 1
RA_req_107_ V 48f8f040 4096 64 2 1
RA_req_107_ V 48f6d040 40960 848 3 2
53*O0 V 48d1d040 4096 768 1 1
bf_prioswee V 48c27040 28672 3032 13 4
memory V 49073040 28672 3032 13 4
GridQuery V 47714040 4096 696 2 1
readahead_0 V 48baf040 167936 4208 217 13
userlbacpoo V 481d2040 8192 3688 2 2
ritem V 47480040 77824 6200 34 7
onlinereorg V 4843e040 20480 1680 4 3
session_mgr V 49123040 28672 3032 13 4
onmode_mon V 48c1e040 32768 3600 20 4
SYS.26.80 V 48c29040 8192 3688 3 2
lgflushpool V 48aef040 4096 464 3 1
2 V 4847d040 16384 3824 16 2
rsam V 473ab040 2383872 35128 5032 19
sscpool0 V 481c0040 12288 1544 2 2
3 V 4849c040 16384 3824 16 2
aslogflush V 48af0040 28672 3032 13 4
aio V 47710040 2125824 123456 413 87
opcinstpool V 481cd040 8192 2664 3 2
EXE.51.105 V 49954040 4096 576 4 1
EXE.51.105 V 498a3040 4096 640 3 1
EXE.51.105 V 492bf040 53248 1536 505 1
dbausrpool V 48202040 8192 2944 15 1
EXE.52.106 V 498cf040 4096 640 3 1
EXE.52.106 V 498ce040 4096 704 2 1
EXE.52.106 V 49329040 4096 504 5 1
EXE.52.106 V 49ac3040 4096 320 8 1
EXE.53.107 V 491ce040 4096 640 3 1
EXE.53.107 V 49af7040 4096 704 2 1
EXE.53.107 V 49aa9040 4096 504 5 1
EXE.53.107 V 492fe040 4096 320 8 1
SES.51.105 V 48dae040 8192 1944 6 2
SES.52.106 V 48e47040 8192 3696 3 2
smartblob V 472ce040 16384 6464 7 3
gls V 481bd040 1327104 3800 6197 8
SES.53.107 V 48ba4040 8192 3696 3 2
policypool V 481d1040 8192 3688 2 2
PRP.51.105 V 48f5d040 4096 176 4 1
DefConvWrit V 48264040 16384 2752 102 2
PRP.52.106 V 48d6b040 4096 360 3 1
proxyWrite V 472c2040 49152 2776 2 2
sysprocpool V 48203040 8192 3592 3 2
resroutpool V 481d0040 8192 3688 2 2
PRP.53.107 V 490d1040 4096 360 3 1
periodic V 49071040 28672 3032 13 4
btscanner_0 V 48cf3040 94208 15568 35 8
auto_tune V 48bd1040 45056 3768 28 5
smx V 481a0040 8192 3328 11 1
sb_delq V 472d5040 49152 8712 4 3
resident R 440ae040 22695936 20376 10 9
dictpool V 481c1040 778240 9360 261 6
grgid_cache V 481ac040 8192 3920 9 1
mt V 46de6040 7651328 293056 6957 131
env_vars V 46dc5040 61440 2752 344 1
50 V 48d9f040 73728 6448 64 7
51 V 48ce8040 622592 140968 395 41
hstad_cache V 481b1040 16384 72 133 1
52 V 48d62040 557056 93376 390 58
53 V 48f28040 557056 91592 397 62
62 V 48fba040 16384 3824 16 2
aggpool V 481cf040 8192 3688 2 2
56 V 48eb4040 16384 2208 22 4
procpool V 481c7040 507904 8664 387 20
extdpool V 481fc040 8192 1744 9 2
global V 46dc4040 15908864 1136960 3959 588
Shard V 47716040 28672 4728 3 2
spnam_cache V 481a9040 8192 2768 21 2
compr_cache V 47712040 8192 4008 8 1
ctcpool V 481cc040 86016 1640 80 2
tmxapool V 48201040 4096 640 3 1
sb_arcspace V 473a5040 8192 1712 4 2
pwnam_cache V 481a3040 12288 2704 41 3
sb_loheader V 4733a040 282624 6528 413 57
RSS V 4844e040 188416 3528 507 43
rascron V 48ea1040 65536 12280 39 15
inhpool V 481f5040 8192 816 44 1
pwuid_cache V 481a6040 8192 2768 21 2
opcpool V 481f4040 8192 3208 18 1
hstnm_cache V 481ae040 8192 3480 13 1
Blkpool Summary:
name class addr size #blks
mt V 46de8890 4575232 61
global V 46de31c0 0 0
