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 |
+--------------------------------+---------+------------+---------------------+--------+-----------+--------------------+---------------------+-------------+-------------------+-----------------+
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 | +--------------------------------+---------+------------+---------------------+--------+-----------+--------------------+---------------------+-------------+-------------------+-----------------+