Db2

Investigating  a high IO condition here and noticed that DMC has an issue to calculate the Physical Reads/min metrics at database level. 

It does not match with real OS metrics observations,  and do not match the metrics reported at buffer-pool level as well. 

On this particular system we are hitting a 24k IOPS condition in every 2 minutes in average.  

When we look at I/O Buffer-Pools metrics,  the values seems to be ok.  


From the above screenshot , the Windows Performance Monitor shows 8k IOPs for 3 different diks (The 3 db storage paths)   giving us a 24k IOPs in total. 

DMC reports 1,369,855 for one Bufferpool, and 318,901 for another one, summing them up, we have a total of   1,688,756 IOs/min, which give us a pretty similar value of 24 IOPs from windows metrics. 

But,  when looking at Database level,  physical reads/min  metrics sows a way lower value of just few k reads per min. 
I have reduced the monitor cycle to capture values for each 1 min. and the physical reads/min metric never breaches 70k reads/min, 

This graph is from Monitor, Database, Database usage page. 
The particular point from the same minute 11:45 from the previous view,  shows only 56k/min while the BPs one shows a value higher than 1M /min. 

Something is definitely wrong here at the database level metric. 

Regards

Samuel Pizarro
Db2 Database Administrator
Kyndryl 

Investigating  a high IO condition here and noticed that DMC has an issue to calculate the Physical Reads/min metrics at database level. 

It does not match with real OS metrics observations,  and do not match the metrics reported at buffer-pool level as well. 

On this particular system we are hitting a 24k IOPS condition in every 2 minutes in average.  

When we look at I/O Buffer-Pools metrics,  the values seems to be ok.  


From the above screenshot , the Windows Performance Monitor shows 8k IOPs for 3 different diks (The 3 db storage paths)   giving us a 24k IOPs in total. 

DMC reports 1,369,855 for one Bufferpool, and 318,901 for another one, summing them up, we have a total of   1,688,756 IOs/min, which give us a pretty similar value of 24 IOPs from windows metrics. 

But,  when looking at Database level,  physical reads/min  metrics sows a way lower value of just few k reads per min. 
I have reduced the monitor cycle to capture values for each 1 min. and the physical reads/min metric never breaches 70k reads/min, 

This graph is from Monitor, Database, Database usage page. 
The particular point from the same minute 11:45 from the previous view,  shows only 56k/min while the BPs one shows a value higher than 1M /min. 

Something is definitely wrong here at the database level metric. 

Regards

Samuel Pizarro
Db2 Database Administrator
Kyndryl 

Monitor, Database, Database usage page. If you want to check whole database, please switch from 'All user workloads' to 'All workloads' on the right side of page.

Investigating  a high IO condition here and noticed that DMC has an issue to calculate the Physical Reads/min metrics at database level. 

It does not match with real OS metrics observations,  and do not match the metrics reported at buffer-pool level as well. 

On this particular system we are hitting a 24k IOPS condition in every 2 minutes in average.  

When we look at I/O Buffer-Pools metrics,  the values seems to be ok.  


From the above screenshot , the Windows Performance Monitor shows 8k IOPs for 3 different diks (The 3 db storage paths)   giving us a 24k IOPs in total. 

DMC reports 1,369,855 for one Bufferpool, and 318,901 for another one, summing them up, we have a total of   1,688,756 IOs/min, which give us a pretty similar value of 24 IOPs from windows metrics. 

But,  when looking at Database level,  physical reads/min  metrics sows a way lower value of just few k reads per min. 
I have reduced the monitor cycle to capture values for each 1 min. and the physical reads/min metric never breaches 70k reads/min, 

This graph is from Monitor, Database, Database usage page. 
The particular point from the same minute 11:45 from the previous view,  shows only 56k/min while the BPs one shows a value higher than 1M /min. 

Something is definitely wrong here at the database level metric. 

Regards

Samuel Pizarro
Db2 Database Administrator
Kyndryl 

Sorry @Zhen Ling Yu

But that is not the case..   Do you really believe that the console (DMC monitoring workload)  would be contributing with this huge GAP ?

DMC workload is negligible compared with the user workload here.

The metrics between 'All user workloads' and  'All Workloads" filters are pretty much the same. 

User workload:   peak of 105,000/min   

And again,  if I take manual queries from mon_Get_database() table function, in the same interval  , physical reads goes ​​up to 1,300 Millions/min

Monitor, Database, Database usage page. If you want to check whole database, please switch from 'All user workloads' to 'All workloads' on the right side of page.

Investigating  a high IO condition here and noticed that DMC has an issue to calculate the Physical Reads/min metrics at database level. 

It does not match with real OS metrics observations,  and do not match the metrics reported at buffer-pool level as well. 

On this particular system we are hitting a 24k IOPS condition in every 2 minutes in average.  

When we look at I/O Buffer-Pools metrics,  the values seems to be ok.  


From the above screenshot , the Windows Performance Monitor shows 8k IOPs for 3 different diks (The 3 db storage paths)   giving us a 24k IOPs in total. 

DMC reports 1,369,855 for one Bufferpool, and 318,901 for another one, summing them up, we have a total of   1,688,756 IOs/min, which give us a pretty similar value of 24 IOPs from windows metrics. 

But,  when looking at Database level,  physical reads/min  metrics sows a way lower value of just few k reads per min. 
I have reduced the monitor cycle to capture values for each 1 min. and the physical reads/min metric never breaches 70k reads/min, 

This graph is from Monitor, Database, Database usage page. 
The particular point from the same minute 11:45 from the previous view,  shows only 56k/min while the BPs one shows a value higher than 1M /min. 

Something is definitely wrong here at the database level metric. 

Regards

Samuel Pizarro
Db2 Database Administrator
Kyndryl 

Sorry @Zhen Ling Yu

But that is not the case..   Do you really believe that the console (DMC monitoring workload)  would be contributing with this huge GAP ?

DMC workload is negligible compared with the user workload here.

The metrics between 'All user workloads' and  'All Workloads" filters are pretty much the same. 

User workload:   peak of 105,000/min   

And again,  if I take manual queries from mon_Get_database() table function, in the same interval  , physical reads goes ​​up to 1,300 Millions/min

Monitor, Database, Database usage page. If you want to check whole database, please switch from 'All user workloads' to 'All workloads' on the right side of page.

Investigating  a high IO condition here and noticed that DMC has an issue to calculate the Physical Reads/min metrics at database level. 

It does not match with real OS metrics observations,  and do not match the metrics reported at buffer-pool level as well. 

On this particular system we are hitting a 24k IOPS condition in every 2 minutes in average.  

When we look at I/O Buffer-Pools metrics,  the values seems to be ok.  


From the above screenshot , the Windows Performance Monitor shows 8k IOPs for 3 different diks (The 3 db storage paths)   giving us a 24k IOPs in total. 

DMC reports 1,369,855 for one Bufferpool, and 318,901 for another one, summing them up, we have a total of   1,688,756 IOs/min, which give us a pretty similar value of 24 IOPs from windows metrics. 

But,  when looking at Database level,  physical reads/min  metrics sows a way lower value of just few k reads per min. 
I have reduced the monitor cycle to capture values for each 1 min. and the physical reads/min metric never breaches 70k reads/min, 

This graph is from Monitor, Database, Database usage page. 
The particular point from the same minute 11:45 from the previous view,  shows only 56k/min while the BPs one shows a value higher than 1M /min. 

Something is definitely wrong here at the database level metric. 

Regards

Samuel Pizarro
Db2 Database Administrator
Kyndryl 

Sorry @Zhen Ling Yu

But that is not the case..   Do you really believe that the console (DMC monitoring workload)  would be contributing with this huge GAP ?

DMC workload is negligible compared with the user workload here.

The metrics between 'All user workloads' and  'All Workloads" filters are pretty much the same. 

User workload:   peak of 105,000/min   

And again,  if I take manual queries from mon_Get_database() table function, in the same interval  , physical reads goes ​​up to 1,300 Millions/min

Monitor, Database, Database usage page. If you want to check whole database, please switch from 'All user workloads' to 'All workloads' on the right side of page.

Investigating  a high IO condition here and noticed that DMC has an issue to calculate the Physical Reads/min metrics at database level. 

It does not match with real OS metrics observations,  and do not match the metrics reported at buffer-pool level as well. 

On this particular system we are hitting a 24k IOPS condition in every 2 minutes in average.  

When we look at I/O Buffer-Pools metrics,  the values seems to be ok.  


From the above screenshot , the Windows Performance Monitor shows 8k IOPs for 3 different diks (The 3 db storage paths)   giving us a 24k IOPs in total. 

DMC reports 1,369,855 for one Bufferpool, and 318,901 for another one, summing them up, we have a total of   1,688,756 IOs/min, which give us a pretty similar value of 24 IOPs from windows metrics. 

But,  when looking at Database level,  physical reads/min  metrics sows a way lower value of just few k reads per min. 
I have reduced the monitor cycle to capture values for each 1 min. and the physical reads/min metric never breaches 70k reads/min, 

This graph is from Monitor, Database, Database usage page. 
The particular point from the same minute 11:45 from the previous view,  shows only 56k/min while the BPs one shows a value higher than 1M /min. 

Something is definitely wrong here at the database level metric. 

Regards

Samuel Pizarro
Db2 Database Administrator
Kyndryl 

Sorry @Zhen Ling Yu

But that is not the case..   Do you really believe that the console (DMC monitoring workload)  would be contributing with this huge GAP ?

DMC workload is negligible compared with the user workload here.

The metrics between 'All user workloads' and  'All Workloads" filters are pretty much the same. 

User workload:   peak of 105,000/min   

And again,  if I take manual queries from mon_Get_database() table function, in the same interval  , physical reads goes ​​up to 1,300 Millions/min

Monitor, Database, Database usage page. If you want to check whole database, please switch from 'All user workloads' to 'All workloads' on the right side of page.

Investigating  a high IO condition here and noticed that DMC has an issue to calculate the Physical Reads/min metrics at database level. 

It does not match with real OS metrics observations,  and do not match the metrics reported at buffer-pool level as well. 

On this particular system we are hitting a 24k IOPS condition in every 2 minutes in average.  

When we look at I/O Buffer-Pools metrics,  the values seems to be ok.  


From the above screenshot , the Windows Performance Monitor shows 8k IOPs for 3 different diks (The 3 db storage paths)   giving us a 24k IOPs in total. 

DMC reports 1,369,855 for one Bufferpool, and 318,901 for another one, summing them up, we have a total of   1,688,756 IOs/min, which give us a pretty similar value of 24 IOPs from windows metrics. 

But,  when looking at Database level,  physical reads/min  metrics sows a way lower value of just few k reads per min. 
I have reduced the monitor cycle to capture values for each 1 min. and the physical reads/min metric never breaches 70k reads/min, 

This graph is from Monitor, Database, Database usage page. 
The particular point from the same minute 11:45 from the previous view,  shows only 56k/min while the BPs one shows a value higher than 1M /min. 

Something is definitely wrong here at the database level metric. 

Regards

Samuel Pizarro
Db2 Database Administrator
Kyndryl 

Hi @Samuel Pizarro

​I checked with Db2 team and for the metrics you are looking related to bufferpool read and write metrics. 

There are differences between how they are collected from MON_GET_DATABASE and MON_GET_WORKLOAD.  ​For MON_GET_DATABASE, it includes metrics from asynchronous read and write operations, e.g. from page cleaners and prefetchers.  And for MON_GET_WORKLOAD, it only includes metrics from fore ground agents.  See more in https://www.ibm.com/docs/en/db2/11.5?topic=elements-time-spent-monitor-element-hierarchy for asynchronous metrics for MON_GET_DATABASE.

Sorry @Zhen Ling Yu

But that is not the case..   Do you really believe that the console (DMC monitoring workload)  would be contributing with this huge GAP ?

DMC workload is negligible compared with the user workload here.

The metrics between 'All user workloads' and  'All Workloads" filters are pretty much the same. 

User workload:   peak of 105,000/min   

And again,  if I take manual queries from mon_Get_database() table function, in the same interval  , physical reads goes ​​up to 1,300 Millions/min

Monitor, Database, Database usage page. If you want to check whole database, please switch from 'All user workloads' to 'All workloads' on the right side of page.

Investigating  a high IO condition here and noticed that DMC has an issue to calculate the Physical Reads/min metrics at database level. 

It does not match with real OS metrics observations,  and do not match the metrics reported at buffer-pool level as well. 

On this particular system we are hitting a 24k IOPS condition in every 2 minutes in average.  

When we look at I/O Buffer-Pools metrics,  the values seems to be ok.  


From the above screenshot , the Windows Performance Monitor shows 8k IOPs for 3 different diks (The 3 db storage paths)   giving us a 24k IOPs in total. 

DMC reports 1,369,855 for one Bufferpool, and 318,901 for another one, summing them up, we have a total of   1,688,756 IOs/min, which give us a pretty similar value of 24 IOPs from windows metrics. 

But,  when looking at Database level,  physical reads/min  metrics sows a way lower value of just few k reads per min. 
I have reduced the monitor cycle to capture values for each 1 min. and the physical reads/min metric never breaches 70k reads/min, 

This graph is from Monitor, Database, Database usage page. 
The particular point from the same minute 11:45 from the previous view,  shows only 56k/min while the BPs one shows a value higher than 1M /min. 

Something is definitely wrong here at the database level metric. 

Regards

Samuel Pizarro
Db2 Database Administrator
Kyndryl 

Hi @Samuel Pizarro

​I checked with Db2 team and for the metrics you are looking related to bufferpool read and write metrics. 

There are differences between how they are collected from MON_GET_DATABASE and MON_GET_WORKLOAD.  ​For MON_GET_DATABASE, it includes metrics from asynchronous read and write operations, e.g. from page cleaners and prefetchers.  And for MON_GET_WORKLOAD, it only includes metrics from fore ground agents.  See more in https://www.ibm.com/docs/en/db2/11.5?topic=elements-time-spent-monitor-element-hierarchy for asynchronous metrics for MON_GET_DATABASE.

Sorry @Zhen Ling Yu

But that is not the case..   Do you really believe that the console (DMC monitoring workload)  would be contributing with this huge GAP ?

DMC workload is negligible compared with the user workload here.

The metrics between 'All user workloads' and  'All Workloads" filters are pretty much the same. 

User workload:   peak of 105,000/min   

And again,  if I take manual queries from mon_Get_database() table function, in the same interval  , physical reads goes ​​up to 1,300 Millions/min

Monitor, Database, Database usage page. If you want to check whole database, please switch from 'All user workloads' to 'All workloads' on the right side of page.

Investigating  a high IO condition here and noticed that DMC has an issue to calculate the Physical Reads/min metrics at database level. 

It does not match with real OS metrics observations,  and do not match the metrics reported at buffer-pool level as well. 

On this particular system we are hitting a 24k IOPS condition in every 2 minutes in average.  

When we look at I/O Buffer-Pools metrics,  the values seems to be ok.  


From the above screenshot , the Windows Performance Monitor shows 8k IOPs for 3 different diks (The 3 db storage paths)   giving us a 24k IOPs in total. 

DMC reports 1,369,855 for one Bufferpool, and 318,901 for another one, summing them up, we have a total of   1,688,756 IOs/min, which give us a pretty similar value of 24 IOPs from windows metrics. 

But,  when looking at Database level,  physical reads/min  metrics sows a way lower value of just few k reads per min. 
I have reduced the monitor cycle to capture values for each 1 min. and the physical reads/min metric never breaches 70k reads/min, 

This graph is from Monitor, Database, Database usage page. 
The particular point from the same minute 11:45 from the previous view,  shows only 56k/min while the BPs one shows a value higher than 1M /min. 

Something is definitely wrong here at the database level metric. 

Regards

Samuel Pizarro
Db2 Database Administrator
Kyndryl 

Hi @Samuel Pizarro

​I checked with Db2 team and for the metrics you are looking related to bufferpool read and write metrics. 

There are differences between how they are collected from MON_GET_DATABASE and MON_GET_WORKLOAD.  ​For MON_GET_DATABASE, it includes metrics from asynchronous read and write operations, e.g. from page cleaners and prefetchers.  And for MON_GET_WORKLOAD, it only includes metrics from fore ground agents.  See more in https://www.ibm.com/docs/en/db2/11.5?topic=elements-time-spent-monitor-element-hierarchy for asynchronous metrics for MON_GET_DATABASE.

Sorry @Zhen Ling Yu

But that is not the case..   Do you really believe that the console (DMC monitoring workload)  would be contributing with this huge GAP ?

DMC workload is negligible compared with the user workload here.

The metrics between 'All user workloads' and  'All Workloads" filters are pretty much the same. 

User workload:   peak of 105,000/min   

And again,  if I take manual queries from mon_Get_database() table function, in the same interval  , physical reads goes ​​up to 1,300 Millions/min

Monitor, Database, Database usage page. If you want to check whole database, please switch from 'All user workloads' to 'All workloads' on the right side of page.

Investigating  a high IO condition here and noticed that DMC has an issue to calculate the Physical Reads/min metrics at database level. 

It does not match with real OS metrics observations,  and do not match the metrics reported at buffer-pool level as well. 

On this particular system we are hitting a 24k IOPS condition in every 2 minutes in average.  

When we look at I/O Buffer-Pools metrics,  the values seems to be ok.  


From the above screenshot , the Windows Performance Monitor shows 8k IOPs for 3 different diks (The 3 db storage paths)   giving us a 24k IOPs in total. 

DMC reports 1,369,855 for one Bufferpool, and 318,901 for another one, summing them up, we have a total of   1,688,756 IOs/min, which give us a pretty similar value of 24 IOPs from windows metrics. 

But,  when looking at Database level,  physical reads/min  metrics sows a way lower value of just few k reads per min. 
I have reduced the monitor cycle to capture values for each 1 min. and the physical reads/min metric never breaches 70k reads/min, 

This graph is from Monitor, Database, Database usage page. 
The particular point from the same minute 11:45 from the previous view,  shows only 56k/min while the BPs one shows a value higher than 1M /min. 

Something is definitely wrong here at the database level metric. 

Regards

Samuel Pizarro
Db2 Database Administrator
Kyndryl 

Hi @Jason Sizto

​For DMC, we understand the Database page is the high level page for users to first eyeball and identify problems.  We will look into your suggestion on how we can distinguish mon_get_database and mon_get_workload discrepancies and how to make the representation clearer. 

Yeah, maybe in the right-upper corner combo, to select the workloads,,  have a new option to grab metrics from mon_ge_database instead..   "Entire Database" ,  or something like that.   
And..   as we now know,  that metrics from workloads does not have assync IOs ,  we should add a new plot just for that metric,  beyond physical IO. 

The the I/O pages,  the metrics are good.  

Only thing that I noticed,  is that  for Prefetchers tab,  the option to look back is blocked..   just realtime values are available..   

Regards 

​

Hi @Samuel Pizarro

​I checked with Db2 team and for the metrics you are looking related to bufferpool read and write metrics. 

There are differences between how they are collected from MON_GET_DATABASE and MON_GET_WORKLOAD.  ​For MON_GET_DATABASE, it includes metrics from asynchronous read and write operations, e.g. from page cleaners and prefetchers.  And for MON_GET_WORKLOAD, it only includes metrics from fore ground agents.  See more in https://www.ibm.com/docs/en/db2/11.5?topic=elements-time-spent-monitor-element-hierarchy for asynchronous metrics for MON_GET_DATABASE.

Sorry @Zhen Ling Yu

But that is not the case..   Do you really believe that the console (DMC monitoring workload)  would be contributing with this huge GAP ?

DMC workload is negligible compared with the user workload here.

The metrics between 'All user workloads' and  'All Workloads" filters are pretty much the same. 

User workload:   peak of 105,000/min   

And again,  if I take manual queries from mon_Get_database() table function, in the same interval  , physical reads goes ​​up to 1,300 Millions/min

Monitor, Database, Database usage page. If you want to check whole database, please switch from 'All user workloads' to 'All workloads' on the right side of page.

Investigating  a high IO condition here and noticed that DMC has an issue to calculate the Physical Reads/min metrics at database level. 

It does not match with real OS metrics observations,  and do not match the metrics reported at buffer-pool level as well. 

On this particular system we are hitting a 24k IOPS condition in every 2 minutes in average.  

When we look at I/O Buffer-Pools metrics,  the values seems to be ok.  


From the above screenshot , the Windows Performance Monitor shows 8k IOPs for 3 different diks (The 3 db storage paths)   giving us a 24k IOPs in total. 

DMC reports 1,369,855 for one Bufferpool, and 318,901 for another one, summing them up, we have a total of   1,688,756 IOs/min, which give us a pretty similar value of 24 IOPs from windows metrics. 

But,  when looking at Database level,  physical reads/min  metrics sows a way lower value of just few k reads per min. 
I have reduced the monitor cycle to capture values for each 1 min. and the physical reads/min metric never breaches 70k reads/min, 

This graph is from Monitor, Database, Database usage page. 
The particular point from the same minute 11:45 from the previous view,  shows only 56k/min while the BPs one shows a value higher than 1M /min. 

Something is definitely wrong here at the database level metric. 

Regards

Samuel Pizarro
Db2 Database Administrator
Kyndryl