Configuring INFORMATION_SCHEMA Plugin Tables for InnoDB Plugin

There are seven new INFORMATION_SCHEMA tables (plugins) available with the InnoDB plugin.  They need to be loaded to be used.

New InnoDB Plugins contain information on:

• Compressed InnoDB tables
• Compressed InnoDB buffer pool
• Current InnoDB transactions
• Transaction Locks
• Blocking transactions
  

These INFORMATION_SCHEMA plugins are available by setting the plugin-load parmeter:

plugin-load=innodb=ha_innodb_plugin.dll;innodb_trx=ha_innodb_plugin.dll;innodb_locks=ha_innodb_plugin.dll;innodb_cmp=ha_innodb_plugin.dll;innodb_cmp_reset=ha_innodb_plugin.dll;innodb_cmpmem=ha_innodb_plugin.dll;innodb_cmpmem_reset=ha_innodb_plugin.dll;

mysql> SELECT table_name, engine, table_type FROM information_schema.tables
             WHERE table_name LIKE 'innodb%'
 

table_name	engine	table_type
INNODB_CMP_RESET	MEMORY	SYSTEM VIEW
INNODB_TRX	MEMORY	SYSTEM VIEW
INNODB_CMPMEM_RESET	MEMORY	SYSTEM VIEW
INNODB_CMP	MEMORY	SYSTEM VIEW
INNODB_CMPMEM	MEMORY	SYSTEM VIEW
INNODB_LOCKS	MEMORY	SYSTEM VIEW

Before loading plugin tables: 

mysql>  SHOW PLUGINS;

Name	Status	Type	Library	License
binlog	ACTIVE	STORAGE ENGINE		GPL
ARCHIVE	ACTIVE	STORAGE ENGINE		GPL
BLACKHOLE	ACTIVE	STORAGE ENGINE		GPL
CSV	ACTIVE	STORAGE ENGINE		GPL
FEDERATED	DISABLED	STORAGE ENGINE		GPL
MEMORY	ACTIVE	STORAGE ENGINE		GPL
MyISAM	ACTIVE	STORAGE ENGINE		GPL
MRG_MYISAM	ACTIVE	STORAGE ENGINE		GPL
partition	ACTIVE	STORAGE ENGINE		GPL
InnoDB	ACTIVE	STORAGE ENGINE	ha_innodb_plugin.dll	GPL

After loading plugin tables: 

mysql>  SHOW PLUGINS;

Name	Status	Type	Library	License
binlog	ACTIVE	STORAGE ENGINE		GPL
ARCHIVE	ACTIVE	STORAGE ENGINE		GPL
BLACKHOLE	ACTIVE	STORAGE ENGINE		GPL
CSV	ACTIVE	STORAGE ENGINE		GPL
FEDERATED	DISABLED	STORAGE ENGINE		GPL
MEMORY	ACTIVE	STORAGE ENGINE		GPL
MyISAM	ACTIVE	STORAGE ENGINE		GPL
MRG_MYISAM	ACTIVE	STORAGE ENGINE		GPL
partition	ACTIVE	STORAGE ENGINE		GPL
InnoDB	ACTIVE	STORAGE ENGINE	ha_innodb_plugin.dll	GPL
INNODB_TRX	ACTIVE	INFORMATION SCHEMA	ha_innodb_plugin.dll	GPL
INNODB_LOCKS	ACTIVE	INFORMATION SCHEMA	ha_innodb_plugin.dll	GPL
INNODB_CMP	ACTIVE	INFORMATION SCHEMA	ha_innodb_plugin.dll	GPL
INNODB_CMP_RESET	ACTIVE	INFORMATION SCHEMA	ha_innodb_plugin.dll	GPL
INNODB_CMPMEM	ACTIVE	INFORMATION SCHEMA	ha_innodb_plugin.dll	GPL
INNODB_CMPMEM_RESET	ACTIVE	INFORMATION SCHEMA	ha_innodb_plugin.dll	GPL

InnoDB parameters example:
[mysqld]
basedir="C:/mysql/5.1.38/"
datadir="C:/mysql/Data/"

#*** INNODB Specific options ***
ignore-builtin-innodb
plugin-load=innodb=ha_innodb_plugin.dll;innodb_trx=ha_innodb_plugin.dll;innodb_locks=ha_innodb_plugin.dll;innodb_cmp=ha_innodb_plugin.dll;innodb_cmp_reset=ha_innodb_plugin.dll;innodb_cmpmem=ha_innodb_plugin.dll;innodb_cmpmem_reset=ha_innodb_plugin.dll

plugin_dir="C:/mysql/5.1.38/lib/plugin"

default-storage-engine=INNODB
innodb_data_home_dir="C:/mysql/innodb"

innodb_data_file_path=ibdata1:1G;ibdata2:1G:autoextend
innodb_autoextend_increment=100
innodb_log_group_home_dir="C:/mysql/innologs"

innodb_buffer_pool_size=120M
innodb_log_file_size=100M