本ブログエントリーはPernixData社のシステムズエンジニアであるTodd Mace氏のブログ記事で、同社のテクノロジーエバンジェリストであるFrank Denneman氏がこの記事を自身のブログに転載したものを翻訳しています。 Frank氏について、詳しくはこちらもご参照ください。
本記事の原文はDatabase workload characteristics and their impact on storage architecture design – part 5 - Query Execution Plansで閲覧可能です。
ネットワールドのPernixDataに関する情報はこちら。本ブログのPernixDataの記事のまとめはこちら。
データベースワークロードの特徴シリーズのパート5へようこそ。データベースは仮想化データセンタ内では最も多くのI/Oを消費するものの一つであると考えられています。データベースの操作とデータベースの設計はそれ自身が研究に値するものですが、データベースの設計の世界を少しかいま見てみるだけでも非常に面白いものだと考えています。データベースのエキスパートで、同僚でもある PernixData社の製品担当VPの Bala Narasimhan 氏のもとへ赴き、データベースの設計とそのI/Oの特徴について教えてもらいました。
このシリーズの過去の回 :
Part 1 - データベースの構造
Part 2 - データパイプライン
Part 3 - データベースチューニングのための補助機構
Part 4 - NoSQLプラットフォーム
データベースは企業にとってクリティカルなアプリケーションで、大抵はストレージの性能要件として高いものを要求します。このブログ記事ではデータベースツールを利用して、クエリレベルでデータベースがどのようにストレージパフォーマンスを要求するのか解説していきます。そして、PernixData FVPがデータベースのストレージパフォーマンス問題だけでなく、ストレージのパフォーマンスがボトルネックになってきた場合に発生するデータベースの管理性の問題までもをどのようにして解決するのかをご説明いたします。全編を通してデータベースのサンプルとしてSQLサーバを利用しますが、内容は幅広く応用可能です。
クエリ実行プラン
C++のような言語でコードを書く際には、実行したいと思うアルゴリズムを記述します。例えば、C++でソートのアルゴリズムを実装するという時にはソートの特定の実装方法にもとづき、コントロールフローを記述します。コードはバブルソートやマージソートなどの実装によって異なりますが、いずれにしてもそれぞれのソートアルゴリズムのコントロールフローをプログラマーが実装しなくてはなりません。
それとは逆に、SQLは宣言型の言語です。SQL文は単にユーザーがやりたいことを記述するだけです。コントロールフローはデータベースが記述します。例えば、2つのテーブルの結合を行う場合、データベースは結合がハッシュ結合なのか、マージ結合なのか、はたまたネスト-ループ結合なのかを記述します。ユーザーはこれを記述することはありません。ユーザーは単に2つのテーブルを結合するというSQL文を実行するだけで、実際にどのような結合アルゴリズムが利用されて実際の結合が行われるかを問われることはないのです。
データベース内でSQL文をどのように実行するかのプランを行うコンポーネントは通常、クエリオプティマイザと呼ばれます。クエリオプティマイザはSQL文によって行われる可能性があるすべての実行可能な内容を検索し、最適なものを1つ選び出します。ご想像のとおり、これによって実行可能なプランのうち、最も最適なプランが実行されるかどうか、という点が問題になります。
SQLは宣言型であるということから、当然クエリのパフォーマンスはもっとも良い物にならないこともあります。クエリオプティマイザはいつも最高のクエリのプランを選択するとは限らないからです。これはしばしば発生します。SQL文が実行される際のインフラストラクチャの状態やシステムの負荷状況といった様々な重要な要素やデータの優先順位などをすべて勘案することができないためです。これが発生する可能性があるケースは結合順(Join Ordering)問題と呼ばれています。3つのテーブルT1, T2, そしてT3を結合するSQLクエリを実行しようとしている場合を考えてみましょう。どの順番でテーブルを結合しますか?はじめに T1 と T2 を結合しますか?それとも T1 と T3が最初ですか? 代わりに T2 と T3 を最初に結合すべきかもしれません。誤った順番が選択されてしまうとクエリのパフォーマンスは著しく低下してしまいます。これはデータベースユーザーとデータベース管理者が最新の注意を払ってデータベースをチューニングするということで回避されています。どちらにしても運用や投資面でのコストが上がってしまうのです。
クエリ最適化の実行
クエリ最適化をより良く理解するために実際の例を上げてみましょう。以下はTPC-Hのようなベンチマークで利用されているSQL文です。
select top 20 c_custkey, c_name, sum(l_extendedprice * (1 - l_discount)) as revenue, c_acctbal, n_name, c_address, c_phone, c_comment from customer, orders, lineitem, nation where c_custkey = o_custkey and l_orderkey = o_orderkey and o_orderdate >= ':1' and o_orderdate < dateadd(mm,3,cast(':1'as datetime)) and l_returnflag = 'R' and c_nationkey = n_nationkey group by c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment order by revenue;
このSQL文は指定された期中に購入したパーツを返却し、結果として売上を失ったトップ20の顧客を探し出すものです。データベースに対してこのクエリを実行する前に、クエリオプティマイザがどのクエリプランを選ぶのか、そして、それがどれほどの負荷になるのか調べることができます。以下の図はSQLサーバ2014がこのSQL宣言を行う際のクエリプランを表しています。[SQLサーバでどのようにクエリプランが生成されるかはこちらのURLで学ぶことが可能です。 https://msdn.microsoft.com/en-us/library/ms191194.aspx]
このクエリプランについては右から左へ読んでください。矢印の向きはクエリの実行される時のコントロールのフローを表しています。プラン内のそれぞれのノードはクエリ実行時にデータベースが行う1つ1つの操作です。このクエリが2つのスキャンで始まっていることに気が付きます。これらはクエリ内に含まれているテーブルへのI/O操作(スキャン)です。これらのスキャンは非常にI/Oが集中するもので、大抵はスループットが重要です。データウェアハウスの環境ではブロックサイズも非常に大きくなることもあります。
SANはこれらの操作によってひどいパフォーマンス問題を抱えることになります。もしもデータがディスク上に正しくレイアウトされていなかった場合、それは非常に巨大なランダムなI/Oとなります。このスキャンが開始された際に、SAN内で他の処理が行われていると、それによって一貫したパフォーマンスを得られなくなることもあります。コントローラーもパフォーマンスの上限が決まっているからです。
クエリは lineitem テーブルと orders テーブルのスキャンの実行から始まります。文の中のそれぞれの操作がどれぐらいの時間の割合になっているのかということをデータベースから聞き出すことも可能です。この例の場合、データベースは全体の実行時間の84%の時間を lineitemのClusterd Index Scanに費やし、他のものへ5%費やすという結果が出ています。言葉を変えるとこのSQL文の実行時間の89%がI/O操作なのです!こういうことが起こるので、データベースを仮想化するのをためらうユーザーが多いというのは不思議なことではありません。
さらにもっと細かい情報をクエリオプティマイザから入手することもできます。SQLサーバ管理スタジオ(SQL Server Management Studio)で、特定の操作に対してマウスオーバーすると、黄色のポップアップボックスが現れ、とても興味深い統計を表示してくれます。以下は私のSQL サーバ 2014環境で、上の図でハイライトされている lineitem テーブルのClustered Index Scanにマウスを重ねた時のサンプルです。
予測されるCPUコストに対して予測されるI/Oのコストが大幅に大きいことに気がつくでしょう。これもまた、このSQL文が大きくI/Oに依存しているということの証明となります。上の図に表示されている内容からさらに様々なことを知ることができます。
運用面でのオーバーヘッド
データベースが生成しているクエリの実行プランを理解することで、自身のインフラが何を必要としているのかと、多くを知ることができます。通常、クエリの実行プランを理解したあとの次のステップはクエリやデータベースをより良いパフォーマンスのためにチューニングすることです。例えば、新しいインデックスを作る場合もあるでしょうし、クエリ自体をより良いパフォーマンスのために書き換えることもあるでしょう。特定のテーブルが頻繁にアクセスされるようであれば早いストレージがRAM上に格納するなどの判断もあるかもしれません。完全にインフラストラクチャを刷新するという決断をされる場合もあるでしょう。
これらの刷新は企業にとっては運用面でのオーバーヘッドになります。まず、このモデルは誰かがクエリを定期的に評価し、データベースをチューニングして、影響がないようにパフォーマンスを確保するということが前提になります。そして、次にこのモデルは環境が一定であるということも前提になります。データベースのスキーマが決まったものであるという前提になっているのです。実行される可能性のある全てのクエリが事前にわかっており、誰かがクエリを調査し、データベースをチューニングするということが前提になっているのです。近年の、柔軟性と俊敏性がビジネスに重要視される時代においてはこの方法で進めていくことはほとんど不可能です。
運用のオーバーヘッドなくデータベースのパフォーマンスを提供するソリューション
もしも、クエリプランが適切でなかったとしても、スキーマの設計が適切でなかったとしても、クエリがほんの一時的に使われるものであったとしても、データベースが必要とするパフォーマンスを提供することができるプラットフォームがあったとしたらどうしますか? そんなストレージパフォーマンスプラットフォームがあれば、適切なクエリのパフォーマンスをなし得るために時には必死になってチューニングを行うようなことは完全になくなってしまうでしょう。そのプラットフォームがあれば、ビジネスに必要とされるSQLを必要なだけ実行でき、ストレージパフォーマンスプラットフォームがクエリプラン関係なくビジネスのSLAに見合うだけのパフォーマンスを提供するのです。
これはまさにPernixData FVPの設計そのものです。PernixData FVPはサーバサイドのフラッシュやメモリ(RAM)を利用するサーバサイドパフォーマンス層を構成することで、ストレージのパフォーマンスとストレージのキャパシティを分離します。これはデータベースからのアクティブなI/Oのすべて ー ReadもWriteも、そしてシーケンシャルであっても、ランダムであっても、ブロックサイズに関係なく － が データベースのすぐそばのサーバ内のFVPによって提供されるのです。もはやSAN内のデータが配置、SAN内のコントローラー、SAN内でSQLが実行されている際に他の処理が走っているなどで制限を受けることはありません。
これはクエリオプティマイザが最も最適ではないクエリプランを生成したとしても、すべてのI/Oがネットワーク接続されたストレージからではなく、サーバサイドのメモリ(RAM)やフラッシュから供給されるため、非常に多くのI/Oが発生しても問題無いということです。いずれ、巨大な中間結果を生成するクエリについても取り上げ、なぜ FVP のようなサーバサイドパフォーマンスプラットフォームが多くの違いを生むのか解説していきます。
この投稿のオリジナルはToddMace.ioでも参照可能です。
記事担当者: マーケティング本部 三好哲生 (@pernixdata_netw)
