無料PHPプログラム

InnoDB 内では、長い PRIMARY KEY を持つと、その値が全てのセカンダリ インデックス レコードを利用して格納される為、ディスク領域の無駄遣いになります。（詳しくは 項13.5.13. 「InnoDB テーブルとインデックス構造」 をご確認ください。）もし主キーが長かったら、AUTO_INCREMENT カラムを主キーとして作成してください。

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

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

クラッシュが発生した時にいくつかの最新のコミットされたトランザクションの損失を受け入れる事ができるなら、innodb_flush_log_at_trx_commit パラメータを0に設定する事ができます。フラッシュが保証されていなくても、InnoDB は1秒に1回ログをフラッシュします。

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

ログ バッファもとても長く作成してください。(約 8MB)

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

row_format=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 アーキテクチャ(AMD Opteron)の Solaris 10で利用する時、 forcedirectio オプションを利用して、InnoDB に関連するファイルを格納するのに利用されるファイル システムをマウントする事が重要です。(Solaris 10/x86_64 のデフォルトはこのオプションを利用 しません。)forcedirectio 利用に失敗すると、このプラットフォーム上での InnoDB のスピードと性能の深刻な劣化を引き起こします。

Solaris 2.6 以降のリリース版と全てのプラットフォーム(sparc/x86/x64/amd64)で、大きい innodb_buffer_pool_size 値と共に InnoDB ストレージ エンジンを利用する時、未加工デバイスや別々のディレクト I/O UFS ファイル システム(マウント オプション forcedirectio を利用。mount_ufs(1M) を参照)上に InnoDB データ ファイルとログ ファイルを置く事で、大幅な性能向上を実現する事ができます。Veritas ファイル システム VxFS ユーザは、マウント オプション convosync=direct を利用しなければいけません。

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 に頼り処理が速くなるようにバッファ プールを増やす事、または特別な方法を利用する事です。詳しくは 項13.5.8.1. 「InnoDB 復旧の強制」 を参照してください。

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

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

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

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

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

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

大きいテーブルに対しては、InnoDB が2番目のインデックス レコードをバッチ内に書く為にそれ自身の挿入バッファを利用する事ができるので、この作業をするとディスク I/O を大幅に節約する事ができます。データが複製キーを含んでいない事を必ず確認してください。UNIQUE_CHECKS はストレージエンジンが複製キーを無視する事を許可しますが、それを要求はしません。

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

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

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

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

[mysqld]
query_cache_type = ON
query_cache_size = 10M

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