ハードウェアのサイジングが何よりも重要
SQL Server はクラスタ化インデックスを理解するのが肝
Intel アーキテクチャの上で一番相性がいいのは Windows + SQL Server 「キーマン Interview : SQL Server 2005 の真実」第 2 回目は、株式会社 CSK Winテクノロジ (旧エイ・エヌ・テイ) の熊澤 幸生 (くまざわ ゆきお) 氏にお越しいただきました。今までに数多くの大規模データベース システムを手がけてきた同氏は、まさにデータベースの第一人者といえる人物です。現在は、SQL Server のコンサルティングを主な業務としており、特に大規模環境でのパフォーマンス チューニングを得意としています。＠IT サイトでの連載記事「Dr. K's SQL Server チューニング研修」の“ドクター K”としてご存知の方も多いのではないでしょうか。
SQL Server 2005 を大規模データベース環境で利用するにあたっての、処理性能を向上させるためのノウハウに、ぜひご注目ください。 大規模データベースではハードウェアのサイジングが重要
お客様のやりたい業務に合った OS やデータベースを選択すべき ―― 私の中では「大規模データベースのチューニングといえば熊澤さん」という印象があるのですが、大規模データベースで処理性能を向上させるコツというのはあるのでしょうか。 熊澤
処理性能を向上させるには、まず第 1 にメモリや CPU、ディスクなどのハードウェアのサイジングをきちんと行っておくことが重要です。中でも一番大事なのがメモリで、その次に CPU、その次にディスクです。そして、これら 3 つのバランスをしっかりと考えていくことが最初の入り口だと思っています。
それに、ハードウェアの選択は、OS を何にするとか、データベースを何にするとかはあまり関係ない話であって、まずお客様の要件を分析して、次に「OS やデータベースは何が最適なのか」と考えていくべきだと思っています。 ―― ハードウェアのサイジングが何よりも重要というわけですね。 熊澤
私が昔から嫌いなのは、Oracle ありき、SQL Server ありき、DB2 ありきとか、データベースありきで始まることです。先にデータベースありきだと、「こういう機能は SQL Server にはないじゃないか」とかつまらない議論になったりして、それは違うのではないかなと。先に考えるべきはお客様の業務であって、お客様のやりたい業務に一番あった OS の環境は何で、データベースは何なんですかと、逆なのではないかなと私はいつも思っています。 ポイント 1: メモリのサイジング
仮想メモリは、しょせんは絵に描いた餅 ―― ハードウェアのサイジングについて、具体的に教えていただけますか。 熊澤
＠IT の連載記事でも第 1 回目にメモリの話から始めているのですが、ハードウェアの中でも、特にメモリのサイジングが一番重要だと思っています。メモリに関しては、まず仮想メモリと物理メモリの 2 つの観点から考えていく必要があります。私がよく言うのは「仮想メモリは、しょせんは絵に描いた餅なのです。どれだけ物理的なメモリが載っていますか」と。物理メモリが十分に足りていなければ、ページングが発生して、余分な CPU への負荷やディスクへの負荷がかかってしまうわけです。
私は“ワーキング セット”という概念でよく説明するのですが、オペレーティング システムと SQL Server のコアの部分が生きていくために最低限必要な物理メモリの大きさはどのくらい必要なのですかと。それに加えて、お客様の環境によってデータ ページであったり、インデックス ページであったり、プロシージャ キャッシュ、ロック用のテーブル、ユーザー用のコネクション、論理ログ ファイルなど、これらは物理メモリ上にないとページングが発生してしまうものです。そのようなページングのオーバーヘッドを避けるためにも、物理メモリをきちんと見積もっておかなければなりません。 ―― 仮想メモリではなく、物理メモリを意識することは本当に重要ですね。 熊澤
それと、それぞれのオブジェクト用の領域がメモリ空間をフルに利用できるようにするには 64-bit 環境を検討しなければなりません。どうしても 32-bit のプロセッサだと、なかなか 4 GB のバリアを超えることができなくて、たとえ AWE (Address Windowing Extensions) の機能を使ったとしても、一部のバッファ キャッシュだけしか拡張メモリ空間を利用できないという制限があります。しかし、64-bit であれば、全部のメモリ内のオブジェクトが 64-bit のメモリ空間をフルに使えるようになるわけです。 ポイント 2: CPU のサイジング
SQL Server は、複数の CPU があることを前提にエンジンが作られている ―― CPU のサイジングについてはいかがですか。 熊澤
メモリをきちんと理解したうえで、次に考えないといけないのが CPU です。SQL Server 2005 は、「SQLOS」(SQL Server 2000 での UMS スケジューラ) と呼ばれる、独自のスレッドというか、コンテキスト スイッチの管理から CPU の資源管理までを行う、SQL Server のカーネルともいえる部分が実装されています。ちょうど SQL Server 自身が独自の OS 機能を備えているようなイメージです。これは、他のデータベースと比べて、SQL Server にしかない特徴です。
なおかつ、SQLOS や各種のスレッドは、複数の CPU があることを前提にデザインされています。レイジー ライターやバッファ フラッシュのための専用のスレッドがあったり、大量のデータをスキャンするための Read Ahead (先行読み取り) のスレッド、バックアップとリストアのためのスレッドがあったり、機能に応じて専用のスレッドがあります。理想からいうと、それらがきちんと動けるだけの物理的な CPU がほしいですね。SQL Server は、複数の CPU があることを前提にエンジンが作られているということを意識することが重要です。 ポイント 3: ディスクのサイジング
メモリと CPU、ディスクの 3 つのバランスをしっかりと考えることが重要 ―― ディスクのサイジングについてはいかがですか。 熊澤
メモリ、CPU に次いで、私が 3 番目に大事だと思っているのがディスクです。ディスクについては 2 つの考え方がありますが、現在の大規模環境では 4、5 年前に生まれたファイバー チャネルを使った SAN (Storage Area Network) を使うのが当たり前になっています。しかし、最近の文献などをみると「SAN はもう古い」という話が出ていたりします。SAN を使うよりも、ローカルの PCIX 上に、たくさんの Serial ATA (SATA) のディスクを並べて使った方がパフォーマンスが良いというわけです。
ディスクのパフォーマンスは意外と盲点になりやすいですが、メモリと CPU、ディスクの 3 つのバランスをしっかりと考えていくことが、サイジングの最初の入り口だと思っています。 ポイント 4: SQL Server のチューニング
クラスタ化インデックスを理解するのが肝 ―― SQL Server 自身のチューニングについてはいかがですか。 熊澤
SQL Server は、ある程度の規模まではほとんどメンテナンス フリーで動くのですが、大規模データベースとなると話は別です。ハードウェアのサイジングは大前提として、そのほかにもデータベースの設計をきちんと行ったり、SQL Server の特性をきちんと理解しておかないと、あとでたいへんなことになります。実際、私が携わった大規模案件は、トラブルのほとんどがそこから生まれています。
また、お客様のタイプも大きく 2 つに分けることができて、1 つは今までにメインフレームや UNIX などできちんとしたデータベース設計を数多く経験してきた人が、SQL Server の特性を理解していなかったがために発生するケース。もう 1 つは、最初はラック マウント型のサーバー 3 台ぐらいの小さい規模で始めて問題なく動いていたのに、どんどんアクセス数が増えて、データ量もあっという間に膨れ上がっていく、いつの間にかパフォーマンスが悪くなっていくというケースです。いわゆるスケーラビリティをお金で買ったというパターンです。この 2 つは、チューニングにあたって、まったく着目点が違います。 ―― どちらも簡単に手軽に導入できる SQL Server ならではこそですね。 熊澤
そうですね。2 つ目のパターンでは、ハードウェアのサイジングや、データベースの設計をきちんと行っておくことが重要です。これに対し、1 つ目のパターンのメイン フレームとか UNIX とかをきちんとやってきた人は、まず SQL Server の特性をよく勉強し直してほしいなと思っています。特にこのパターンでは、クラスタ化インデックスを理解していない人が圧倒的に多いです。クラスタ化インデックスを持ったテーブルと、非クラスタ化インデックスのみのテーブルでは、RID (行識別子) を使った格納制御になったりと、まったく動きが違いますから。 ―― クラスタ化インデックスは、本当に重要ですね。 熊澤
Oracle などを使っていたエンジニアにとっては理解しづらいかもしれませんが、SQL Server の場合は、クラスタ化インデックスを理解しないと、データベースの物理設計はあり得ないと思っています。実際、クラスタ化インデックスの特性を理解していなかったがために、データが消えてしまったというトラブルに陥ったお客様もいるくらいです。
また、インデックスはロックの動作とも密接に関係していることにも注意しなければなりません。たとえば、クラスタ化インデックスは、ユニークではないキー (重複値のあるデータ列) に対しても作れます。そうすると、排他ロックの単位が行にはなりません。一番下のリーフ レベルが、ページ単位のロックになってしまうわけです。特にデータの種類が 10 個くらいの少ない場合だと、いくつかのプロセスが更新でつかんでしまうと、デッドロックが多発したり、もう完全にロックで止まってしまうことになります。なので、ロック回りでトラブルに陥らないためにも、インデックスの内部構造を、データの格納構造とロックの動作まで含めて、確実に理解しておくことが重要です。 Windows プラットフォームはロードマップがしっかりしている
Intel アーキテクチャの上で一番相性がいいのは Windows + SQL Server
安心して使えるレベルまで品質が上がった ―― 熊澤さんは、古くから多数のデータベースに携わっておられますが、Windows プラットフォームについてはどのようにお考えですか。 熊澤
Windows プラットフォームは、ロードマップがしっかりしているのが魅力ですね。今回の SQL Server 2005 の場合は、SQL Server の第 3 世代の基盤として 5 年以上をかけて作っていますし、Windows Server 2003 の後継 OS が出るのも 2007 年以降です。その間にじっくりと時間をかけてテストをし、本当に安心して使えるレベルまで品質が上がったというのが、非常に大きいと思います。 ―― SQL Server 2005 に関しては、負荷テストに費やした時間は SQL Server 2000 のときの 40 倍以上 (延べで 150,800 マシン時間)、負荷ワークロードを 10 倍以上に増やして品質を追求したという話を私も聞いたことがあるのですが、これには本当にびっくりしました。 熊澤
Windows Server は最初のバージョンの Windows NT 3.1 から、SQL Server も最初のバージョンの 4.2 から利用していますが、昔のマイクロソフト製品というと、毎年新しいバージョンが出たり、バージョン アップの頻度が早かったりと本当に困りました。当時を振り返ると、あれは何だったのかなと思ってしまいますね (笑)。 ―― 私もそう思います。最近のマイクロソフトは、OS のロードマップに関しては、4 年ごとにバージョン アップをして、2 年ごとにリリースをアップデートしていくというのをきちんと明言しているようです。これは Linux や UNIX 環境などと比べて安心感がありますね。 熊澤
そうですね。最近は本当にロードマップがしっかりしていて、じっくりと作られているので、お客様にとっても安心して利用できるのではないでしょうか。昨年、SQL Server の Version 7.0 以降の開発に大きな影響を与え、SQL Server の育ての親ともいえる Microsoft Research のジム グレイ博士にお会いする機会があったのですが、そのとき彼は、数年後、そして数十年後の SQL Server と Windows OS の方向性をきちんと考えていて、それを示してくれました。将来起こり得るであろう課題を予測して、それに対して今の段階からきちんと対策を講じておく、きちんとデザインしているというのは本当にすごいと思いました。 ―― 方向性をきちんと示してくれるのは本当にありがたいですね。熊澤さんは、SQL Server 以外のデータベースを提案されるということはあるのでしょうか。 熊澤
それはないですね。Intel アーキテクチャとの相性を考えると、間違いなく Windows と SQL Server の組み合わせが一番いいと思っています。Intel プラットフォームの上できちんと動く OS とデータベースを両方とも持っているのはマイクロソフトだけですから。一方は Linux であったり、もう一方は Oracle であったり、PostgreSQL や MySQL とかも 2 つとも持っているというのはありません。OS とデータベースは切り離せないものですので、やはり PC サーバー (IA サーバー) 上でデータベースを構築するとなったら、好きとか嫌いとかではなくて、Windows と SQL Server しかないと思っています。 SQL Server の大規模案件は非常に増えている
SQL Server にとって一番大きなインパクトは 64-bit 対応 ―― SQL Server 2005 は、NASDAQ や名古屋銀行、Barclays Capital、Mediterranean Shipping Company、Barnes and Noble といった大規模かつミッション クリティカルな環境での導入実績を目にすることがありますが、実際、日本でも大規模な案件は増えてきているのでしょうか。 熊澤
ここ数年すごく増えていますね。やはり SQL Server にとって一番大きなインパクトは 64-bit 対応だったと思います。Intel Itanium プロセッサの上で、Windows Server 2003 と SQL Server 2000 の Enterprise Edition (64-bit 版) が使えるようになって、これが 1 つのブレークスルーでした。
AWE などの 32-bit のプロセッサの限界を考えると、お客様には、なるべく早く 64-bit の環境に移って、全部のメモリ内のオブジェクトが 64-bit のメモリ空間をフルに使えるようにしていきましょうという話をしています。それと、今までは IA64 (Intel Itanium ベースの 64-bit) にしか対応していませんでしたが、SQL Server 2005 からは X64 (Intel EM64T、AMD64) にも対応するようになりました。これで比較的安価なハードウェアで 64-bit 環境を実現できるようになります。 ―― 実際に SQL Server を大規模環境で動かしている中で不安などはありますか。 熊澤
まったくないですね。私のお客様で証券系のミッション クリティカルなシステムで SQL Server を利用している所もありますし、問題なく動いています。もちろん、先ほどの話のように、高速なストレージや大容量のメモリ、64-bit の複数 CPU の搭載といったハードウェアのサイジングをしっかりと行い、かつデータベースの設計もきちんと行っておくことが、安定稼動させるコツになります。あと SQL Server のインデックスとロックの特性を十分に理解しておくことも忘れてはなりません。そうすれば、大規模環境でもまったく問題なく動作させることができます。 大規模環境には欠かせないデータパーティショニング
動的管理ビュー (DMV) で内部動作がすべて見られるようになる ―― SQL Server 2005 で、熊澤さんが気に入っている機能はありますか。 熊澤
やはり、何といってもデータ パーティショニングですね。大容量のデータをうまく運用管理していくにはパーティショニングが欠かせません。パーティショニングがあれば、パーティション単位でのバックアップやリストアしたり、古いデータを定期的にアーカイブ テーブルへ移動するスライディング ウインドウのシナリオも簡単に実現できます。
そして何よりインデックスもパーティション化できるのが大きなメリットです。従来の SQL Server 2000 では、大規模データ ウェアハウスや大量のデータを取り扱うようなトランザクション処理システムでは、パーティション ビューという機能を利用しなければならなかったんですが、これはテーブルを複数に分けて UNION ALL でつなげたビューを作る機能です。しかしパーティション ビューだと、クエリ オプティマイザが正しい実行プランを判断してくれないんです。これでは、クエリのパフォーマンスに問題が出てしまいます。でも SQL Server 2005 のパーティショニングのようにインデックスをパーティション化できれば、オプティマイザは正しく動いてくれます。 ―― インデックスのパーティション化は待ちに待った機能ですね。 熊澤
昨年の案件でもパーティショニングが利用できればどんなに楽だったかというのがありました。そのシステムは、毎日バッチ形式のトランザクション処理で大量のデータが蓄積され、1 か月で 数千万件、6 か月では 数億件ものデータが蓄積されていきます。このデータを複数の同一構造を持つテーブルに分割し、ビューで結合し、アプリケーションから検索と更新処理で利用しています。
SQL Server 2000 で動いているのですが、本稼動直前に、同じ処理なのに時間がかかったり、遅かったりするということで、第三者の専門的な見解を求められ、私のところへ相談がありました。
各種パフォーマンス監視ツールで分析した結果、Oracle に精通したエンジニアがデザインしたデータベース物理設計で、SQL Server のインデックスの特性の理解不足が原因でトラブルが起きていました。このときに SQL Server 2005 が発売されていてパーティショニングが利用できていれば、どんなに楽だっただろうと思いました。お客様へパーティショニング機能の提案を行ったところ、アプリケーションとデータベース設計の初期の段階で、私のデザイン レビューを受ければ、さらなるスケーラビリティが達成できたと残念がっていらっしゃいました。 ―― そのほかに SQL Server 2005 で注目している機能はありますか。 熊澤
あとは、動的管理ビュー (DMV: Dynamic Management View) ですね。従来の SQL Server 2000 だと、ドキュメントに記載されていない隠しコマンドを駆使しないと、細かいチューニングをしたり、内部動作を調べたりすることができなくて、まさにブラックボックスでした。でも SQL Server 2005 では、動的管理ビューのおかげで内部動作がすべて見られるようになっています。これで本当にチューニングがしやすくなりました。 どんなデータベースも基本は同じ ―― 最後に、これから SQL Server で大規模データベースに携わるエンジニアへメッセージをお願いします。 熊澤
データベースは、種類に関係なく基本は同じです。私自身、今までに IMS (IBM 社の階層型データベース) や CODASYL 型 (ネットワーク型) データベースの IDMS/R (Cullinet 社)、AIM (富士通)、RDB では Infomix など、いろいろなデータベースに携わってきました。そのときに学んだデータベースの設計や領域管理、トランザクションの ACID プロパティといった基本知識は、どんなデータベースにも当てはまります。あとは SQL Server 独自の格納構造とインデックスの特性、ロックの動作をしっかりマスターしておけば、大規模データベースでも安定稼動させることができるでしょう。
これから当社でも、データベースの基本をしっかりと身に付けたエンジニアを 500 人育成していこうとしています。また、PASSJ などのセミナーを通しても、自分の経験したノウハウを公開し、どんどん情報共有していきたいと思っています。 ―― 本日はどうもありがとうございました。熊澤さんが執行役員を務めておられる CSK Winテクノロジでは、昨年の 10 月からマイクロソフトの協力を得て、定額制の SQL Server チューニング サービスを始めています。何か困ったことがありましたら、一度相談してみてはいかがでしょうか。 キーマン熊澤さんがお勧めするデータベース チューニング関連サイトCSK Winテクノロジの提供する「定額 SQL Server パフォーマンス チューニング サービス」はこちら ＠IT の連載記事「Dr. K's SQL Server チューニング研修」はこちら インタビューア松本美穂さんお勧めのデータベース チューニング関連サイトMVP インタビュー : パフォーマンスと拡張性が向上した SQL Server 2005 はこちら PASSJ Joint コラム 「クエリのパフォーマンスチューニング方法を理解する」 NEC 鈴木さんの記事 SQL Server 2000 チューニング全工程
| 
