Much has been written about the new InnoDB pluggable storage engine, which Innobase released at the MySQL conference last month. We've written posts ourselves about its fast index creation capabilities and the compressed row format, and how that affects performance. One of the nice things they added in this InnoDB release is INFORMATION_SCHEMA tables that show some status information about InnoDB. Here are the tables:
PLAIN TEXT
SQL:

1. [FONT='Courier New', Courier, monospace]mysql> SHOW TABLES FROM INFORMATION_SCHEMA LIKE 'INNODB%';[/font]
2. [FONT='Courier New', Courier, monospace]+----------------------------------------+[/font]
3. [FONT='Courier New', Courier, monospace]| Tables_in_INFORMATION_SCHEMA (INNODB%) |[/font]
4. [FONT='Courier New', Courier, monospace]+----------------------------------------+[/font]
5. [FONT='Courier New', Courier, monospace]| INNODB_CMP |[/font]
6. [FONT='Courier New', Courier, monospace]| INNODB_CMP_RESET |[/font]
7. [FONT='Courier New', Courier, monospace]| INNODB_CMPMEM |[/font]
8. [FONT='Courier New', Courier, monospace]| INNODB_CMPMEM_RESET |[/font]
9. [FONT='Courier New', Courier, monospace]| INNODB_LOCK_WAITS |[/font]
10. [FONT='Courier New', Courier, monospace]| INNODB_LOCKS |[/font]
11. [FONT='Courier New', Courier, monospace]| INNODB_TRX |[/font]
12. [FONT='Courier New', Courier, monospace]+----------------------------------------+ [/font]

The _CMP tables show statistics about compression; they contain a lot of useful information about compression, decompression, memory management, fragmentation etc. Beware that selecting from the tables whose names contain RESET has a side effect: it resets the statistics back to 0.
There are also locks and transactions tables. A while ago, the InnoDB developers contacted me to ask my opinion about what would be useful to put in the INFORMATION_SCHEMA. I told them the single biggest thing I could not get from InnoDB at the time was visibility into which transactions are blocking others when there are lock waits. It appears that they agreed this was important to add. (I subsequently discovered that it is possible to find out more information on InnoDB locks even in the older versions of InnoDB, but it's not really easy.)
These tables are fully documented in the InnoDB plugin manual, along with extensive examples of how to use them to find out what is blocking what and so on. Note that the InnoDB plugin manual is being maintained on www.innodb.com, not as part of the regular MySQL manual.
Entry posted by Baron Schwartz | No comment
Add to: | | | |


INFORMATION_SCHEMA tables in the InnoDB pluggable storage engine - Read More...