1. バッファを増やす、または減らす
チューニングの基本中の基本であるが、適切なバッファサイズを設定することはパフォーマンスチューニングの要である。主なバッファは次の通り。* innodb_buffer_pool_size・・・InnoDBだけを利用する場合は空きメモリの7〜8割程度を割り当てる最も重要なバッファである。余談だが、実際にはここで割り当てた値の5〜10%ぐらいを多めにメモリを使うので注意が必要だ。
* key_buffer_size・・・MyISAMだけを利用する場合は、空きメモリの3割程度を割り当てるといい。残りはファイルシステムのキャッシュ用に残しておこう。
* sort_buffer_size・・・ソート処理に利用するバッファである。OLTPでは256K〜1Mぐらいを割り当てると良い。これがあまり大きすぎると、メモリの割り当てのオーバーヘッドが大きくなるので注意しよう。DWH系の処理などで大きなソートが必要な場合、セッションごとに動的に調整すると良い。
* read_buffer_size・・・全件スキャンをするときに利用するバッファ。OLTPでは128K〜512Kぐらいを割り当てると良い。
* read_rnd_buffer_size・・・ソート処理でインデックスを利用する場合に利用するバッファ。OLTPでは256K〜1MぐらいをDWH系の処理などで大きなソートが必要な場合、セッションごとに動的に調整すると良い。
バッファは増やせば増やすほどいいかと言えばそうではない。メモリの割り当てがオーバーヘッドになるので、無駄に大きくし過ぎることは禁物である。また、バッファを増やしすぎたためにスワップが発生するとパフォーマンスが悲惨なことになるのでくれぐれも空きメモリ容量には注意しよう。
2. 高速なディスクを利用する
MySQLだけに限った話ではないが、RDBMSのボトルネックは99.99999%がディスクI/Oである。特にディスクのシークタイムによる待ち時間が大きい。理想的にはバッテリーバックアップ付きのRAID装置を利用するのがいい。最近はRAID装置に匹敵するほど高速なSSDが出てきているので楽しみである。前述のようにバッファを大きくするとディスクI/Oの回数や量が減るので、必ずしも高速なディスクが性能を向上させるというわけではないが、データサイズが大きくてバッファに収まりきらない場合などにはどうしてもI/Oが大量に発生してしまう。そんな時は高速なディスク装置を利用するといい。
3. クエリを最適化する
実は最も大事なのがクエリの最適化である。いくら他の部分を最適化したところで、毎回全件スキャンが発生していたのでは話にならない。適切にインデックスを使ったり、サブクエリをJOINに書き換えたりすることで、フェッチしないといけない行数ができるだけ少なくなるようにクエリを書きかえよう。クエリを最適化するには、まずEXPLAINで実行計画をチェックしよう。EXPLAINの見方についてはいずれ解説しようと思う。
また、テーブルから全件フェッチしてからアプリケーション側で行を絞り込むというようなロジックを実装してはいけない。必ずSQL文、つまりWHERE句で行の絞り込みができるようにしよう。
クエリを手当たり次第チューニングしていてはいくら時間があっても足りないだろう。問題のあるクエリだけをチューニングするべきであるが、そのようなクエリを見付けるにはスロークエリログや商用のクエリアナライザを用いると効果的である。
4. テーブルを最適化する
基本中の基本は、適切なデータタイプを使うということである。できるだけカラムサイズが小さくなるようなデータタイプを選ぼう。数値をVARCHAR(桁数)などのデータタイプで格納しているのをたまに見かけるが、これは誤りである。INTまたはBIGINTなどを利用したほうがずっとデータサイズが小さくなるし高速である。
また、適切なカラムに対してインデックスをつけるのも重要である。どのカラムにインデックスをつけるかは、クエリのパターンに因る。インデックスが多すぎると更新時のオーバーヘッドが大きくなるだけでなく、インデックスツリーを格納するためのデータ容量が増えてしまうので、インデックスのつけすぎには注意しよう。たまに全てのカラムにインデックスがついているテーブルを見かけるが、そのようなテーブル設計は誤りである。クエリのパターンによっては、マルチカラムインデックスやパーティショニングが必要になるなどいろいろと工夫が必要になる。
カラム数がが多くなりすぎたら、まずは正規化できるかどうかを検討してみて欲しい。DWH用途などでは逆に非正規化すると性能が向上する場合がある。
5. 目的に合ったストレージエンジンを選択する
これはMySQLの醍醐味である。ストレージエンジンはそれぞれ性能特性がまったく違うので、目的に合ったストレージエンジンを選択すると劇的に性能が向上する場合がある。例えば、OLTPではInnoDB、参照系が多い場合はMyISAM、ログ目的であればARCHIVE、リアルタイム並列処理であれば NDBCLUSTERなど。他にもSun/MySQL以外のサードベンダーやコミュニティからリリースされているストレージエンジン（SPIDER、 PBXT、XtraDB、Q4M、Infobright、Kickfireなど）もあるので、目的に合わせて色々検討してみるといいだろう。
6. レプリケーションで負荷分散する
MySQLほどお手軽に、そして安価にレプリケーションを利用出来るRDBMSは他にないだろう。レプリケーションを用いてたくさんのスレーブへ参照系の処理を負荷分散するテクニックは、Webサイトなどで頻繁に利用されているテクニックである。参照系の負荷分散を行う場合だけでなく、例えばOLTPのデータを元にBIなどの処理を毎日行う場合などにも有効である。スレーブ上でBIを行えば、マスター上のOLTP系の処理に影響を与えることがない。
7. ストアドプログラムを多用しない
残念ながら、MySQLはストアドプロシージャ、ストアドファンクション、トリガなどの性能はあまりよくない。出来るだけそれらを利用せずに、ロジックをアプリケーション側に持っていくといいだろう。
8. ファイルシステムをチューニングする
Linuxであればデフォルトはext3（そろそろext4になっていくだろうか？）であるが、ext3ではなくXFSを利用すると性能が向上する場合がある。また、I/Oスケジューラを変更することで、同じext3であっても性能特性が変化する。SolarisではUFS、ZFS、QFSなどの利用を検討するといいだろう。WindowsならNTFS以外にあまり選択肢はないが、MyISAMの場合はLargeSystemCacheを有効にするなどのチューニングが必要である。
9. コネクションプールを利用する
アプリケーションがDB操作が必要なときに都度MySQLサーバへ接続していたのでは、接続のためのオーバーヘッドが無視出来なくなる。そんなときはコネクションプールを利用するといい。
10. ベンチマークする
どんなチューニングでも、実際に効果があるかどうかは測定してみるまで分からない。また、あるアプリケーションで効果があるチューニングでも、他のアプリケーションの負荷パターンでは逆効果になってしまうということは多々ある。なので、アプリケーションの負荷を擬似的に作り出してチューニングの効果を測定することはとても重要なのである。
11. インテルコンパイラ版を使う。
MySQLのダウンロードサイトではLinux向けにインテルのコンパイラを使って作成されたバイナリが配布されている。インテル版のコンパイラは、もちろん処理によって得手不得手はあるのだが、GCCを利用して作成されたバイナリよりも良い性能を発揮することが多い。インテルコンパイラ版を使うというのは非常にお手軽な高速化テクニックであろう。ちょうどSourceForge.JP Magazineで「インテル コンパイラーの実力を測る――インテル コンパイラー版MySQLは本当に速いのか？」という記事が書かれているので参照して欲しい。こちらではベンチマークも行っている。
ちなみに、SolarisにはSunStudioというサン・マイクロシステムズ純正のコンパイラがあり、こちらのコンパイラで作成したバイナリもGCCより高速になる場合が多い。残念ながら、SunStudioで作成されたバイナリは配布されていないので、SunStudioを試したい人は自分でコンパイルしよう。SunStudioは無料で配布されている。
12. 同時実行スレッド数を調節する。
ちょっとやそっとの負荷ではあまり問題にならないが、1000のクライアントから一斉に接続してクエリを実行する場合などは同時実行スレッド数を調節するといい。処理を並列に行うとスループットの向上が期待出来るが、同時に実行中のスレッドがあまりに多すぎるとロックの競合が多発してしまって返って性能を落とす原因になってしまう。innodb_thread_concurrencyオプションを利用するとInnoDBが内部的に利用するスレッド数を調節することができる。このオプションはいくつがいいかということについては明確な答えはない。アプリケーションの負荷によるところもあるし、CPU数やOSのスケジューラにも影響されるからである。従って、適切な値を決めるにはベンチマークが必須である。逆に言うと、ベンチマーク時には必須の調整パラメータであると言える。特にCPUコア数が増えた場合には調整することにより性能の向上が見込める。innodb_thread_concurrencyオプションのデフォルト値はバージョンによって異なるが現在は8である。0に設定得るとスレッド数の上限がないことを示す。最大値は1000。まずは0、4、8、12、16、20辺りで変化させて性能を計測するといいだろう。MySQL 6.0ではさらにスレッドプールが搭載される予定である。InnoDB内部の同時実行スレッド数だけでなく、MySQLサーバの接続スレッドの同時実行数を調節することができるようになる。例えば、同時に実行するスレッド数を100までに限定したい場合には次のように指定する。
thread_handling=pool-of-threads
thread_pool_size=100
現時点ではLOCK_event_loopの競合が発生するという問題があるためスレッドプールによる性能向上は見込めないが、正式版がリリースするまでには問題は解消されスレッドプールによって同時実行性能が改善することだろう。ちなみに、MySQL 6.0のスレッドプールはlibeventで実装されている。スレッドプールもMySQL 6.0のリリースを楽しみにさせてくれる要素の一つである。
13. 適切なサイズでCOMMITする。
一度に大量の更新を行うと、更新している間はその行に対して排他ロックがかけられるためロックの競合が発生する。ロックの競合が多発すると同時実行性能の低下に繋がってしまう。逆に言うと、OLTP系のアプリケーションで用いる場合などは、一回のトランザクションでCOMMITするサイズを小さく調節することでデータベース全体のスループットを向上させるのがいいだろう。また、更新のサイズがInnoDBのログサイズより大きい場合には、COMMITの前にテーブルスペース内にUndoログを大量に作成する必要が生じるため、I/O性能が低下するという問題が発生する。I/O性能だけでなく、不要になったUNDOログはパージスレッドが回収するまでテーブルスペース内に残り続けてしまうので容量も圧迫してしまう。LOAD DATA IN FILEなどで一度に大量のデータを更新するのは避けた方がいいだろう。
もし新たなInnoDBテーブルにCSVファイルからデータを大量にロードしたい場合にはLOAD DATAコマンドを利用せず、CSVストレージエンジンからInnoDBテーブルにALTER TABLEするという技が存在するのでお勧めである。ALTER TABLE時には1万行ごとにCOMMITしてくれるので、上記のようなUNDOログの問題は発生しない。
MySQL Performance Blogにおいてfifoを使ってLOAD DATAを小刻みに行う方法が紹介されているので、興味のある人は参照して欲しい。
14. InnoDBのログサイズを調整する。
InnoDBのログファイルのサイズが小さすぎるとInnoDBの更新処理の性能が低下してしまう。前述したように大きなデータをロードする際のパフォーマンス低下も問題であるが、ログファイルのサイズが小さいとチェックポイント処理が頻繁に発生することによる影響がとても大きくなってしまう。ならば巨大なログファイルを作ればいいじゃないか？と思うだろうが話はそう簡単ではない。ログファイルのサイズが大きいと今度はクラッシュリカバリ（Redoログ適用）の時間が増えてしまうからだ。特にHA化されたMySQLサーバではクラッシュリカバリの時間が増長するとフェイルオーバーに時間がかかる時間も長くなってしまうので好ましくない。従って、適切なログファイルのサイズを選択することは非常に大切である。InnoDBのログサイズ調節方法については過去の投稿で説明したので参照して欲しい。
15. 最新版を利用する。
希に「MySQLは4.0が一番性能がよかった」などと嘯（うそぶ）く人を見かけるが、そんなことはない。MySQLもバージョンが上がるごとに性能向上のための機能が追加されている。* 5.0 GA ... greedyオプティマイザ、Index Merge、InnoDBのTRUNCATE性能改善、NDBのCondition Pushdownなど。
* 5.0.30 ... InnoDBバッファプールのロック改善
* 5.0.50 ... クエリキャッシュのパフォーマンス改善
* 5.0.54 ... InnoDBのCPUスケーラビリティの向上
* 5.1.x ... InnoDBのAUTO-INCロックの性能改善
MySQL 5.0.30と5.0.54で行われた改善については、マイコミジャーナルの記事で詳細な説明が行われているので参照して欲しい。
さらにMySQL 6.0では性能に関するものとして次の新機能が追加される予定である。
* BKA JOIN
* Multi Read Range
* Index Condition Pushdown
* サブクエリ最適化
* スレッドプール（前述）
単純なクエリのレスポンスタイムはあまり変わらないかも知れないし、処理の内容によってはバージョン間で得手不得手が存在するが、システム全体で見た時のスループットは着実に向上しつつある。
16. READ COMMITTED分離レベルを使う。
InnoDBはデフォルトではREPEATABLE READ分離レベルを利用する。REPEATABLE READ分離レベルを利用するとPHANTOM READという問題を防ぐことができるが、ロックの競合がより頻繁に発生するようになってしまう。InnoDBではREAD COMMITTED分離レベルおよびinnodb_locks_unsafe_for_binlogオプションを利用することで、ロックの競合を減らして同時実行性能を向上させることが可能であるので、同時実行性能が重要な場合にはREAD COMMITTED分離レベルを利用しよう。（ちなみに、InnoDBは他のRDBMSとは違ってREAD COMMITTED分離レベルでNON REPEATABLE READは発生しない。）ただし、ステートメントベースレプリケーション（SBR）しか利用出来ないMySQL 5.0以前のバージョンでは、innodb_locks_unsafe_for_binlogオプション利用時にバイナリログの一貫性を保証できないという問題があった。（そしてinnodb_locks_unsafe_for_binlog利用時には同時実行性能が改善しないというオチがある。）MySQL 5.1では行ベースレプリケーション（RBR）が追加されたため、同時実行性能を保ちつつバイナリログの一貫性を保証することが可能になっている。ただしこの変更により、READ COMMITTED分離レベル利用時またはinnodb_locks_unsafe_for_binlogオプションが有効になっている場合にはRBRの利用が必須となった。
READ COMMITTEDが利用出来るのはどのような場合か？明確なのは一回のトランザクションで複数の処理を行う必要がない場合である。例えばSNSサイトの日記データなどはREAD COMMITTEDで問題ないだろう。更新は日記の文章をINSERT、参照は最新のデータのSELECTをそれぞれ一回行うだけで実装出来るため、一回のトランザクション内で複数の処理を行う機会はあまりない。多くのWebアプリケーションでは一回のリクエストが一回のクエリに対応する。従って、多くの WebアプリケーションでREAD COMMITTEDが利用出来るだろう。
ちなみに、利用出来る分離レベルはストレージエンジンによって異なる。他のストレージエンジン、例えばMySQL ClusterではREAD COMMITTED分離レベルしか利用出来ない。 FalconではREAD COMMITTED、REPEATABLE READ、SERIALIZABLEをサポートしている。
READ COMMITTEDの有効性については別途紹介したい。
17. EXPLAIN以外のチューニングコマンド。
SHOW STATUSコマンドはMySQLサーバ上の統計情報を見ることができるコマンドである。スコープ（GLOBALまたはSESSION）を指定することにより、SHOW GLOBAL STATUSではサーバ全体の統計情報を、SHOW SESSION STATUSではセッションごとの統計情報を見ることが可能である。SHOW GLOBAL STATUSを利用するときは、10分ほど間隔を空けて2回情報を採取し、増分を見ることで単位時間あたりの負荷を測定するのがコツ。（逆にいうと累積情報を見てもあまり意味がない場合が多い。）また、SHOW SESSION STATUSを利用するときは、FLUSH STATUSコマンドを実行することでセッションにおける統計情報をクリアすることができる。特定のクエリ実行時の統計情報を見るときなどに便利である。SHOW STATUSコマンドで気をつけるべき主な変数は次の通り。
* Created_tmp_disk_tables・・・ディスク上のテンポラリテーブルが作成された回数。
* Handler_read_first・・・フルインデックススキャンの回数。
* (1-Key_reads/Key_read_requests)・・・MyISAMキーバッファのキャッシュヒット率。
* Opened_tables・・・テーブルがOpenされた回数。急激に増えている場合にはテーブルキャッシュを増やす。
* Qcache_*・・・クエリキャッシュ関係。Qcache_insertsに対してQcache_hitsが少ないとヒット率が悪い。
* Select_full_join・・・2つ以上のテーブルにおいて全件同士でJOINした回数。最も致命的な兆候。
* Select_full_range_join・・・片方のテーブルで全件、もう片方のテーブルで範囲検索を行ってJOINした回数。
* Select_scan・・・全件スキャンの回数。
* Sort_scan・・・全件スキャンによるソートの回数。
* Threads_created・・・スレッドが作成された回数。急激に増えている場合にはスレッドキャッシュを増やす。
クエリのチューニングにおける基本コマンドはあくまでもEXPLAINであるが、クエリの実行状況を詳細に確認したい場合にはプロファイリング（SHOW PROFILEコマンド）を利用するといい。プロファイリングについては過去の投稿「プロファイリングで快適MySQLチューニング生活」で解説したのでそちらを参照して頂きたい。
おまけ： Sharding
巷ではGoogle Friend ConnectにおけるShardingの例が紹介されている。大規模なWebサイトなどで劇的にトラフィックやデータ量が増えてくると、単一のデータベースでは対応しきれなくなってくる。そのような場合、特定のカラムなどによってデータの格納先を振り分けるテクニックが利用されることが多いのだが、そのようなテクニックをShardingと呼ぶ。（MixiのひとはLevel2分散と呼んでいるらしい。）とはいっても、Shardingが必要になるほど大規模なデータベースはどちらかといえば少数派なので、あまり一般的なケースには当てはまらないかも知れないが、そのようなニーズがある人達のためにオマケとしてMySQLにおけるShardingのソリューションを紹介しておこうと思う。
MySQL Cluster・・・サン・マイクロシステムズが提供するオフィシャルなストレージエンジンの一つである。主キーに基づいて内部的にShardingを行うので、ユーザはShardingについて意識する必要がない。
SPIDER・・・STグローバルの斯波氏によるストレージエンジンである。MySQLユーザコンファレンス2009のスライドではShardingという単語は一切登場しなかったが、SPIDERストレージエンジンがやってることはまごう事なきShardingである。SPIDERを利用するとテーブル定義以外でユーザがShardingについて意識する必要がないので便利である。
Spock Proxy・・・MySQL ProxyをベースにしたShardingソフトウェア。Luaスクリプトを利用せずにC/C++でShardingのロジックを実装しているためとても速い。今のところMySQL ProxyではShardingを実装することは出来ない。
その他・・・Hibernate Shards、HiveDB、HBase、BigTable、HScaleなど。
