ちょっと硬派なコンピュータフリークのBlogです。

カスタム検索

2010-03-10

InnoDB Pluginことはじめ。快適ストレージエンジン生活はじまる!

MySQL 5.1.38からMySQL本体にInnoDB Pluginバンドルされている。一部の先駆的なユーザー以外に、「InnoDB使ってますよ!」もしくは「検証してるよ!」という話をあまり聞かない。そもそもであるが、InnoDB Pluginってなんぞ?!という人が多いんではないかと思うのだが、実際はどうなのだろう?現在はRC版(リリース候補版)という位置づけのInnoDB Pluginであるが、一部影響度の高いバグが残っていたりしてGA版ほどの安定性は求められないものの、ほとんど実用に耐えうる品質になっているといえる。そんなわけで、今日は改めてInnoDB Pluginの使い方・使いどころについて説明するので、ぜひ皆さんの手でInnoDB Pluginを評価してみて頂きたい。

なお、以下の解説は現在の最新バージョンである、InnoDB Plugin 1.0.6を前提にしているので、将来のバージョンでは変更されている可能性があるが、その点はご了承頂きたい。本稿では概要を説明するので、詳細についてはInnoDB Pluginのマニュアルを参照して頂きたい。

InnoDB Pluginとは何か?

「InnoDB Plugin」という名称を聞いて、皆さんは何を思い浮かべるだろう?おそらくは「InnoDB Pluginって何なのさ?!InnoDBと何が違うの?Plugin化されただけ?」という感じの印象を持たれるのではないだろうか。だが、実際はそうではない。端的に言うと、InnoDB Pluginとは機能強化版InnoDBであり、現在最新の開発版であるMySQL 5.5では従来のInnoDBと置き換えられている。つまり、次期バージョンのInnoDBとも言える存在なのだ。

InnoDB PluginはInnoDBにある機能は全てサポートしており、いわゆるスーパーセットになっている。InnoDBにはなく、InnoDB Pluginで追加された機能には次のようなものが存在する。
  • Performance and scalability enhancements
  • Fast index creation
  • Data compression
  • New row format
  • File format management
  • INFORMATION_SCHEMA tables
現時点でInnoDB Pluginを使う動機は「これら最新の機能を利用したいから」であろう。いずれも非常に魅力的な機能であるので、以下に順を追って主要なものを説明しよう。

とりあえずInnoDB Pluginを使ってみる

MySQL 5.1.38以降のバージョンを利用しているなら、次の2行を追加するとInnoDB Pluginが有効になる。
[mysqld]
ignore-builtin-innodb
plugin-load=innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_locks=ha_innodb_plugin.so;innodb_lock_waits=ha_innodb_plugin.so;innodb_cmp=ha_innodb_plugin.so;innodb_cmp_reset=ha_innodb_plugin.so;innodb_cmpmem=ha_innodb_plugin.so;innodb_cmpmem_reset=ha_innodb_plugin.so
ただし、現時点で最新のMySQL 5.1.44のLinux向けパッケージの一部、具体的にはRHEL3、RHEL4、SuSE 9およびGeneric Linux向けRPM版パッケージにはInnoDB Pluginは含まれていないので注意が必要だ。詳細はリリースノートを参照のこと。RHEL5、SuSE 10、11向けRPMパッケージ、もしくはtar.gz版パッケージを使えば問題はないのでそちらを使って頂きたい。

また、現在最新の開発版であるMySQL 5.5では既存のInnoDBの代わりにInnoDB Pluginが組み込まれているので、上記のような設定をしなくてもInnoDB Pluginが利用出来る。

入ってますか?

InnoDB Pluginが有効になっている場合でも、ストレージエンジン名は「InnoDB_Plugin」とはならず「InnoDB」のままである。従って、SHOW ENGINESコマンドではどちらを利用しているかは見分けが付かないので注意されたい。
mysql> show engines;
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                        | Transactions | XA   | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         | NO           | NO   | NO         |
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| BLACKHOLE  | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV        | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| FEDERATED  | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| ARCHIVE    | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
8 rows in set (0.00 sec)
InnoDB Pluginを利用しているかどうかは、次のようにinnodb_versionシステム変数を調べるのがよいだろう。
mysql> select @@innodb_version;
+------------------+
| @@innodb_version |
+------------------+
| 1.0.6            |
+------------------+
1 row in set (0.00 sec)
従来のInnoDBにはこの変数はない。他にもシステム変数が追加されているが、それらについては後述する。

データファイルの互換性

InnoDB Pluginを使うにあたり、もっとも多い疑問は「既存のデータファイルと互換性はあるの?」ということだろう。答えは「互換性あり!」である。既存のデータファイルはそのまま利用できるので、テスト環境などで気軽に試すことが可能だ。InnoDB Pluginでは新たなファイルフォーマットが追加されているが、デフォルトではそれを利用しないようになっている。そのため、InnoDB Pluginに変更した後でも、そのデータファイルを従来のInnoDBで再び利用することも可能だ。

後述するデータ圧縮機能などを利用するには、まずinnodb_file_formatというオプションを次のように設定する必要がある。
[mysqld]
innodb_file_per_table
innodb_file_format=Barracuda
このBarracudaというのがファイルフォーマットのバージョンで、以前のものはAntelopeと呼ばれている。このオプションを設定した上で、CREATE TABLEもしくはALTER TABLEコマンド実行時にファイルフォーマットを次のように指定すると、新しいフォーマットが利用出来るのである。
mysql> ALTER TABLE t ROW_FORMAT=Compressed;
ちなみに、ROW_FORMATに指定できるフォーマットには次の4種類のものが存在する。
  • Redundant(Antelope)
  • Compact(Antelope)
  • Dynamic(Barracuda)
  • Compressed(Barracuda)
ROW_FORMATの指定がない場合、デフォルトで使用されるのはCompactであり、これは従来のInnoDBのデフォルト値と同じだ。現在利用中のファイルフォーマットは、SHOW TABLE STATUSコマンドか、INFORMATION_SCHEMA.TABLESで確認出来る。
mysql> SHOW TABLE STATUS LIKE 't'\G
*************************** 1. row ***************************
           Name: t
         Engine: InnoDB
        Version: 10
     Row_format: Compressed <---- ここ
           Rows: 524668
 Avg_row_length: 35
    Data_length: 18399232
Max_data_length: 0
   Index_length: 0
      Data_free: 2621440
 Auto_increment: 720874
    Create_time: 2010-03-10 12:17:04
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: row_format=COMPRESSED <---- ここも
        Comment: 
1 row in set (0.01 sec)
Barracudaバージョンのフォーマットを使用した場合でも、そのようなテーブルを全て「ALTER TABLE テーブル名 ROW_FORMAT=Compact」しておけば、従来のInnoDBでデータファイルを利用することが出来るので、切り戻したい時のために覚えておくといいだろう。

性能とスケーラビリティの改善

InnoDB Pluginは目玉機能が目白押しだが、恐らく多くの人が気にしているのが性能の改善についてだろう。InnoDB PluginではCPU、メモリ、I/Oの全てにおいて改良がなされている。主なものは、
  • 効率的なロックアルゴリズムの利用
  • 効率的なメモリアロケータの利用
  • グループコミットの改善
  • 非同期I/Oのためのバックグラウンドスレッド増加
  • バッファプールのLRUアルゴリズムの改善
などだ。特に非同期I/Oのためのバックグラウンドスレッド増加はI/O性能の向上に大きく寄与するので、I/Oがボトルネックになりがちなデータベース管理システムにとって、最も重要な改善点であると言えるだろう。I/Oスレッド数はオプションで調整可能で、SHOW ENGINE INNODB STATUSコマンドでその様子を見ることが出来る。
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 6 state: waiting for i/o request (write thread)
I/O thread 7 state: waiting for i/o request (write thread)
I/O thread 8 state: waiting for i/o request (write thread)
I/O thread 9 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
37 OS file reads, 7 OS file writes, 7 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
また、グループコミットの改善については、松信氏の「InnoDB Plugin 1.0.4 - InnoDB史上極めて重要なリリース」で、LRUアルゴリズムの改善についてはsh2氏の「MySQL 5.1.41リリース」というエントリで説明されているので、そちらを参照して頂きたい。

データ圧縮

必要なディスクスペースを節約したり、I/O帯域を節約して性能の向上に繋げたりできるデータ圧縮は、かなり多くのMySQL DBAにとって待望の技術ではないだろうか。データ圧縮については、sh2氏によって完璧なまでに解説がされているのでそちらを参照して頂きたい。 少しだけ引用させて頂くと、
ここまでInnoDB Pluginのデータ圧縮機能について、その仕組みと圧縮効果を見てきました。zlibがデータを半分以下のサイズに圧縮してくれることを期待してKEY_BLOCK_SIZEがデフォルト8KBになっており、実際にWikipedia日本語版のデータがおよそ半分にまで圧縮されたことを確認しました。
  • 非圧縮状態でもメモリに載るデータ量の場合、CPUコストはおよそ1.4倍となり、性能は0.9倍に低下する
  • 非圧縮状態でメモリに載らないデータ量になると、できるだけ多くのデータを圧縮状態でメモリに載せようとする
  • その場合CPUコストはおよそ4倍となるが、ディスクI/Oが抑制できる場合は性能が最大で3.5倍に向上する
  • 圧縮状態でもメモリに載らないデータ量になると、性能向上は1.1倍にとどまる
というように、データ圧縮の有用性が示されている。ぜひ利用して頂きたい。

Fast Index Creation

これは、主キー以外のインデックスの追加・削除・変更が高速になるというものである。

従来、ALTER TABLEやCREATE INDEXコマンドでキーを追加すると、MySQLは新しい定義のテーブルを作成し、そのテーブルに対してデータのコピーを行い、コピー完了後にテーブルをリネームして元のテーブルと入れ替えるという操作を行っていた。(その間、テーブルはロックされ、参照だけが可能な状態になる。)当然ながら、これには膨大なデータのコピーおよびインデックスの再構築が伴う。ALTER中にエラーが生じた場合には、新しいテーブルを破棄するだけで切り戻しが出来るというメリットがあるものの、効率がよくない処理であると言える。

InnoDB Pluginではこの点が改善され、対象のセカンダリインデックスだけの構築・破棄だけで作業が完了するようになった。InnoDBはクラスタインデックスを採用しており、全てのデータは主キーのリーフノードに格納されている。そして、セカンダリインデックスは主キーの値を示すようになっている。そのため、主キーの再構築時にはテーブル全体を再構築する必要があるのだが、セカンダリインデックスだけなら単独で再構築が可能なのである。これにより、劇的に(例えば1/10とかに)セカンダリインデックスの操作にかかる時間が短縮されるのである。特にALTERなどの文法には変更はなく、InnoDB Pluginが自動的に最適なアルゴリズムを選択してくれる。

ただし、ALTER中はテーブルがロックされ、参照だけが可能な状態になるという点に変更はない。従って、テーブルメンテナンスに掛かる時間が短縮されるとはいえ、メンテナンス中は更新が出来ない状態がしばらく発生するのは従来通りである。また、その構造上、セカンダリインデックスの追加・削除は高速になるが、カラムの追加・削除もしくは主キーの操作が必要な場合、従来通りテーブル全体の再構築が必要になるので気をつけよう。

※Fast Index Creation関係のバグがhirose31氏の「メモ: InnoDB Plugin」というエントリで紹介されているが、Bug #47622は現在Patch Pendingというステータスになっているので、近々修正されると思われる。

INFORMATION_SCHEMAの拡張

InnoDBの状態を調べる方法としては、これまでステータス変数(SHOW GLOBAL STATUS)とInnoDBモニタ(SHOW ENGINE INNODB STATUS)だけであったが、InnoDB PluginではINFORMATION_SCHEMAにも新たにテーブルが加わっている。
mysql> SHOW TABLES IN INFORMATION_SCHEMA LIKE 'INNO%';
+--------------------------------------+
| Tables_in_information_schema (INNO%) |
+--------------------------------------+
| INNODB_CMP_RESET                     |
| INNODB_TRX                           |
| INNODB_CMPMEM_RESET                  |
| INNODB_LOCK_WAITS                    |
| INNODB_CMPMEM                        |
| INNODB_CMP                           |
| INNODB_LOCKS                         |
+--------------------------------------+
7 rows in set (0.00 sec)
これらのテーブルの使い方については、sh2氏のエントリ「MySQL InnoDBにおけるロック競合の解析手順」で詳しく説明されているのでそちらを参照して頂きたい。

InnoDB Pluginで追加されたオプション

InnoDB Pluginでは、従来のInnoDBから次のオプションが追加されている。
  • innodb_adaptive_flushing
  • innodb_change_buffering
  • innodb_file_format
  • innodb_file_format_check
  • innodb_io_capacity
  • innodb_old_blocks_pct
  • innodb_old_blocks_time
  • innodb_read_ahead_threshold
  • innodb_read_io_threads
  • innodb_spin_wait_delay
  • innodb_stats_sample_pages
  • innodb_strict_mode
  • innodb_use_sys_malloc
  • innodb_write_io_threads
本エントリでは各パラメーターに対する説明を省略するが、最もインパクトのあるものはinnodb_io_capacity、innodb_read_io_threads、innodb_write_io_threadsだろう。SSDやRAID装置などの高速なストレージを利用している場合は、そのIOPSと同程度の値をinnodb_io_capacityに設定し、innodb_read_io_threads、innodb_write_io_threadsを増やしてみるといいだろう。ちなみに、主要なパラメータの詳細は執筆中の書籍のほうで解説しているのでお楽しみに!

まとめ

InnoDB Pluginは既存のInnoDBのスーパーセットであり、なおかつ魅力的な機能をいくつも備えている。使い方は従来のInnoDBとほとんど同じであり、設定もそれほど難しくはない。なので是非皆さんもInnoDB Pluginの利用にチャレンジして見て頂きたい!

なお、InnoDB Pluginについて非常に役立つエントリを既にたくさん書かれているsh2には多大なる敬意を表したい。

0 コメント:

コメントを投稿