DSAS開発者の部屋

MySQL 5.7 で JSON 型が追加されたのに関連して、私がメンテナをしている MySQL ドライバーで罠にハマったので、 MySQL のテキストプロトコルの闇を書き残しておくことにします。

Text Resultset

MySQL にクエリを投げるコマンドが COM_QUERY で、そのレスポンスとして返されるのが Text Resultset と呼ばれる一連のパケットです。その中身は次のようになっています。

• column count (整数1つだけのパケット)
• column definition * column count (各カラムの定義)
• EOF
• row * N (結果行数分)
• EOF

この記事で注目するのは column definition パケット (定義) の中にある、 column_type と character set です。

Python の MySQL ドライバーは、この2つの値を元に、 row に含まれる各バイト列を Python の適切な型の値に変換しています。

なお、以降に書く MySQL の振る舞いは、 MySQL 5.7.13 に基いています。

Character Set の闇

Q. connection encoding が latin1 のコネクションで、UTF-8 の VARCHAR 型のカラムに保存された「こんにちは」を SELECT したとき、 column definition の charset と row に含まれるバイト列はどうなるでしょう？

1. charset=utf8, row には UTF-8 のバイト列
2. charset=latin1, row には latin1で "?????"

A. 2

どうやら、 Column Definition にある character set は Column Character Set と無関係に connection encoding になるようです。 カラム毎に変わらないなら、なんで Column Definition に character set 書いてるんでしょうね?

あと、カラムの値は、カラムのエンコーディングからコネクションのエンコーディングにサーバーサイドで変換されるようです。 テキストの関数を使う必要が無いのなら、 VARCHAR や TEXT より VARBINARY や BLOB 使ったほうがエンコーディングに関わる問題を踏みにくそうです。

TEXT 型の闇

column type の一覧 を見てみると、 VARCHAR, VAR_STRING, STRING, TINY_BLOB, MEDIUM_BLOB, LONG_BLOB, BLOB といった型があるのですが、 TEXT 型がありません。

Q. TEXT 型のカラムを SELECT したとき、 column definition の column_type は何になるでしょうか？

1. VARCHAR
2. STRING
3. BLOB

A. 3

どうやら、 column_type は特にテキストとバイナリの区別を付けていないようですね。 区別付けないのはいいとして、型の名前として VARBINARY じゃなくて VARCHAR を使っているのに、 TEXT じゃなくて BLOB を使ってるのはどうなんでしょうか？テキストかバイナリのどちらかに寄せておいたほうが混乱が少ないと思うのですが。

さて、 Python ではテキストとバイナリを明確に区別したいです。 Java や C# もきっと同じだと思います。 その場合、 character set を見てテキストとバイナリを区別することができます。 character set が connection encoding と同じならテキストで、 binary ならバイナリです。

なお、 INT 型などは charset は binary が設定されます。 例えば 42 は ASCII の "42" として返ってくるので、文字列を10進整数としてパースしたい場合はいったん ASCII でデコードしてからパースすることになると思います。 これを擬似コードで書くとこうなります。

if column_type in [VARCHAR, VAR_STRING, ... 全ての文字列/バイナリ型]:
    if charset == CHARSET_BINARY:
        # バイト列
        return data
    else:
        return data.decode(connection_encoding)
else: # 文字列以外
    data = data.decode('ascii')  # ascii でデコードしておいて、
    return converters[column_type](data)  # column_type ごとに用意した変換関数で変換する

JSON 型の闇

column_type には JSON が追加されました。そして MySQL 内部では JSON は常に UTF-8 (utf8mb4) で扱っているはずです。

さて、 column definition の character set はどうなっているでしょうか？

Q. connection encoding が utf8 のコネクションで、JSON 型のカラムを SELECT したとき、 character set は？

1. binary
2. utf8
3. utf8mb4

A. 1

きっとテキスト型とちがって、カラムに character set を保存して無いので、文字列／バイナリ型以外の整数型などと同じように binary を返してしまうんでしょうね。

Q. connection encoding が utf8 のコネクションで、 SELECT CAST('{"hello": "world"}' AS JSON) AS anon_1 したとき、 character set は？

1. binary
2. utf8
3. utf8mb4

A. 2

カラムと違ってコネクションには character set があるので、 それを返しているんでしょうね。でも、整数型とかは SELECT 42 しても binary なんですよね…

さて、上に書いた擬似コードを見て、JSON型のカラムをテキストとして返すにはどうすればいいか考えてみましょう。

@methane

MySQL 5.6 の検証中に MySQL 5.5 とは違うタイプのレプリケーション遅延を見つけたので紹介します。

MySQL のレプリケーションのおさらい

MySQL のレプリケーションは次のような仕組みで動作しています。

• マスターの更新トランザクションが binlog を書く
• スレーブの I/O スレッドがマスターに接続し、 binlog を取得し、 relaylog を書く.
  • マスター側はスレーブからの接続を受け付けると(dump スレッド)、指定された場所から最新までの binlog を転送する
  • binlog が追記されるのを待ってさらにスレーブに送る
• スレーブのSQLスレッドが relaylog を再生する

MySQL 5.5 でよくあったレプリケーション遅延

マスターは並列してトランザクションを処理して、最終的にコミットした順で反映されれば問題ないようになっています。
一方、スレーブはマスターと確実に同じ順序で再生する必要があるため、トランザクションを並列に実行できません。
そのため、スレーブの更新性能はマスターより低く、更新処理が増えるとスレーブの SQL スレッドが間に合わなくなってきます。

SQL スレッドが間に合わなくなると、スレーブに relaylog はあるのに SQL スレッドに実行されてない状態のレプリケーション遅延が発生します。
このレプリケーション遅延は、 SHOW SLAVE STATUS で Seconds_Behind_Master を見ることで監視できます。

このレプリケーション遅延では、参照クエリをスレーブに向ける場合はかなり古いデータを見ても大丈夫なように気をつけないといけませんが、 relaylog は大抵マスターの最新から1秒以内の遅延で済んでいるので、突然マスターが死んで RAID の復旧などが不可能だった場合でも失うのはわずかな時間分のコミットで済みます。

MySQL 5.6 で問題になるレプリケーション遅延

MySQL 5.6 でグループコミットが導入されました。
これは、 binlog が有効かつ sync_binlog=1 の場合に、並列する複数のトランザクションのコミットを、トランザクションログではなく binlog をまとめて flush することで永続化します。(binlog が flush されるまでコミットが終わりません)

この binlog が flush される前にスレーブに転送されると、マスターでまだコミットが完了していないトランザクションがスレーブに反映されることになります。
スレーブを参照しているクエリは、まだマスターでSELECTしても見えない未来のデータを参照するかもしれませんし、マスターをクラッシュリカバリした時に flush が終わってない (コミットが終わってない) データが消えると、そのデータを受け取っていたスレーブではレプリケーションが再開できなくなってしまいます。

そのため、 MySQL 5.6.17 で、 binlog の flush が終わるまで LOCK_log というロックを取得しつづけ、 dump スレッドはそのロックを待ってからスレーブに転送する用になりました。

http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-17.html

Such problems are expected on less durable settings (sync_binlog not equal to 1), but it should not happen when sync_binlog is 1. To fix this issue, a lock (LOCK_log) is now held during synchronization, and is released only after the binary events are actually written to disk. (Bug #17632285, Bug #70669)

この修正の副作用として、更新負荷が増えると dump スレッドが LOCK_log を取得するのに時間がかかり、 binlog がなかなか転送されなくなってしまいます。
この問題は MySQL 5.7.2 で、 dump スレッドが LOCK_log を取得しなくてもどこまで転送していいか分かるようになって改善されたそうです。
(参考: Dump Thread Enhancement On MySQL-5.7.2)

このレプリケーション遅延では、 binlog がスレーブに転送されるのが遅れるので、 マスターが死んで復旧不可能だった場合に、遅延していた時間だけの情報が失われてしまいます。
この問題は SHOW SLAVE STATUS では監視できないので、 SHOW MASTER STATUS の File, Position と SHOW SLAVE STATUS の Master_Log_File, Read_Master_Log_Pos を比較する必要があるでしょう。

対策

この問題を軽減するには、 LOCK_log を待つ dump スレッドを減らすためにマスターに直接接続するスレーブを1つだけにし、他のスレーブは多段レプリケーションを活用する事ができます。

解決は難しいですが、次のような対応が考えられます。

1. MySQL 5.6 をスキップして、 5.7 が GA になるまで 5.5 を使い続ける
2. スレーブのフライングを許容し、 5.6.17 の該当部分の変更を revert してしまう。 (フライングの弊害として、マスターがクラッシュリカバリしてからレプリケーションを再開できない可能性があります)
3. MySQL 5.6 へ移行する代わりに、グループコミットやレプリケーションが別方式になった MariaDB に移行する。
4. RAID を信用して、マスターが死んでも簡単にはスレーブを昇格せずにディスク換装などでの復旧を試みる
5. LOCK_log の影響を受けない、ブロックデバイスレベルのレプリケーションを利用する. (RDS の Multi-AZ replication と同じ手法)
6. Semi-sync replication を利用し、 binlog が確実に転送されるのを待つ.

新卒や海外拠点の新しい Web プログラマーに MySQL のインデックスや explain, トランザクションと MVCC について説明するための研修資料を作りました。特に社内情報が含まれていないのでそのまま公開します。

MySQL Bootcamp

他人に講師役を任せたり、ある程度独習もできるようにしたつもりなので、よかったらご活用ください。

タイトルの通り、 innodb_flush_log_at_trx_commit=1 の場合も、トランザクションログが fsync されません。トランザクションがディスクに書かれるシーケンスは次のようになります。

1. prepare (fsync)
2. write binlog (grouped, fsync)
3. commit

このうち 1 は従来通り各スレッドで並列に実行され、 2 と 3 が昨日紹介した sql/binlog.cc の MYSQL_BIN_LOG::ordered_commit() の中のでグループ化されてシーケンシャルに実行される処理です。 この ordered_commit() は先頭で

     thd->durability_property= HA_IGNORE_DURABILITY;

この場合、データの永続性は prepare されたトランザクションの内容と binlog で担保されます。 クラッシュリカバリの時は、 binlog を読み込んで、そこに書かれているprepare済みトランザクションをコミットしていきます。 (このため、クラッシュしたマシンからDBをコピーしてMySQLを再起動する場合など、binlogのコピーをサボるとコミットしたはずのトランザクションが消えてしまうので注意が必要です。今のところリカバリに読まれるのは最後の binlog だけで、 binlog のローテート時はトランザクションログにも fsync します)

最終的に、トランザクションごとの fsync の数は、 MySQL 5.5 の場合は (sync_binlog=0 の場合) 2回だったのに対して、 MySQL 5.6 では2回のうち1回は負荷が高くなるほどグループ化されて複数のコミットを1度で fsync されることになります。この点で MariaDB の方が速そうというのは勘違いでした。

この辺りはドキュメントが整ってなくて、 MySQL-internals に質問のスレッドが立っているのでそこが参考になります。

これKristianがMonty Program ABの人で、MatsがOracleの人で、「お前の設計はおかしい」って喧嘩してるスレッドじゃないか。面白すぎる lists.mysql.com/internals/38705

— SH2さん (@sh2nd) 2013年4月26日

さらにZardoshtがTokuDBの人だった RT @sh2nd: これKristianがMonty Program ABの人で、MatsがOracleの人で、「お前の設計はおかしい」って喧嘩してるスレッドじゃないか。面白すぎる lists.mysql.com/internals/38705

— SH2さん (@sh2nd) 2013年4月26日

昨日の記事 で innodb_support_xa = 0にすると RDS が速くなることを紹介したのですが、その後 Twitter で innodb_support_xa = 0 にするとクラッシュ時だけでなく通常時も binlog とトランザクションログの一貫性が無くなる（コミットする順序が前後する）可能性があることを指摘していただきました。

innodb_support_xa=0すると、トランザクションがコミットされた順番でバイナリログに載ることが保証できなくなるんだけどいいのかな？ DSAS開発者の部屋:AWS RDS の書き込み性能チューニング dsas.blog.klab.org/archives/52108…

— ts. yokuさん (@yoku0825) 2013年4月24日

実際に、 MySQL 5.5 と 5.6 両方で、 innodb_support_xa の説明にそう書かれています。

• MySQL 5.5 の innodb_support_xa の解説
• MySQL 5.6 の innodb_support_xa の解説

// innodb_support_xa = 1 のとき
1. prepare (sync)
2. lock
3. write binlog (sync if sync_binlog)
4. commit (sync)
5. unlock

// innodb_support_xa = 0 のとき
1. lock
2. write binlog (sync if sync_binlog)
3. commit (sync)
4. unlock

しかし、 MySQL 5.5.31 のコードを見ると、実際には次のようになっていました。

// innodb_support_xa = 1 のとき
1. prepare (sync, lock)
2. write binlog (sync if sync_binlog)
3. commit (unlock, sync)

// innodb_support_xa = 0 のとき
1. prepare (do nothing)
2. write binlog (sync if sync_binlog)
3. commit (sync)

しかし、 MySQL 5.5 と 5.6 では大幅に書きなおされているらしいです。

prepare_commit_mutexがMySQL 5.6に見当たらないんです

— SH2さん (@sh2nd) 2013年4月24日

@methane 了解です。ha_innodb.ccのinnobase_xa_prepareでmutex取ってた部分がMySQL 5.6では空になっていて、だいぶリファクタリングされた感じです

— SH2さん (@sh2nd) 2013年4月24日

MySQL 5.6 のソースを呼んでみたところ、 sql/binlog.cc の MYSQL_BIN_LOG::ordered_commit() という関数で、 binlog とトランザクションの順序を守る処理が入っていました。 (というか、 5.5 で呼び出し側がそれを保証しないせいで innodb 側でロックをとると言う設計が無理矢理過ぎですよね)

この関数は binlog の書き込み、 sync, トランザクションのコミットまで順番に行う関数で、複数スレッドで実行されても大丈夫なようにしつつ、 binlog をグループコミットするような実装になっています。

1. トランザクションを flush キューに入れる
2. lock(log)
3. (stage1) flush キュー内の全てのトランザクションを binlog に書き、  sync キューに入れる
4. lock(sync) & unlock(log)
5. (stage2) sync キュー内の全てのトランザクションを取り出し、
    必要であれば最後のトランザクションまでのbinlogを sync してから、 commit キューに入れる
6. lock(commit) & unlock(sync)
7. (stage 3) commit キューの中身をすべて取り出し、順番にコミットしていく
8. unlock(commit)

この動作の解説とベンチマークが Binary Log Group Commit in MySQL 5.6 にありました。 XA を OFF にしてもトランザクションが失敗しない限りは binlog とトランザクションの一貫性が崩れないとはいえ、 innodb_support_xa=1 の時の性能が大幅に上がっているので、危険を冒してまで XA を OFF にする必要性は無さそうです。

この改善により、高負荷時は複数のトランザクションの binlog がまとめて書かれ sync されるのですが、残念なのは innodb 側のコミットは順番に1つずつ実行してしまうことです。 せっかく複数のトランザクションをコミット順に並べたのですから、トランザクションログへの書き込みも1回の fsync にまとめられたら性能が上がりそうです。

そういう改善がされていないのか検索してみたところ、 MySQL ではなくて MariaDB の方に見つけました。

[MDEV-232] Remove one fsync() inside engine's commit() method

該当のコミット

この MDEV-232 により、各 commit ではトランザクションログを書くだけで fsync せず、最後にまとめて fsync するようになります。 最近いくつかのLinuxディストリビューションで採用されたことで話題の MariaDB ですが、 AWS の Multi-AZ のように fsync が遅い環境では MySQL よりも高い性能を出せそうです。また機会があれば、 MySQL 5.6 と Maria DB のベンチマークを取ってみたいと思います。

4/25追記: innodb_support_xa=0 はクラッシュ時以外にも binlog と innodb の整合性が取れなくなる問題がありました。 innodb_support_xa と binlog の危ない関係 もご覧ください。

KLab でも最近は AWS を使ったプロジェクトがかなり増えてきました。 AWS で問題になりがちなのが、 RDB の性能が DSAS 環境に比べて低いことです。

DSAS ではバッテリーバックアップ付きのRAID + 非同期レプリケーションを使っているのですが、 RDS では Multi-AZ を使って耐障害性を確保しています。

この違いによって書き込み性能のチューニングのポイントが変わってきます。RAIDカードはデータが書き込みバッファに乗っている間は fsync が高速なのに対して、 Multi-AZ では別のAZにあるブロックデバイスに対して同期書き込みが実行されるので、 fsync のレイテンシが増えてしまいます。そこで、 fsync の数を減らすことと、書き込み並列度を上げることがより重要になってきます。

と、いうことは理屈でわかるのですが、実際にパラメータチューニングでどれくらい性能が上がるのかを実験してみました。対象となるパラメータは innodb_support_xa (デフォルト=1)と innodb_write_io_threads (デフォルト=4) です。

innodb_support_xa は2層コミットを有効にするオプションで、 mysql の内部でトランザクションログとbinlogの整合性をとるために利用されています。具体的には両方のログを書いて同期し、それが終わってからコミットして同期します。 これをオフにするとトランザクションログを書いてコミットして同期するだけで済む (デフォルトで sync_binlog=0 なので binlog 側は同期されない) ので、 fsync の数が減ります。

innodb_write_io_threads はバックグラウンドの書き込みスレッド数らしく、並列に同期する数ではないので、数字を上げてもどれくらい効果があるのかわからないのですが、とりあえず上限は16なのでこちらも設定してみます。

ということで、実際に試してみたときのスクリプトと結果がこちらです。
RDSベンチマーク結果

autocommit=1 の状態で簡単な update 文をひたすら並列に発行してトランザクション数を数えた結果、秒間トランザクション数が最大で2倍になりました。

注意点として、 innodb_support_xa を無効にすることで、フェイルオーバー時に binlog の整合性が保たれないので、 read replica を作り直す必要があります。これはDBのサイズによっては結構時間がかかるので、フェイルオーバー後は数時間サービスを停止するか、一時的にアプリのスレーブ参照をマスターに向けるか、（スレーブ参照はもともとレプリケーション遅延を見込んでいるはずなので）古い read replica をレプリケーションが止まったまま参照し続けるかする必要があります。

障害時の動作も試してみたかったのですが、電源ケーブルを抜いたりLANケーブルを抜いて突然死させられないのが RDS の難点ですね。

KLab Advent Calendar 2011 「DSAS for Social を支える技術」の11日目です。

「SHOW FULL PROCESSLIST を使った MySQL のプロファイリング」 で紹介したプロファイラですが、 id:sh2 さんからはてブで

秒間10回叩く例も http://developer.cybozu.co.jp/kazuho/2009/07/mysql-539d.html 。変数ぽい部分をカットする処理はmysqldumpslowのコードを移植するといいかも

というコメントをいただきました。 そろそろネタに困っていたので、 せっかくなので、多くのユースケースで 便利に使えるように改良しました。ぜひご活用ください。

myprofiler.py (gist)
github

解説

クエリのサマライズ

前のバージョンでは = 以降をバッサリとカットしてしまっていたのですが、 例えば "WHERE user_id='xyz' AND enable=1" が "WHERE user_id=" になってしまうので、 問題のクエリを探すのが難しいケースがありました。

mysqldumpslow を参考にクエリのサマライズを改良し、 "WHERE user_id='S' AND enable=N" のような形式で集計できるようにしました。

ini形式のファイルからMySQLの設定を読み込むように

オプション無しで実行した場合、 ~/.my.cnf の [DEFAULT] セクションから user, password, host, port などを読み込みます。 (port はオプション)。

別のファイルから読み込むときは -c 設定ファイルパス でファイルを指定できます。

別のセクションから設定を読み込むときは -s セクション名 でセクション名を指定できます。

行数やインターバルを指定できるように

頻出する順で何件を表示するかを -n オプションで指定できるようにしました。

また、 show full processlist を実行する間隔を -i オプションで指定できます。 秒間10回サンプリングしたい場合は -i 0.1 と指定してください。

加工前のクエリを出力できるように

標準出力に表示しているクエリは、同じ形のクエリをまとめて集計するために 実際のパラメータ等が消えてしまっています。例えば LIMIT 100 でも LIMIT 5 でも LIMIT N と表示されてしまいます。

実際のパラメータを調べたり、 explain を実行するために、元のクエリが見えた 方がいいこともあるので、 -o 出力ファイル名 で、取得したクエリをファイルに 出力できるようにしました。

注意点

Python 2.6 以上で動くようにするつもりで書いていますが、自分の環境も DSAS for Social も Python 2.7 を利用しているので Python 2.6 での動作は 未確認です。 (Python 2.5 以下は窓から投げ捨ててください) (追記: Python 2.5 で使えない機能も避けました。未確認ですが使えるかもしれません)

MySQL への接続に MySQLdb を使っていますが、これは拡張ライブラリなので、 インストールには libmysqlclient のヘッダファイルや Python のヘッダファイルが 必要になります。 手軽にインストールしたい場合は、ピュアPythonの PyMySQL をインストールしてください。

KLab Advent Calendar 2011 「DSAS for Social を支える技術」の5日目です。

@methane による MySQL を骨までしゃぶるチューニングシリーズ (シリーズ名は今考えました)のまとめとして、現在の DSAS for Social の MySQL のリアルな性能値や直面しているボトルネックを赤裸々に公開 してしまいます。

innodb_io_capacity を増やそう

本題に入る前に、まだ紹介してないけど1記事にするほどではなかった パラメータを紹介しておきます。

innodb_io_capacity は、 InnoDB に教えるヒントで、 Disk の IO/sec を指定します。 デフォルトでは、通常のHDDでも使えるように中途半端な値(バージョンによって100か200) になっているのですが、BBU付きバッファがあるRAIDカードを使うなどで　IO/sec が 高いマシンでは大き目に設定すると、若干ですが更新クエリ/secの限界値が上がりました。

いくつかの値を試してみたのですが、RAIDカードを使っている場合は 1000〜2000 くらいに設定すると性能が上がり、それ以上あげても性能が変わらなかったので、 この値を参考にしてください。

Gangliaのグラフを大公開

あるゲームでイベントをしているときのグラフを紹介します。 チューニングした結果ちゃんとサービスできてはいるけれど、 もうあちこちが限界です。

(順番に、マスターののCPU使用率、disk io, ネットワーク帯域とパケット数, 秒間クエリ数、SELECT,INSERT,UPDATE,DELETEクエリの内訳、そして スレーブのクエリの内訳になります)

まず、CPU使用率の半分をSystemが占めていますが、これはネットワーク 周りに起因するもので、純粋にクエリ数を減らさないと削減できなさそうです。 しかも、ソフトウェア割り込み処理などあまりコア数に対してスケールできない 部分があるので、2倍どころか1.5倍にも耐えられそうにありません。

Disk 書き込み速度は 50MB/sec にヒットしています。ダンプしたデータを取り込む時など、 ほとんどシーケンシャルな書き込みなら100MB/secを超えるのですが、通常のアクセスでは もう限界でしょう。

ネットワークでは、帯域こそギガビットを使いきっていないものの、 パケット/sec は 80K になっています。NICのハードやドライバにも よりますが、100K〜150K が限界で、限界に到達するとパケットロスが 大量発生してまともにサービスできなくなってしまいます。

秒間クエリ数を見てみると、トータルでは30K、更新クエリの数はマスター側では 見づらいのでスレーブ側を見るとだいたい 6.5K といったところでしょうか。 このグラフの時点ではレプリケーション遅延は時々数秒発生する程度なのですが、 もうすこし負荷が高いときは innodb_flush_log_at_trx_commit=0 にしても どんどんレプリケーションが遅延していくという状況になっていました。 (SELECTクエリがほとんどマスターに行っているのは数十分以上の レプリケーション遅延を前提としているためです)

MySQL チューニングの今後

CPUもネットワークもDisk IOも限界近くて、レプリケーションもどんどん遅延すると なると、もうチューニングでどうにかなる範囲を超えています。 分割するしかありません。

実は、上のグラフはすでに垂直分割をした後のもので、これ以上負荷が増えたら もう一段垂直分割することになるかもしれませんが、バランスよく分割できる 回数には限度があります。 面倒な水平分割から逃げるためにここまでチューニングしてきましたが、 腹をくくって今後のアプリケーションは水平分割を前提に設計していきます。

チューニングは楽しいですが、限界があり、限界を超えるにはアーキテクチャを 変えるしか無いという事実を覚えておく必要がありますね。

MySQLのチューニングにおいて非常に重要となるメモリ(バッファ)関連のパラメータについて、

• チューニングのポイント
• DSASのとあるDBサーバ(実メモリ4GB)の実際の設定値

をまとめてみます。
また、必要メモリの総量の計算や限界値を越えてないかチェックしてくれるスクリプトも紹介します。
是非、参考にしてみてください！

まず最初に注意点を。
バッファには2つのタイプがあります。

• グローバルバッファ
• スレッドバッファ

グローバルバッファはmysqld全体でそのバッファが1つだけ確保されるもので、 これに対し、 スレッドバッファはスレッド(コネクション)ごとに確保されるものです。

チューニングの際にはグローバル/スレッドの違いを意識するようにしましょう。 なぜなら、スレッドバッファに多くのメモリを割り当てると、コネクションが増えたとたんにアッという間にメモリ不足になってしまうからです。

innodb_buffer_pool_size

用途

InnoDBのデータやインデックスをキャッシュするためのメモリ上の領域です。

バッファ種別

グローバル

DSAS値

512MB

グローバルバッファなので、どかんと割り当ててください。
MySQL ABのドキュメントには、搭載メモリの80%とも書かれています。

innodb_additional_mem_pool_size

用途

InnoDBの内部データなどを保持するための領域です。

バッファ種別

グローバル

DSAS値

20MB

それほど大量に割り当てる必要はありません。足りなくなったらエラーログにその旨、警告が出ますので、そしたら増やせばいいのではないかと思います。

innodb_log_buffer_size

用途

InnoDBの更新ログを記録するメモリ上の領域です。

バッファ種別

グローバル

DSAS値

16MB

たいていは8MB、多くても64MBで十分で、あんまり多くする必要はありません。 なぜなら、バッファはトランザクションがCOMMITされるごと、または毎秒ディスクにフラッシュされるので、ほかのパラメータを厚くしたほうが得策です。

innodb_log_file_size

用途

InnoDBの更新ログを記録するディスク上のファイルです。
メモリではないのですがチューニングの上で重要なので解説します。

バッファ種別

---

DSAS値

128MB

大きくした方がパフォーマンスが向上します。
なぜなら、innodb_log_fileがいっぱいになると、メモリ上のinnodb_buffer_poolの中の更新された部分のデータを、ディスク上のInnoDBのデータファイルに書き出すしくみになっているからです。
ですので、innodb_buffer_pool_sizeを大きくしたら、innodb_log_file_sizeもあわせて調整しないとパフォーマンスが向上しません。

innodb_log_file_sizeの値は、1MB以上で、32bitマシンの場合は4GB以下にしなければならないとMySQL ABのドキュメントには書いてあります。
また、もうひとつ上限があります。innodb_log_fileはinnodb_log_files_in_groupの数(デフォルトは2)だけつくられるのですが、innodb_log_file_size×innodb_log_files_in_groupがinnodb_buffer_pool_sizeを越えてもいけません。
まとめるとこのようになります。

1MB < innodb_log_file_size < MAX_innodb_log_file_size < 4GB

MAX_innodb_log_file_size = innodb_buffer_pool_size * 1/innodb_log_files_in_group

もうひとつ注意しなければならないのは、innodb_log_file_sizeを大きくすればするほど、InnoDBのクラッシュリカバリの時間が長くかかるようになるという点です。

sort_buffer_size

用途

ORDER BYやGROUP BYのときに使われるメモリ上の領域です。

バッファ種別

スレッド

DSAS値

2MB

スレッドバッファなので、むやみに大きくするとメモリが足りなくなるので注意です。
DSASでは2MBか4MBにしています。

read_rnd_buffer_size

用途

ソート後にレコードを読むときに使われるメモリ上の領域です。
ディスクI/Oが減るのでORDER BYの性能向上が期待できます。

バッファ種別

スレッド

DSAS値

1MB

これもスレッドバッファなので、割り当て過ぎには注意が必要です。
DSASでは512KB〜2MBにしています。

join_buffer_size

用途

インデックスを用いないテーブル結合のときに使われるメモリ上の領域です。

バッファ種別

スレッド

DSAS値

256KB

スレッドバッファです。
そもそもインデックスが使われないようなテーブル結合はパフォーマンス向上の観点からすると避けるべきなので、このパラメータはそれほど大きくする必要はないでしょう。

read_buffer_size

用途

インデックスを用いないテーブルスキャンのときに使われるメモリ上の領域です。

バッファ種別

スレッド

DSAS値

1MB

これも、パフォーマンスを考えるならば、インデックスを使うようなクエリを発行するべきなので、それほど多くする必要はないでしょう。

key_buffer_size

用途

MyISAMのインデックスをキャッシュするメモリ上の領域です。

バッファ種別

グローバル

DSAS値

256MB

グローバルバッファです。
多く割り当てた方がパフォーマンスが向上します。グローバルバッファなのでどかんと割り当てられます。
もし、MyISAMを(あまり)使ってないのならば、小さくしてほかのパラメータに回すのもアリです。

ちなみに、キーキャッシュのヒット率は、SHOW STATUSの値を使って、以下の式で算出できます。

キーキャッシュのヒット率 = 100 - ( key_reads / key_read_requests × 100 )

myisam_sort_buffer_size

用途

MyISAMで以下の時のインデックスのソートに使われるメモリ上の領域です。

• REPAIR TABLE
• CREATE INDEX
• ALTER INDEX

バッファ種別

スレッド

DSAS値

1MB

通常のクエリ(DML)では使われないようなので、それほど多くする必要はないでしょう。

mymemcheck - メモリ関連のチェックツール

最後にmymemcheckという自家製のツールを紹介します。
ダウンロードはこちらから。

• mymemcheck v1.01 (gzip)

mymemcheckは、my.cnfもしくはSHOW VARIABLESの結果を元に、以下の3つのチェックを行います。

• 最低限必要な物理メモリの大きさ
• IA-32のLinuxでのヒープサイズの制限
• innodb_log_file_sizeの最大サイズ

実行結果例はこのようになります。

$ ./mymemcheck my.cnf

[ minimal memory ]
ref
  * 『High Performance MySQL』, Solving Memory Bottlenecks, p125

global_buffers
  key_buffer_size                     268435456   256.000 [M]
  innodb_buffer_pool_size             536870912   512.000 [M]
  innodb_log_buffer_size               16777216    16.000 [M]
  innodb_additional_mem_pool_size      20971520    20.000 [M]
  net_buffer_length                       16384    16.000 [K]

thread_buffers
  sort_buffer_size                      2097152     2.000 [M]
  myisam_sort_buffer_size               1048576  1024.000 [K]
  read_buffer_size                      1048576  1024.000 [K]
  join_buffer_size                       262144   256.000 [K]
  read_rnd_buffer_size                  1048576  1024.000 [K]

max_connections                             250


min_memory_needed = global_buffers + (thread_buffers * max_connections)
                  = 843071488 + 5505024 * 250
                  = 2219327488 (2.067 [G])

[ 32bit Linux x86 limitation ]
ref
  * http://dev.mysql.com/doc/mysql/en/innodb-configuration.html

  * need to include read_rnd_buffer.
  * no need myisam_sort_buffer because allocate when repair, check alter.

          2G > process heap
process heap = innodb_buffer_pool + key_buffer
               + max_connections * (sort_buffer + read_buffer + read_rnd_buffer)
               + max_connections * stack_size
             = 536870912 + 268435456
               + 250 * (2097152 + 1048576 + 1048576)
               + 250 * 262144
             = 1919418368 (1.788 [G])

          2G > 1.788 [G] ... safe

[ maximum size of innodb_log_file_size ]
ref
  * http://dev.mysql.com/doc/mysql/en/innodb-start.html

  1MB < innodb_log_file_size < MAX_innodb_log_file_size < 4GB

MAX_innodb_log_file_size = innodb_buffer_pool_size * 1/innodb_log_files_in_group
                         = 536870912 * 1/2
                         = 268435456 (256.000 [M])

    innodb_log_file_size < MAX_innodb_log_file_size
               134217728 < 268435456
             128.000 [M] < 256.000 [M] ... safe

いずれもMySQL ABのドキュメントなどに書かれている制限事項、そのままです。
パラメータ同士が関係しあっていることが多々あるので、パラメータを変更する際は、このようなチェックツールで確認するのがいいのではないかと思います。

参考

• MySQL ABのドキュメント
  • System Variables
  • InnoDB Performance Tuning Tips
• MySQL Users Conference 2005の発表資料
  • Advanced InnoDB Performance Tuning (PDF)
• MySQL Performance Blog >> MySQL Performance Presentations