MySQL Bugs: #72930: Please expose some more innodb metrics in structured tables

Bug #72930	Please expose some more innodb metrics in structured tables
Submitted:	10 Jun 2014 9:05	Modified:	10 Jun 2014 19:27
Reporter:	Simon Mudd (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S4 (Feature request)
Version:	5.6.17	OS:	Any
Assigned to:	Daniel Price	CPU Architecture:	Any

Description:
http://dev.mysql.com/doc/refman/5.6/en/innodb-buffer-pool.html describes the InnoDB buffer pool.
It also talks about new/young and old pages and the mid point inserting etc, all of which is very interesting.

However "Monitoring the buffer pool" references the output of SHOW INNODB ENGINE STATUS\G which to collect the required metrics requires complex text parsing of the output. This should not be necessary.

How to repeat:
Read the above URL and look at the description of how to monitor the buffer pool.
It requires complex text processing to obtain the required metrics.

Suggested fix:
Please expose these values as structured values, perhaps in I_S.INNODB_METRICS.

Values like: 
- old database pages
- pages made young, not young
- young/s, not youngs/s
- young making rate
- not

Whether these values should be stored as is, or as counters and the collector can calculate the difference from the previous collection and thus any related rates is not too important, but making these values easier to collect and graph means that it's easier to see if configuration changes may need to be made and monitoring the effect they have on performance.

Note: this ticket arose due to trying to debug if InnoDB behaviour was ideal when performing heavy dumping of data to external sources, and therefore if the following parameters are correctly set:

- innodb_old_blocks_pct
- innodb_old_blocks_time

We do have this:

mysql> select * from information_schema.innodb_buffer_pool_stats\G
*************************** 1. row ***************************
                         POOL_ID: 0
                       POOL_SIZE: 8192
                    FREE_BUFFERS: 7804
                  DATABASE_PAGES: 387
              OLD_DATABASE_PAGES: 0
         MODIFIED_DATABASE_PAGES: 0
              PENDING_DECOMPRESS: 0
                   PENDING_READS: 0
               PENDING_FLUSH_LRU: 0
              PENDING_FLUSH_LIST: 0
                PAGES_MADE_YOUNG: 0
            PAGES_NOT_MADE_YOUNG: 0
           PAGES_MADE_YOUNG_RATE: 0
       PAGES_MADE_NOT_YOUNG_RATE: 0
               NUMBER_PAGES_READ: 387
            NUMBER_PAGES_CREATED: 0
            NUMBER_PAGES_WRITTEN: 1
                 PAGES_READ_RATE: 0.8570816370259268
               PAGES_CREATE_RATE: 0
              PAGES_WRITTEN_RATE: 0
                NUMBER_PAGES_GET: 12107
                        HIT_RATE: 999
    YOUNG_MAKE_PER_THOUSAND_GETS: 0
NOT_YOUNG_MAKE_PER_THOUSAND_GETS: 0
         NUMBER_PAGES_READ_AHEAD: 0
       NUMBER_READ_AHEAD_EVICTED: 0
                 READ_AHEAD_RATE: 0
         READ_AHEAD_EVICTED_RATE: 0
                    LRU_IO_TOTAL: 0
                  LRU_IO_CURRENT: 0
                UNCOMPRESS_TOTAL: 0
              UNCOMPRESS_CURRENT: 0
1 row in set (0.00 sec)

Shane, thanks.

Information in MySQL is spread around in different places. I missed this.

Indeed collecting this info is much easier. So given the information is available in a structured manner I guess it's only the documentation which could be adjusted to reference this table.

Changed to docs request:
http://dev.mysql.com/doc/refman/5.6/en/innodb-buffer-pool.html

Should link to:
http://dev.mysql.com/doc/refman/5.6/en/innodb-buffer-pool-stats-table.html

A reference has been added to the InnoDB Buffer Pool INFORMATION_SCHEMA. INNODB_BUFFER_POOL_STATS table and to the InnoDB status variables. 

The revised content should appear soon, with the next published documentation build. 

http://dev.mysql.com/doc/refman/5.5/en/innodb-buffer-pool.html
http://dev.mysql.com/doc/refman/5.6/en/innodb-buffer-pool.html
http://dev.mysql.com/doc/refman/5.7/en/innodb-buffer-pool.html

Thank you for the bug report.