sidebarちょっと硬派なコンピュータフリークのBlogです。カスタム検索2009-03-04さらにMySQLを高速化する7つの方法MySQLを高速化する10の方法という記事がとても好評だったようである。記事を読んで頂いた皆さん、ありがとう。PostgreSQLを高速化する16のポイントという記事を書いて頂いたようだが、そちらの方もかなり人気だったようである。他人が作ったソフトウェアに改良を加えるというフリーソフトウェアやオープンソースソフトウェアの精神も基本は便乗であるので、便乗については大いに賛成したいというかむしろ取り上げてくれてありがとう!!と思うわけであるが、ここでさらに俺はこう考える。自分も便乗するしかない!!と。Web屋のネタ帳さんの記事では16のポイントが紹介されているが、漢(オトコ)のコンピュータ道の記事は10の方法だったのであと6つ足りない。オトコは数で勝負!!というわけで今日はネタを振り絞ってさらに7つのMySQL高速化テクニックを紹介しよう。インテルコンパイラ版を使う。MySQLのダウンロードサイトではLinux向けにインテルのコンパイラを使って作成されたバイナリが配布されている。インテル版のコンパイラは、もちろん処理によって得手不得手はあるのだが、GCCを利用して作成されたバイナリよりも良い性能を発揮することが多い。インテルコンパイラ版を使うというのは非常にお手軽な高速化テクニックであろう。コンパイラー版MySQLは本当に速いのか?」という記事が書かれているので参照して欲しい。こちらではベンチマークも行っている。ちなみに、SolarisにはSunStudioというサン・マイクロシステムズ純正のコンパイラがあり、こちらのコンパイラで作成したバイナリもGCCより高速になる場合が多い。残念ながら、SunStudioで作成されたバイナリは配布されていないので、SunStudioを試したい人は自分でコンパイルしよう。SunStudioは無料で配布されている。同時実行スレッド数を調節する。負荷ではあまり問題にならないが、1000のクライアントから一斉に接続してクエリを実行する場合などは同時実行スレッド数を調節するといい。処理を並列に行うとスループットの向上が期待出来るが、同時に実行中のスレッドがあまりに多すぎるとロックの競合が多発してしまって返って性能を落とす原因になってしまう。innodb_thread_concurrencyオプションを利用するとInnoDBが内部的に利用するスレッド数を調節することができる。このオプションはいくつがいいかということについては明確な答えはない。アプリケーションの負荷によるところもあるし、CPU数やOSのスケジューラにも影響されるからである。従って、適切な値を決めるにはベンチマークが必須である。逆に言うと、ベンチマーク時には必須の調整パラメータであると言える。CPUコア数が増えた場合には調整することにより性能の向上が見込める。innodb_thread_concurrencyオプションのデフォルト値はバージョンによって異なるが現在は8である。0に設定得るとスレッド数の上限がないことを示す。0、4、8、12、16、20辺りで変化させて性能を計測するといいだろう。6.0ではさらにスレッドプールが搭載される予定である。InnoDB内部の同時実行スレッド数だけでなく、MySQLサーバの接続スレッドの同時実行数を調節することができるようになる。同時に実行するスレッド数を100までに限定したい場合には次のように指定する。thread_handling=pool-of-threadsthread_pool_size=100現時点ではLOCK_event_loopの競合が発生するという問題があるためスレッドプールによる性能向上は見込めないが、正式版がリリースするまでには問題は解消されスレッドプールによって同時実行性能が改善することだろう。6.0のスレッドプールはlibeventで実装されている。6.0のリリースを楽しみにさせてくれる要素の一つである。適切なサイズでCOMMITする。一度に大量の更新を行うと、更新している間はその行に対して排他ロックがかけられるためロックの競合が発生する。ロックの競合が多発すると同時実行性能の低下に繋がってしまう。逆に言うと、OLTP系のアプリケーションで用いる場合などは、一回のトランザクションでCOMMITするサイズを小さく調節することでデータベース全体のスループットを向上させるのがいいだろう。更新のサイズがInnoDBのログサイズより大きい場合には、COMMITの前にテーブルスペース内にUndoログを大量に作成する必要が生じるため、I/O性能が低下するという問題が発生する。I/O性能だけでなく、不要になったUNDOログはパージスレッドが回収するまでテーブルスペース内に残り続けてしまうので容量も圧迫してしまう。FILEなどで一度に大量のデータを更新するのは避けた方がいいだろう。TABLEするという技が存在するのでお勧めである。TABLE時には1万行ごとにCOMMITしてくれるので、上記のようなUNDOログの問題は発生しない。DATAを小刻みに行う方法が紹介されているので、興味のある人は参照して欲しい。InnoDBのログサイズを調整する。InnoDBのログファイルのサイズが小さすぎるとInnoDBの更新処理の性能が低下してしまう。前述したように大きなデータをロードする際のパフォーマンス低下も問題であるが、ログファイルのサイズが小さいとチェックポイント処理が頻繁に発生することによる影響がとても大きくなってしまう。巨大なログファイルを作ればいいじゃないか?と思うだろうが話はそう簡単ではない。ログファイルのサイズが大きいと今度はクラッシュリカバリ(Redoログ適用)の時間が増えてしまうからだ。HA化されたMySQLサーバではクラッシュリカバリの時間が増長するとフェイルオーバーに時間がかかる時間も長くなってしまうので好ましくない。従って、適切なログファイルのサイズを選択することは非常に大切である。InnoDBのログサイズ調節方法については過去の投稿で説明したので参照して欲しい。希に「MySQLは4.0が一番性能がよかった」などと嘯(うそぶ)く人を見かけるが、そんなことはない。MySQLもバージョンが上がるごとに性能向上のための機能が追加されている。Pushdownなど。5.0.30と5.0.54で行われた改善については、マイコミジャーナルの記事で詳細な説明が行われているので参照して欲しい。6.0では性能に関するものとして次の新機能が追加される予定である。Pushdownサブクエリ最適化スレッドプール(前述)単純なクエリのレスポンスタイムはあまり変わらないかも知れないし、処理の内容によってはバージョン間で得手不得手が存在するが、システム全体で見た時のスループットは着実に向上しつつある。COMMITTED分離レベルを使う。READ分離レベルを利用する。READという問題を防ぐことができるが、ロックの競合がより頻繁に発生するようになってしまう。COMMITTED分離レベルを利用しよう。READは発生しない。5.0以前のバージョンでは、innodb_locks_unsafe_for_binlogオプション利用時にバイナリログの一貫性を保証できないという問題があった。(そしてinnodb_locks_unsafe_for_binlog利用時には同時実行性能が改善しないというオチがある。5.1では行ベースレプリケーション(RBR)が追加されたため、同時実行性能を保ちつつバイナリログの一貫性を保証することが可能になっている。COMMITTED分離レベル利用時またはinnodb_locks_unsafe_for_binlogオプションが有効になっている場合にはRBRの利用が必須となった。COMMITTEDが利用出来るのはどのような場合か?明確なのは一回のトランザクションで複数の処理を行う必要がない場合である。COMMITTEDで問題ないだろう。更新は日記の文章をINSERT、参照は最新のデータのSELECTをそれぞれ一回行うだけで実装出来るため、一回のトランザクション内で複数の処理を行う機会はあまりない。多くのWebアプリケーションでは一回のリクエストが一回のクエリに対応する。COMMITTEDが利用出来るだろう。ちなみに、利用出来る分離レベルはストレージエンジンによって異なる。COMMITTED分離レベルしか利用出来ない。READ、SERIALIZABLEをサポートしている。COMMITTEDの有効性については別途紹介したい。EXPLAIN以外のチューニングコマンド。STATUSコマンドはMySQLサーバ上の統計情報を見ることができるコマンドである。STATUSではセッションごとの統計情報を見ることが可能である。STATUSを利用するときは、10分ほど間隔を空けて2回情報を採取し、増分を見ることで単位時間あたりの負荷を測定するのがコツ。(逆にいうと累積情報を見てもあまり意味がない場合が多い。STATUSコマンドを実行することでセッションにおける統計情報をクリアすることができる。特定のクエリ実行時の統計情報を見るときなどに便利である。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・・・スレッドが作成された回数。急激に増えている場合にはスレッドキャッシュを増やす。PROFILEコマンド)を利用するといい。プロファイリングについては過去の投稿「プロファイリングで快適MySQLチューニング生活」で解説したのでそちらを参照して頂きたい。ConnectにおけるShardingの例が紹介されている。大規模なWebサイトなどで劇的にトラフィックやデータ量が増えてくると、単一のデータベースでは対応しきれなくなってくる。そのような場合、特定のカラムなどによってデータの格納先を振り分けるテクニックが利用されることが多いのだが、そのようなテクニックをShardingと呼ぶ。(MixiのひとはLevel2分散と呼んでいるらしい。)とはいっても、Shardingが必要になるほど大規模なデータベースはどちらかといえば少数派なので、あまり一般的なケースには当てはまらないかも知れないが、そのようなニーズがある人達のためにオマケとしてMySQLにおけるShardingのソリューションを紹介しておこうと思う。Cluster・・・サン・マイクロシステムズが提供するオフィシャルなストレージエンジンの一つである。主キーに基づいて内部的にShardingを行うので、ユーザはShardingについて意識する必要がない。SPIDER・・・STグローバルの斯波氏によるストレージエンジンである。MySQLユーザコンファレンス2009のスライドではShardingという単語は一切登場しなかったが、SPIDERストレージエンジンがやってることはまごう事なきShardingである。SPIDERを利用するとテーブル定義以外でユーザがShardingについて意識する必要がないので便利である。ProxyをベースにしたShardingソフトウェア。Luaスクリプトを利用せずにC/C++でShardingのロジックを実装しているためとても速い。ProxyではShardingを実装することは出来ない。Shards、HiveDB、HBase、BigTable、HScaleなど。今日の投稿では7つ+オマケしか紹介していない割には説明が長くなってしまったが、MySQLチューニング時に役立てて頂ければ幸いである。Okuno私は所謂ギークです。普段はMySQL関係の仕事をしています。今の時代、フリーソフトウェア(自由なソフトウェア)こそギークの歩く道だと信じています。コンピュータに手を染めてはや二十年。コンピュータ道はいくら進んでも終わりが見えない果てしない世界。コンピュータにはロマンがある。(18)MySQLのEXPLAINを徹底解説!!なぜMySQLのサブクエリは遅いのか。filesortMySQLのプロンプトを変更する。高速SSDの落とし穴。本ブログにおいて示されている見解は、私自身の見解であって、オラクルの見解を必ずしも反映したものではありません。