InnoDB_Pluginについて

13.6. The InnoDB Storage Engineからの抜粋
At the 2008 MySQL User Conference, Innobase announced availability of an InnoDB Plugin for MySQL.
This plugin for MySQL exploits the “pluggable storage engine” architecture of MySQL.
The InnoDB Plugin is included in MySQL 5.5 releases as the built-in version of InnoDB.
The version of the InnoDB Plugin is 1.0.6 as of MySQL 5.5.1 and is considered of Release Candidate (RC) quality.

The InnoDB Plugin offers new features, improved performance and scalability, enhanced reliability
and new capabilities for flexibility and ease of use.
Among the features of the InnoDB Plugin are ….. 以下プラグイン概要….

————————————————————————-
① Fast index creation
② Table and index compression
③ File format management
④ New INFORMATION_SCHEMA tables
⑤ Capacity tuning
⑥ Multiple background I/O threads
⑦ Group commit.
————————————————————————-

詳細は以下のinnodb.comを参照
InnoDB Plugin 1.0 for MySQL 5.1 (Early Adopter Release) User’s Guide

先日、MYSQL5.5をインストールしたので、Innodb_pluginはディフォルトで有効になっている。
以下のコマンドでバージョンを確認する事が出来ます。


select PLUGIN_NAME,PLUGIN_VERSION,PLUGIN_STATUS
from information_schema.plugins;

SELECT @@innodb_version;

innodb_plugin_version

メモ: INFORMATION_SCHEMA.PLUGINSテーブルにはマイナーバージョン番号は表示されないようです。
Note that the PLUGIN_VERSION column in the table INFORMATION_SCHEMA.PLUGINS does not
display the third component of the version number, only the first and second components, as in 1.0.

その他留意事項
———————————————————————————————————-
1.5. Operational Restrictions
———————————————————————————————————-
Because the InnoDB Plugin introduces a new file format, with new on-disk data structures within
both the database and log files, there are important restrictions on the use of the plugin in typical user
environments. Specifically, you should pay special attention to the information presented here about
file format compatibility with respect to the following scenarios:

1) Downgrading from the InnoDB Plugin to the built-in InnoDB, or otherwise using different
versions of InnoDB with database files created by the InnoDB Plugin
2) Using mysqldump
3) Using MySQL replication
4) Using InnoDB Hot Backup

WARNING: Once you use the InnoDB Plugin on a set of database files, care must be taken to avoid
crashes and corruptions when using those files with an earlier version of InnoDB, as might happen by
opening the database with MySQL when the plugin is not installed. It is strongly recommended that
you use a “slow shutdown” (SET GLOBAL innodb_fast_shutdown=0) when stopping the MySQL server
when the InnoDB Plugin is enabled. This will ensure log files and other system information written by the
plugin will not cause problems when using a prior version of InnoDB.
11.3, “How to Downgrade”.

WARNING:If you dump a database containing compressed tables with mysqldump,
the dump file may contain CREATE TABLE commands that attempt to create compressed tables,
or those using ROW_FORMAT=DYNAMIC in the new database. Therefore, you should be sure
the new database is running the InnoDB Plugin, with the proper settings for innodb_file_format and innodb_file_per_table, if you want to have the tables re-created as they exist in the original database.
Typically, however, when the mysqldump file is loaded, MySQL and InnoDB will ignore
CREATE TABLE options they do not recognize, and the table(s) will be created in a format
used by the running server.

WARNING: If you use MySQL replication, you should be careful to ensure all slaves are configured
with the InnoDB Plugin, with the same settings for innodb_file_format and innodb_file_per_table.
If you do not do so, and you create tables that require the new “Barracuda” file format, replication
errors may occur. If a slave MySQL server is running the built-in InnoDB, it will ignore the
CREATE TABLE options to create a compressed table or one with ROW_FORMAT=DYNAMIC,
and create the table uncompressed, with ROW_FORMAT=COMPACT.

WARNING: The current version of InnoDB Hot Backup does not support the new “Barracuda” file
format. Using InnoDB Hot Backup Version 3 to backup databases in this format will cause
unpredictable behavior. A future version of InnoDB Hot Backup will support databases used
with the InnoDB Plugin. As an alternative, you may back up such databases with mysqldump.

———————————————————————————————————-

Innovative Technologiesfor Performance andData Protection

mysql> select Variable_name from
-> information_schema.GLOBAL_VARIABLES
-> where Variable_name like ‘innodb_%’;
+———————————+
| Variable_name |
+———————————+
| INNODB_VERSION |
| INNODB_LOCKS_UNSAFE_FOR_BINLOG |
| INNODB_BUFFER_POOL_SIZE |
| INNODB_CONCURRENCY_TICKETS |
| INNODB_OLD_BLOCKS_PCT |
| INNODB_LOG_BUFFER_SIZE |
| INNODB_MAX_PURGE_LAG |
| INNODB_DOUBLEWRITE |
| INNODB_IO_CAPACITY |
| INNODB_TABLE_LOCKS |
| INNODB_AUTOEXTEND_INCREMENT |
| INNODB_THREAD_SLEEP_DELAY |
| INNODB_REPLICATION_DELAY |
| INNODB_STATS_ON_METADATA |
| INNODB_ROLLBACK_ON_TIMEOUT |
| INNODB_CHANGE_BUFFERING |
| INNODB_FILE_FORMAT |
| INNODB_DATA_FILE_PATH |
| INNODB_STRICT_MODE |
| INNODB_MAX_DIRTY_PAGES_PCT |
| INNODB_AUTOINC_LOCK_MODE |
| INNODB_COMMIT_CONCURRENCY |
| INNODB_MIRRORED_LOG_GROUPS |
| INNODB_SUPPORT_XA |
| INNODB_SYNC_SPIN_LOOPS |
| INNODB_ADAPTIVE_FLUSHING |
| INNODB_ADAPTIVE_HASH_INDEX |
| INNODB_DATA_HOME_DIR |
| INNODB_READ_IO_THREADS |
| INNODB_WRITE_IO_THREADS |
| INNODB_FORCE_RECOVERY |
| INNODB_LOG_FILES_IN_GROUP |
| INNODB_OPEN_FILES |
| INNODB_FILE_FORMAT_CHECK |
| INNODB_READ_AHEAD_THRESHOLD |
| INNODB_LOG_GROUP_HOME_DIR |
| INNODB_FAST_SHUTDOWN |
| INNODB_THREAD_CONCURRENCY |
| INNODB_STATS_SAMPLE_PAGES |
| INNODB_FLUSH_LOG_AT_TRX_COMMIT |
| INNODB_FLUSH_METHOD |
| INNODB_CHECKSUMS |
| INNODB_LOG_FILE_SIZE |
| INNODB_SPIN_WAIT_DELAY |
| INNODB_FILE_PER_TABLE |
| INNODB_LOCK_WAIT_TIMEOUT |
| INNODB_OLD_BLOCKS_TIME |
| INNODB_ADDITIONAL_MEM_POOL_SIZE |
| INNODB_USE_SYS_MALLOC |
+———————————+
49 rows in set (0.00 sec)

mysql>

    新機能として

[innodb_file_format」
InnoDBのファイルフォーマットを指定可能
Antelope  従来のファイルフォーマット
Barracuda 圧縮機能サポートファイルフォーマット

innodb_file_format

検証
CREATE DATABASE `T_INNODB` /*!40100 DEFAULT CHARACTER SET utf8 */


CREATE TABLE `T_Antelope` (
`number` int(11) DEFAULT NULL,
`comment` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `T_Barracuda` (
`number` int(11) DEFAULT NULL,
`comment` varchar(100) DEFAULT NULL
) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 DEFAULT CHARSET=utf8;

ROW_FORMAT

* REDUNDANT
* COMPACT
* DYNAMIC (Barracuda format & No Compress)
* COMPRESSED: (Barracuda format & Compress)

innodb_plugin_test

よく見てみると、上記QueryはWarrningが出ていてテーブルが圧縮されていない事が分かった。

オプションファイルに以下の設定を入れて再度、MYSQLを再起動して設定を反映させた。
innodb_file_format= Barracuda

innodb_file_format_bara

mysql> show variables like 'innodb_file%';

barracuda

ファイルフォーマットがBarracudaに変わったので再度テーブルを作成してみる。


CREATE TABLE `T_Antelope` (
`number` int(11) DEFAULT NULL,
`comment` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `T_Barracuda` (
`number` int(11) DEFAULT NULL,
`comment` varchar(100) DEFAULT NULL
) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4 DEFAULT CHARSET=utf8
/* BLOCKサイズを4に変更した */;


select TABLE_SCHEMA,TABLE_NAME,ENGINE,ROW_FORMAT
from information_schema.tables
where TABLE_SCHEMA = 'T_INNODB';

innodb_file_formatを変更した事でwarrningが消えた事を確認
barracuda_confirm

実際のファイルを確認してみるとファイルサイズが違う。
※ ブロックサイズを8で作成してもファイルサイズはBarracudaの方が小さい。

compare

information_schema.INNODB_CMPの変化の確認。
圧縮を行った回数(compress_ops), 圧縮が成功した回数(compress_ops_ok)
などを見て圧縮への対応を検討していく事が可能です。


CREATE TABLE `T_Antelope_Key` (
`number` int(11) unsigned NOT NULL AUTO_INCREMENT,
`comment` varchar(100) DEFAULT NULL,
PRIMARY KEY (`number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `T_Barracuda_Key` (
`number` int(11) unsigned NOT NULL AUTO_INCREMENT,
`comment` varchar(100) DEFAULT NULL,
PRIMARY KEY (`number`)
) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 DEFAULT CHARSET=utf8;

select * from information_schema.INNODB_CMP;

innodb_cmp

【メモ:パフォーマンス検証の際は、以下の値を確認】
innodb_buffer_pool_size
innodb_flush_methodをO_DIRECT

参考サイト
——————————————
INNOBASE
InnoDB Plugin Change History
13.6. The InnoDB Storage Engine
InnoDB Plugin 1.0.4 – InnoDB史上極めて重要なリリース

Comments are closed.

Post Navigation