Monitoring Network Operations
Onstat -g ntu Print Net User Thread Profile Information by VP
This command allows you to monitor network packet reads and writes in the IDS engine.
$ onstat -g ntu Informix Dynamic Server Version 7.30.UC3 -- On-Line -- Up 2 days 22:15:01 -- 18464 Kbytes global network information: #netscb connects read write q-free q-limits q-exceed 4/ 6 0 0 0 1/ 1 135/ 10 0/ 0 Individual thread network information (basic): netscb type thread name sid fd poll reads writes q-nrm q-exp c1c43b68 soctcp sqlexec 40 3 5 0 0 0/ 0 0/ 0 cla6a018 soctcp sqlexec 34 2 5 0 0 0/ 0 0/ 0 cla51628 soctcp soctcplst 3 1 5 0 0 0/ 0 0/ 0 cla503f8 soctcp soctcppoll 2 0 5 0 0 0/ 0 0/ 0
In the example above, the first two threads are user (sqlexec) threads and the last two are poll threads. Monitor the reads and writes by the user threads to find out how much load individual user threads are placing on the network.
Onstat -g ntt Print Net User Thread Access Times
This command provides user times for the various threads that are accessing network resources. This command is also totally undocumented, but it does show one piece of information that is difficult to find anywhere else. In the example printout, look at the "soctcplst" entry. Off to the right is an address that includes a machine name, a socket/tcp-port-number, and an Informix network protocol. This can be useful in investigating connections to your database that you cannot trace to SQL running in the engine.
The open and read times can also give you an idea how long a connection has been active and when the last activity occurred. This can help you investigate long-running tasks or connections.
$ onstat -g ntt Informix Dynamic Server Version 7.30.UC3 -- On-Line -- Up 2 days 22:15:04 -- 18464 Kbytes global network information: #netscb connects read write q-free q-limits q-exceed 4/ 6 0 0 0 1/ 1 135/ 10 0/ 0 Individual thread network information (times): netscb thread name sid open read write address c1c43b68 sqlexec 40 13:35:22 13:39:32 13:39:32 c1a6a018 sqlexec 34 13:22:51 13:52:26 13:52:26 c1a51628 soctcplst 3 17:24:52 13:52:04 rock.shared.com/4001/soctcp 08/14/98 c1a503f8 soctcppoll 2 17:24:46 08/14/98
Onstat -g ntd Print Net Dispatch Information
This command shows the number of accepted and rejected network packets arranged by type of client. It can tell you a couple of things. First, the total number of packets read and written can give you an estimate of the amount of work being done by various client types and can help you investigate clients that may be placing an inappropriate load on the network. Look at the read and write columns for this data.
You can also look for a large number of rejected entries that will help alert you to network problems. These rejects can occur for several reasons:
Network timeout caused by network overload or other network problems
User table overflow. Check onstat -p and look for ovuserthreads greater than 0.
If you are seeing excessive network timeouts, there are a couple of environmental variables that you can set either in setnet32 or in your user environment:
| • INFORMIXCONTIME | Wait time for a connection. Try increasing it. |
| • INFORMIXCONRETRY | Number of times to retry a failed connection. |
For example, if you have INFORMIXCONTIME set to 1200 and INFORMIXCONRETRY set to 20, the client will retry the connection every 1200/20 = every 60 seconds for 20 times, for a grand total of 1200 seconds. If your network is very busy or if you are connecting over a slow network such a RAS or PPP connection, you may manipulate these connection parameters in an attempt to cut down on rejected connections.
$ onstat -g ntd Informix Dynamic Server Version 7.30.UC3 -- On-Line -- Up 2 days 22:15:13 -— 18464 Kbytes global network information: #netscb connects read write q-free q-limits q-exceed 4/ 6 0 0 0 1/ 1 135/ 10 0/ 0 Client Type Calls Accepted Rejected Read Write sqlexec yes 42 0 0 0 srvinfx yes 0 0 0 0 onspace yes 0 0 0 0 onlog yes 0 0 0 0 onparam yes 0 0 0 0 oncheck yes 0 0 0 0 onload yes 0 0 0 0 onunload yes 0 0 0 0 onmonitor yes 0 0 0 0 dr_accept yes 0 0 0 0 cdraccept no 0 0 0 0 ontape yes 0 0 0 0 srvstat yes 0 0 0 0 asfecho yes 0 0 0 0 listener yes 0 0 0 0 crsamexec yes 0 0 0 0 safe yes 0 0 0 0 Totals 42 0 0 0
Onstat -g dic Print Dictionary Cache Information
IDS maintains a dictionary cache in the virtual portion of shared memory. Every time an SQL statement references one of the system tables, the information from the system table is cached in stored memory. Just about every SQL statement will require access to one of the system tables. This dictionary cache size cannot be modified with documented parameters, but there are two undocumented parameters that can affect the cache size. They are:
| • DD_HASHSIZE | must be prime (default 31) |
| • DD_HASHMAX | Integer<32767 (default 10) |
Set these two parameters so that DD_HASHSIZE * DD_HASHMAX is greater than the estimated number of active tables in all databases (including remote databases) and you will cut down on thrashing in the dictionary cache.
The dictionary cache is ordered into a series of linked lists. When a table's dictionary data is cached, the engine chooses a list and adds the internal data structure to the list. The ordering is not sequential, so there is no easy way here to tell when the table's data was accessed, only that it was accessed and has not been deleted from the cache yet.
$ onstat -g dic
Informix Dynamic Server Version 7.30.UC3 -- On-Line -- Up 2 days 22:15:38 -- 18464 Kbytes
Dictionary Cache: Number of lists: 31, Maximum list size: 10
list# size refcnt dirty? heapptr table name
--------------------------------------------------------
0 7 0 no c1d15520 crm3@inf_rock1:informix.summ_mbr_ret_h_mg
0 no c1cef420 crm3@inf_rock1:informix.summ_mbr_ret_hdr
0 no c1d3c6e0 crm3@inf_rock1:informix.levels
0 no c1d39f20 crm3@inf_rock1:informix.mbr_points
0 no c1d08700 crm3@inf_rock1:informix.batch_history
0 no c1d28c20 crm3@inf_rock1:informix.batch_rpt_hdr
0 no c1d25020 crm3@inf_rock1:informix.sweepstakes_audit
1 4 0 no c1d3cee0 crm3@inf_rock1:informix.location_assoc
0 no c1d41020 crm3@inf_rock1:informix.mbr_id
0 no c1d62be0 crm3@inf_rock1:informix.summ_lp_level
0 no c1d39320 crm3@inf_rock1:informix.mbr_points_year
<many lines deleted for simplicity>
29 4 0 no c1d7b020 bsgsec@inf_rock1:informix.sysdefaults
0 no c1d596c0 crm3@inf_rock1:informix.summ_l_level_mg
0 no c1d44020 crm3@inf_rock1:informix.item_group
0 no c1c5fe80 crm3@inf_rock1:informix.sysdefaults
30 5 0 no c1d1c4c0 crm3@inf_rock1:informix.summ_lp_dg_mg_ig
0 no c1cd9c20 crm3@inf_rock1:informix.summ_new_mbr_hdr
0 no c1d63b40 crm3@inf_rock1:informix.summ_lp_level_pro
0 no c1d0b1c0 crm3@inf_rock1:informix.sweepstakes_detail
0 no c1cffc20 crm3@inf_rock1:informix.systableext
Total number of dictionary entries: 181
Meanings of the fields follow:
| • list# | ID number of the hash list |
| • size | How many dictionary structures are in this list |
| • refcnt | How many current threads are referencing this data |
| • dirty | Has any of the dictionary data changed? |
| • heapptr | Pointer to the data structure in the heap |
| • table name | Fully qualified table name |
This information in the dictionary cache is not changed by an onstat -z. It changes only when the capacity of the cache is reached and some data has to be dropped from the cache. It is also reinitialized whenever the engine is stopped and restarted.
Onstat -g opn Print Open Tables
This command lists data about tables that are open at the time the command is run.
$ onstat -g opn Informix Dynamic Server Version 7.30.UC3 -- On-Line -- Up 2 days 22:15:43 -- 18464 Kbytes tid rstcb isfd op_mode op_flags partnum ucount ocount lockmode 76 Oxcl8db2e8 0 0x00000400 0x00000397 0x0070001f 2 2 0 76 Oxcl8db2e8 1 0x00000002 0x00000003 0x0070001f 2 2 0 76 Oxcl8db2e8 2 0x00000400 0x00000693 0x00700040 1 0 0 70 Oxcl8db79c 0 0x00000400 0x00000397 0x0070001f 2 2 0 70 Oxcl8db79c 1 0x00000002 0x00000117 0x0070001f 2 2 0 70 Oxcl8db79c 4 0x00000400 0x00000417 0x00700041 1 0 0
Field meanings are:
Onstat -g qst Print Queue Statistics
In order to use this command, you first have to set up your engine to collect statistics on queue activity. This is done by setting the QSTATS parameter to 1 in the ONCONFIG file. This data collection will slow the engine down significantly and shoul be done only if the user is chasing down a particularly obstinate problem.
This best done in conjunction with Informix Support, and we will not go into detail here.
D:INFORMIXetc>onstat -g qst Informix Dynamic Server Version 7.30.TC3 -- On-Line -- Up 00:00:56 -- 9536 Kbytes Mutex Queue Statistics name nwaits avg_time max_time avgq maxq nservs avg_time
This command gives you information about the queues that control access to mutexes. It gives the name, number of wait events, average wait times, maximum wait times, average queue length and maximum queue length for the queues. The last two columns are so far undocumented.
Onstat -g wst Print Thread Wait Statistics
Thread wait statistics is like queue statistics in that you first have to set the engine up to track thread waiting events. To enable the collection of wait statistics, set WSTATS to 1 in the ONCONFIG file. Beware that setting these data collections statistics can slow your server down significantly. Like queue statistics, this is best done in conjunction with Informix Support.
$onstat -g wai Informix Dynamic Server Version 7.30.TC3 -- On-Line -- Up 00:00:47 -- 9536 Kbytes Waiting threads: tid tcb rstcb prty status vp-class name 2 c274200 0 2 sleeping forever 31io vp 0 3 c274440 0 2 sleeping forever 4pio vp 0 4 c2746a8 0 2 sleeping forever 5aio vp 0 5 c274910 0 2 sleeping forever 6msc vp 0 6 c2c8230 c18e018 4 sleeping sees: 1 1cpu main_loop() 9 c2c9d28 0 3 sleeping forever 1cpu soctcplst 10 c2cc350 cl8e4b8 2 sleeping forever 1cpu flush_sub(0) 11 c2cc590 0 4 sleeping forever 5aio kaio 12 c2ccb38 0 4 sleeping forever 1cpu kaio 13 c2cd!70 c18e958 3 sleeping forever 1cpu aslogflush 14 c2cd4aO c18edf8 2 sleeping sees: 19 1cpu btclean 30 c274feO c18fbd8 4 sleeping sees: 1 1cpu onmodejnon