この記事に対する便乗（？）でWeb屋のネタ帳: PostgreSQLを高速化する16のポイントという記事を書いて頂いたようだが、そちらの方もかなり人気だったようである。他人が作ったソフトウェアに改良を加えるというフリーソフトウェアやオープンソースソフトウェアの精神も基本は便乗であるので、便乗については大いに賛成したいというかむしろ取り上げてくれてありがとう！！と思うわけであるが、ここでさらに俺はこう考える。
さらに自分も便乗するしかない！！
と。
Web屋のネタ帳さんの記事では16のポイントが紹介されているが、漢（オトコ）のコンピュータ道の記事は10の方法だったのであと6つ足りない。オトコは数で勝負！！というわけで今日はネタを振り絞ってさらに7つのMySQL高速化テクニックを紹介しよう。
1. インテルコンパイラ版を使う。
MySQLのダウンロードサイトではLinux向けにインテルのコンパイラを使って作成されたバイナリが配布されている。インテル版のコンパイラは、もちろん処理によって得手不得手はあるのだが、GCCを利用して作成されたバイナリよりも良い性能を発揮することが多い。インテルコンパイラ版を使うというのは非常にお手軽な高速化テクニックであろう。ちょうどSourceForge.JP Magazineで「インテル コンパイラーの実力を測る――インテル コンパイラー版MySQLは本当に速いのか？」という記事が書かれているので参照して欲しい。こちらではベンチマークも行っている。
ちなみに、SolarisにはSunStudioというサン・マイクロシステムズ純正のコンパイラがあり、こちらのコンパイラで作成したバイナリもGCCより高速になる場合が多い。残念ながら、SunStudioで作成されたバイナリは配布されていないので、SunStudioを試したい人は自分でコンパイルしよう。SunStudioは無料で配布されている。
2. 同時実行スレッド数を調節する。
ちょっとやそっとの負荷ではあまり問題にならないが、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のリリースを楽しみにさせてくれる要素の一つである。
3. 適切なサイズで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を小刻みに行う方法が紹介されているので、興味のある人は参照して欲しい。
4. InnoDBのログサイズを調整する。
InnoDBのログファイルのサイズが小さすぎるとInnoDBの更新処理の性能が低下してしまう。前述したように大きなデータをロードする際のパフォーマンス低下も問題であるが、ログファイルのサイズが小さいとチェックポイント処理が頻繁に発生することによる影響がとても大きくなってしまう。ならば巨大なログファイルを作ればいいじゃないか？と思うだろうが話はそう簡単ではない。ログファイルのサイズが大きいと今度はクラッシュリカバリ（Redoログ適用）の時間が増えてしまうからだ。特にHA化されたMySQLサーバではクラッシュリカバリの時間が増長するとフェイルオーバーに時間がかかる時間も長くなってしまうので好ましくない。従って、適切なログファイルのサイズを選択することは非常に大切である。InnoDBのログサイズ調節方法については過去の投稿で説明したので参照して欲しい。
5. 最新版を利用する。
希に「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 6.0では性能に関するものとして次の新機能が追加される予定である。
単純なクエリのレスポンスタイムはあまり変わらないかも知れないし、処理の内容によってはバージョン間で得手不得手が存在するが、システム全体で見た時のスループットは着実に向上しつつある。
6. 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の有効性については別途紹介したい。
7. 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・・・スレッドが作成された回数。急激に増えている場合にはスレッドキャッシュを増やす。
おまけ： 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など。
今日の投稿では7つ＋オマケしか紹介していない割には説明が長くなってしまったが、MySQLチューニング時に役立てて頂ければ幸いである。
0 件のコメント:
コメントを投稿