Skip navigation links
9.13.2 SHOW ENGINE INNODB STATUS と InnoDB モニター »
Section Navigation      [Toggle]

9.13.1. InnoDB パフォーマンスチューニングヒント

  • InnoDB 内では、長い PRIMARY KEY を持つと、その値がすべての二次インデックスレコードを利用して格納されるため、ディスク領域の無駄遣いになります。(詳細は、項9.10. 「InnoDB テーブルとインデックス構造」 を参照してください。)もし主キーが長かったら、AUTO_INCREMENT カラムを主キーとして作成してください。

  • もし 2 番目のキー上に UNIQUE 制限があったら、インポート操作の最中に一時的に一意性確認を切り、テーブルインポートのスピードを上げることができます: 

    SET unique_checks=0;
... import operation ...
SET unique_checks=1;

    大きいテーブルに対しては、InnoDB が二次インデックスレコードをバッチ内に書くためにそれ自身の挿入バッファーを利用することができるので、この作業をするとディスク I/O を大幅に節約することができます。データが重複キーを含んでいないことを必ず確認してください。

  • もしテーブル内に FOREIGN KEY 制約があったら、インポートセッションの持続時間に対して外部キーチェックを切ることでテーブルインポートのスピードを早めることができます: 

    SET foreign_key_checks=0;
... import operation ...
SET foreign_key_checks=1;

    大きいテーブルに対しては、これでディスク I/O を大幅に節約することができます。

  • もし Unix top ツールか、Windows タスクマネージャーが、作業負荷 CPU 使用率が 70% 以下であると表示したら、その作業負荷はおそらくディスクに頼っているでしょう。トランザクションコミットをたくさん作りすぎているか、バッファープールが小さすぎるということでしょう。バッファープールを大きく作成することも良いですが、物質的メモリーの 80% 以上に設定しないでください。

  • いくつかの変更を単一のトランザクション内にまとめることで、フラッシュ操作の回数を減らします。InnoDB は、もしトランザクションがデータベースに変更を行うなら、各トランザクションコミットの際にディスクにログをフラッシュしなければいけません。もしディスクが OS を 「欺かなければ」、ディスクの回転速度は一般的に最大 167 回転/秒で、コミット数も 1 秒につき 167th に制限されます。

  • クラッシュが発生したときにいくつかの最新のコミットされたトランザクションの損失を受け入れることができるなら、innodb_flush_log_at_trx_commit パラメータを 0 に設定することができます。フラッシュが保証されていなくても、InnoDB は 1 秒に 1 回ログをフラッシュします。さらに、ディスクデータとバイナリログを同期するためのディスクフラッシュの回数が減るように、innodb_support_xa の値を 0 に設定すべきです。

  • バッファープールと同じ大きさまでログファイルを大きくしてください。InnoDB がログファイルにいっぱいになるまで書き込むと、それはチェックポイント内でバッファープールの変更された内容をディスクに書き込まなければいけません。小さいログファイルは多くの不必要なディスク書き込みを引き起こします。大きいログファイルの欠点は、復旧時間が長いということです。

  • ログバッファーのサイズも (8M バイトのオーダーで) 非常に大きくしてください。

  • もし可変長文字列を格納していたり、カラムが NULL 値をたくさん含んでいたら、CHAR の代わりに VARCHAR データ型を利用してください。CHAR(N) カラムは文字列が短かったりその値が NULL だとしても、データを格納するためにいつも N 文字を取ります。小さいテーブルはバッファープール内によりフィットし、ディスク I/O を減らします。

    COMPACT 行形式 (MySQL 5.1 内のデフォルト InnoDB 形式) と、utf8 や sjis のような可変長キャラクタセットを利用するとき、CHAR(N) は最低でも N バイト分の変数量領域を占有します。

  • GNU/Linux と Unix のいくつかのバージョンでは、Unix fsync() コール (InnoDB がデフォルトで利用するもの) を利用してファイルをディスクにフラッシュする方法やそれと似た方法は、スピードが大変遅いです。もしデータベースの書込み性能に満足していなければ、O_DSYNC に innodb_flush_method パラメータを設定してみるのが良いかもしれません。ほとんどのシステム上で O_DSYNC フラッシュ方式のスピードは遅いかもしれませんが、お使いのものはそうではないかもしれません。

  • InnoDB ストレージエンジンを x86_64 アーキテクチャー (AMDOpteron) の Solaris10 で利用するとき、forcedirectio オプションを利用して、InnoDB に関連するファイルを格納するのに利用されるファイルシステムをマウントすることが重要です。(Solaris 10/x86_64 のデフォルトはこのオプションを利用 しません)。forcedirectio 利用に失敗すると、このプラットフォーム上での InnoDB のスピードと性能の深刻な劣化を引き起こします。

    Solaris 2.6 以降のリリース版とすべてのプラットフォーム (sparc/x86/x64/amd64) で、大きい innodb_buffer_pool_size 値と共に InnoDB ストレージエンジンを利用するとき、raw デバイスや別々のディレクト I/O UFS ファイルシステム (forcedirectio マウントオプションを利用。mount_ufs(1M) を参照) 上に InnoDB データファイルとログファイルを置くことで、大幅な性能向上を実現できる可能性があります。Veritas ファイルシステム VxFS のユーザーは、convosync=direct マウントオプションを使用すべきです。raw パーティションまたは直接入出力ファイルシステムを使用する場合としない場合でテストを実行し、実際のシステム上でパフォーマンスが改善されるかどうかを確認することをお勧めします。

    MyISAM テーブルに対するものなどのようなその他 MySQL データファイルはディレクト I/O ファイルシステム上に置くべきではありません。実行ファイルやライブラリは、ディレクト I/O ファイルシステム上に置いてはいけません。

  • InnoDB にデータをインポートするとき、MySQL が自動コミットを持っていると各挿入ごとにディスクへのログフラッシュが要求されるので、自動コミットを持っていないことを確認してください。インポート操作の最中に自動コミットを無効にするには、それを次のように、SET autocommit と COMMIT ステートメントで囲んで下さい。 

    SET autocommit=0;
... SQL import statements ...
COMMIT;

    もし mysqldump オプション --opt を利用すれば、SET autocommit と COMMIT ステートメントで囲まなくても InnoDB テーブル内にすばやくインポートできるダンプファイルを得ることができます。

  • 大量挿入の大きいロールバックに気をつけてください:InnoDB は挿入時にディスク I/O を節約するために挿入バッファーを利用しますが、対応するロールバック内ではそのような仕組みは利用されません。ディスクに頼ったロールバックを実行するには、それと対応する挿入操作の 30 倍の時間がかかります。データベース処理を停止しても、ロールバックはサーバー起動の際にもう一度起動するので意味がありません。暴走ロールバックを無くす唯一の方法は、ロールバックが CPU に頼り処理が速くなるようにバッファープールを増やすこと、または特別な方法を利用することです。項9.6.2. 「InnoDB 復旧の強制」 を参照してください。

  • その他のディスクに頼った大きい操作にも気をつけてください。テーブルを空にするには DROP TABLE と CREATE TABLE を利用し、DELETE FROM tbl_name は利用しないでください。

  • もし行をたくさん挿入したいのであれば、クライアントとサーバー間の伝達オーバーヘッドを減らすために複数行 INSERT 構文を利用してください: 

    INSERT INTO yourtable VALUES (1,2), (5,5), ...;

    この方法は、InnoDB テーブルだけではなく、すべてのテーブルへの挿入に有効なヒントです。

  • もし頻繁には更新されないテーブルに自動更新クエリーを持っていたら、次のようにしてクエリキャッシュを有効にしてください: 

    [mysqld]
query_cache_type = 1
query_cache_size = 10M

  • MyISAM とは違い、InnoDB はそのテーブル内にインデックスカーディナリティーを格納しません。代わりに、InnoDB は、起動してからはじめてアクセスするテーブルに対して自動更新を算出します。多数のテーブルがあると、この操作はかなり時間がかかります。重要なのは初期テーブル起動操作なので、後ほど利用するときに備えてテーブルを 「暖める」 ために、SELECT 1 FROM tbl_name LIMIT 1 のようなステートメントを発行することで起動後に速やかにこれにアクセスします。