January 28, 2014 By Stephane Combaudon
我々がパフォーマンス監査の仕事をする時には、MySQLの設定のレビューと改善提案を求められる。大抵の場合、たくさんのオプションがある中でほんのいくつかの設定しか変更するように提案しないことに、多くの顧客は驚く。この記事のゴールは、もっとも重要な設定をいくつか挙げてみることにある。
既にこういった提案は過去にもしているが数年前のもので、それ以来MySQLの世界ではたくさんの変化があったのだ。
話の前に
熟練した人でも、重大なトラブルを引き起こすミスをしでかすことがある。従って、ここに挙げたものを盲目的に適用する前に、以下のことを心に留めておいてほしい。
- 一度に変更するのは1つだけにしよう！その設定が有効かどうかを計測する唯一のやり方だ。
- 多くの設定は、オンラインで
SET GLOBALを使って変更できる。これは便利で、問題があっても簡単に元の設定に戻せる。しかし最終的には、設定を永続化したいなら設定ファイルに書き込むことになる。 - 設定ファイルに書き込んだ設定がMySQLを再起動してから有効になっていいない場合、正しい設定ファイルを使っているだろうか？正しいセクションに入れているだろうか？(この記事で挙げている設定は全て
[mysqld]セクションに入るものだ) - 設定変更後にMySQLが起動しない場合、正しい単位をつけているか？例えば、
innodb_buffer_pool_sizeはMB単位で書かなければならず、max_connectionsには単位はつけない。 - 設定ファイルには同じ設定を重複して書かないように。設定の変更を追いたいなら、バージョン管理システムを使おう。
- 「新しいサーバは2倍のRAMが載ってるから、この設定も前の2倍にしよう」といった、甘い考えに基づく計算をしないこと。
基本的な設定
ここでは確認すべき3つの設定を挙げる。これをしない場合、すぐに何らかの問題が発生する可能性が高い。
innodb_buffer_pool_size: InnoDBを使っている場合、どんな環境でも一番最初に設定しなければならない値。バッファプールとは、データとインデックスがキャッシュされる領域だ。これをできる限り大きくしておくことで、読み出しの処理をディスクからではなくメモリから行うことができるようになる。一般的な値は、5-6GB(8GB RAMの場合)、20-25GB(32GB RAM)、100-120GB(128GB RAM)。innodb_log_file_size: redoログのサイズだ。redoログは、書き込みの高速化と耐久性の確保、クラッシュリカバリのために使われる。MySQL 5.1以前は調整が難しく、高速化のためには大きなredoログに、高速なクラッシュリカバリのためには小さなredoログに設定する必要があった。幸い、クラッシュリカバリのパフォーマンスはMySQL 5.5で大きく改善されたので、書き込みの高速化と高速なクラッシュリカバリを両立できるようになった。MySQL 5.5までは、redoログは全体で4GB(デフォルトで2ファイル)までに制限されていたが、MySQL 5.6で引き上げられた。
まず、innodb_log_file_size = 512M (1GB分のredoログ)から始めるのは、書き込みに十分な余裕があってよいだろう。アプリケーションが、書き込みが多く、MySQL 5.6を使っているなら、innodb_log_file_size = 4Gでよい。
max_connections: 「Too many connections」エラーによく出くわすなら、max_connectionsが低すぎるということだ。アプリケーションがデータベースへの接続を正常に閉じないというのはよくあることで、そうなるとデフォルトの151コネクションよりも多く必要になってしまう。max_connectionsの値を大きく設定する場合(例えば1000以上)の問題としては、何らかの理由により1000あるいはそれ以上のトランザクションを処理することになった場合、サーバが応答しなくなる可能性があることだ。アプリケーションレベルでコネクションプールを使ったり、スレッドプールを使うと、これらの問題を軽減できるだろう。
InnoDBの設定
InnoDBはMySQL 5.5からデフォルトのストレージエンジンであり、他のストレージエンジンよりもよく使われている。それゆえ、注意して設定をする必要がある。
innodb_file_per_table: この設定は、データやインデックスを共有の表領域に保存すべきか(innodb_file_per_table = OFF)、テーブルごと別々のファイルに保存すべきか(innodb_file_per_table = ON)をInnoDBに伝えるものだ。テーブルごとにファイルを作ることで、テーブルのDROP、TRUNCATE、あるいはリビルドの際にディスク使用量を減らすことができる。また、圧縮などの高度な設定を使う場合に必須の設定でもある。一方で、パフォーマンス上の利点は特にない。この設定をONにしない主な理由としては、テーブルの数が非常に多い(1万以上)場合くらいだろう。 MySQL 5.6では、デフォルト値がONになっているので、大抵の場合は変更の必要はない。それより前のバージョンでは、データを投入する前に明示的にONにしないと、新しく作られたテーブルにしかこの設定は反映されない。innodb_flush_log_at_trx_commit: デフォルト設定である1は、InnoDBが完全にACID準拠であることを意味している。マスタデータベースなど、一番気にしたいことがデータの保全なら、この設定はベストなものだ。しかし、全ての変更をREDOログに書き込むために余計なfsync(ファイルシステムとの同期)が必要になるため、遅いディスクを使用している場合は致命的なオーバーヘッドになり得る。これを2に設定すれば、コミットされたトランザクションは1秒おきにREDOログに書き込まれることになるので、少し信頼性が落ちる。しかしマスタでもこれを許容できる場面はあるだろうし、レプリケーション先のスレーブでは最も良い設定だろう。0はより高速だが、クラッシュ時にデータが失われる可能性がより高くなるので、スレーブにのみ適した設定だ。innodb_flush_method: この設定は、データとログをどのようにディスクに書き込むかを制御するものだ。バッテリで保護されたライトバックキャッシュ付きのハードウェアRAIDコントローラ搭載サーバの場合によく使われるのはO_DIRECTで、それ以外の場合はデフォルトのfdatasyncでよいだろう。sysbenchは、どちらの値を使うか決めるのに役立つだろう。innodb_log_buffer_size: コミットされていないトランザクションのためのバッファのサイズだ。デフォルト値(1MB)は多くの場合ちょうどいいが、大きなblobやテキストのカラムを扱うトランザクションを使っている場合、すぐにバッファはいっぱいになり、余計なI/Oを引き起こすことになる。SHOW ENGINE INNODB STATUSのInnodb_log_waitsを確認してそれが0でなければ、innodb_log_buffer_sizeを増やすこと。
その他の設定
query_cache_size: クエリキャッシュは、並列度が低い時でもボトルネックになることがある機能として知られている。一番いい設定は、初めからquery_cache_size = 0にして無効にしてしまうことだ(MySQL 5.6からはこれがデフォルトだ)。そして、参照のクエリは、正しいインデックス、レプリケーションによる参照負荷の分散、外部キャッシュの仕組み(memcachedやRedisなど)といった、別な方法で高速化しよう。既にクエリキャッシュを有効にした状態でアプリケーションを作ってしまっていて、かつ特に問題が出ていないようなら、クエリキャッシュの機能は有用だと言える。この場合はむしろクエリキャッシュを無効にするのに慎重になるべきだ。log_bin: サーバをレプリケーション構成のマスタにしたいならバイナリロギングの有効化は必須だ。その場合は、server_idをユニークな値にするのも忘れないように。それから、シングルサーバ構成の時も、ポイントインタイムリカバリをしたいなら便利な設定だ。最新のバックアップデータをリストアし、それからバイナリログを適用すればよい。バイナリログファイルは、ひとたび作られたら消されることはない。従って、ディスク使用量を一杯にしてしまいたくなければ、PURGE BINARY LOGSコマンドで古いログファイルをパージ(削除)するか、ログファイルが何日後に自動消去されるかをexpire_logs_daysに設定する必要がある。 バイナリログは(パフォーマンスに対して)タダではないので、マスタでないレプリケーションスレーブなどの場合、無効のままが推奨だ。skip_name_resolve: クライアントが接続してきたら、サーバはホスト名の名前解決をしようとする。その時DNS応答が遅いと、コネクションの生成も遅くなってしまう。このため、skip-name-resolveを設定してDNS名前解決を無効にしてサーバを起動するのをおすすめする。これによる唯一の制限が、GRANT文でIPアドレスしか使えなくなることだ。既存のシステムにこの設定を追加する時はその点に注意しよう。
まとめ
もちろんこの他にも負荷の種類やハードウェアによって他の設定値がある。メモリが少なく高速なディスクがある場合、クエリの並列度が高い場合、書き込みの方が多い場合などでは、特別なチューニングが必要だ。とはいえ、この記事で目的としたのは、重要でないMySQLの設定をいじったり、何が重要なのかを理解するためにドキュメントを読むのに時間を使いすぎず、素早く健全なMySQLの設定ができることなので、その点を注意されたい。
