WL#5316: InnoDB Information Schema Metrics Table
Status: Complete
InnoDB Information Schema Metrics Table provides a generic resource and performance monitoring system for InnoDB. This worklog also addresses BUG#49000: Export number of rollbacks, long lock waits and deadlocks from InnoDB
Overview
This Specification documents the Information Schema Metrics Table feature in
InnoDB. The feature is to provide a generic resource and performance monitoring
system with minimum performance impact for Innodb. With such system, various
resource and performance related monitoring counters can be added by feature
developer and/or module owners, providing users/DBAs a quick overview of system
resource usage as well as performance statistics in their interested areas. This
functionality shall compliment the performance monitoring feature from
Performance Schema and provide user/developer comprehensive overview of the
system status and health.
Motivation and User Value of Proposed Feature
The monitor/metrics and monitoring mechanism have been widely used for server
monitoring and system tuning. "Monitors" are sets of "probes" planted in the
server internal modules to obtain critical resource and performance information.
A quick overview of the monitoring information can provide users a overall
snapshot of the health of the server. A deep drill down of the data can help
users, DBAs as well as developers to locate performance bottlenecks, resource
shortage and even identify potential defects in the system.
Competitive Summary
Table monitors/performance metrics are available in almost every major database
system. Many large scale DBMS systems such as DB2, Oracle, SQL server have
extensive monitor/metrics systems focus on a variety of sub-modules of the
system. However, the set of monitors varies depending on each implementation of
the server, and thus is very much system dependent.
In fact, the monitoring output can have different audiences. Many counters can
be used by DBAs to determine various resource configuration parameters, and
system performances and many other counters are used internally for performance
groups to diagnostic code level bottlenecks. So the output can be extensive and
detail as we seen in the above example.
MySQL also created "Performance Schema" engine for server to run time stats
collection, we have done work to support such work, please refer to
performance_schema for more information.
Requirements for the Proposed Feature
The current project is to implement a basic infrastructure for the monitoring
system in Innodb, so developers can add various monitor counters along with
their projects quickly. Here are the requirement for the project:
1. Information Schema Metrics tables - Provide Information Schema Metrics table
as the main interface with users. There will be one single metrics table for all
monitor counters. In the future, as the number of counters increases, we might
consider additional metrics tables for sub-module. For now, one single metrics
table provides users a concise overview of the server.
2. Monitor Counter Infrastructure - Provide infrastructure that facilitates
future addition of monitor counters. The feature shall hide the Information
Schema details from future monitor counter addition. Once the monitor system is
in place, developers would just need to add monitor counter definition and their
counting location for additional monitor counters, and do not need to worry
about display and control of these monitor counters.
3. Controls for Monitor Counter - We will provide monitor counter "start",
"stop", "reset" and "reset all" capabilities in the granularity of individual
counters. So users can start a particular monitor counter, stop it after certain
duration of data collection, and view the result, or they can leave the counter
on, and sample the result time from time.
4. Basic set of monitor counter groups and monitor counter - Will add a basic
set of monitor modules for various system modules, and a basic monitor counters
for each module.
The following are NOT requirements of this feature:
1. Comprehensive Monitor Counters -The main focus of the project is to provide
an infrastructure for the monitoring metrics system, specific monitor counters
are not the focus of the project. However, a fair number of monitor counters in
different subsystem will be added. We encourage feature owners to add any
additional monitor counters as their projects evolve.
5. Access Control - Currently the "PROCESS" privilege is required to display all
Information Schema data. We will continue this access control for the metrics
table. Some counters might be system specific, however, users have the choice
not to turn them on. However, since monitor counters are server wide global
counters, the sampling privilege should mostly restricted to DBAs.
User Interface
The Information Schema Metrics Table has following column names (datatype and
description)
NAME string counter name
SUBSYSTEM string the module or the feature the metric pertains to
COUNT int64 value since the counter is enabled
MAX_COUNT int64(nullable) max value since the counter is enabled
MIN_COUNT int64(nullable) min value since the counter is enabled
AVG_COUNT int64(nullable) average since the counter is enabled
COUNT_RESET int64 counter value since last reset
MAX_COUNT_RESET int64 (nullable) max value since last reset
MIN_COUNT_RESET int (nullable) min value since last reset
AVE_COUNT_RESET float(nullable) avg value since last reset
TIME_ENABLED timestamp (nullable) Timestamp of last start
TIME_DISABLED timestamp (nullable) Timestamp of last stop
TIME_ELAPSED int64 time elapsed since counter started (in seconds)
TIME_RESET timestamp (nullable) Timestamp of last stop
STATUS string whether the counter is still running or stopped
TYPE string describes whether the counter is incremental
counter or current resource
COMMENT string additional description
Control System
* We provide four operations for the Monitor Counters:
1) Start, this is to start the monitor counting since last time stopped. The
value is accumulative, and does not reset.
2) Stop, this is to stop the monitor counting.
3) Reset, this is to reset the monitor counter to zero, so fields
"COUNT_RESET" start the counting again.
4) Reset All, this is to reset all monitor counter values.
In addition, the control can be applied to a group of counters by specifying the
module name instead of counter name to above set command. More information for
modules will be discussed later.
Monitor Counter Operation
* Only following monitor counter operations can be performed,
1) The counter can be incremented or decremented
2) The counter can be incremented or decremented by a value
3) The counter can be set to a particular value (counter resetting is a
control system operation).
Counter Modules Monitor counters are grouped into modules. Each module comes
with a module name started with "module_", and we can turn on/off/reset all
counters in a module by specifying its name to set them to the global control
variables.
Following counter modules are provided:
1 module_metadata
2 module_lock
3 module_buffer
4 module_buf_page
5 module_os
6 module_trx
7 module_purge
8 module_log
9 module_compress
10 module_compress
11 module_file
12 module_index
13 module_adaptive_hash
14 module_ibuf_system
15 module_srv
16 module_dml
17 module_ddl
External User Interfaces
For external users, as the examples shown above, user interfaces are the global
variables to turn on/off/reset the counter, here is a summary of the commands
for the metrics table:
To turn on the counter:
mysql> set global innodb_monitor_enable = counter_name;
To turn off the counter:
mysql> set global innodb_monitor_disable = counter_name;
To reset the counter:
mysql> set global innodb_monitor_reset = counter_name;
To reset all the values:
mysql> set global innodb_monitor_reset_all = counter_name;
Please note, we support wildcard match for the counter name, user has to include
at least one wildcard match character "%"in the counter_name string.
To view the result:
mysql> select * from information_schema.innodb_metrics where metric_name =
counter_name
"all" is a key word, that allows to turn on/off/reset "all" counters.
mysql> set global innodb_monitor_reset_all = all;
Query OK, 0 rows affected (0.00 sec)
or user can use one or more "%" to turn all all monitors:
mysql> set global innodb_monitor_reset_all = "%";
Query OK, 0 rows affected (0.00 sec)
Turn on monitor "dml_num_inserts"
mysql> set global innodb_monitor_enable = dml_num_inserts;
Query OK, 0 rows affected (0.00 sec)
or user can do so also through wildcard match:
mysql> set global innodb_monitor_enable = "dml_num_i%"
Check the values, notice if monitor is in the "started" state, the "stop_time"
is the time when this query is processed
mysql> select * from information_schema.innodb_metrics
where name="dml_inserts" \G
*************************** 1. row ***************************
NAME: dml_inserts
SUBSYSTEM: dml
COUNT: 0
MAX_COUNT: 0
MIN_COUNT: NULL
AVG_COUNT: 0
COUNT_RESET: 0
MAX_COUNT_RESET: 0
MIN_COUNT_RESET: NULL
AVG_COUNT_RESET: NULL
TIME_ENABLED: 2011-02-14 19:20:15
TIME_DISABLED: NULL
TIME_ELAPSED: 27
TIME_RESET: NULL
STATUS: enabled
TYPE: status_counter
COMMENT: Number of rows inserted
1 row in set (0.01 sec)
Insert three rows, and check the counters again:
mysql> insert into test values(9);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test values(10);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test values(11);
Query OK, 1 row affected (0.00 sec)
mysql> select * from information_schema.innodb_metrics where
name="dml_inserts" \G
*************************** 1. row ***************************
NAME: dml_inserts
SUBSYSTEM: dml
COUNT: 3
MAX_COUNT: 3
MIN_COUNT: NULL
AVG_COUNT: 0.030303030303030304
COUNT_RESET: 3
MAX_COUNT_RESET: 3
MIN_COUNT_RESET: NULL
AVG_COUNT_RESET: NULL
TIME_ENABLED: 2011-02-14 19:20:15
TIME_DISABLED: NULL
TIME_ELAPSED: 99
TIME_RESET: NULL
STATUS: enabled
TYPE: status_counter
COMMENT: Number of rows inserted
1 row in set (0.00 sec)
Let's reset the counter, notice we could also specify "module_dml" to reset all
counters in DML module
mysql> set global innodb_monitor_reset = module_dml;
Query OK, 0 rows affected (0.00 sec)
Let's select only related columns, notice "count_reset" and
"max_count_reset" are reset:
mysql> select NAME, COUNT, MAX_COUNT, COUNT_RESET,
-> MAX_COUNT_RESET, TIME_ENABLED, TIME_DISABLED, STATUS
-> from information_schema.innodb_metrics
-> where name="dml_inserts" \G
*************************** 1. row ***************************
NAME: dml_inserts
COUNT: 3
MAX_COUNT: 3
COUNT_RESET: 0
MAX_COUNT_RESET: 0
TIME_ENABLED: 2011-02-14 19:20:15
TIME_DISABLED: NULL
STATUS: enabled
1 row in set (0.00 sec)
While the monitor counter is "started", we cannot use
"innodb_monitor_reset" to reset all values, such as "COUNT"
and "MAX(MIN)_COUNT", only COUNT_RESET" can be reset to 0.
Following message will be printed to error log:
mysql> set global innodb_monitor_reset_all = module_dml;
Query OK, 0 rows affected (0.00 sec)
Following printed in errorlog:
InnoDB: Cannot reset all values for a monitor counter while it is on. Please
turn it off and retry.
Only after you turn off the monitor counter, this reset_all command can be used:
mysql> set global innodb_monitor_disable = module_dml;
Query OK, 0 rows affected (0.00 sec)
mysql> set global innodb_monitor_reset_all = module_dml;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from information_schema.innodb_metrics where
name="dml_inserts"\G
*************************** 1. row ***************************
NAME: dml_inserts
SUBSYSTEM: dml
COUNT: 0
MAX_COUNT: NULL
MIN_COUNT: NULL
AVG_COUNT: NULL
COUNT_RESET: 0
MAX_COUNT_RESET: NULL
MIN_COUNT_RESET: NULL
AVG_COUNT_RESET: NULL
TIME_ENABLED: NULL
TIME_DISABLED: NULL
TIME_ELAPSED: NULL
TIME_RESET: NULL
STATUS: disabled
TYPE: status_counter
COMMENT: Number of rows inserted
1 row in set (0.01 sec)
While the monitor is in the "disabled" state, its counting activity would
completely stop:
mysql> insert into test values(9);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test values(9);
Query OK, 1 row affected (0.01 sec)
mysql> select * from information_schema.innodb_metrics where
name="dml_num_inserts"\G
*************************** 1. row ***************************
NAME: dml_inserts
SUBSYSTEM: dml
COUNT: 0
MAX_COUNT: NULL
MIN_COUNT: NULL
AVG_COUNT: NULL
COUNT_RESET: 0
MAX_COUNT_RESET: NULL
MIN_COUNT_RESET: NULL
AVG_COUNT_RESET: NULL
TIME_ENABLED: NULL
TIME_DISABLED: NULL
TIME_ELAPSED: NULL
TIME_RESET: NULL
STATUS: disabled
TYPE: status_counter
COMMENT: Number of rows inserted
1 row in set (0.01 sec)
Only if you turn on the monitor, the counter will continue:
mysql> set global innodb_monitor_enable =dml_inserts;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test values(9);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test values(9);
Query OK, 1 row affected (0.00 sec)
mysql> select * from information_schema.innodb_metrics where
name="dml_inserts"\G
*************************** 1. row ***************************
NAME: dml_inserts
SUBSYSTEM: dml
COUNT: 2
MAX_COUNT: 2
MIN_COUNT: NULL
AVG_COUNT: 0.10526315789473684
COUNT_RESET: 2
MAX_COUNT_RESET: 2
MIN_COUNT_RESET: NULL
AVG_COUNT_RESET: NULL
TIME_ENABLED: 2011-02-14 19:25:11
TIME_DISABLED: NULL
TIME_ELAPSED: 19
TIME_RESET: NULL
STATUS: enabled
TYPE: status_counter
COMMENT: Number of rows inserted
1 row in set (0.00 sec)
If a monitor is already turned on, if we try to turn it on again, the operation
will fail, unless user uses a wildcard match which we assume user wants to turn
on all affected monitors indiscriminately. This is to prevent different users to
turn on counters that has been used, and thus potentially reset the start time
and value for the counter.
mysql> set global innodb_monitor_enable = module_dml;
Query OK, 0 rows affected (0.00 sec)
2:14:40 [Warning] Monitor dml_num_inserts already turned on.
mysql> select name, status from information_schema.innodb_metrics where name
like "dml%" \G
*************************** 1. row ***************************
name: dml_num_reads
status: enabled
*************************** 2. row ***************************
name: dml_num_inserts
status: enabled
*************************** 3. row ***************************
name: dml_num_deletes
status: disabled
*************************** 4. row ***************************
name: dml_updates
status: disabled
4 rows in set (0.00 sec)
In such scenario, the best option will be turn off all counters in the module
and turn them on again:
mysql> set global innodb_monitor_disable = module_dml;
Query OK, 0 rows affected (0.00 sec)
mysql> set global innodb_monitor_enable = module_dml;
mysql> select name, status from information_schema.innodb_metrics where name
-> like "dml%" \G
*************************** 1. row ***************************
name: dml_num_reads
status: enabled
*************************** 2. row ***************************
name: dml_num_inserts
status: enabled
*************************** 3. row ***************************
name: dml_num_deletes
status: enabled
*************************** 4. row ***************************
name: dml_updates
status: enabled
4 rows in set (0.00 sec)
And then the counting operation will be started on all monitor counter in the group:
mysql> update test set a =10 where a = 9;
Query OK, 5 rows affected (0.00 sec)
Rows matched: 5 Changed: 5 Warnings: 0
mysql> select name, count, max_count, count_reset,
-> max_count_reset, time_enabled, time_disabled, status
-> from information_schema.innodb_metrics
-> where name like "dml%" \G
*************************** 1. row ***************************
name: dml_reads
count: 5
max_count: 5
count_reset: 5
max_count_reset: 5
time_enabled: 2011-02-14 19:29:57
time_disabled: NULL
status: enabled
*************************** 2. row ***************************
name: dml_inserts
count: 2
max_count: 2
count_reset: 2
max_count_reset: 2
time_enabled: 2011-02-14 19:25:11
time_disabled: NULL
status: enabled
*************************** 3. row ***************************
name: dml_deletes
count: 0
max_count: 0
count_reset: 0
max_count_reset: 0
time_enabled: 2011-02-14 19:29:57
time_disabled: NULL
status: enabled
*************************** 4. row ***************************
name: dml_updates
count: 5
max_count: 5
count_reset: 5
max_count_reset: 5
time_enabled: 2011-02-14 19:29:57
time_disabled: NULL
status: enabled
4 rows in set (0.00 sec)
There are some counters are really not "counter", most of them are resource
related, and display the amount of resource we uses. The total number of buffer
page is a good example. For these counters, we only display the current value,
and in the "type" column, it will display as "current_value" instead of "counters":
mysql> set global innodb_monitor_enable = buffer_pool_total_page;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from information_schema.innodb_metrics where name =
"buffer_pool_pages_total"\G
*************************** 1. row ***************************
NAME: buffer_pool_pages_total
SUBSYSTEM: buffer
COUNT: 512
MAX_COUNT: 512
MIN_COUNT: 512
AVG_COUNT: NULL
COUNT_RESET: 512
MAX_COUNT_RESET: 512
MIN_COUNT_RESET: 512
AVG_COUNT_RESET: NULL
TIME_ENABLED: 2011-02-14 19:20:15
TIME_DISABLED: NULL
TIME_ELAPSED: 702
TIME_RESET: NULL
STATUS: enabled
TYPE: value
COMMENT: Total buffer pool size in pages (innodb_buffer_pool_pages_total)
Turn on monitor counters with server configure files:
We also support turning on monitor counters through server configure file. Please
use "loose-innodb_monitor_enable" option to specify the monitor
counters/groups you want to turn on by default at server start up. The count
name can be separated by ";", "," or space:
loose-innodb_monitor_enable="metadata_table_opened;lock%"
Finally, there are a few rules regarding Wildcard match with counter names.
1) Wildcard match only applies to individual counters and does not applies to
modules (except for module_buf_page which can be turned on only as a module).
2) At least one "%" should appear in the match string for the server to switch
to wildcard match. "%" matches multiple character and "_" matches single
character. However if the string contains only "_" but with no "%", wildcard
match will not be turned on.
3) Single or multiple "%" means all counters are selected.
4) User can supplies wildcard match string in the server configure file too:
loose-innodb_monitor_enable="lock%"
===========================================================================
Implementation: Design and Details
1) The monitor counter control system
One main design issue is how we implement the monitor control system, ie. how to
turn on/off and reset the counters.
For the existing information_schema.innodb_cmp table, we have a corresponding
information_schema.innodb_cmp_reset table. A selection of innodb_cmp_reset table
will reset the cmp values.
However, in our current design, we have many different counters in one single
table, we would like to be able to control each individual counter separately
without introducing additional tables. So we need to look outside of Information
Schema table for the solution.
Our design still base on the "set" command, however we will have four set
commands, one for turn on counters, one for turn off and two for resetting the
counter. Their set values are the counters to be operated on.
For example, as shown in last section, we could use following commands to
start, stop and reset them:
# use "innodb_monitor_enable" to turn on monitor count
set global innodb_monitor_enable = dml_updates;
# use "innodb_monitor_disable" to turn off monitor
set global innodb_monitor_disable = dml_updates;
# use " innodb_monitor_reset" to reset monitor
set global innodb_monitor_reset = dml_updates;
# use "innodb_monitor_reset_all" to reset counter since the counter
turned on.
set global innodb_monitor_enable = dml_updates;
set global innodb_monitor_disable = dml_updates;
set global innodb_monitor_reset = dml_updates;
set global innodb_monitor_reset_all = dml_updates;
Each time the innodb_enable(disable)_monitor_counter global variable are used,
we will register the status (enabled/disabled) of the monitor counter in a bit
array. So we could know which monitor counter has been turned on, and which has
been turned off:
/* This monitor set table is a bitmap records whether a particular monitor
counter is turned on or off */
extern ib_uint32_t monitor_set_tbl[NUM_MONITOR / 32 + 1];
/* Macros to turn on/off the control bit in monitor_set_tbl for a monitor
counter option. */
#define MONITOR_ON(monitor) \
(monitor_set_tbl[monitor / 32] |= (1 << (monitor % 32)))
#define MONITOR_OFF(monitor) \
(monitor_set_tbl[monitor / 32] &= ~(1 << (monitor % 32)))
/* Check whether the requested monitor is turned on/off */
#define MONITOR_IS_ON(monitor) \
(monitor_set_tbl[monitor / 32] & (1 << (monitor % 32)))
The "monitor_set_tbl" is a bit array uses one bit for each counter. If the bit
is set, then it means the particular monitor counter has been turned on.
Of course, each global variable can only hold value from the most recent set
command, its value only represent the "last" counter we operated on. So if you
do "select @@innodb_monitor_counter_on", it only shows the most recent turned on
counters. For comprehensive counter's status, please select the "status" field
in the information schema metrics table.
User Privilege Monitor counters are system counters, in theory, they are used by
DBAs to probe into the the system. However, different from many commercial DBMS,
we do not have strict access control to limit this option to DBA only. However,
we do want to make user aware certain counters are turned on, and some other
users might collecting data.
So user cannot "turn on" and "reset all" a counter/a module when a counter is
already turned on. They must intentionally turn off a counter before they can
turn on/reset it. However, they still can "reset" a counter/a module while it is
on.
2) Internal Design
A new srv0mon.h file was added. And two monitor structures are defined in this
file. One is "monitor_value_t" which contains "dynamic" counter values for each
counter. The other is "monitor_info_t", which contains "static" information
(counter name, desc etc.) for each counter. In addition, an enum datatype
"monitor_id_t" is also defined, it identify each monitor with an internally used
symbol (monitor id), whose integer value indexes into above two structure for
its dynamic and static information.
/** Structure containing the actual values of a monitor counter. */
struct monitor_value_struct {
ib_time_t mon_start_time; /*!< Start time of monitoring */
ib_time_t mon_stop_time; /*!< Stop time of monitoring */
ib_time_t mon_reset_time; /*!< Time counter resetted */
lint mon_value; /*!< Current counter Value */
lint mon_max_value; /*!< Current Max value */
lint mon_min_value; /*!< Current Min value */
lint mon_value_reset;/*!< value at last reset */
lint mon_max_value_start; /*!< Max value since start */
lint mon_min_value_start; /*!< Min value since start */
lint mon_start_value;/*!< Value at the start time */
lint mon_last_value; /*!< Last set of values */
monitor_running_t mon_status; /* whether monitor still running */
};
The average value is not included, it can be calculated dynamically.
/* struct monitor_info describes the basic/static information
about each monitor counter. */
typedef struct monitor_info_struct {
const char* monitor_name; /* Monitor name */
const char* monitor_module; /* Sub Module the monitor belongs to */
const char* monitor_desc; /* Brief desc of monitor counter */
int monitor_type; /* Type of Monitor Info */
int monitor_id; /* Monitor ID as defined in enum monitor_id_t */
} monitor_info_t;
To add a new monitor counter, just add it to the innodb_counter[] array under
its corresponding module:
/* This array defines basic static information of monitor counters,
including each monitor's name, sub module it belongs to, a short
description and its property/type and corresponding monitor_id. */
static monitor_info_t innodb_counter_info[] =
{
/* A dummy item to mark the module start, this is
to accomodate the default value (0) set for the
global variables with the control system. */
{"module_start", "module_start", "module_start",
MONITOR_MODULE, MONITOR_DEFAULT_START},
/* ========== Counters for Server Metadata ========== */
{"module_metadata", "Server Metadata", "Server Metadata",
MONITOR_MODULE, MONITOR_MODULE_METADATA},
{"metadata_table_opened", "Server Metadata",
"Number of table handlers opened", 0, MONITOR_TABLE_OPEN},
{"metadata_table_closed", "Server Metadata",
"Number of table handlers closed", 0, MONITOR_TABLE_CLOSE},
.....
.....
.....
/* ========== Counters for DML operations ========== */
{"module_dml", "DML", "Statistics for DMLs",
MONITOR_MODULE, MONITOR_MODULE_DMLSTATS},
{"dml_num_reads", "DML", "Number of rows read",
MONITOR_EXISTING, MONITOR_OLVD_ROW_READ},
{"dml_num_inserts", "DML", "Number of rows inserted",
MONITOR_EXISTING, MONITOR_OLVD_ROW_INSERTED},
{"dml_num_deletes", "DML", "Number of rows deleted",
MONITOR_EXISTING, MONITOR_OLVD_ROW_DELETED},
{"dml_updates", "DML", "Number of rows updated",
MONITOR_EXISTING, MONITOR_OLVD_ROW_UPDTATED},
/* ========== To turn on/off reset all counters ========== */
{"all", "All Counters", "Turn on/off and reset all counters",
MONITOR_MODULE, MONITOR_ALL_COUNTER}
};
And following Macros defines the Monitor Counter operations (INC, DEC and SET)
are used by developer to "plant" into code for monitor counting.
1) MONITOR_INC(monitor)
2) MONITOR_DEC(monitor)
3) MONITOR_INC_NOCHECK(monitor)
4) MONITOR_DEC_NOCHECK(monitor)
5) MONITOR_SET
6) MONITOR_INC_VALUE_CUMULATIVE
7) MONITOR_INC_VALUE
8) MONITOR_DEC_VALUE
9) MONITOR_INC_TIME_IN_MICRO_SECS
The second and the last step to add a monitor counter is to put the
MONITOR_INC(), MONITOR_DEC()or MONITOR_SET macros in their appropriate places.
And developer will not need to worry about information schema table as well as
the global variable values, as they are all being done automatically.
Counter Synchronization
The monitor counter does not come with mutex protection as part of performance
consideration, atomic increment/update is used if platform supports. User should
"plant" such probes in appropriate places that already have high level
concurrency controls.
Global Variables
Four global variables are added as control of the monitor counters, for turn
on/off/reset the counters:
static MYSQL_SYSVAR_STR(monitor_enable, innobase_enable_monitor_counter,
PLUGIN_VAR_RQCMDARG,
"Turn on a monitor counter",
innodb_monitor_on_validate,
innodb_monitor_on_update, NULL);
static MYSQL_SYSVAR_STR(monitor_disable, innobase_disable_monitor_counter,
PLUGIN_VAR_RQCMDARG,
"Turn off a monitor counter",
innodb_monitor_off_validate,
innodb_monitor_off_update, NULL);
static MYSQL_SYSVAR_STR(monitor_reset, innobase_reset_monitor_counter,
PLUGIN_VAR_RQCMDARG,
"Reset a monitor value",
innodb_monitor_reset_validate,
innodb_monitor_reset_update, NULL);
static MYSQL_SYSVAR_STR(monitor_reset_all,
innobase_reset_all_monitor_counter,
PLUGIN_VAR_RQCMDARG,
"Reset all values for a monitor counter",
innodb_monitor_reset_validate,
innodb_monitor_reset_all_update, NULL);
In addition, as mentioned in last section, we are also considering counter
grouping, for example users want to turn on all counters for lock module. They
can specify the group/module name ("module_lock") for the control, which will
turn on all counters in the lock modules.
Internal Representation of Monitor Counter
Internally, monitor counters are identified by its "monitor id", ie., its tag in
the enumeration "monitor_id_t". Developers use these tags to identify each
counter internally. For example, if we want to increment the server_table_open
counter, we will put MONITOR_INC(MON_TABLE_OPEN) wherever the table is opened.
/** This enumeration defines internal monitor identifier used internally
to identify each particular counter. Its value indexes into two arrays,
one is the "innodb_counter_value" array which records actual monitor
counter values, the other is "innodb_counter_info" array which describes
each counter's basic information (name, desc etc.). A couple of
naming rules here:
1) If the monitor defines a module, it starts with MONITOR_MODULE
2) If the monitor uses exisitng counters from "status variable", its ID
name shall start with MONITOR_OVLD
Please refer to "innodb_counter_info" in srv/srv0mon.c for detail
information for each monitor counter */
enum monitor_id_value {
/* This is to identify the default value set by the metrics
control global variables */
MONITOR_DEFAULT_START = 0,
/* Start of Metadata counter */
MONITOR_MODULE_METADATA,
MONITOR_TABLE_OPEN,
MONITOR_TABLE_CLOSE,
....
/* Lock manager related counters */
MON_MODULE_LOCK,
MON_DEADLOCK,
MON_TIMEOUT,
MON_LOCKREC_WAIT,
MON_NUM_RECLOCK_REQ,
MON_RECLOCK_CREATED,
MON_RECLOCK_REMOVED,
MON_MUM_RECLOCK,
MON_TABLELOCK_CREATED,
MON_TABLELOCK_REMOVED,
MON_NUM_TABLELOCK,
/* Buffer and I/O related counters*/
MON_MODULE_BUFFER,
MON_PAGES_READ,
MON_PAGES_WRITTEN,
MON_PENDING_READ,
MON_DIRTY_INFLUSH,
MON_MAX_IO_FLUSH,
/* Transaction related counters */
MON_MODULE_TRX,
MON_TRX_COMMIT,
MON_TRX_ABORT,
MON_TRX_ACTIVE,
MON_NUMROW_PURGE,
/* Recovery related counters */
MON_MODULE_RECOVERY,
MON_NUM_CHECKPOINT,
MON_LSN_FLUSHDISK,
MON_LSN_CHECKPOINT,
MON_LSN_CURRENT,
MON_PENDING_LOG_WRITE,
MON_PENDING_CHECKPOINT_WRITE,
MON_LOG_IO,
MON_PURGE_DML_DELAY,
MON_NUM_FLUSH_DIRTY,
MON_UNDO_SLOT,
/* Page Manager related counters */
MON_MODULE_PAGE,
MON_PAGE_COMPRESS,
MON_PAGE_DECOMPRESS,
/* Index related counters */
MON_MODULE_INDEX,
MON_INDEX_SPLIT,
MON_INDEX_MERGE,
/* This must be the last member */
NUM_MONITOR
} monitor_id_t;
Existing Counters from System Status Variables
There are some counters already existed in the Innodb system, and their values
are displayed through system status variable interfaces. For example, a global
variable "srv_buf_pool_write_requests" records the number of buffer write
requests, and its external variable name is "Innodb_buffer_pool_write_requests".
We would like to consolidate these variables into our information schema metrics
table, so user has one stop shop for all the monitoring information.
These existing global variables are not under any controls, so they cannot be
stopped, started and reset. To consolidate these variables into out monitor
metrics table system, we created addition members in the monitor
MONITOR_VALUE_TYPE, so we can record these existing global variable values when
we start/stop/reset their corresponding monitor counters. And their actual
values are derived from these recorded information.
For these counters, their monitor ID would starts with "MONITOR_OVLD_", for
example, the monitor ID for "Innodb_buffer_pool_write_requests" is
MONITOR_OVLD_BUF_POOL_WRITE_REQUEST, and its external name is
"buffer_write_request". For such counters, it also comes with MONITOR_EXISTING
flag. Its internal innodb_counter_info entry is like following:
* {"buffer_read_request", "Buffer", "Number of read requests",
o MONITOR_EXISTING, MONITOR_OVLD_BUF_POOL_READ_REQUESTS},
External Design / User Interface
In fact, this feature would have two type of clients. The external users will be
DBAs/developers who want to monitor the system with selected monitor counters.
The other is the developer who would like to add monitor counters into their
designs for peroformance study and monitoring.
Copyright (c) 2000, 2024, Oracle Corporation and/or its affiliates. All rights reserved.