Informix Dynamic Server onstat Option

Onstat-D
INFORMIX-OnLineVersion7.22.UC1 -- On-Line -- Up00: 00: 38 -- 423064 Kbytes
Dbspaces
Addressnumberflagsfchunknchunksflagsownername
Ad5c100 1 111 Ninformixrootdbs
Ad5dc282 121 Ninformixdbs7
Ad5dc983 131 Ninformixdbs8
3 active, 2047 maximum
Chunks
Addresschk/dbsoffsetpageRdpageWrpathname
Ad5c17011501259289/dev/pa11
Ad5c638225011 11/dev/pa12
Ad5c71033503 0/dev/pa13
3 active, 2047 maximum
Monitor the number of page reads and writes in the "pageRd" and "PageWr" columns to check the balance of each chunk access. Remember that a device may have multiple chunks.
Onstat-F
InformixDynamicServer2000Version9.21. FC1 -- On-Line -- Up18: 05: 32 -- s
　
FgWrites LRUWrites ChunkWrites
0 602010 355973
Address flusher statedata
C0000000ba33e828 0I0 = 0X0
C0000000ba33f028 1I0 = 0X0
C0000000ba33f828 2I0 = 0X0
C0000000ba340028 3I0 = 0X0

States: ExitIdleChunkLru
Monitors the types of write operations on the system. The front-end writing (FgWrites) should be restricted. LRU writing and chunk writing may vary with the system.
The OLTP system should reduce LRU writes, but there will always be chunk writes, but LRU writes will reduce the duration of checkpoints. In the DSS System, chunk writing should be greatly improved, but some LRU writing will still occur to restrict front-end writing. At the checkpoint time, monitor the page cleaner refresh) to make sure they are all busy with chunk writing.

Onstat-l
InformixDynamicServer2000Version9.21. FC1 -- On-Line -- Up18: 07: 45--4
046800 Kbytes
PhysicalLogging
Bufferbufusedbufsizenumpagesnumwritspages/io
P-245220003647872881266.62
Phybeginphysizephyposphyused % used
1021595000003211664800.10
LogicalLogging
Bufferbufusedbufsizenumrecsnumpagesnumwritsrecs/pagespages/io
L-23100011052069515302241221.4213.6
SubsystemnumrecsLogSpaceused
OLDRSAM110520691038267280
Addressnumberflagsuniqidbeginsizeused % used
C00000001807d4c04U-B --- 67243000352500025000100.00
C00000001807d4e05U-B ---- 67253061dd2500025000100. 00
C00000001807d5006U-B ---- 672630c00002500025000100. 00
C00000001807d5207U-B ---- 672731252d2500025000100. 00
C00000001807d5408U-B ---- 67283186d52500025000100. 00
C00000001807d5609U-B ---- 672931e87d2500025000100. 00
Monitor the usage of the physical log buffer and observe the bufsize and pages/io columns in the output line. For example, divide pages/io by bufsize by about 75%, it can be determined that the buffer is more efficient to use. If it is less than 75%, the physical log buffer may be too large. If the ratio is greater than 90%. The physical buffer is too small.
Monitor the usage of the logical log buffer in the same way. However, if no buffer log is used, the buffer refresh depends on the transaction size, rather than the usage of the buffer, which may always be true. If most transactions are smaller than the logical log buffer page, this proportion may not always be involved and the logical buffer log is maintained by its default value. The physical log file should be monitored when the checkpoint occurs to determine whether the ratio is close to 75%. The adjusted physical log file is almost full when the checkpoint occurs. If the file is not full, disk space is wasted. You should also monitor logical log files to ensure they are backed up. Using the sysmaster database, you can determine that the logical log file is idle. In the sytrans table, find the line min (tx_logunip)> 0 to find the last logical log file that contains the open transaction.
Onstat-m

InformixDynamicServer2000Version9.21. FC1 -- On-Line -- Up18: 07: 45--4
MessageLogFile:/informix/online_cqys.log
15: 04: 02Checkpointloguniq6739, logpos0x884360
15: 09: 06 FuzzyCheckpointCompleted: durationwas0seconds, 12066buffersno.
15: 09: 06Checkpointloguniq6739, logpos0x1719768
15: 14: 11 FuzzyCheckpointCompleted: durationwas2seconds, 3236buffersnot.
15: 14: 11Checkpointloguniq6739, logpos0x36aa018
15: 19: 14 FuzzyCheckpointCompleted: durationwas0seconds, 4645buffersnot.
15: 19: 14Checkpointloguniq6739, logpos0x39d8454
15: 24: 17 FuzzyCheckpointCompleted: durationwas0seconds, 5692buffersnot.
15: 24: 17Checkpointloguniq6739, logpos0x3bfb06c
15: 29: 20 FuzzyCheckpointCompleted: durationwas0seconds, 6731buffersnot.
15: 29: 20Checkpointloguniq6739, logpos0x3df959c
15: 34: 23 FuzzyCheckpointCompleted: durationwas0seconds, 7886buffersnot.
15: 34: 23Checkpointloguniq6739, logpos0x403f258
Monitors message files to find possible special events. Monitors the checkpoint occurrence frequency and running time.
Onstat-P
InformixDynamicServer2000Version9.21. FC1 -- On-Line -- Up18: 10: 41 -- s
Profile
Dskreadspagreadsbufreads % cacheddskwritspagwritsbufwrits % cached
260596443144011937078372392.97148814022302741293075988.49
Isamtotopenstartreadwriterewritedeletecommitrollbk
1482319405204271778439595437285925906180816250117716082821
Gp_readgp_writegp_rewrtgp_delgp_allocgp_freegp_curs
0000000
Ovlockovuserthreadovbuffusercpusyscpunumckptsflushes
00023394.7814060.69173442
Bufwaitslokwaitslockreqsdeadlksdltoutsckpwaitscompressseqscans
331530290319982775200025454700529618
Ixda-RAidx-RAda-RARA-pgsusedlchwaits
33421515700171864787220544716204519
Profile information contains a lot of content that can be monitored
For the OLTP system, the cache read hit rate is very important. The read hit rate is equal to or greater than 95%. This is not always possible because the application uses data, but it is a starting point. Increasing more buffers can usually increase the cache read hit rate. You also need to monitor the cache write hit rate, but it is not easy to adjust. As the buffer increases, it will also increase. The initial target should be set to 85% or higher, but the ratio may not be reached due to different applications. Lock conflicts can be observed in the locwaits and locreqs columns. If lockwaits is equal to or greater than 1% of lockreqs, there may be lock conflicts, changing the way applications use isolation levels and locks will improve the probability of lock conflicts.
When a deadlock occurs in an application, check the deadlks and dltouts columns again. deadlks indicates the number of deadlocks detected Online and abandoned, and dltouts indicates the number of queries that exceed the deadlock time. A deadlock occurs only in Distributed queries. In case of a deadlock, the application should be changed.
Onstat-R
NFORMIX-OnLineVersion7.22.UC1 -- On-Line -- Up00: 44: 42 -- 423064 Kbytes
16 bufferLRUqueuepairs
# F/mlength % ofpairtotal
0f188100. 0% 188
1m00. 0%
2f188100. 0% 188
3m00. 0%
4f188100. 0% 188
5m00. 0%
6f188100. 0% 188
7m00. 0%
8f188100. 0% 188
9 m00. 0%
10f188100. 0% 188
11m00. 0%
12f188100. 0% 188
13 m00. 0%
14f188100. 0% 188
15m00. 0%
0 dirty, 3000 queued, 3000 total, 4096 hashbuckets, 2048 buffersize
Startcleanat20 % (ofpairtotal) dirty, or37buffsdirty, stopat10 %

Observe the percentage of modified buffers in each queue when a checkpoint occurs. Compare it with the LRU_MAX_DIRTY parameter. It should always be smaller than LRU_MAX_DIRTY. If this is not the case, you can determine whether the pagecleaner that executes the write request is insufficient, whether the AIOVP is insufficient, whether the KAIO thread that executes the physical write is insufficient, whether the disk controller is insufficient or the drive is saturated. For OLTP systems, reducing LRU_MAX_DIRTY to a smaller percentage than the dirty page in the LRU queue usually reduces the duration of the checkpoint.
Onstat-gioq

InformixDynamicServer2000Version9.21. FC1 -- On-Line -- Up18: 42: 23--4
046800 Kbytes
AIOI/Oqueues:
Qname/idlenmaxlentotalopsdskreaddskwritedskcopy
Adt0000000
Msc00127777000
Aio00193209532743195810
Pio00129702970
Lio0012556025560
Gfd30168251224860030
Gfd4019900
Gfd501174411744100
Gfd6013234323400
Gfd7021318161800
Gfd80181000109764055240870
Gfd9016130108778102710
Gfd10013210
Gfd11013210
Gfd12016874384053380
Gfd130118173577414620062737680
Gfd14012411511659606302135350
Monitor the length of the I/O Request queue len) and its maximum length maxlen ). If maxlen is greater than 25 or len is greater than 10, the request is not served in a timely manner. If the disk or controller is not saturated, adding more VP will help.
Onstat-glsc

InformixDynamicServer2000Version9.21. FC1 -- On-Line -- Up18: 15: 12 -- s
LightScanInfo
Descriptoraddressnext_lpagenext_ppageppage_leftbufcntlook_e
0c41edcd0875002234901N
1c41efe68856000cc4971Y
1c41f07b8837000cc4971Y
Onstat-glsc is used to determine whether a light scan is used. If the table is larger than the buffer size, the isolation level is set to drity read, or the committed read or shared lock is applied to the table, a light scan is performed. The light scan significantly accelerates the scanning of large tables. The ppage_left column displays the number of pages to be scanned for a given table partition.
Onstat-gntd

InformixDynamicServer2000Version9.21. FC1 -- On-Line -- Up18: 18: 29 -- s
Globalnetworkinformation:
# Netscbconnectsreadwriteq-freeq-limitsq-exceedalloc/max
455/4635594810133881975928/92135/100/0461/461
ClientTypeCallsAcceptedRejectedReadWrite
Sqlexecyes52621672951017370133
Srvinfxyes246000
Onspaceyes0000
Onlogyes0000
Onparamyes0000
Oncheckyes1102896150000253
Onloadyes0000
Onunloadyes0000
Onmonitoryes0000
Dr_acceptyes0000
Cdracceptno0000
Ontapeyes510542517538569
Srvstatyes2402448
Asfechoyes0000
Listeneryes00560612
Crsamexecyes0000
Safeyes0000
Onutilyes007578
Totals5594251013388197592
Monitors the number of accepted/denied connections. If the number of denied connections is large, the user table has exceeded onstat-povuserthreads, or the network times out during connection.
Onstat-gppf

INFORMIX-OnLineVersion7.22.UC1 -- On-Line -- Up9days08: 06: 18 -- 593176Ks
Partitionprofiles
Partnumlkrqslkwtsdlkstoutsisrdiswrtisrwtisdelbfrdbfwrtseqsc
2300005051100000
2400009000000
25000011710800000
28000027000000
102600000900000
209716498189900056503417143409360674706623040
2097166108244109800054016338120571102083835417248424
629146234525000800088836188500
1048594739761212000276335577323089743120041555615417
1048603811793045000154939911307384313455143856324555

Monitor the number of read/write operations performed by using isrd, iswrt, isrwt, and isdel on opened shards. Because only opened tables are listed, onstat-gppf is used for a long time for sampling, it can be pointed out which tables are frequently used. Use segsc to determine whether sequential scanning is used when reading data from a table. Compare shards in the same table to determine whether I/O of each shard is balanced.