数々のデフォルト値の改善によって、過去のバージョンと比べてMySQL 5.6では設定しなくてはならない値がかなり減った。とは言え、変更すべきものについてここで書いておきたい。
InnoDBの設定
innodb_buffer_pool_size- デフォルトは128M。これは、メモリにロードされるデータとインデックスのためにInnoDBがどのくらいメモリを使うかを指定するものなので、設定すべき重要な値だ。MySQLの専用サーバなら、搭載されているメモリの50%から80%が推奨される設定値だ。例えば、64GBのRAMを搭載しているサーバなら、バッファプールは50GB程度にすべきだろう。innodb_log_file_size- デフォルトは48M。書き込みが高負荷なシステムでは、パフォーマンス改善に貢献するよう、バックグラウンドでのディスクへの書き込みまでの時間を延ばすために、値を大きくしてもよい。4G以上の値が安全。歴史的には、大きなログファイルの操作の欠点としてクラッシュリカバリの時間が長くなることが言われてきたが、5.5や5.6でこの点は大幅に改善された。innodb_flush_method- デフォルトはfdatasync。ハードウェアRAIDコントローラを使っているなら、この値はO_DIRECTにしてもよい。これにより、InnoDBバッファプールのページを読む時に、それがInnoDBとOSのファイルシステムキャッシュの両方にあるという「ダブルバッファリング」を防止する。ハードウェアRAIDコントローラを使っていないか、SAN環境の場合、
O_DIRECTはパフォーマンス悪化の原因となり得る。これに関しては、マニュアルとバグ #54306に詳細がある。innodb_flush_neighbors- デフォルトは1。シーケンシャルIOに対してパフォーマンスの向上が現れないSSDでは、0(無効)に設定すべきだ。ハードウェアの設定によっては、RAIDを使用している際も無効にするとよい場合がある。これは、論理的にシーケンシャルなブロックの並びが、物理的な並びと同じであることが保障されないためだ。innodb_io_capacityとinnodb_io_capacity_max- これらの設定は、InnoDBが秒単位でどの程度バックグラウンド処理を行うかに影響を与える。以前の記事で、(InnoDBのログの書き込み以外に)どの程度の書き込みがバックグラウンドで行われているかについて書いた。ハードウェアについての知識があるなら(あるいはそのハードウェアが程度の処理を行えるか知っているなら)、何もしない状態にしておくよりはそのキャパシティは使った方がいい。これは、席を空っぽにして飛び立つ飛行機のようなものだ、というのがこれを説明するのに好きな比喩だ。その席は、悪天候で欠航するかもしれない後の時間のフライトに乗るべきだった人を乗せておいた方がよいこともあるだろう。つまり、後に残る負担を減らすために、バックグラウンド処理はなるべく早いうちに済ませておいた方がよいということだ。
簡単な数学で表すと、200IOPSの書き込みができるディスクを使うと、10ディスクのRAID10だと(10/2) * 200 = 1000IOPSだ。「簡単な数学」と言ったが、RAIDコントローラは処理をマージして、処理できるIOPSを効率的に増やすことができる。SSDの場合、IOPS値は数千の単位になるだろう。
この値をあまりにも大きくしすぎると、ディスクの処理性能に対して、フォアグラウンド処理を邪魔するほどのバックグラウンド処理をさせてしまうことになる。歴史的には、この値を大きくしすぎると、InnoDBが内部ロックを起こしてしまいパフォーマンスを悪化させてしまうことがあった(私の知る限り、これは5.6で劇的に改善されている)。
innodb_lru_scan_depth- デフォルトは1024。これはMySQL 5.6から導入された設定だ。どう設定すべきかはMark Callaghan氏がアドバイスを書いている。簡単に言えば、innodb_io_capacityを増やしたら、innodb_lru_scan_depthも増やすべき、ということだ。
レプリケーション
このサーバでレプリケーションあるいはポイントインタイムリカバリをしたいとしよう。この場合は以下の設定が必要だ。
log-bin- バイナリログを有効にする。デフォルトではバイナリログはクラッシュセーフではないが、以前にも書いたように、ほとんどのユーザは永続性を主眼に置くべきだと考えている。この場合、以下の設定もするべきだ。expire-logs-days - デフォルトでは、古いバイナリログは永遠に保存される。この値は1から10日にすることをおススメする。長い値を設定しても、バックアップからリストアした方が短時間で済むことが多いので、あまり意味はない。
server-id- レプリケーションのトポロジ内のサーバはそれぞれユニークなserver-idを持つ必要がある。binlog_format=ROW- 行ベースのレプリケーションに変更する。行ベースのレプリケーションについては最近書いたように、ロックを減らしてパフォーマンスをよくするので、私はこの設定が大好きだ。もう2つ有効にする必要がある設定がある。
その他
timezone=GMT- タイムゾーンをGMTにする。全サーバGMTにそろえておくべきだというシステム管理者が増えている。今日ではほとんどすべてのビジネスがグローバルなので、個人的にもこの設定が大好きだ。最初に使い始めたロケールに関しては任意のものだ。character-set-server=utf8とcollation-server=utf8_general_ci- 以前の記事でも書いたように、新しいアプリケーションにはutf8をデフォルトにするのがよい。その代わりに、アプリケーションが設定しようとしている文字コードを無視するのに、skip-character-set-client-handshakeを設定してもよい。sql-mode- MySQLのデフォルトは非常にゆるく、何の警告もなしにデータを切り捨ててしまう。前に書いた記事で、新しいアプリケーションには以下の設定がよいと書いた。STRICT_TRANS_TABLES
ERROR_FOR_DIVISION_BY_ZERO
NO_AUTO_CREATE_USER
NO_AUTO_VALUE_ON_ZERO
NO_ENGINE_SUBSTITUTION
NO_ZERO_DATE
NO_ZERO_IN_DATE
ONLY_FULL_GROUP_BY
skip-name-resolve- 入ってくるコネクションに対しての名前の逆引きを無効にする。システムによっては、DNS名前解決は遅かったり不安定になることがあるので、権限の一部にホスト名を使わないのなら、名前解決はしない事をおすすめする。max_connect_errors- Todd Farmer氏が書いているように、「(この機能は)ブルーとフォースアタックに対してなんら意味をなさない」。その上、skip-name-resolve(上で推奨した値)が設定されている場合にはmax_connect_errorsは有効にすらならない。この場合はファイアウォールが適しているし、通常こういった時にはパブリックもインターナルもポート3306を閉じて、MySQLへのアクセス権限があるアプリケーションだけが接続できるようにしてしまう。それから、「二重の設定」を回避してそれが影響を与えないように、
max_connect_errorsを10000に設定する。max-connections- デフォルトは151。この値を300～1000といった大きな値に設定しているユーザをよく見る。大抵の場合、これを大きく設定するのはやむを得ないことだろう。しかし、16コアのマシンはIOブロッキングの間おそらく2倍から10倍の処理を受け止められるだけだと思われることから、少し私は神経質になってしまう。ここであなた方が望むのは多くのコネクションはアイドル状態になることだろうが、ひとたびそれらが全てアクティブになると、スレッドのスラッシングが起きてしまうだろう。
私が考えるこの問題を解決する理想的な方法は、たくさんのコネクションを張った状態を保ったり、プールしていないコネクションを可能な限りの速さで張ったり切ったりするよりも、データベースとのやり取りのためにコネクションプールをうまく設定することだ。5.5以降(そして数少ないMySQLのcommunity editionとenterprise editionの違いでもある)においてこの問題を解決する別の方法としては、スレッドプールプラグインがある。
結論
それでは、以下のMySQLサーバを設定することを考えてみよう。
- 64GB RAM
- ハードウェアRAIDコントローラ(最低でも2000IOPS処理できると考える)
- レプリケーションが必要
- 新規開発のアプリケーション
- ファイアウォールがある
- 権限にホスト名を使う必要はない
- グローバルなアプリケーションで、ひとつのタイムゾーンだけで使われるわけではない
- アプリケーションは永続性が必要
これがサンプルの設定だ。
# InnoDBの設定
innodb_buffer_pool_size=50G
innodb_log_file_size=2G
innodb_flush_method=O_DIRECT
innodb_io_capacity=2000
innodb_io_capacity_max=6000
innodb_lru_scan_depth=2000
# バイナリログ、レプリケーション
log-bin
sync_binlog=1
sync_relay_log=1
relay-log-info-repository=TABLE
master-info-repository=TABLE
expire_logs_days=10
binlog_format=ROW
transaction-isolation=READ-COMMITTED
innodb_autoinc_lock_mode = 2
# その他
timezone=GMT
character-set-server=utf8
collation-server=utf8_general_ci
sql-mode="STRICT_TRANS_TABLES,
ERROR_FOR_DIVISION_BY_ZERO,
NO_AUTO_CREATE_USER,
NO_AUTO_VALUE_ON_ZERO,
NO_ENGINE_SUBSTITUTION,
NO_ZERO_DATE,
NO_ZERO_IN_DATE,
ONLY_FULL_GROUP_BY"
skip-name_resolve
max-connect-errors=100000
max-connections=500
# このマシンに一意なもの
server-id=123
これが主要なものを網羅できていたらうれしい。もし確実なアドバイスをお持ちなら教えて欲しい。このリストに追加するよ！
