Skip to Content

What do @@*_busy variables mean in the new kernel mode=threaded world?

I have an old script that kept track of how busy an ASE server was using calculated deltas from the following query:

select @@system_busy, @@user_busy,  @@cpu_busy, @@idle, @@io_busy, @@pack_received, @@pack_sent, @@packet_errors, @@total_errors, @@total_read, @@total_write

This worked fine until we recently switched to ASE "kernel mode"="threaded" (ie., use OS level threads for multi-tasking within Sybase).

After the switch, the io_busy ticks went up (as a percentage of total ticks) and idle ticks went down.

Looking at sp_symon with the "kernel" option, the Kernel Utilization -> Engine Utilization section also shows most time being spent in I/O (and not much idle). For example 93% I/O busy and 2% idle. sp_monitor gives similar numbers. But I know my ASE server isn't that busy.

Looking farther down in sp_sysmon output, I see the Thread Utilization section. Here, things look more reasonable. For example, in the syb_default_pool, I see 81% idle, 18% User Busy, 1% System Busy. Similar numbers in the syb_system_pool threads.

What's the best way to extract these sort of monitoring stats from a "threaded" ASE server so I can record them in my own historical database? Sum columns from monEngine instead?

monSysLoad is interesting, but it only gives averages per engine. To get server wide stats I'd have to take averages of these averages, which is not really a meaningful thing to do.

Thanks in advance
Ben Slade
near Washington DC

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

5 Answers

  • Dec 12, 2017 at 09:19 PM

    I should have also said, we're interested in this for load routing purposes. Ie., to determine which ASE server is least loaded and have an application go there.

    Doing some research, I found links explaining the problem with @@io_busy counts in new threaded mode:

    https://archive.sap.com/discussions/thread/3764504

    which says

    When SAP ASE is running Kernel mode threaded, I/O Busy in sp_sysmon is over weighted.
    
    Currently, when in threaded mode, engines are counted as "I/O Busy" if Adaptive Server has any I/O outstanding and the engine is idle. 
    
    If there is one I/O outstanding and three engines sitting idle, each engine is counted as "I/O Busy".
    
    That sp_sysmon report like this we will try to address with change request # 757246 not so easy as thread mode change the way engine works.
    
    There’s also a similar discussion in https://archive.sap.com/discussions/thread/3941596

    This also applies to @@io_busy (it's overweighted in threaded mode)

    I was thinking of using something like this instead:

    select sum(CPUTime),  sum(IdleCPUTime) from monEngine

    I didn't include sum(IOCPUTime) because according to http://www.asemon.se/history.html, IOCPUTime is also overcounted in threaded mode.

    Also, according to http://www.asemon.se/history.html, IdleCPUTime is a subset CPUTime (when the CPU is spinning waiting for work). If so, that seems to imply that monEngines doesn't keep track of the other kind of idle time, when the CPU stops spinning for a while.

    So what stats can I programatically access to figure out if one ASE server is busier than another? (that will run on 15.5 and 16.0)

    Add comment
    10|10000 characters needed characters exceeded

  • Dec 26, 2017 at 08:53 PM

    One other thing to pay attention to with respect to @@io_busy and @@idle in "kernel mode"="threaded":

    It looks like idle time is counted as io_busy ticks *instead* of idle tickets.

    Here's an example with 4 I/O bound processes on a 8 "thread engine" ASE server, looking at the sum of time deltas for totals from the monThread table where ThreadPoolName="syb_default_pool" (typically, user I/O) versus the deltas for various @@ variables:

    Thread pool delta sums for syb_default_pool only:
     num_threads BusyTicks    IdleTicks     SleepTicks     TotalTicks     DiskIOCompleted 
    ------------ --------- --------- ----------- ---------- --------------- 8 333 1267 0 1600 130721 global @@counter deltas: cpu_busy_ticks io_busy_ticks idle_ticks pack_received pack_sent total_read 148 1446 6 21 21 130751

    So according monThread, there were 333 busy ticks in CPUS and 1267 idle ticks in CPUs

    But the @@variables show 148+1446 cpu+io busy ticks and only 6 @@idle ticks

    So should @@io_busy be considered a form of idle on ASE 16 in threaded mode?

    Stated another way, "io_busy" on ASE 16 in threaded mode, isn't really a form of "busy". Ie., The CPUs/threads spend very little time actually processing I/O. They're all available for other CPU work even under heavy I/O load.

    For determining whether one ASE server is busier than another, you probably need to include some sort of I/O % busy statistic. Maybe at the OS level?

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 16 at 08:06 PM

    One other possibly useful way to figure out which ASE server is busy or not is to use the monSysLoad table:

    [104] MYSERVER.master-14:45:26-1> select Statistic,Avg_1min=avg(Avg_1min) 
     from monSysLoad group by Statistic;
     Statistic                      Avg_1min            
     ------------------------------ --------------------
     disk reads per second                      0.000000
     disk writes per second                     0.000000
     kernel run queue length                    0.000000
     network reads per second                   0.000000
     network writes per second                  0.000000
     outstanding disk i/os                      0.014396
     percent i/o busy                          10.092863
     percent system busy                        0.027771
     percent user busy                         15.596598
     run queue length                           0.172890
    

    As with the @@io_busy global variable, the "percent i/o busy" value is really a form of idle (I think). So it's value is too high here.

    Note that the above query is really taking the average of a set of averages, so it's not exactly accurate. But since the monSysLoad only keeps track of 1 minute averages per "engine" (or per user "engine thread" in threaded mode (all threadpools of type "Engine"?)), there's no other way to calculate the average across all "engines".

    Also, I think maybe monSysLoad is only looking at user threads (ie., ignoring the syb_blocking_pool and syb_system_pool thread pools). Maybe this is why I never see non-zero disk read/writes or network read/writes in monSysLoad.

    Anyway, I think some combination of the outstanding disk i/os, percent system busy, percent user busy, and run queue length 1 minute average values could be used to decide which ASE server is least (or most) busy.

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 19 at 04:19 PM

    Hmm, for the monSysLoad query, maybe the busy percentages should be averaged, but the other stats (like outstanding disk i/os and run queue length) should be summed?

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 19 at 10:24 PM

    Looking at the archived discussion in https://archive.sap.com/discussions/thread/3941596, they're saying any "thread engine" (ie., type="Engine" in sp_helpthread. Typically the syb_default_pool for regular user threads) can start an I/O, but the DiskController thread in the syb_system_pool thread pool polls for I/O completion, and adds a user task to the run queue once the I/O has completed.

    So the only thread business associated with I/O is a thread submitting I/O, the DiskController thread polling for completion, and a thread running the user task once the I/O is complete. Ie., user threads don't wait for I/O. There's no concept of I/O busy for them.

    Maybe a more interesting metric would be user tasks (not threads) that are waiting for I/O to complete (waiting for the DiskController thread to reschedule that user task onto a user "engine thread" once the I/O completes)

    Add comment
    10|10000 characters needed characters exceeded