Transactions on InnoDB

In our 5.6 release, we introduced a new feature – “InnoDB Metrics Table” as part of our effort to extend InnoDB diagnostic and monitoring capability. The “Metrics Table” feature consolidates all InnoDB related Performance and Resource related counters (as of today, 176 counters) and makes them available through an information schema table. In addition, it gives the user the ability to control these counters for their monitoring need.

In following sections, we will go over the feature in detail and focus on its usage to get you started with “metrics counters”.

1) The user interface – InnoDB Metrics Table

Before this feature, InnoDB already had dozens of performance counters. They are exposed to the user through “MySQL System Variables”. These counters are permanent counters, and there are no external controls on them. However, they had become important probes that allow users to peek into the server running state and make appropriate decisions to tune the system to its best state.

In an effort to extend our system monitoring capability, we decided to consolidate the monitoring mechinery, and created a dedicated monitoring module. And the interface to the monitor counters will be a relational table, so users can perform SQL queries on the result. And thus we have the “information_schema.innodb_metrics” table as the interfaces of the “Metrics Table” feature.

The table has following 17 columns:

1.  NAME             string              counter name
2.  SUBSYSTEM        string              the module or the feature the counter pertains to
3.  COUNT            int64               value since the counter is enabled
4.  MAX_COUNT        int64(nullable)     max value since the counter is enabled
5.  MIN_COUNT        int64(nullable)     min value since the counter is enabled
6.  AVG_COUNT        int64(nullable)     average since the counter is enabled
7.  COUNT_RESET       int64              counter value since last reset operation
8.  MAX_COUNT_RESET   int64 (nullable)   max value since last reset
9.  MIN_COUNT_RESET   int (nullable)     min value since last reset
10. AVE_COUNT_RESET   float(nullable)    avg value since last reset
11. TIME_ENABLED      timestamp (nullable) timestamp of last start
12. TIME_DISABLED     timestamp (nullable) timestamp of last stop
13. TIME_ELAPSED      int64 (nullable)     time elapsed since counter started (in seconds)
14. TIME_RESET        timestamp (nullable) timestamp of last reset
15. STATUS            string             whether the counter is running or stopped
16. TYPE              string             describes whether the counter is incremental
                                         or resource counter
17. COMMENT           string             counter description

The columns are pretty much self explanatory. The most basic ones are the “NAME” and “COUNT” columns, which gives you the counter name and its value.

2) The Control Mechanism

In addition to allowing user to view counters through relational tables, we added four simple operation options to allow the user to control the counters, these options are:

1. Enable Counting- this is to start the monitor counter.
2. Disable Counting – this is to stop the monitor counter.
3. Reset – this is to reset the monitor counter to zero while the counter still in the “enabled” mode. Notice we have “COUNT” column and  “COUNT_RESET” column. “Reset” operation only resets the “COUNT_RESET” column.
4. Reset All, this operation has to be preceded by a “Disable” operation. It will reset all values, including both “COUNT” and  “COUNT_RESET” columns.

Above 4 operations are achieved through MySQL set option on 4 option variables:

• Enable – To enable or turn on the counter, use “innodb_monitor_enable”:

mysql> set global innodb_monitor_enable = counter_name;

• Disable  – To disable or turn off the counter, use “innodb_monitor_disable”:

mysql> set global innodb_monitor_disable = counter_name;

• Reset – To reset the counter, use “innodb_monitor_reset”:

mysql> set global innodb_monitor_reset = counter_name;

• Reset all – To reset all the values, use innodb_monitor_reset_all:

mysql> set global innodb_monitor_reset_all = counter_name;

Wild card match for counter name:

Note that you would have to state the counter name for above operations. To make things easy,  we support wildcard match for the “counter name”. So the user does not need to remember the exact name for the counter, he/she would just need to include at least one wildcard match character “%”in the counter_name string:

For example, if you would like to turn on monitor “dml_inserts”

You can use “dml_inserts” directly or simply use “dml_i%” or “dml_%”, the later will turn on other counters such as “dml_deletes” too:

1) mysql> set global innodb_monitor_enable = dml_inserts;

Query OK, 0 rows affected (0.00 sec)

2) mysql> set global innodb_monitor_enable = “dml_%”

Check the counter, notice its status is in the “enabled” state:

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)

Or if you want to turn on all monitors, you can simply use “%” for “counter_name”

mysql> set global innodb_monitor_reset_all = “%”;

Query OK, 0 rows affected (0.00 sec)

Finally, there are a few rules regarding Wildcard match with counter names.

• Wildcard match only applies to individual counters and does not applies to modules (a concept we will discuss later).
• 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.
• As shown above, single or multiple “%”  with no other characters means all counters are selected.
• User can supplies wildcard match string in the server configure file too: loose-innodb_monitor_enable=”lock%” (turn on monitors in server configure file is discussed in the next section)

Turn on monitor counters with server configure files or startup time options:

We also support turning on monitor counters through server configure file. Please use “innodb_monitor_enable” option to specify the monitor counters/groups you want to turn on at the server start up. The count name can be separated by “;”, “,” or space. For example, follow option turns on monitor counter “metadata_table_opened” and all counters start with “lock” prefix:

loose-innodb_monitor_enable=”metadata_table_opened;lock%”

Monitor Counter Modules:

Monitor counters are grouped in modules. And each monitor counter prefixed with its module name. For example, counter “dml_inserts” and “dml_deletes” and “dml_updates” all belong to the “DML” module. So far, we had about 19 modules are defined:

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_index
11 module_adaptive_hash
12 module_table_space
13 module_change_buffer
14 module_innodb
15 module_file
16 module_ibuf_system
17 module_dml
18 module_ddl
19 module_icp

We created these modules to make the counters more manageable. You can apply these module names directly to the 4 set options defined above, and the action will apply to all counters in the module. For example:

set global innodb_monitor_enable = “module_dml”

3) Examples

Let’s finish this quick tutorial with some examples. Let’s continue to look at the “dml_inserts” counter:

1) Enable the dml_insert counter

mysql> set global innodb_monitor_enable = "dml_i%"

2) 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)

3) Reset – 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 only “count_reset” and “max_count_reset” are reset, but not the “count” and “max_count” values:

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 “enabled”, we cannot use “innodb_monitor_reset_all” to reset all values, but we can apply “innodb_monitor_reset”, for which only “COUNT(MAX_COUNT)_RESET” would be reset to 0. If you do use “innodb_monitor_reset_all” while the counter is in enabled state, the 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)
InnoDB: Cannot reset all values for a monitor counter while it is on. Please
turn it off and retry.

4) Only after you disable 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 be
completely stopped:
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)

One last point before we finish. There are some counters that are really not behave as a “counter”. Most of them are resource related and display the amount of resource we uses. The total number of buffer pages 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)