Bug #98990 avg_count_reset for monitor set owner is always NULL in I_S.INNODB_METRICS
Submitted: 18 Mar 2020 11:41 Modified: 23 Apr 2020 14:56
Reporter: Fungo Wang (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:8.0.19 OS:Any
Assigned to: CPU Architecture:Any
Tags: avg_count_reset, innodb_metrics, moniotr set

[18 Mar 2020 11:41] Fungo Wang
Description:
The avg_count_reset for buffer_flush_batch_total_pages is kept with NULL value, which actually should not be NULL.

mysql> select name, status, type, time_enabled, count, max_count, avg_count, time_reset, count_reset, avg_count_reset, max_count_reset from information_schema.innodb_metrics where name in ('buffer_flush_batch_total_pages', 'buffer_flush_batches', 'buffer_flush_batch_pages');
+--------------------------------+---------+------------+---------------------+---------+-----------+-------------------+---------------------+-------------+--------------------+-----------------+
| name                           | status  | type       | time_enabled        | count   | max_count | avg_count         | time_reset          | count_reset | avg_count_reset    | max_count_reset |
+--------------------------------+---------+------------+---------------------+---------+-----------+-------------------+---------------------+-------------+--------------------+-----------------+
| buffer_flush_batch_total_pages | enabled | set_owner  | 2020-03-18 19:06:24 | 1882172 |   1882172 |              3021 | 2020-03-18 19:06:55 |     1796027 |               NULL |         1796027 |
| buffer_flush_batches           | enabled | set_member | 2020-03-18 19:06:24 |     623 |      NULL |                 1 | 2020-03-18 19:06:55 |         591 | 0.9983108108108109 |            NULL |
| buffer_flush_batch_pages       | enabled | set_member | 2020-03-18 19:06:24 |    8660 |      8995 | 13.90048154093098 | 2020-03-18 19:06:55 |        3191 |  5.390202702702703 |            3526 |
+--------------------------------+---------+------------+---------------------+---------+-----------+-------------------+---------------------+-------------+--------------------+-----------------+

Some background info about monitor set concept for innodb metrics, refer WL#5776

> 5) Added a new concept called monitor set, which associate a set of monitors
together. The "owner" monitor is marked as "MONITOR_SET_OWNER". We only check
the on/off status of monitor owner for monitor set on/off. Other monitors are
marked "MONITOR_SET_MEMBER". And "MONITOR_SET_OWNER" could be averaged by a
"MONITOR_SET_MEMBER" if specified member name in owner's define:

For example, in our case, following three counters are a set:

buffer_flush_batch_scanned
buffer_flush_batch_num_scan
buffer_flush_batch_scanned_per_call

And buffer_flush_batch_scanned is the set owner is averaged by
buffer_flush_batch_num_scan.

How to repeat:
1. disable and clear all monitor metrics
set global innodb_monitor_disable = all;
set global innodb_monitor_reset_all = all;

2. enable the buffer_flush_batch_total_pages set, and check values by reteriving I_S.INNODB_METRICS 
set global innodb_monitor_enable = 'buffer_flush_batch_total_pages';
set global innodb_monitor_enable = 'buffer_flush_batches';
set global innodb_monitor_enable = 'buffer_flush_batch_pages';

select name, status, type, time_enabled, count, max_count, avg_count, time_reset, count_reset, avg_count_reset, max_count_reset from information_schema.innodb_metrics where name in ('buffer_flush_batch_total_pages', 'buffer_flush_batches', 'buffer_flush_batch_pages');

mysql> select name, status, type, time_enabled, count, max_count, avg_count, time_reset, count_reset, avg_count_reset, max_count_reset from information_schema.innodb_metrics where name in ('buffer_flush_batch_total_pages', 'buffer_flush_batches', 'buffer_flush_batch_pages');
+--------------------------------+---------+------------+---------------------+-------+-----------+-----------+------------+-------------+-----------------+-----------------+
| name                           | status  | type       | time_enabled        | count | max_count | avg_count | time_reset | count_reset | avg_count_reset | max_count_reset |
+--------------------------------+---------+------------+---------------------+-------+-----------+-----------+------------+-------------+-----------------+-----------------+
| buffer_flush_batch_total_pages | enabled | set_owner  | 2020-03-18 19:21:07 | 53533 |     53533 |      3345 | NULL       |       53533 |            NULL |           53533 |
| buffer_flush_batches           | enabled | set_member | 2020-03-18 19:21:07 |    16 |      NULL |         1 | NULL       |          16 |            NULL |            NULL |
| buffer_flush_batch_pages       | enabled | set_member | 2020-03-18 19:21:08 |  3294 |      3375 |     219.6 | NULL       |        3294 |            NULL |            3375 |
+--------------------------------+---------+------------+---------------------+-------+-----------+-----------+------------+-------------+-----------------+-----------------+

The avg_count_reset filed in all rows are NULL, because we have not reset them yet, the NULL value is expected now.

3. reset the 3 metrics, and check values

set global innodb_monitor_reset = 'buffer_flush_batch_total_pages';
set global innodb_monitor_reset = 'buffer_flush_batches';
set global innodb_monitor_reset = 'buffer_flush_batch_pages';

select name, status, type, time_enabled, count, max_count, avg_count, time_reset, count_reset, avg_count_reset, max_count_reset from information_schema.innodb_metrics where name in ('buffer_flush_batch_total_pages', 'buffer_flush_batches', 'buffer_flush_batch_pages');

mysql> select name, status, type, time_enabled, count, max_count, avg_count, time_reset, count_reset, avg_count_reset, max_count_reset from information_schema.innodb_metrics where name in ('buffer_flush_batch_total_pages', 'buffer_flush_batches', 'buffer_flush_batch_pages');
+--------------------------------+---------+------------+---------------------+--------+-----------+--------------------+---------------------+-------------+-----------------+-----------------+
| name                           | status  | type       | time_enabled        | count  | max_count | avg_count          | time_reset          | count_reset | avg_count_reset | max_count_reset |
+--------------------------------+---------+------------+---------------------+--------+-----------+--------------------+---------------------+-------------+-----------------+-----------------+
| buffer_flush_batch_total_pages | enabled | set_owner  | 2020-03-18 19:21:07 | 519928 |    519928 |               3466 | 2020-03-18 19:23:29 |       28003 |            NULL |           28003 |
| buffer_flush_batches           | enabled | set_member | 2020-03-18 19:21:07 |    150 |      NULL |                  1 | 2020-03-18 19:23:29 |           8 |               1 |            NULL |
| buffer_flush_batch_pages       | enabled | set_member | 2020-03-18 19:21:08 |   6991 |      7014 | 46.919463087248324 | 2020-03-18 19:23:29 |        3502 |          437.75 |            3525 |
+--------------------------------+---------+------------+---------------------+--------+-----------+--------------------+---------------------+-------------+-----------------+-----------------+

You can check multiple times, the avg_count_reset for buffer_flush_batch_total_pages is always NULL, which should be count_reset(buffer_flush_batch_total_pages 28003) / count_reset (buffer_flush_batches = 3) = 3500 in the above output.

You need to give mysqld some load, such OLTP_RW, to make the values keep changing

Suggested fix:
In i_s_metrics_fill(), looks like we forgot to call `fields[METRIC_AVG_VALUE_RESET]->set_notnull();` in below branch.

1912      if (MONITOR_VALUE(monitor_info->monitor_related_id)) {
1913        OK(fields[METRIC_AVG_VALUE_RESET]->store(
1914            MONITOR_VALUE(count) /
1915                MONITOR_VALUE(monitor_info->monitor_related_id),
1916            FALSE));
1917      } else {

I'm not familiar with the table and field stuff, while after applying the below patch, it solves my problem :)

diff --git a/storage/innobase/handler/i_s.cc b/storage/innobase/handler/i_s.cc
index 7ce1579..4c46462 100644
--- a/storage/innobase/handler/i_s.cc
+++ b/storage/innobase/handler/i_s.cc
@@ -1914,6 +1914,7 @@ static int i_s_metrics_fill(
             MONITOR_VALUE(count) /
                 MONITOR_VALUE(monitor_info->monitor_related_id),
             FALSE));
+        fields[METRIC_AVG_VALUE_RESET]->set_notnull();
       } else {
         fields[METRIC_AVG_VALUE_RESET]->set_null();
       }

mysql> select name, status, type, time_enabled, count, max_count, avg_count, time_reset, count_reset, avg_count_reset, max_count_reset from information_schema.innodb_metrics where name in ('buffer_flush_batch_total_pages', 'buffer_flush_batches', 'buffer_flush_batch_pages');
+--------------------------------+---------+------------+---------------------+--------+-----------+--------------------+---------------------+-------------+-------------------+-----------------+
| name                           | status  | type       | time_enabled        | count  | max_count | avg_count          | time_reset          | count_reset | avg_count_reset   | max_count_reset |
+--------------------------------+---------+------------+---------------------+--------+-----------+--------------------+---------------------+-------------+-------------------+-----------------+
| buffer_flush_batch_total_pages | enabled | set_owner  | 2020-03-18 19:37:51 | 579907 |    579907 |               3411 | 2020-03-18 19:38:01 |      539714 |              3373 |          539714 |
| buffer_flush_batches           | enabled | set_member | 2020-03-18 19:37:51 |    170 |      NULL |                  1 | 2020-03-18 19:38:01 |         160 |                 1 |            NULL |
| buffer_flush_batch_pages       | enabled | set_member | 2020-03-18 19:37:52 |   8648 |     11923 | 51.171597633136095 | 2020-03-18 19:38:02 |        3192 | 20.07547169811321 |            6467 |
+--------------------------------+---------+------------+---------------------+--------+-----------+--------------------+---------------------+-------------+-------------------+-----------------+
[18 Mar 2020 14:44] MySQL Verification Team
Hi Mr. Wang,

Thank you for your bug report.

I have checked your report and it seems that you are correct.

Thank you also for your patch. However, we can not accept the patch, until you sign the OCA agreement with our company.

You will find all the facts about it in the comment that follows.
[18 Mar 2020 14:44] MySQL Verification Team
Thank you very much for your patch contribution, we appreciate it!

In order for us to continue the process of reviewing your contribution to MySQL, please send us a signed copy of the Oracle Contributor Agreement (OCA) as outlined in http://www.oracle.com/technetwork/community/oca-486395.html

Signing an OCA needs to be done only once and it's valid for all other Oracle governed Open Source projects as well.

Getting a signed/approved OCA on file will help us facilitate your contribution - this one, and others in the future.  

Please let me know, if you have any questions.

Thank you for your interest in MySQL.
[26 Mar 2020 11:17] Fungo Wang
Hi,

I have signed the OCA, and it has been approved.

Thanks.
[26 Mar 2020 13:47] MySQL Verification Team
Thank you Mr. Wang !!!!

This bug is now verified as reported and your significant contribution can be used.

Thanks again !!!!!!
[23 Apr 2020 14:56] Daniel Price
Posted by developer:
 
Fixed as of the upcoming 5.7.31, 8.0.21 release, and here's the proposed changelog entry from the documentation team:

The INNODB_METRICS table AVG_COUNT_RESET value for a counter defined as a
module owner reported NULL. The METRIC_AVG_VALUE_RESET field was
incorrectly marked as NULL. 

Thanks to Fungo Wang for the
contribution.
[24 Apr 2020 12:26] MySQL Verification Team
Thank you, Daniel.