How to measure what user processes are waiting for in ASE

[sladebe]

Using sp_sysmon, I can see all sort of things about what engines are doing or waiting for (although, in default threaded mode, all idle engines show up as I/O Busy if there's at least one I/O pending causing I/O Busy to look way to high. KBA 1863319).

I can also see the number of "Runnable Task" waiting for an engine. I assume, that doesn't include tasks waiting for I/O. Ie., my understanding of "Runnable" means a thread waiting in a queue for a CPU (er, core) to become available to run that thread.

But I don't see any way to see what user threads are waiting for disk I/O. I guess the same question applies to waiting for network I/O.

In the same way that "Average Runnable Tasks" means "Average Number of Threads Waiting for a CPU", should there be "Average Number of Threads Waiting for Disk I/O" and "Average Number of Threads Waiting for Network I/O"?

This is the detailed instant snapshot of this info, but it's not the same as running totals:

select Description=substring(b.Description,1,60), proc_count=count(*)
from monProcess a, monWaitEventInfo b
where a.WaitEventID=b.WaitEventID
group by substring(b.Description,1,60) order by count(*) desc

 Description                                                  proc_count 
 ------------------------------------------------------------ -----------
 waiting for incoming network data                                     88
 xact coord: pause during idle loop                                     4
 wait for message                                                       3
 hk: pause for some time                                                3
 waiting for regular buffer read to complete                            3
 Process is running                                                     2
 checkpoint process idle loop                                           1
 waiting on run queue after sleep                                       1
 waiting for client connection request                                  1
 waiting for next port manager request                                  1
 Bucket pool manager consolidator sleep                                 1
 Wait until heartbeat or check interval expires                         1
 waiting for deadlock check configuration to change                     1

I think the "waiting for incoming network data" is really a form of idle and the "waiting for regular buffer read to complete" is waiting for disk i/o.

[former_member89972]

My comment/answer is more from process kernel mode !

So CPU usage for I/O (disk + network) under threaded kernel mode is an uncharted area for me as of now.

Thanks for sharing the CR #, hopefully it fixes it both kernel modes.

Avinash

[former_member89972]

Your initial question was "wait" related.

For this time delta values extracted from MDA tables are best suited.

AFAIU : what sysmon shows is actual "usage" and not "waits".

I/O busy : is CPU usage towards all I/Os (disk + network)

Your observation on disk I/O and network I/O not adding up to total I/O is accurate.

Someone from SAP should shed light on that.

HTH

Avinash

[sladebe]

Re: I/O busy : is CPU usage towards all I/Os (disk + network)

Do you have any reference for this? @@io_busy in the new kernel threaded environment currently has a weird definition of any idle CPU while at least one I/O is waiting to complete. So if you have 8 cores idle, and 1 I/O pending, it counts all 8 cores as io_busy.

Re: disk I/O and network I/O not adding up to total I/O

I actually opened an incident with SAP about that. The "Total I/Os" number is inaccurate. To be fixed someday. CR 818885

[sladebe]

Well, I think I found an answer to the "How to measure what user processes are waiting for in ASE?" question.

You can use the command:

sp_sysmon <length_of_sample_delta_time_string>,appmgmt,appl_and_login

For example, to see what individual processes are doing during a 1 minute sample:

sp_sysmon "00:01:00",appmgmt,appl_and_login

which, after displaying the normal "Application Management" section of sp_sysmon output, also displays per application/login info like this:

------------------------------------------------------------------------------- 
  Application->Login:   my_app_name->my_login_name
 
  Application Activity            per sec      per xact       count  % of total
  -------------------------  ------------  ------------  ----------
    CPU Busy                          0.1           0.1           1       0.4 %
    I/O Busy                         27.9          34.9         279      99.6 %
    Idle                              0.0           0.0           0       0.0 %
 
    Number of Times Scheduled        25.9          32.4         259       n/a   
 
  Application Priority Changes    per sec      per xact       count  % of total
  -------------------------  ------------  ------------  ----------  ---------- 
  Total Priority Changes              0.0           0.0           0       n/a   
 
  Application I/Os Completed     per sec      per xact       count  % of total
  -------------------------  ------------  ------------  ----------  ---------- 
    Disk I/Os Completed               0.5           0.6           5       1.0 %
    Network I/Os Completed           18.2          22.8         182      35.7 %
  -------------------------  ------------  ------------  ----------
  Total I/Os Completed               51.0          63.8         510             
 
  Resource Limits Violated      per sec      per xact       count  % of total
  -------------------------  ------------  ------------  ----------  ---------- 
  Total Limits Violated               0.0           0.0           0       n/a   

Looking at the above sample, it looks like I/O Busy includes disk I/O and Network I/O.

The documentation defines "I/O Busy" as:

“I/O Busy” reports the number of clock ticks during which the user task was
performing I/O during the sample interval. 

If a task is waiting for an I/O to complete, does that count as I/O Busy for that task?

[sladebe]

Note that in the sp_sysmon output above Disk I/Os Complete and Network I/Os Completed doesn't add up to Total I/Os Completed. I'm not sure which one is in error.

[sladebe]

Note there is a known bug with sp_sysmon appl_only and appl_and_login options

2776095 - sp_sysmon Application Management is incorrect for appl_only and appl_and_login - SAP ASE

The "appl_only" and "appl_and_login" option for sp_sysmon produces incorrect values for Application Activity, Application Priority Changes and Application I/Os Completed when ther's more than one Application or Application + Login pair connected to ASE.

Example of invalid output for Application I/Os Completed:

Application I/Os            Completed/sec    per xact      count      % of total
-------------------------    ------------    ------------  ---------- ----------
Disk I/Os Completed           3.6            0.0            112        3.6 %
Network I/Os Completed       29.3            0.0            907        29.3 %
-------------------------    ------------    ------------  ----------
Total I/Os Completed         99.9            0.1           3098    <--- bad values, too high

Note the Total I/Os completed is [erroneously] 3098 for this sample while the [correct] sum of Disk and Network I/O [subcategories] reported is 1019.

Environment

SAP Adaptive Server Enterprise (ASE)

Cause

The sp_sysmon_appmgmt was not aggregating the monitoring data to include duplicate "Application" or "Application+Login" connections.

The issue has been logged under SAP CR 818885. Its description reads, "When running sp_sysmon using the appl or appl_and_login option, the application or application->login section reports inaccurate Disk and Network I/Os when there are multiple sessions with the same application or application->login pairing".

Resolution

SAP CR 818885 is fixed in:

SAP ASE 15.7 SP141

SAP ASE 16.0 SP02 PL09

SAP ASE 16.0 SP03 PL07

[former_member89972]

I checked monWaitClassinfo and found following classes related to disk (read & write) and network (receive and send) :

DISK

2 waiting for a disk read to complete

3 waiting for a disk write to complete

NETWORK

7 waiting for input from the network

8 waiting to output to the network

So if you take waits for events with WaitClassID 2 and 3, that should give you disk related stats

And similarly for the Network stats. For network I would not count stats for WaitEventID 250.

HTH

Avinash