クエリ処理にもコスト削減:MySQLの達人が語る、パフォーマンスチューニングの勘所

データベースのチューニングの良し悪しは、システムのパフォーマンスを大きく左右し、ひいてはIT投資にまで影響する。オープンソースデータベース「MySQL」のエキスパートによるチューニングのポイントを紹介する。


 12月9日、都内で、サン・マイクロシステムズ株式会社(以下、Sun)が主催する「事例に学ぶ!オープンソースの実践的導入・活用セミナー 〜 「次の一手」につながる効率的な情報投資とは? 〜」が開催された。当日MySQLパフォーマンスチューニングに関するセッションを担当した、Sun MySQLサポートサービス本部 シニアテクニカルサポートエンジニアの奥野幹也氏は、日々ユーザーのサポート業務を担当しているMySQLのエキスパートだ。

サン・マイクロシステムズ株式会社 MySQLサポートサービス本部 シニアテクニカルサポートエンジニアの奥野幹也氏 サン・マイクロシステムズ株式会社 MySQLサポートサービス本部 シニアテクニカルサポートエンジニアの奥野幹也氏

 Sunでは、MySQLサブスクリプション契約のうち「Gold」「Platinum」を契約しているユーザーに対して「コンサルティングサポート」と呼ばれる上級サービスをはじめさまざまなサポートを奥野氏が所属する部署で提供している。パフォーマンスチューニングはもちろん、トラブルシューティングや顧客の環境に対する具体的なアドバイスやサポート作業も含まれる、MySQLの包括的な知識が求められる現場の最前線である。

 「チューニングとはデータベース処理の無駄を省くこと。同じハードウェアでも、パフォーマンスは大きく違ってくる」と奥野氏はチューニングの重要性を語る。以下にセッションで解説されたチューニングのポイントを紹介する。

新たに強力な機能を装備した監視ツールが、チューニング作業をさらに効率化

 パフォーマンスの改善には、クエリ処理に時間がかかっている理由を知ることが重要となる。MySQLでは、クエリ分析のツールとして「クエリアナライザ」が用意されている。これはクエリ統計情報を集計し、実行に要した時間やネットワーク転送量、行数などの情報をクエリごとに分析できるツールだ。

 クエリアナライザを活用することで、集計した統計情報からボトルネックとなっているクエリを容易に見付け出すことができる。パフォーマンスチューニングにおいても、またトラブルシューティングの際にも役に立つ。

 クエリアナライザは、MySQL Enterprise Monitorの最新版で新たに追加された「相関グラフ」を使うことでさらに便利なツールとなる。これは、CPUやメモリなどのリソース使用率、コネクション数などといったシステム状況のグラフと、クエリアナライザを連動させる機能だ。画面のグラフ上でドラッグして時間帯を絞り込むと、クエリアナライザ側では、その時間帯に実行されたクエリのみを絞り込んで集計するようになっている。

MySQL Enterprise Monitor 2.1の新機能、「相関グラフ」の利用例。まずはCPU利用率のグラフ上でドラッグして時間帯を指定する(薄い紫色になった部分を指定) MySQL Enterprise Monitor 2.1の新機能、「相関グラフ」の利用例。まずはCPU利用率のグラフ上でドラッグして時間帯を指定する(薄い紫色になった部分を指定)

 「パフォーマンス低下には、I/Oの飽和やメモリ不足、CPUリソース不足など、さまざまな原因が考えられます。システム負荷が高まった時間帯をグラフ上で指定して、直接クエリ分析を行うことで、ボトルネックになっているクエリがすぐ分かり、どこをチューニングするのが効果的かを見極められます。スロークエリログ(指定した時間よりも処理が遅かったクエリのログ)などを基に1つずつ分析するときのように時間を掛けることなく、修正するべきクエリを一目で把握できます」(奥野氏)

 一般的に、パフォーマンスチューニングやトラブルシューティングの際には、問題点を特定するまでの調査の段階で特に多くの時間や手間を要するものだ。MySQL Enterprise Monitorの機能を活用することでパフォーマンス改善作業を効率化することができ、より短期間で効果的なチューニングを実施できるようになる。

クエリアナライザが指定した時間帯のクエリを集計し、その結果がグラフの下に表示される。表の個々の項目名をクリックすることで、各指標によるソート結果やクエリの詳細を確認できる クエリアナライザが指定した時間帯のクエリを集計し、その結果がグラフの下に表示される。表の個々の項目名をクリックすることで、各指標によるソート結果やクエリの詳細を確認できる

チューニングの実施には、データベース内部の処理を知ることが重要

 クエリアナライザで特定されたクエリを分析するにはMySQLのコマンドを使い、クエリがどのように処理されるかを調べていくのが基本的な手法だ。データベースのパフォーマンスは、しばしばハードウェアやストレージ、ネットワークなどの環境や、OSの各種パラメータなどにも影響されるが、データベース自体の内部処理が原因となるケースも少なくない。MySQLの内部処理を調べる上では、基本である「EXPLAIN」コマンドを使いこなすことが重要だと奥野氏は言う。

 「EXPLAINの出力結果を読み解くにはコツが要りますが、ポイントさえ掴めば難しくはありません。EXPLAINで得られる情報にはレコードへのアクセス方法や、オプティマイザによるヒントが示されており『なぜそのクエリが遅いのか』を理解するのに役立ちます」(奥野氏)

 具体的な内容については別途ホワイトペーパーで紹介するが、MySQLのオプティマイザは与えられたクエリをCPUへの負荷やI/Oの回数などを元にコストを算出し、もっとも効率的なクエリを選択する。この判断基準を理解することが、パフォーマンスチューニングの際に大いに役立つ。ただ、オプティマイザの選択はあくまでも概算のコスト判断によるものであり、最適解を得られない場合もある。

 チューニングはアプリケーション側の挙動にも影響される。特にWeb系システムでは、頻繁に改修されることが多く、データベース側も柔軟性が求められてくる。

 「そのような場合は、テーブルを最大限にチューニングするのではなく、ある程度の柔軟性を持たせた内容が望ましいです。常に環境に合わせたチューニングを意識することが大切です」と奥野氏は言う。

 「チューニングとは、人間が頭を使って、オプティマイザに最適解を選ばせるようクエリやテーブルを見直していく作業です。そのためには、日ごろからMySQLの仕組みを意識しながら業務にあたることが必要になるでしょう」(奥野氏)

 MySQLのパフォーマンスチューニングを行うには、自社のエンジニアがMySQLを学んで手掛けるか、パートナーに依頼するか、このコンサルティングサポートを利用するか、どの方法を選ぶにせよ若干のコストは発生する。しかし、適切なチューニングを行えば大きな効果が得られ、ハードウェアやインフラへの無駄な増強をせずに済むなど、その後のコスト増を避けることができる。チューニングによるパフォーマンス向上は、しばしば数倍、場合によっては1日以上の処理時間を要するクエリを1秒に短縮したケースもあるというから、検討しない手はないだろう。

達人とMySQL

 奥野氏は現在、MySQLのトラブルシューティングに関する著書を執筆中だ。全800ページもの分量で、2010年4月頃の発売を目指しているという。もちろん、日頃のサポート業務などを通じて培ってきた知識がその背景にあることは間違いないが、執筆を通じて勉強したことも多いとのことだ。

 「執筆に際してはソースコードを徹底的に読み、内部の挙動を分析しました。それを読み込むことで、背景にある思想なども分かってきます。仕事では必要な部分を中心に読んでいますが、目にするのは100万行を超えるMySQL5.1のソースコードのほんの一部で、どうしても知識は偏ってしまいます。今回の書籍では包括的な内容を目指し、包括的に多くの箇所に目を通しました。おかげで、顧客からの問い合わせに即答できる率が高まりました」(奥野氏)

 奥野氏にとっても、ソースコードから得られた知識量は、膨大なものだったという。その知識の一端は、今回のセミナーでも披露され、ホワイトペーパーでも一部を紹介している。

 奥野氏はSunに入社した後、一貫してサポートを担当し、アカウントマネジャーなどを経てMySQLへ移った。技術試験で、トラブルシュートの調査能力が認められ採用されたという。現在の主な仕事場は栃木県内の自宅で、都心のオフィスに出るのは週に1度くらい。仕事の大半はインターネット上で進められており、IRCにアクティブであることが勤務中を示すようなものだ、とのこと。

 「上手にチューニングできたときには、難しいパズルを解いたときのような喜びがあります。しかも、そのことが顧客に喜ばれます。私が技術を磨くのは、それを評価してもらいたいから。今の職場も、技術を評価してくれます。年齢が上がれば現場を離れマネジメントへ、という企業は多いですが、MySQLに関しては、かなり年配のベテランエンジニアも現役で技術の仕事をしています。技術一本で、ずっと仕事していける環境があるのは嬉しいことですね」(奥野氏)

この記事に興味のある方におすすめのホワイトペーパー

th_sun_100118wp.jpg

EXPLAIN コマンド概説

MySQLの内部処理を知ってより効果的なパフォーマンスチューニングを

データベースのパフォーマンスチューニングにおいては、その内部的な挙動を知ることが大いに役立つ。本ホワイトペーパーでは、サン・マイクロシステムズが12月9日に開催した「事例に学ぶ!オープンソースの実践的導入・活用セミナー 〜 「次の一手」につながる効率的な情報投資とは?〜」での講演内容から、オープンソースデータベース「MySQL」のチューニングを行う際に重要なEXPLAINコマンドの出力結果について紹介する。(2010/01/18)

ホワイトペーパーのダウンロードページへ (TechTargetジャパン)




提供:サン・マイクロシステムズ株式会社
アイティメディア営業企画/制作:ITmedia エンタープライズ編集部/掲載内容有効期限:2010年2月17日

ホワイトペーパー

EXPLAIN コマンド概説
MySQLの内部処理を知ってより効果的なパフォーマンスチューニングを