FrontPage  Index  Search  Changes  RSS  Login

[MySQL] パフォーマンス関連メモ

InnoDB パフォーマンスチューニング

長過ぎる PRIMARY KEY を避けてディスク領域の無駄遣いを避ける

  • セカンダリインデックス用に余計な領域を使わないよう、長い主キーを避ける
    • 主キーが長い場合、代わりに AUTO_INCREMENT なカラムを主キーとして作成するとよい

補足

  • 全ての InnoDB テーブルは、データがどの行に格納されたかに関する、clustered Index と呼ばれる特別なインデックスを持っている
  • PRIMARY KEY を定義しているなら、InnoDB はそれを clusstered Index として利用する
  • PRIMARY KEY を定義していないなら、MySQL は、NOT NULL 制約を持つカラムのみからなる、最初の UNIQUE INDEX を主キーとし、InnoDB はそれを clusstered Index として利用する
  • PRIMARY KEY も適した UNIQUE INDEX もなければ、InnoDB は、行 ID 値を含むカラムを組み合わせて、hidden clustered index を内部で生成する
    • InnoDB がそのようなテーブルの行に割り当てた ID によって、行は並べられる
    • 行 ID は、新しい行が挿入されると単純に増加する 6 バイトのフィールド
    • 行 ID によって並べられた行は物理的な挿入順になる
  • 行データがインデックス検索が導くところと同じページ上に存在するため、clustered index を利用した行へのアクセスは高速
  • InnoDB では、nonclustered index (セカンダリインデックスとも呼ばれる) 内のレコード群は、セカンダリインデックスに存在しない行の主キーカラムを含む
    • InnoDB はこの主キー値をclustered index 内の行を探すために利用する
    • もし主キーが長い場合、セカンダリインデックスは余計な領域を使うため、短い主キーを持った方がよい

UNIQUE 制約を一時的に回避してテーブルのインポート効率を向上させる

  • 副キー群に UNIQUE 制約がある場合、インポート処理中に一時的に一意性チェックを無効にする事で、処理効率を上げる事ができる
  • ただし、データに重複するキーが存在しない事を確認しておく必要はある
  • 特に、大きなテーブルに対して、ディスク I/O を大幅に節約する事ができる
SET UNIQUE_CHECKS=0
... インポート処理 ...
SET UNIQUE_CHECKS=1

FOREIGN KEY 制約を一時的に回避してテーブルのインポート効率を向上させる

  • FOREIGN KEY 制約を持つテーブルがある場合、インポート処理中に一時的に外部キーチェックを無効にする事で、処理効率を上げる事ができる
  • 特に、大きなテーブルに対して、ディスク I/O を大幅に節約する事ができる
SET FOREIGN_KEY_CHECK=0
... インポート処理 ...
SET FOREIGN_KEY_CHECK=1

CPU 使用率

  • CPU 使用率が 70% 以下の場合、ディスクがボトルネックになっている可能性がある
  • トランザクションコミットが多すぎるか、バッファプールが小さすぎるかが、原因として考えられる
  • バッファプールを大きくすると改善されるかもしれないが、物理メモリの 80% を超えないようにすること

コミットを減らしてフラッシュ回数を減らす

  • フラッシュする回数を減らすために、複数の変更を一つのトランザクションにまとめること
  • トランザクションがデータベースに変更を行うなら、InnoDB はトランザクションをコミットする度にログをディスクに書き込まなければならない
  • コミット数はディスク性能に依存する

トランザクションコミットごとのフラッシュをやめる

  • innodb_flush_log_at_trx_commit を 0 にする事で、トランザクションコミット時のフラッシュがなくなる
    • 1 秒に 1 回、ディスクへのフラッシュはログファイル上で行われる
  • ただし、信頼性の問題がある事を理解する事

ログファイルのサイズを調整して不必要なディスク書き込みを減らす

  • バッファプールとログファイルを同じ大きさにしておくこと
  • InnoDB は、ログファイルいっぱいまで書き込むと、チェックポイントになり、バッファプールの変更された内容をディスクに書き込まなければならない
  • 小さなログファイルは、不必要なディスク書き込みを生じさせる
  • 大きなログファイルのデメリットは、復旧に時間がかかる事

ログバッファ

  • ログバッファは十分大きくすること(およそ 8MB)

CHAR の利用に注意し、無駄なディスク I/O をなくす

  • CHAR(N) は、データが空であったり短い文字列であったとしても、データを格納するために N 文字分を必要とする
  • 小さいテーブルはバッファプールによりフィットし、ディスク I/O を節約できる
  • row_format=compact と、utf8 や sjis の様な可変長文字セットを利用する場合、CHAR(N) のカラムは常に N バイト分の変数量領域を占有する

innodb_flush_method で書き込み性能が向上するかもしれない

  • システム次第ではあるが、デフォルトの fdatasync から O_DSYNC や O_DIRECT に変えてみるとよいかもしれない
    • fdatasync (デフォルト)
      • データファイルとログファイルの両方のフラッシュに fsync() を利用する
    • O_DSYNC
      • ログファイルのオープンとフラッシュに O_SYNC を使い、データファイルのフラッシュに fsync() を使う
    • ODIRECT
      • データファイルのオープンに O_DIRECT (Solaris では directio()) を使い、データファイルとログファイルのフラッシュに fsync() を使う

自動コミットに注意する

  • 自動コミットが有効になっている場合、行を挿入するたびに自動的にコミットが実行される
  • 大量データのインポート時など、周囲する必要がある
SET AUTOCOMMIT=0;
... SQL import statements ...
COMMIT;

ディスクに頼った大きな操作を避ける

  • 大量の挿入後のロールバックに注意が必要
    • InnoDB は挿入時にディスク I/O を節約するために挿入バッファを利用するが、ロールバック内ではそのような仕組みは利用されない
    • ディスクに頼ったロールバックは、挿入操作の 30 倍は時間がかかる
  • テーブルを空にする場合は、"DELETE FROM" ではなく、"DROP TABLE" と "CREATE TABLE" を利用する事

暖気運転

  • MyISAM と違い、InnoDB はテーブル内にインデックスカーディナリティ値を格納しない
  • 代わりに、InnoDB は起動してから初めてアクセスするテーブルに対してカーディナリティを計算する
  • 起動後、速やかに "SELECT 1 FROM table_name LIMIT 1" などを発行しておくとよい

MySQL とメモリ

メモリ割り当てパラメータ：グローバルバッファとスレッドバッファ

  • 全体で共有されるグローバルバッファと、スレッドごとのスレッドバッファ
  • パラメータを設定する際には、この違いを考慮する必要がある
    • スレッドバッファに大きすぎる値を割り当てた場合、接続が増えた時にメモリが足りなくなる事が予想される
    • スレッドは、最大で最大接続数(max_connections)の数だけ増える

key_buffer_size (グローバル)

  • default: 8,388,608 [B]
  • MyISAM テーブルのインデックスをバッファするサイズを設定する
  • 32 bit プラットフォームでは、最大 4GB
    • MySQL 5.1.23 では、64 bit プラットフォームで 4GB より大きな値を設定可能
  • 一般に、搭載している物理メモリの 25% 程度がちょうど良い
    • 50% を超えるほどにすると、値が大きすぎてパフォーマンスの低下を招きかねない
  • MySQL は OS のファイルキャッシュに依存するため、ファイルキャッシュにメモリを割く事も重要
  • SHOW STATUS の結果から Key_read_requests, Key_reads, Key_write_requests, Key_writes を確認する事で、キーバッファのパフォーマンスがわかる
  • 一般に、Key_reads / Key_read_requests の比率は 0.01 よりも小さい事が望ましい
  • MySQL :: MySQL 5.1 リファレンスマニュアル :: 6.4.6 MyISAMキーキャッシュ

thread_stack (スレッド)

net_buffer_length (スレッド)

  • default: 16,384 [B]
  • 接続バッファと結果バッファの初期サイズ
  • 必要に応じて max_allowed_packet で設定した値まで大きくなる
    • net_buffer_length の値はできる限り変更しないこと
  • 最大値は 1MB

max_allowed_packet (スレッド)

  • default: 1,048,576 [B]
  • 1 パケットの最大サイズ
  • 最大値は 1GB

read_buffer_size (スレッド)

  • default: 131,072 [B]
  • テーブルのシーケンシャルスキャンを行うときに割り当てられるバッファのサイズ
  • 値は 4KB の倍数とするべき
    • 値が 4KB の倍数でなければ、最も近い倍数に丸められる
  • 最大値は 2GB

read_rnd_buffer_size (スレッド)

  • default: 262,144 [B]
  • ソート後など、レコードをランダムな順序で読み取る際に割り当てられるバッファのサイズ
  • スレッドごとの設定値であるため、必要に応じてセッション内で値をセットした方がよい
  • 最大値は 2GB

tmp_table_size

  • default: システム依存
  • ほとんどの結合はテンポラリテーブルを使用しない
  • テンポラリテーブルのほとんどは、メモリベース(HEAP)テーブル
  • レコード長の大きなテンポラリテーブルや BLOB カラムを含むテンポラリテーブルは、ディスク上に格納される
  • メモリ内の heap テーブルのサイズが tmp_table_size を超えた場合、MySQL はディスクベースの MyISAM に切り替える

sort_buffer_size (スレッド)

  • default: 2,097,144 [B]
  • ソートを実行する必要があるスレッドがこのサイズのバッファを割り当てる
  • 最大値は 4GB (32 bit)

myisam_sort_buffer_size (スレッド)

  • default: 8,388,608 [B]
  • 以下の場合に割り当てられるバッファのサイズ
    • REPAIR TABLE 実行時に、MyISAM テーブルのインデックスをソートする場合
    • CREATE INDEX や ALTER TABLE などでインデックスを作成する場合
  • 最大値は 4GB (32 bit)

join_buffer_size (スレッド)

  • default: 131,072 [B]
  • インデックスを使用しない結合に使用するバッファのサイズ
  • 原則として、インデックスを使用しない結合を避けるべき
  • 最大値は 4GB (32 bit)

innodb_additional_mem_pool_size (グローバル)

  • default: 1,048,576 [B]
  • InnoDB が、データ辞書情報と他の内部データ構造を格納するために利用する
  • より多くのテーブルをアプリケーション内に持った場合、より多くのメモリが必要になる
  • InnoDB がこのメモリを使い果たした場合、OS からメモリを割り当て始め、エラーログに警告メッセージを出力する

innodb_buffer_pool_size (グローバル)

  • default: 8,388,608 [B]
  • InnoDB がテーブルのデータとインデックスをキャッシュするために利用するバッファのサイズ
  • 搭載する物理メモリの 80% に設定するとよい
    • ただし、物理メモリの競合が OS 内でページングを引き起こす可能性もあるため、大きすぎる値は避けるべき

innodb_log_buffer_size (グローバル)

  • default: 1,048,576 [B]
  • InnoDB がディスク上のログファイルに書き込むために利用するバッファのサイズ
  • 実用的な値の範囲は、1 MB から 8 MB
  • 大きなトランザクションを見込んでいるなら、この値を大きくしておっ事で、ディスク I/O を節約できる
innodb_log_file_size
  • default: 5,242,880 [B]
  • innodb_log_file_size は全グループのログファイルを結合して 4GB 以下(32 bit)でなければならない
  • グループ数(innodb_log_files_in_group)を N とし、バッファプールサイズ(innodb_log_buffer_size)を M とした場合の実用的な値は、1 [MB] から (M [B] / N [th]) [B]
  • 最大値は 4 GB (32 bit)
innodb_log_files_in_group
  • default: 2 (推奨値でもある)

myisam_use_mmap

  • MySQL 5.1.14 以降、myisam_use_mmap システム変数によって、全ての MyISAM テーブルに対してメモリマップが可能

FLUSH TABLES によるメモリ解放

  • FLUSH TABLES もしくは mysqladmin flush-tables によって、使用中でない全てのテーブルを閉じ、現在実行中のスレッドの終了時に使用中のテーブル全てが閉じられるように指定できる
  • これによって、効率的に使用中メモリに空きを作る事ができる
  • FLUSH TABLES は全てのテーブルが閉じられるまで返されない

DSAS の mymemcheck を参考に計算式を整理する

パラメータから必要なバッファサイズを計算し、どれだけの物理メモリが必要とされるかを計算したい。

  • MySQL のパラメータ情報
    • グローバルバッファに関するパラメータ
    • スレッドバッファに関するパラメータ
    • 他、必要なパラメータ
  • プラットフォーム情報(32-bit or 64-bit)
    • OS については無視する(Windows や、自分に縁遠い環境は考慮しない)
  • innodb_log_file_size のサイズチェックも行う

グローバルバッファ

  • key_buffer_size
  • innodb_additional_mem_pool_size
  • innodb_buffer_pool_size
  • innodb_log_buffer_size

これらの総計が、必要なメモリサイズ。

※ DSAS 版では net_buffer_length がグローバルとされているが、スレッドではないのだろうか？

スレッドバッファ

  • net_buffer_length
  • read_buffer_size
  • read_rnd_buffer_size
  • sort_buffer_size
  • myisam_sort_buffer_size
  • join_buffer_size
  • (スタック = thread_stack ？)
    • DSAS 版 mymemcheck では、256 KB 固定にしている

※ 必要最低限のメモリサイズを見積もりたいので、max_allowed_packet ではなく net_buffer_length を利用する(グローバルバッファで見積もるべきなのか自信がない)

これらの総計に、接続数(max_connections)を掛けた結果が、必要なメモリサイズ。

innodb_log_file_size

  • innodb_log_buffer_pool_size
  • innodb_log_buffer_size
  • innodb_log_files_in_group
  • 目安として 32 bit 環境の制限に基づいた警告を出せるようにする
    • 実用的な目安と、制限値の間に入るかどうかを確認する
1MB < innodb_log_file_size < MAX_innodb_log_file_size < 4GB (32bit OS)
MAX_innodb_log_file_size = innodb_buffer_pool_size * 1/innodb_log_files_in_group

スクリプト

Last modified:2010/04/14 12:37:35
Keyword(s):[mysql]
References:
Generated by Hiki 0.8.6 (2006-07-03).
Powered by Ruby 1.8.7 (2009-12-24).
Founded by koshigoe.