Informix

Informix

Connect with Db2, Informix, Netezza, open source, and other data experts to gain value from your data, share insights, and solve problems.

 View Only
Back to discussions
Expand all | Collapse all

What is the load on an individual listener or sqlexec thread?

  • 1.  What is the load on an individual listener or sqlexec thread?

    Posted Tue December 30, 2025 12:33 PM

    (Deep breath ;-)

    Greetings, Family.

    I can see if a specific thread is getting used  lot: Just run onstat -g  cpu:
    Thread CPU Info:
     tid    name              vp       Last Run           CPU Time     #scheds    status
     2      lio vp 0          3lio*   12/30 03:36:02        0.0000          1    IO Idle
     3      pio vp 0          4pio*   12/30 03:36:04        0.1001          2    IO Idle
     4      aio vp 0          5aio*   12/30 10:55:01       29.3996      37303    IO Idle
     5      msc vp 0          6msc*   12/30 10:55:01        6.7929       5839    IO Idle
     ...
     10     tlitcppoll      106tli*   12/30 11:02:17    26766.2113   10322919    running
     11     tlitcppoll      107tli*   12/30 11:02:17    26756.1968   10076435    running
    ...
     42     tlitcplst         1cpu*   12/30 11:02:17       34.6124     310553    sleeping forever
     43     tlitcplst         8cpu*   12/30 03:37:00        0.0042         58    sleeping forever
     44     tlitcplst         9cpu*   12/30 03:36:21        0.0012          7    sleeping forever
    ...
     63     flush_sub(9)     26cpu    12/30 11:02:17        0.2980      33480    sleeping secs: 1
     64     flush_sub(10)    26cpu    12/30 11:02:17       12.7093      34354    sleeping secs: 1
     65     flush_sub(11)    27cpu    12/30 11:02:17       12.6535      35911    sleeping secs: 1
     66     flush_sub(12)    29cpu    12/30 11:02:17       12.9527      46429    sleeping secs: 1
    ...
     9826   sqlexec          63cpu    12/30 11:00:25        0.0464        484    cond wait  netnorm
     9831   sqlexec          26cpu    12/30 11:00:45      831.7456    5244663    cond wait  netnorm
     9841   sqlexec          27cpu    12/30 11:00:51        0.5651       5792    cond wait  netnorm
     9842   sqlexec          15cpu    12/30 11:01:27        0.0285        492    cond wait  netnorm
     9845   sqlexec          14cpu    12/30 11:02:12      825.7890    5168963    cond wait  netnorm

    OK, we get the idea. Even within one pool of VPs, the work distribution is quite uneven. For example, I see that one listener thread is getting a plurality of the tlitcplst action and the poll threads are all getting lots of action.  But if I want to know what they are actually doing, I'd have to find another onstat command that related these thread IDs.  So far, onstat -u and onstat -g ses don't seem to fit this bill (though there is a SI column to "join" those).  Of course these values are fleeting but if run simultaneous onstat command I might be abe to capture some "join" condition.

    I'm looking into view sysuserthreads because that has column us_tid and us_sid in one.  Problem: But it also has many entries where us_tid - the thread ID - is 0.  Which makes no sense.  But I tried this query:

    select us_tid, us_sid, hex(us_address) address,
           hex(us_scb) ucb, hex(us_mttcb) mttcb,
           us_name::char(10) username
      from sysuserthreads
     where us_tid > 0
     --and us_sid > 0    -- Note: This is commented out
     order by us_sid, us_tid;
     
    One problem with this is that the tid's from onstat -g cpu do not show up in this query. And vice-versa. Here is the first row from that query:
     
    us_tid    50   <<=== Does not show up on onstat -g cpu.  And what happened to threads 2-49?
    us_sid    0
    address   0x000000016f1f0918  <--- I can grep the output of onstat -u for this.
    ucb       0x0000000000000000
    mttcb     0x00000001801c6a10
    username  informix

    •  So how do I properly associate a running with a thread id so that I can say someting intelligent about its workload?
    • Now the kicker question: It seemed a good bet that the listener threads are also listed in sysuserthreads.  Apparently not.  So how can I get this performance information about the daemon threads, like the listeners.

    (WHEW!  This guy's allovertheplace!)

    Thanks for some insights.  And correction of assumptions. (Good bet I made one bad assumption about sysuserthreads)



    ------------------------------
    +-----------------------------------------------------------+
    | I am pleased to report that I had no problems today. |
    | I had only issues, opportunities, challenges and valuable |
    | learning experiences. |
    +------------------------------------------ Jacob S --------+
    ------------------------------


  • 2.  RE: What is the load on an individual listener or sqlexec thread?

    Posted Tue December 30, 2025 03:40 PM
    this help at all ?

    The CPU time shown for a thread is updated each time the thread yields and is based on the change in clock time since the thread last started. For threads such as the poll threads, which spend the majority of their time in an OS call such as select(), epoll_wait() or similar, the oninit process (the SOC VP) will not be consuming any processor time yet IDS will show that thread as running. Hence for poll threads the onstat -g cpu time will be unhelpful(1), and the time shown by onstat -g glo for the SOC VP will be accurate as this reflects that actual time consumed by the process which is calculated by the kernel. It reflects real time / clock time and not the amount of actual cpu time consumed by that thread. IDS does not know when a thread makes a system call that potentially will block (these calls happen all the time for the poll threads). All IDS knows is that the thread's state is running (from the IDS perspective) but that does not mean the process is running at the OS level.


    On 12/30/2025 11:33 AM, Jacob Salomon via IBM TechXchange Community wrote:
    0100019b7051fdf6-300460f6-6fd3-4539-8276-69b5f15d195a-000000@email.amazonses.com">
    (Deep breath ;-) Greetings, Family. I can see if a specific thread is getting used lot: Just run onstat -g cpu: Thread CPU Info: tid ... -posted to the "Informix" group



    Original Message


Related Content