Db2

Hi Team

This is related to a customer question on how to interpret several places in DSM and it's reports showing CPU utilization % getting higher than 100%. 

This discussion has started on internal IBM slack channel,  and per @KAI DING request, I am posting the issue here, so it can be tracked for DMC as well to make sure this is not happening or to be "fixed".

The CPU% metrics going higher than 100% can be seen in several reports and monitor pages on DSM like pictures bellow:
​Monitor / Throughput / WLM Workload

 

WLM Service class:

db-Partition Summary:

On Overview Reports: 

On Workload Manager Reports,  same metrics can be seen as well..  

Regards

Hi Team

This is related to a customer question on how to interpret several places in DSM and it's reports showing CPU utilization % getting higher than 100%. 

This discussion has started on internal IBM slack channel,  and per @KAI DING request, I am posting the issue here, so it can be tracked for DMC as well to make sure this is not happening or to be "fixed".

The CPU% metrics going higher than 100% can be seen in several reports and monitor pages on DSM like pictures bellow:
​Monitor / Throughput / WLM Workload

 

WLM Service class:

db-Partition Summary:

On Overview Reports: 

On Workload Manager Reports,  same metrics can be seen as well..  

Regards

Hi Team

This is related to a customer question on how to interpret several places in DSM and it's reports showing CPU utilization % getting higher than 100%. 

This discussion has started on internal IBM slack channel,  and per @KAI DING request, I am posting the issue here, so it can be tracked for DMC as well to make sure this is not happening or to be "fixed".

The CPU% metrics going higher than 100% can be seen in several reports and monitor pages on DSM like pictures bellow:
​Monitor / Throughput / WLM Workload

 

WLM Service class:

db-Partition Summary:

On Overview Reports: 

On Workload Manager Reports,  same metrics can be seen as well..  

Regards

Hi @KAI DING

Thanks very much for the followup and feedback . Good to hear that this has been addressed on new DMC tool.  Our customer is still evaluating the optoin to upgrade to DMC, so we can't really test that in a real workload scenario.  

Just curious,  can you share what was the previous formula used by DSM,  to calculate this CPU % utilization ?  
I have the impression that it was not being normalized taking consideration the number of cores per db-partition available in the system.  So,  if you dont take the # of cores into consideration,   you have the chances of running more than 100%, as multiple threads are running in parallel.   

Regards​

Hi Team

This is related to a customer question on how to interpret several places in DSM and it's reports showing CPU utilization % getting higher than 100%. 

This discussion has started on internal IBM slack channel,  and per @KAI DING request, I am posting the issue here, so it can be tracked for DMC as well to make sure this is not happening or to be "fixed".

The CPU% metrics going higher than 100% can be seen in several reports and monitor pages on DSM like pictures bellow:
​Monitor / Throughput / WLM Workload

 

WLM Service class:

db-Partition Summary:

On Overview Reports: 

On Workload Manager Reports,  same metrics can be seen as well..  

Regards

Hi @KAI DING

Thanks very much for the followup and feedback . Good to hear that this has been addressed on new DMC tool.  Our customer is still evaluating the optoin to upgrade to DMC, so we can't really test that in a real workload scenario.  

Just curious,  can you share what was the previous formula used by DSM,  to calculate this CPU % utilization ?  
I have the impression that it was not being normalized taking consideration the number of cores per db-partition available in the system.  So,  if you dont take the # of cores into consideration,   you have the chances of running more than 100%, as multiple threads are running in parallel.   

Regards​

Hi Team

This is related to a customer question on how to interpret several places in DSM and it's reports showing CPU utilization % getting higher than 100%. 

This discussion has started on internal IBM slack channel,  and per @KAI DING request, I am posting the issue here, so it can be tracked for DMC as well to make sure this is not happening or to be "fixed".

The CPU% metrics going higher than 100% can be seen in several reports and monitor pages on DSM like pictures bellow:
​Monitor / Throughput / WLM Workload

 

WLM Service class:

db-Partition Summary:

On Overview Reports: 

On Workload Manager Reports,  same metrics can be seen as well..  

Regards

Hi @KAI DING

Thanks for the feedback..   Yes.  this explains why they value goes higher in our system,  We have a total of 80 Cores on the server,  with 6 db-partitions running, which gives us a rate of 13.333 cores per db-partition.  

When trying to provide system wide CPU utilization it's important to take that into account as described in page 16 of this Best Practice article. 

Another approach that we could take as well,  is instead of trying to provide a CPU utilization from system-wide perspective..  (relative to time interval). 

we could provide a % in terms of DB-engine utilization.  (for connections,  Workloads,  and Statements only).   

For example: 

CPU% for workload A =  (total cpu time for workload-A) / Sum of (total CPU time) for all workloads. 

CPU% for Connection x = (total cpu time for connection-X) /  sum of (total cpu time)  among all connections. 

This metric will be normalized by the total db2 engine utilization,  and will not represent the total CPU % from system perspective..  but it would still useful,  as in that monitoring interval being analyzed,  it would provide us a better metric, of the top most consumer (relative to the total db utilization).  and it would never breached 100%.  

Regards. 

------------------------------
Samuel Pizarro

Original Message:
Sent: Sun June 28, 2020 11:13 PM
From: KAI DING
Subject: CPU utilization on Multi-Partitioned systems - Higher than 100%

Hi Samuel,

The DSM CPU utilization logic is to aggregate used CPU time and divide by the time interval, and transform to percentage value. For multi-core CPU, this value could be more than 100%. This also occurs on the metric aggregation of multi-partition Db2. For multiple threads on a single-core CPU, I think the total CPU used time should not be more than the time interval, because the CPU could be used by only 1 thread at a time, so the CPU usage percentage should not be more than 100%. Hope it is helpful. thanks.

------------------------------
KAI DING

Original Message:
Sent: Fri June 26, 2020 09:13 AM
From: Samuel Pizarro
Subject: CPU utilization on Multi-Partitioned systems - Higher than 100%

Hi @KAI DING

Thanks very much for the followup and feedback . Good to hear that this has been addressed on new DMC tool.  Our customer is still evaluating the optoin to upgrade to DMC, so we can't really test that in a real workload scenario.  

Just curious,  can you share what was the previous formula used by DSM,  to calculate this CPU % utilization ?  
I have the impression that it was not being normalized taking consideration the number of cores per db-partition available in the system.  So,  if you dont take the # of cores into consideration,   you have the chances of running more than 100%, as multiple threads are running in parallel.   

Regards​

Hi Team

This is related to a customer question on how to interpret several places in DSM and it's reports showing CPU utilization % getting higher than 100%. 

This discussion has started on internal IBM slack channel,  and per @KAI DING request, I am posting the issue here, so it can be tracked for DMC as well to make sure this is not happening or to be "fixed".

The CPU% metrics going higher than 100% can be seen in several reports and monitor pages on DSM like pictures bellow:
​Monitor / Throughput / WLM Workload

 

WLM Service class:

db-Partition Summary:

On Overview Reports: 

On Workload Manager Reports,  same metrics can be seen as well..  

Regards

Hi @KAI DING

Thanks for the feedback..   Yes.  this explains why they value goes higher in our system,  We have a total of 80 Cores on the server,  with 6 db-partitions running, which gives us a rate of 13.333 cores per db-partition.  

When trying to provide system wide CPU utilization it's important to take that into account as described in page 16 of this Best Practice article. 

Another approach that we could take as well,  is instead of trying to provide a CPU utilization from system-wide perspective..  (relative to time interval). 

we could provide a % in terms of DB-engine utilization.  (for connections,  Workloads,  and Statements only).   

For example: 

CPU% for workload A =  (total cpu time for workload-A) / Sum of (total CPU time) for all workloads. 

CPU% for Connection x = (total cpu time for connection-X) /  sum of (total cpu time)  among all connections. 

This metric will be normalized by the total db2 engine utilization,  and will not represent the total CPU % from system perspective..  but it would still useful,  as in that monitoring interval being analyzed,  it would provide us a better metric, of the top most consumer (relative to the total db utilization).  and it would never breached 100%.  

Regards. 

------------------------------
Samuel Pizarro

Original Message:
Sent: Sun June 28, 2020 11:13 PM
From: KAI DING
Subject: CPU utilization on Multi-Partitioned systems - Higher than 100%

Hi Samuel,

The DSM CPU utilization logic is to aggregate used CPU time and divide by the time interval, and transform to percentage value. For multi-core CPU, this value could be more than 100%. This also occurs on the metric aggregation of multi-partition Db2. For multiple threads on a single-core CPU, I think the total CPU used time should not be more than the time interval, because the CPU could be used by only 1 thread at a time, so the CPU usage percentage should not be more than 100%. Hope it is helpful. thanks.

------------------------------
KAI DING

Original Message:
Sent: Fri June 26, 2020 09:13 AM
From: Samuel Pizarro
Subject: CPU utilization on Multi-Partitioned systems - Higher than 100%

Hi @KAI DING

Thanks very much for the followup and feedback . Good to hear that this has been addressed on new DMC tool.  Our customer is still evaluating the optoin to upgrade to DMC, so we can't really test that in a real workload scenario.  

Just curious,  can you share what was the previous formula used by DSM,  to calculate this CPU % utilization ?  
I have the impression that it was not being normalized taking consideration the number of cores per db-partition available in the system.  So,  if you dont take the # of cores into consideration,   you have the chances of running more than 100%, as multiple threads are running in parallel.   

Regards​

Hi Team

This is related to a customer question on how to interpret several places in DSM and it's reports showing CPU utilization % getting higher than 100%. 

This discussion has started on internal IBM slack channel,  and per @KAI DING request, I am posting the issue here, so it can be tracked for DMC as well to make sure this is not happening or to be "fixed".

The CPU% metrics going higher than 100% can be seen in several reports and monitor pages on DSM like pictures bellow:
​Monitor / Throughput / WLM Workload

 

WLM Service class:

db-Partition Summary:

On Overview Reports: 

On Workload Manager Reports,  same metrics can be seen as well..  

Regards

Hi @KAI DING

I managed to find the Best-Practice pdf in the new Db2 (On Premisse and Cloud) Community.  It's named

Performance monitoring in a data warehouse 

This URL should work for you now
https://community.ibm.com/community/user/hybriddatamanagement/viewdocument/performance-monitoring-in-a-data-wa?CommunityKey=ea909850-39ea-4ac4-9512-8e2eb37ea09a&tab=librarydocuments&LibraryFolderKey=03d75973-3232-4631-b848-d6fc6743f8ad&DefaultView=folder

​Regards

Hi @KAI DING

Thanks for the feedback..   Yes.  this explains why they value goes higher in our system,  We have a total of 80 Cores on the server,  with 6 db-partitions running, which gives us a rate of 13.333 cores per db-partition.  

When trying to provide system wide CPU utilization it's important to take that into account as described in page 16 of this Best Practice article. 

Another approach that we could take as well,  is instead of trying to provide a CPU utilization from system-wide perspective..  (relative to time interval). 

we could provide a % in terms of DB-engine utilization.  (for connections,  Workloads,  and Statements only).   

For example: 

CPU% for workload A =  (total cpu time for workload-A) / Sum of (total CPU time) for all workloads. 

CPU% for Connection x = (total cpu time for connection-X) /  sum of (total cpu time)  among all connections. 

This metric will be normalized by the total db2 engine utilization,  and will not represent the total CPU % from system perspective..  but it would still useful,  as in that monitoring interval being analyzed,  it would provide us a better metric, of the top most consumer (relative to the total db utilization).  and it would never breached 100%.  

Regards. 

------------------------------
Samuel Pizarro

Original Message:
Sent: Sun June 28, 2020 11:13 PM
From: KAI DING
Subject: CPU utilization on Multi-Partitioned systems - Higher than 100%

Hi Samuel,

The DSM CPU utilization logic is to aggregate used CPU time and divide by the time interval, and transform to percentage value. For multi-core CPU, this value could be more than 100%. This also occurs on the metric aggregation of multi-partition Db2. For multiple threads on a single-core CPU, I think the total CPU used time should not be more than the time interval, because the CPU could be used by only 1 thread at a time, so the CPU usage percentage should not be more than 100%. Hope it is helpful. thanks.

------------------------------
KAI DING

Original Message:
Sent: Fri June 26, 2020 09:13 AM
From: Samuel Pizarro
Subject: CPU utilization on Multi-Partitioned systems - Higher than 100%

Hi @KAI DING

Thanks very much for the followup and feedback . Good to hear that this has been addressed on new DMC tool.  Our customer is still evaluating the optoin to upgrade to DMC, so we can't really test that in a real workload scenario.  

Just curious,  can you share what was the previous formula used by DSM,  to calculate this CPU % utilization ?  
I have the impression that it was not being normalized taking consideration the number of cores per db-partition available in the system.  So,  if you dont take the # of cores into consideration,   you have the chances of running more than 100%, as multiple threads are running in parallel.   

Regards​

Hi Team

This is related to a customer question on how to interpret several places in DSM and it's reports showing CPU utilization % getting higher than 100%. 

This discussion has started on internal IBM slack channel,  and per @KAI DING request, I am posting the issue here, so it can be tracked for DMC as well to make sure this is not happening or to be "fixed".

The CPU% metrics going higher than 100% can be seen in several reports and monitor pages on DSM like pictures bellow:
​Monitor / Throughput / WLM Workload

 

WLM Service class:

db-Partition Summary:

On Overview Reports: 

On Workload Manager Reports,  same metrics can be seen as well..  

Regards